Aller au contenu principal
Dernière mise à jour le

Surveiller avec pg_stat_statements

pg_stat_statements est une extension Postgres qui fournit une vue statistique détaillée de l'exécution des instructions SQL au sein de votre base de données Lakebase Postgres. Il suit des informations telles que les nombres d'exécutions, les temps d'exécution totaux et moyens, et plus encore, vous aidant à analyser et à optimiser les performances des queries SQL.

Quand utiliser pg_stat_statements​

Utilisez pg_stat_statements lorsque vous avez besoin :

  • Statistiques d'exécution de query détaillées et métriques de performance
  • Identification des query lentes ou fréquemment exécutées
  • Analyse des performances des queries et insights d'optimisation
  • Analyse de la charge de travail de la base de données et planification de la capacité
  • Intégration avec des outils de monitoring personnalisés et des tableaux de bord

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 :

    SQL
    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

  3. L’extension commence à collecter les 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 compute Lakebase est suspendu ou redémarré. Par exemple, si votre compute diminue en raison d'une inactivité, toutes les statistiques existantes sont perdues. De nouvelles statistiques sont collectées une fois votre compute redémarré.

Ce comportement signifie que :

  • Les statistiques sont Reset après les redémarrages ou les suspensions du compute.
  • L'analyse des performances de longue durée nécessite une disponibilité constante du compute.
  • Vous souhaiterez peut-être exporter des statistiques importantes avant une maintenance planifiée ou des redémarrages.
remarque

Pensez à exécuter régulièrement vos monitoring queries et à stocker les résultats en externe si vous avez besoin de données de performance historiques sur les événements du cycle de vie du compute.

En savoir plus : extensions Postgres

Statistiques d'exécution de la query​

Après avoir activé l'extension, vous pouvez interroger les statistiques d'exécution à l'aide de la vue pg_stat_statements. Cette vue contient une ligne par query de base de données distincte, affichant diverses statistiques :

SQL
SELECT * FROM pg_stat_statements LIMIT 10;

La vue contient des détails tels que :

ID utilisateur

dbid

ID de query

Saisir une requête

appels

16 391

16 384

-9047282044438606287

SELECT * FROM users;

10

ID utilisateur

dbid

ID de query

Saisir une requête

appels

16 391

16 384

-9047282044438606287

SELECT * FROM users;

10

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

Requêtes de monitoring clés​

Utilisez ces queries pour analyser les performances de votre base de données :

Rechercher les queries les plus lentes​

Cette query identifie les query ayant le temps d'exécution moyen le plus élevé, ce qui peut indiquer des query inefficaces qui nécessitent une optimisation :

SQL
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;

Trouvez les query les plus fréquemment exécutées​

Les requêtes les plus fréquemment exécutées sont souvent des chemins critiques et des candidats à l'optimisation. Cette requête inclut des taux de succès du cache pour aider à identifier les requêtes qui pourraient bénéficier d'une meilleure indexation :

SQL
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;

Trouver les query avec les E/S les plus élevées​

Cette query identifie les query qui effectuent le plus d'Opérations d'E/S sur disque, ce qui peut avoir un impact sur les performances globales de la base de données :

SQL
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;

Trouver les requêtes les plus chronophages​

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

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

Recherchez les requêtes qui renvoient de nombreuses lignes​

Cette query identifie les queries qui renvoient de grands ensembles de résultats, qui pourraient bénéficier de la pagination ou du filtrage :

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

Reset statistics​

Pour Reset les statistiques collectées par pg_stat_statements:

remarque

Seuls databricks_superuser rôles ont le privilège requis pour exécuter cette fonction. Le rôle default créé avec un projet Lakebase se voit accorder l'adhésion au rôle databricks_superuser. Les rôles créés dans l’application Lakebase peuvent être attribués à cette adhésion de manière facultative.

SQL
SELECT pg_stat_statements_reset();

Cette fonction efface toutes les données statistiques accumulées, telles que les temps d’exécution et les comptes pour les instructions SQL, et commence à collecter de nouvelles données. C'est particulièrement utile lorsque vous souhaitez start fresh avec la collecte de statistiques de performance.

Ressources​

En savoir plus : documentation PostgreSQL

Sur cette page