Comment identifier et résoudre les problèmes de performance et les requêtes qui tournent lentement dans mon instance RDS for PostgreSQL ou Aurora PostgreSQL ?

Solution

Les performances de votre instance Amazon RDS pour PostgreSQL peuvent être affectées pour plusieurs raisons, notamment :

• Matériel sous-dimensionné
• Changements dans la charge de travail
• Trafic accru
• Problèmes de mémoire
• Plans de requêtes sous-optimaux

Identifiez la cause

Utilisez une combinaison de ces outils pour identifier la cause de la lenteur des requêtes :

• Métriques Amazon CloudWatch
• Métriques Enhanced Monitoring
• Métriques Performance Insights
• Statistiques de base de données
• Outils de base de données natifs

Métriques CloudWatch

Pour identifier les goulots d'étranglement des performances causés par des ressources insuffisantes, surveillez ces mesures CloudWatch courantes disponibles pour votre instance Amazon RDS DB.

• CPUUtilization : pourcentage de la capacité de traitement informatique utilisée
• FreeableMemory : RAM disponible sur l'instance DB (en mégaoctets)
• SwapUsage - Espace d'échange utilisé par l'instance DB (en mégaoctets)

Un pourcentage plus élevé d'utilisation du processeur indique généralement une charge de travail active sur l'instance et le besoin de ressources CPU supplémentaires. Une utilisation plus élevée de la mémoire et une consommation d'espace d'échange indiquent des échanges fréquents en raison de la faible disponibilité de la mémoire pour la charge de travail. Cela peut signifier que votre instance n'est pas en mesure de suivre la charge de travail. Une utilisation élevée des ressources du processeur et de la mémoire est généralement due à des requêtes de longue durée, à une augmentation soudaine du trafic ou à un grand nombre de connexions inactives.

Exécutez cette commande pour afficher les requêtes actives avec runtime :

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

Exécutez cette commande pour connaître le nombre de connexions inactives présentes dans la base de données :

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

Exécutez ensuite cette commande pour mettre fin aux connexions inactives qui consomment des ressources.

Remarque : veillez à remplacer example-pid par le PID de la connexion inactive que vous avez obtenu de pg_stat_activity :

SELECT pg_terminate_backend(example-pid);

Vérifiez que le débit réseau souhaité est atteint sur l'instance RDS en vérifiant les métriques suivantes :

• NetworkReceiveThroughput
• NetworkTransmitThroughput

Ces mesures indiquent le trafic réseau entrant et sortant en octets/seconde. Les classes d'instance sous-dimensionnées ou non optimisées pour Amazon Elastic Block Service (Amazon EBS) peuvent affecter le débit du réseau, ce qui entraîne des instances lentes. Un faible débit réseau peut entraîner des réponses lentes pour toutes les demandes d'applications, quelles que soient les performances de la base de données.

Évaluez les performances d'E/S en vérifiant les métriques suivantes :

• ReadIOPS and WriteIOPS : nombre moyen d'opérations de lecture ou d'écriture sur disque par seconde
• ReadLatency and WriteLatency : durée moyenne d'une opération de lecture ou d'écriture en millisecondes
• ReadThroughput and WriteThroughput : nombre moyen de mégaoctets lus ou écrits sur le disque par seconde
• DiskQueueDepth : nombre d'opérations d'E/S en attente d'écriture ou de lecture sur le disque

Pour plus d’information, consultez la page comment résoudre les problèmes de latence des volumes Amazon EBS causés par un goulot d'étranglement IOPS dans mon instance Amazon RDS ?

Métriques Enhanced Monitoring

Grâce à la surveillance améliorée, vous pouvez consulter les métriques au niveau du système d'exploitation et la liste des 100 principaux processus qui consomment beaucoup d'UC et de mémoire. Vous pouvez activer la surveillance améliorée au niveau par seconde pour identifier les problèmes de performance intermittents sur votre instance RDS.

Vous pouvez évaluer les métriques disponibles du système d'exploitation pour diagnostiquer les problèmes de performances qui peuvent être liés au processeur, à la charge de travail, aux E/S, à la mémoire et au réseau.

