AnalyticDB for PostgreSQL provides a slow query diagnostics feature - AnalyticDB

Prérequis

Avant de commencer, vérifiez que :

Votre instance exécute AnalyticDB for PostgreSQL V7.0 en mode de stockage élastique avec une version mineure du moteur V7.0.5.0 ou ultérieure. Pour vérifier ou mettre à jour la version mineure du moteur, consultez Afficher la version mineure du moteur et Mettre à jour la version mineure du moteur.
La fonctionnalité de diagnostic des requêtes lentes est activée. Consultez la section Activer ou désactiver le diagnostic des requêtes lentes.

Remarques d'utilisation

Les journaux des requêtes lentes conservent les données des 7 derniers jours et n'enregistrent pas les requêtes ayant échoué.
Les instructions SQL dépassant 1 024 octets sont tronquées dans le journal.
Par défaut, toutes les instructions SQL dont la durée d'exécution est supérieure ou égale à 1 seconde, ainsi que toutes les instructions DDL, sont enregistrées. Ce seuil est contrôlé par le paramètre de configuration unifiée (GUC) slow_query_min_duration. Pour garantir la stabilité du système, nous vous recommandons de ne pas modifier la valeur par défaut de ce paramètre.

Activer ou désactiver le diagnostic des requêtes lentes

Connectez-vous à la base de données postgres et exécutez les commandes suivantes.

Pour vérifier l'état actuel :

SHOW adbpg_feature_enable_query_monitor;

Pour activer la fonctionnalité pour une base de données spécifique :

-- Replace <database_name> with the name of your business database.
ALTER DATABASE <database_name> SET adbpg_feature_enable_query_monitor TO ON;

Pour désactiver la fonctionnalité, définissez la valeur sur OFF.

Interroger les journaux des requêtes lentes

Tous les exemples ci-dessous s'exécutent sur qmonitor.instance_slow_queries (niveau instance) ou qmonitor.host_slow_queries (niveau nœud). Connectez-vous à la base de données postgres avant de les exécuter.

Pour une description complète de tous les champs des deux vues, consultez la section Afficher les champs .

Par plage de temps

Interrogez toutes les instructions lentes exécutées au cours des 30 dernières minutes :

SELECT
    query_start       AS "Start time",
    query_end         AS "End time",
    query_duration_ms AS "Duration (ms)",
    query_id          AS "Query ID",
    query             AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min';

Interrogez toutes les instructions lentes exécutées à une date spécifique (par exemple, le 26 février 2024) :

SELECT
    query_start       AS "Start time",
    query_end         AS "End time",
    query_duration_ms AS "Duration (ms)",
    query_id          AS "Query ID",
    query             AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= '2024-02-26 00:00:00'
  AND query_end   <= '2024-02-27 00:00:00';

Par consommation de ressources (niveau instance)

Le champ query_duration_ms correspond à la somme de quatre sous-champs :

Sous-champ	Ce qu'il mesure
`optimizer_duration_ms`	Temps nécessaire pour générer le plan d'exécution
`lock_wait_time_ms`	Temps d'attente des verrous
`queue_wait_time_ms`	Temps d'attente d'une file d'attente de ressources
`executor_duration_ms`	Temps d'exécution de la requête sur le moteur d'exécution

Lorsqu'une requête est lente, vérifiez quel sous-champ domine afin d'identifier le goulot d'étranglement. Par exemple, une valeur élevée pour queue_wait_time_ms indique une contention de la file d'attente de ressources plutôt qu'un problème d'exécution.

Top 20 par temps CPU au cours des 30 dernières minutes :

SELECT
    (cpu_time_ms / 1000)::text || ' s' AS "CPU time",
    query_start                         AS "Start time",
    query_end                           AS "End time",
    query_duration_ms                   AS "Duration (ms)",
    query_id                            AS "Query ID",
    query                               AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min'
ORDER BY cpu_time_ms DESC
LIMIT 20;

Top 20 par utilisation de la mémoire au cours des 30 dernières minutes :

SELECT
    pg_size_pretty(mem_bytes) AS "Memory consumed",
    query_start               AS "Start time",
    query_end                 AS "End time",
    query_duration_ms         AS "Duration (ms)",
    query_id                  AS "Query ID",
    query                     AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min'
ORDER BY mem_bytes DESC
LIMIT 20;

mem_bytes enregistre l'utilisation cumulative de la mémoire maximale sur tous les nœuds. Cette valeur approxime l'utilisation réelle de la mémoire et n'est pas exacte, car la mémoire fluctue pendant l'exécution de la requête.

