Superviser avec pg_stat_statements

Important

La mise à l’échelle automatique Lakebase est en version bêta dans les régions suivantes : eastus2, westeurope, westus.

La version Autoscaling de Lakebase est la dernière de Lakebase, offrant l'autoscaling, la mise à l'échelle à zéro, la bifurcation et la restauration instantanée. Pour la comparaison des fonctionnalités avec Lakebase Provisioned, consultez le choix entre les versions.

pg_stat_statements est une extension Postgres qui fournit une vue statistique détaillée de l’exécution d’instructions SQL dans votre base de données Postgres Lakebase. Il effectue le suivi des informations telles que le nombre d’exécutions, le nombre total et le temps d’exécution moyen, et bien plus encore, ce qui vous permet d’analyser et d’optimiser les performances des requêtes SQL.

Quand utiliser pg_stat_statements

Utilisez pg_stat_statements quand vous avez besoin des éléments suivants :

• Statistiques détaillées sur l’exécution des requêtes et métriques de performances
• Identification des requêtes lentes ou fréquemment exécutées
• Analyse des performances des requêtes et insights d’optimisation
• Analyse de la charge de travail de base de données et planification de la capacité
• Intégration à des outils et tableaux de bord de surveillance personnalisés

Activer pg_stat_statements

L’extension pg_stat_statements est disponible dans Lakebase Postgres. Pour l’activer :

1. Connectez-vous à votre base de données à l’aide de l’éditeur SQL ou d’un client Postgres.

2. Exécutez la commande SQL suivante pour créer l’extension :

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   

3. L’extension commence à collecter des statistiques immédiatement après la création.

Persistance des données

Les statistiques collectées par l’extension pg_stat_statements sont stockées en mémoire et ne sont pas conservées lorsque votre calcul Lakebase est suspendu ou redémarré. Par exemple, si votre calcul diminue en raison de l’inactivité, toutes les statistiques existantes sont perdues. De nouvelles statistiques sont collectées une fois vos calculs redémarrés.

Ce comportement signifie que :

• Réinitialisation des statistiques après redémarrages ou suspensions de calcul
• L’analyse des performances de longue durée nécessite une disponibilité de calcul cohérente
• Vous pouvez exporter des statistiques importantes avant la maintenance planifiée ou les redémarrages

Note

Envisagez d’exécuter régulièrement vos requêtes de surveillance et de stocker des résultats en externe si vous avez besoin de données de performances historiques sur les événements de cycle de vie du calcul.

En savoir plus : extensions Postgres

Statistiques d’exécution des requêtes

Après avoir activé l’extension, vous pouvez interroger des statistiques d’exécution à l’aide de la pg_stat_statements vue. Cette vue contient une ligne par requête de base de données distincte, affichant différentes statistiques :

SELECT * FROM pg_stat_statements LIMIT 10;

La vue contient des détails tels que :

identifiant utilisateur	dbid	queryid	query	calls
16391	16384	-9047282044438606287	SELECT * Utilisateurs FROM ;	10

Pour obtenir la liste complète des colonnes et descriptions, consultez la documentation PostgreSQL.

Requêtes de surveillance principales

Utilisez ces requêtes pour analyser les performances de votre base de données :

Rechercher les requêtes les plus lentes

Cette requête identifie les requêtes avec le temps d’exécution moyen le plus élevé, ce qui peut indiquer des requêtes inefficaces nécessitant une optimisation :

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    (total_exec_time / calls) AS avg_time_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Rechercher les requêtes les plus fréquemment exécutées

Les requêtes exécutées le plus fréquemment sont souvent des chemins critiques et des candidates à l’optimisation. Cette requête inclut des ratios d’accès au cache pour aider à identifier les requêtes susceptibles de bénéficier d’une meilleure indexation :

SELECT
    query,
    calls,
    total_exec_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

Rechercher des requêtes avec les E/S les plus élevées

Cette requête identifie les requêtes qui effectuent la plupart des opérations d’E/S sur disque, ce qui peut avoir un impact sur les performances globales de la base de données :

SELECT
    query,
    calls,
    shared_blks_read + shared_blks_written AS total_io,
    shared_blks_read,
    shared_blks_written
FROM pg_stat_statements
ORDER BY (shared_blks_read + shared_blks_written) DESC
LIMIT 20;

Rechercher les requêtes les plus fastidieuses

Cette requête identifie les requêtes qui consomment le temps d’exécution le plus total sur toutes les exécutions :

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Rechercher des requêtes qui retournent de nombreuses lignes

Cette requête identifie les requêtes qui retournent des jeux de résultats volumineux, qui peuvent tirer parti de la pagination ou du filtrage :

SELECT
    query,
    calls,
    rows,
    (rows / calls) AS avg_rows_per_call
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

Réinitialiser les statistiques

Pour réinitialiser les statistiques collectées par pg_stat_statements:

Note

Seuls les rôles databricks_superuser ont le privilège nécessaire pour exécuter cette fonction. Le rôle par défaut associé à un projet Lakebase et les rôles créés dans l'application Lakebase se voient attribuer l'appartenance au rôle databricks_superuser.

SELECT pg_stat_statements_reset();

Cette fonction efface toutes les données statistiques accumulées, telles que les temps d’exécution et le nombre d’instructions SQL, et commence à collecter de nouvelles données. Il est particulièrement utile lorsque vous souhaitez commencer à démarrer avec la collecte des statistiques de performances.

Ressources

En savoir plus : documentation PostgreSQL