Dans la liste des processus, identifiez le processus dont les valeurs sont élevées pour CPU% ou Mem%. Ensuite, recherchez la connexion associée dans la base de données.

Par exemple :

NOM	VIRT	RES	CPU%	MEM%	VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322]	250,66 MO	27,7 MO	85,93	2,21	illimité

Connectez-vous à la base de données, puis exécutez cette requête pour rechercher la connexion et les informations relatives à la requête :

SELECT * FROM pg_stat_activity WHERE pid = 10322;

Métriques Performance Insights

Performance Insights vous permet d'évaluer les charges de travail de base de données divisées en fonction des attentes, du SQL, de l'hôte ou des utilisateurs. Vous pouvez également capturer la base de données et les métriques de niveau SQL.

L'onglet des meilleurs SQL du tableau de bord Performance Insights affiche les instructions SQL qui contribuent le plus à la charge de la base de données. Une charge de base de données ou une charge par attente (AAS) supérieure à la valeur Max vCPU indique une charge de travail limitée sur la classe d'instance.

La latence moyenne par appel dans les statistiques SQL fournit la durée d'exécution moyenne d'une requête. Il est courant de voir un SQL différent comme étant le principal contributeur de charge de base de données que celui ayant le temps d'exécution moyen le plus élevé. En effet, la liste des meilleurs SQL est basée sur la durée totale d'exécution.

Statistiques de base de données

Les statistiques suivantes peuvent vous aider à évaluer les performances des bases de données dans PostgreSQL :

• Statistiques de distribution des données
• Statistiques étendues
• Statistiques de surveillance

Pour savoir comment lire et comprendre ces statistiques, consultez la section Comprendre les statistiques dans PostgreSQL.

Outils de base de données natifs

Pour identifier les requêtes lentes, utilisez l'outil natif pgBadger. Pour plus d'informations, consultez Optimisation et réglage des requêtes dans Amazon RDS pour PostgreSQL en fonction d'outils natifs et externes.

Optimiser les performances

Paramètres de la mémoire de syntonisation

Le serveur de base de données PostgreSQL alloue une certaine zone de mémoire pendant toute sa durée de vie pour la mise en cache des données afin d'améliorer les accès en lecture et en écriture. Cette zone de mémoire est appelée tampons partagés. La quantité de mémoire utilisée par la base de données pour les tampons de mémoire partagée est contrôlée par les paramètres shared_buffers.

Hormis la zone de mémoire partagée, chaque processus principal consomme de la mémoire pour effectuer des opérations au sein d'un serveur de base de données. La quantité de mémoire utilisée dépend des valeurs définies pour les paramètres work_mem et maintenance_work_mem. Pour plus d'informations, consultez la documentation PostgreSQL pour la configuration du serveur.

Si vous constatez en permanence une pression de mémoire élevée sur l'instance de base de données, envisagez de réduire les valeurs de ces paramètres. Vous pouvez réduire les valeurs de ces paramètres dans le groupe de paramètres personnalisés qui est attaché à votre instance de base de données.

Gestion du plan de requêtes Aurora PostgreSQL

Utilisez la gestion des plans de requêtes de l'édition compatible avec Amazon Aurora PostgreSQL pour contrôler comment et quand les plans d'exécution des requêtes changent. Pour plus d'informations, consultez la section Meilleures pratiques pour la gestion des plans de requêtes compatibles avec Aurora PostgreSQL.

Résolution des problèmes liés aux requêtes à exécution lente

Vous rencontrez généralement des requêtes à exécution lente en cas de problèmes d'infrastructure ou lorsque la consommation globale des ressources est élevée. Les requêtes à exécution lente peuvent également être le résultat d'une planification des requêtes sous-optimale par le planificateur de requêtes. Le planificateur de requêtes PostgreSQL utilise les statistiques créées pour la table afin de créer des plans de requête. Ces plans peuvent être affectés en raison de modifications de schéma et de statistiques obsolètes. La surcharge de la table et des index peut également entraîner un ralentissement des requêtes.