Top 10 par temps CPU dans une fenêtre temporelle spécifique (par exemple, de 00:00 à 12:00 le 26 février 2024) :

SELECT
    (cpu_time_ms / 1000)::text || ' s' AS "CPU time",
    query_start                         AS "Start time",
    query_end                           AS "End time",
    query_duration_ms                   AS "Duration (ms)",
    query_id                            AS "Query ID",
    query                               AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= '2024-02-26 00:00:00'
  AND query_end   <= '2024-02-26 12:00:00'
ORDER BY cpu_time_ms DESC
LIMIT 10;

Par consommation de ressources (niveau nœud)

Utilisez qmonitor.host_slow_queries pour diagnostiquer les requêtes lentes sur un nœud spécifique.

Top 20 par temps CPU sur un nœud au cours des 30 dernières minutes :

SELECT
    (host_cpu_time_ms / 1000)::text || ' s' AS "CPU time",
    query_start                              AS "Start time",
    query_end                                AS "End time",
    query_duration_ms                        AS "Duration (ms)",
    query_id                                 AS "Query ID",
    query                                    AS "SQL statement"
FROM qmonitor.host_slow_queries
WHERE hostname    = '<node_hostname>'
  AND query_start >= now() - interval '30 min'
ORDER BY host_cpu_time_ms DESC
LIMIT 20;

Top 20 par utilisation de la mémoire sur un nœud au cours des 30 dernières minutes :

SELECT
    pg_size_pretty(host_mem_bytes) AS "Memory consumed",
    query_start                    AS "Start time",
    query_end                      AS "End time",
    query_duration_ms              AS "Duration (ms)",
    query_id                       AS "Query ID",
    query                          AS "SQL statement"
FROM qmonitor.host_slow_queries
WHERE hostname    = '<node_hostname>'
  AND query_start >= now() - interval '30 min'
ORDER BY host_mem_bytes DESC
LIMIT 20;

Remplacez <node_hostname> par le nom d'hôte réel du nœud à diagnostiquer.

Par utilisateur

Comptez les requêtes lentes par utilisateur au cours des 10 dernières minutes pour identifier les comptes qui génèrent le plus de requêtes lentes :

SELECT
    user_name AS "User",
    COUNT(1)  AS "Slow query count"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '10 min'
GROUP BY user_name
ORDER BY COUNT(1) DESC;

Par ID de requête

Récupérez les détails complets d'une requête lente spécifique à l'aide de son ID :

SELECT * FROM qmonitor.instance_slow_queries
WHERE query_id = '<query_id>';

Exporter les journaux des requêtes lentes

Utilisez des instructions SELECT pour exporter les données de qmonitor.instance_slow_queries ou de qmonitor.host_slow_queries vers une table interne, un compartiment Object Storage Service (OSS) ou une table MaxCompute. Pour connaître les méthodes d'exportation, consultez Data Lake Analysis.

Dans AnalyticDB for PostgreSQL, des index sont créés pour les journaux des requêtes lentes et ces journaux sont partitionnés sur query_start. Pour de meilleures performances, incluez toujours query_start dans la clause WHERE afin d'éviter les analyses complètes de table.

Par exemple, pour exporter les journaux de 13:00 à 16:00 le 26 février 2024 :

-- Add this condition to your SELECT statement.
WHERE query_start >= '2024-02-26 13:00:00'
  AND query_start <= '2024-02-26 16:00:00'

Configurer les seuils

Seuls les superutilisateurs peuvent modifier ces éléments de configuration.

`slow_query_min_duration`

Contrôle le temps d'exécution minimal pour qu'une requête soit enregistrée comme requête lente. La valeur par défaut est 1s. Vous pouvez également modifier cet élément de configuration pour collecter les requêtes lentes dont la durée est inférieure à 1 seconde.

Si le temps d'exécution est supérieur ou égal à cette valeur, l'instruction SQL, le temps d'exécution et les métriques associées sont enregistrés.
Définissez la valeur sur -1 pour désactiver complètement la collecte des requêtes lentes.

Pour modifier le seuil pour une base de données spécifique (nécessite un superutilisateur) :

-- Collect slow queries that take 5 seconds or longer.
ALTER DATABASE '<database_name>' SET slow_query_min_duration = '5s';

Pour modifier le seuil uniquement pour la session actuelle (tout utilisateur peut exécuter cette commande) :

SET slow_query_min_duration = '5s';

`slow_query_plan_min_duration`

Contrôle le temps d'exécution minimal pour que le plan d'exécution d'une requête lente soit collecté. La valeur par défaut est 10s.

Si le temps d'exécution est supérieur ou égal à cette valeur, le plan d'exécution est collecté avec les métriques de la requête.
Pour inspecter un plan ponctuel, utilisez EXPLAIN ; il n'est pas nécessaire de collecter automatiquement les plans.
Définissez la valeur sur -1 pour désactiver complètement la collecte des plans d'exécution.

Pour modifier le seuil pour une base de données spécifique (nécessite un superutilisateur) :

ALTER DATABASE '<database_name>' SET slow_query_plan_min_duration = '10s';

Pour modifier le seuil uniquement pour la session actuelle :

SET slow_query_plan_min_duration = '10s';

Afficher les champs

`qmonitor.instance_slow_queries` (niveau instance)

Champ	Type	Description
`query_id`	text	ID unique de la requête.
`session_id`	integer	ID de la session de requête.
`db_name`	character varying(128)	Nom de la base de données interrogée.
`user_name`	character varying(128)	Nom de l'utilisateur qui a initié la requête.
`application_name`	character varying(128)	Application qui a initié la requête.
`client_hostname`	character varying(128)	Nom d'hôte du client.
`client_addr`	character varying(128)	Adresse IP du client.
`client_port`	character varying(32)	Port du client.
`rsg_name`	character varying(128)	Groupe de ressources associé à la table accédée par la requête.
`query_start`	timestamptz	Heure de début de la requête.
`query_end`	timestamptz	Heure de fin de la requête.
`query_duration_ms`	bigint	Temps total de la requête en millisecondes. Égal à `optimizer_duration_ms` + `lock_wait_time_ms` + `queue_wait_time_ms` + `executor_duration_ms`.
`optimizer_duration_ms`	bigint	Temps nécessaire pour générer le plan d'exécution, en millisecondes. Les instructions SQL complexes prennent plus de temps.
`lock_wait_time_ms`	bigint	Temps d'attente des verrous, en millisecondes.
`queue_wait_time_ms`	bigint	Temps d'attente d'une file d'attente de ressources, en millisecondes.
`executor_duration_ms`	bigint	Temps d'exécution de la requête sur le moteur d'exécution, en millisecondes.
`query`	text	Texte de l'instruction SQL.
`is_plpgsql`	boolean	Indique si la requête est une procédure stockée PL/pgSQL.
`query_optimizer`	character varying(16)	Optimiseur utilisé. Valeurs valides : `ORCA`, `Planner`.
`access_tables`	text	Nom de la table accédée par la requête.
`result_rows`	bigint	Nombre de lignes renvoyées. Pour les instructions `INSERT`, nombre de lignes insérées.
`num_segments`	integer	Nombre de nœuds de calcul où la requête a été exécutée.
`num_slices`	integer	Nombre de tranches dans le plan d'exécution de la requête.
`cpu_time_ms`	numeric	Temps CPU total en millisecondes, y compris le nœud coordinateur et tous les nœuds de calcul.
`mem_bytes`	numeric	Utilisation cumulative de la mémoire maximale par nœud. Approxime l'utilisation réelle de la mémoire ; il ne s'agit pas d'une valeur exacte.
`spill_bytes`	numeric	Valeur cumulative des nombres maximaux de fichiers enregistrés sur les disques des nœuds de calcul où la requête s'exécute. Cette valeur reflète approximativement l'espace disque consommé par la requête, mais n'est pas une valeur exacte.

`qmonitor.host_slow_queries` (niveau nœud)

Champ	Type	Description
`hostname`	character varying(128)	Nom d'hôte du nœud.
`hostrole`	text	Rôle du nœud. Valeurs valides : `master` (nœud coordinateur), `segment` (nœud de calcul).
`query_id`	text	ID unique de la requête.
`db_name`	character varying(128)	Nom de la base de données interrogée.
`user_name`	character varying(128)	Nom de l'utilisateur qui a initié la requête.
`query_start`	timestamptz	Heure de début de la requête.
`query_end`	timestamptz	Heure de fin de la requête.
`query`	text	Texte de l'instruction SQL.
`query_duration_ms`	bigint	Temps d'exécution de la requête, en millisecondes.
`optimizer_duration_ms`	bigint	Temps nécessaire pour générer le plan d'exécution, en millisecondes.
`host_cpu_time_ms`	numeric	Temps CPU de la requête sur ce nœud, en millisecondes.
`host_mem_bytes`	numeric	Utilisation maximale de la mémoire de la requête sur ce nœud.
`host_spill_bytes`	numeric	Données maximales cumulatives déversées sur le disque sur ce nœud.