Le démon autovacuum est chargé de créer des processus de travail autovacuum qui suppriment les lignes mortes des tables chaque fois que l’on atteint le seuil de lignes mortes. Le démon autovaccum est également responsable de l'exécution de l'opération ANALYZE qui actualise les statistiques stockées pour une table particulière.

Exécutez la requête suivante pour trouver des informations sur :

• Tuples morts
• Nombre d'opérations d'aspiration automatique ou d'aspiration
• Nombre de cycles d'analyse automatique ou d'analyse
• Date de la dernière exécution de ces opérations

SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,
autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count 
FROM pg_stat_user_tables
ORDER BY 5 DESC;

Vous pouvez utiliser la vue pg_stat_activity pour rechercher des données relatives aux activités en cours. Cette vue fournit le pid du backend, la requête, et d'autres détails. Pour rechercher des requêtes de longue durée, exécutez cette requête :

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU' 
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

Notez que les requêtes en attente de verrouillage peuvent être lentes. Par conséquent, vérifiez si la requête attend des verrouillages en exécutant cette requête :

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,
CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view’s locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

Votre instance RDS for PostgreSQL vous permet de créer l'extension pg_stat_statements à partir de la base de données :

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

À l'aide de pg_stat_statements, vous pouvez afficher les statistiques des requêtes. Assurez-vous d'ajouter l'entrée pg_stat_statements à shared_preload_libraries avant de créer l'extension.

Remarque : vous pouvez modifier les paramètres de ce module uniquement lorsqu'un groupe de paramètres personnalisé est joint à votre instance de base de données.

Utilisez ces requêtes pour identifier les principales requêtes SQL qui affectent les performances de votre instance.

Pour rechercher les requêtes qui passent plus de temps dans la base de données, exécutez cette requête pour les versions 12 et antérieures de PostgreSQL :

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

Exécutez cette requête pour les versions 13 et ultérieures de PostgreSQL :

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

Pour trouver les requêtes dont le taux de réussite du cache tampon est le plus faible, exécutez cette requête pour les versions 12 et antérieures de PostgreSQL :

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

Exécutez cette requête pour les versions 13 et ultérieures de PostgreSQL :

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +
shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY 5 ASC
LIMIT 10;

Vous pouvez également capturer des requêtes ou des plans de requête de longue durée dans les journaux d'erreurs de base de données en définissant de manière appropriée le paramètre log_min_duration_statement pour votre instance et à l’aide du module auto_explain. Le paramètre log_min_duration_statement entraîne l'enregistrement de la durée de chaque instruction terminée si l'instruction a été exécutée pendant au moins la durée spécifiée. Par exemple, si vous définissez ce paramètre sur 250 ms, toutes les instructions SQL qui s'exécutent pendant 250 ms ou plus sont journalisées. Le module auto_explain vous permet de capturer le plan d'explication des requêtes exécutées dans la base de données.

Vous pouvez également capturer le plan en utilisant les commandes explain (expliquer) etexplain analyze (expliquer analyser). Identifiez les opportunités de réglage des requêtes en fonction du module auto_explain ou des commandes explain pour la requête. Pour plus d'informations, consultez la documentation PostgreSQL relative à Utiliser EXPLAIN.

Si votre système est bien réglé et que vous êtes toujours confronté à une limitation des ressources, pensez à augmenter la taille de la classe d'instance. Augmentez la classe d'instance pour allouer davantage de ressources de calcul et de mémoire à votre instance de base de données. Pour plus d'informations, consultez Spécifications matérielles pour les classes d'instance de base de données.

---

Informations connexes

Comment puis-je résoudre les problèmes liés à l'utilisation élevée du processeur pour Amazon RDS ou Amazon Aurora PostgreSQL-Compatible Edition ?

Utilisation des paramètres de votre instance de base de données RDS for PostgreSQL

Pourquoi mon instance de base de données Amazon RDS utilise la mémoire d'échange alors que je dispose d'assez de mémoire ?