Comment puis-je résoudre un problème d’utilisation élevée du processeur pour Amazon RDS ou Amazon Aurora PostgreSQL ?

Brève description

Pour déterminer la cause d'une utilisation élevée du processeur, utilisez les outils suivants :

• Métriques d’Amazon CloudWatch
• Métriques de surveillance améliorée
• Métriques de Performance Insights
• Vues et catalogues PostgreSQL natifs, tels que pg_stat_statements, pg_stat_activity et pg_stat_user_tables
• Connexions inactives dans la base de données
• Commande ANALYZE sur le site Web de PostgreSQL
• Paramètres de journalisation PostgreSQL pour consigner les requêtes de longue durée, l’autovacuum, les délais de verrouillage et les demandes de connexion et de déconnexion

Résolution

Examiner les métriques CloudWatch

Utilisez les métriques CloudWatch pour identifier les tendances du processeur sur de longues périodes. Comparez les graphiques WriteIOPs, ReadIOPs, ReadThroughput et WriteThroughput à l’utilisation du processeur pour déterminer les moments où la charge de travail sollicite fortement le processeur.

Une fois cette période identifiée, examinez les données de surveillance améliorée qui sont associées à votre instance de base de données. Vous pouvez définir la surveillance améliorée à des intervalles de collecte de 1, 5, 10, 15, 30 ou 60 secondes afin de recueillir les données à un niveau plus granulaire.

Utiliser la surveillance améliorée

La surveillance améliorée fournit une vue au niveau du système d’exploitation. Par exemple, vous pouvez consulter la charge de travail moyenne, la distribution du processeur (System% ou Nice%) et la liste des processus du système d’exploitation. Pour plus d'informations, consultez la section Surveillance du système d'exploitation.

Vous pouvez vérifier les données loadAverageMinute à des intervalles de 1, 5 et 15 minutes. Lorsque la charge moyenne est supérieure au nombre de processeurs virtuels, l'instance est confrontée à une charge importante. Si la charge moyenne est inférieure au nombre de processeurs virtuels pour la classe d’instance de base de données, il est possible que la limitation du processeur ne soit pas à l’origine de la latence de l’application. Lorsque vous recherchez la cause d'une utilisation élevée du processeur, vérifiez la charge moyenne pour éviter les faux positifs.

Par exemple, supposons que vous ayez une instance de base de données qui utilise une classe d’instance db.m5.2xlarge de 3000 IOPS provisionnées qui atteint le quota du processeur. Huit processeurs virtuels sont associés à la classe d'instance. Si la même moyenne de charge dépasse 170, cela signifie que la machine est soumise à une charge importante pendant la période mesurée.

Charge moyenne en minutes :

• Quinze : 170,25
• Cinq : 391,31
• Un : 596,74

Utilisation du processeur :

• Utilisateur (%) : 0,71
• Système (%) : 4,9
• Nice (%) : 93,92
• Total (%) : 99,97

Remarque : Dans le cadre de la surveillance améliorée, Nice% correspond à la quantité de processeur utilisée par votre charge de travail par rapport à la base de données.

Une fois que la surveillance améliorée est activée, vous pouvez également consulter la liste des processus du système d’exploitation associée à l’instance de base de données. La surveillance améliorée vous permet d'identifier un maximum de 100 processus qui affectent les performances. Vous pouvez utiliser les résultats de la surveillance améliorée avec les résultats de pg_stat_activity pour vous aider à identifier l’utilisation des ressources par les requêtes.

Utiliser Performance Insights

Utilisez l’analyse des performances d’Amazon RDS pour identifier la requête responsable de la charge de la base de données. Vérifiez l'onglet SQL qui correspond à une période spécifique.

Consulter les vues et les catalogues natifs de PostgreSQL

Au niveau du moteur de base de données, vous pouvez utiliser pg_stat_activity et pg_stat_statements. Si le problème se produit en temps réel, utilisez pg_stat_activity ou pg_stat_statements pour regrouper les machines, les clients et les adresses IP qui envoient le plus de trafic.

Utilisez les données pour détecter des augmentations au fil du temps ou des augmentations liées aux serveurs d’applications. Vous pouvez également vérifier si un serveur d’applications présente des sessions bloquées ou des problèmes de verrouillage. Pour en savoir plus, consultez les pages pg_stat_activity et pg_stat_statements sur le site Web de PostgreSQL.

Pour activer pg_stat_statements, procédez comme suit :

1. Modifiez le groupe de paramètres de base de données personnalisé existant.

2. Ajoutez pg_stat_statements à shared_preload_libraries.

3. Définissez track_activity_query_size sur 4096.

4. Définissez pg_stat_statements.track sur ALL.

5. Définissez pg_stat_statements.max sur 10000.

6. Choisissez Appliquer immédiatement, puis redémarrez l’instance de base de données.

7. Sur la base de données que vous souhaitez surveiller, exécutez la commande suivante :

   demo=> select current_database();current_database------------------  
   demo  
   (1 row)  
   
   demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

   Remarque : La commande précédente installe l’extension dans la base de données de démonstration.

Après avoir configuré pg_stat_statements, utilisez l’une des méthodes suivantes pour surveiller la sortie. Vous pouvez consulter la requête qui passe le plus de temps dans la base de données, dont le taux d'accès au cache de mémoire tampon est le plus faible ou qui est exécutée individuellement lors de chaque exécution.

Pour voir quelles requêtes passent le plus de temps dans la base de données, exécutez la requête suivante pour votre version de PostgreSQL.

PostgreSQL versions 12 et antérieures :

SELECT total_time, queryFROM pg_stat_statements  
ORDER BY total_time DESC LIMIT 10;

PostgreSQL versions 13 et ultérieures :

SELECT total_plan_time+total_exec_time as total_time, queryFROM pg_stat_statements  
ORDER BY 1 DESC LIMIT 10;

Pour répertorier les requêtes présentant un taux d'accès au cache de mémoire tampon inférieur, exécutez la requête suivante pour votre version de PostgreSQL.

PostgreSQL versions 12 et antérieures :

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

PostgreSQL versions 13 et ultérieures :

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_percentFROM pg_stat_statements   
ORDER BY 3 DESC LIMIT 10;

Pour répertorier les requêtes par exécution afin de les échantillonner au fil du temps pour votre version PostgreSQL, exécutez la requête suivante :

PostgreSQL versions 12 et antérieures :

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_writtenFROM pg_stat_statements  
WHERE calls != 0  
ORDER BY total_time DESC LIMIT 10;

PostgreSQL versions 13 et ultérieures :

SELECT query,calls,  
(total_plan_time+total_exec_time as total_time)/calls as avg_time_ms,   
 rows/calls as avg_rows,  
temp_blks_read/calls as avg_tmp_read,  
 temp_blks_written/calls as avg_temp_written  
FROM pg_stat_statements  
WHERE calls != 0  
ORDER BY 3 DESC LIMIT 10;

Rechercher des connexions inactives dans la base de données

Les connexions inactives dans la base de données peuvent utiliser des ressources de calcul, telles que la mémoire et le processeur. Si le processeur est fortement sollicité par votre instance, vérifiez que la base de données ne contient pas de connexions inactives. Pour en savoir plus, consultez la section Impact des connexions PostgreSQL inactives sur les performances.

Vous pouvez utiliser la surveillance améliorée pour consulter la liste des processus du système d’exploitation pour détecter les connexions inactives. Cependant, la liste ne montre qu'un maximum de 100 processus. Pour rechercher des connexions inactives au niveau de la base de données, exécutez les requêtes suivantes.

Consulter les sessions en cours qui sont inactives et actives :

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activityWHERE query != '<IDLE>  
'AND query NOT ILIKE '%pg_stat_activity%'  
AND usename!='rdsadmin'  
ORDER BY query_start desc;  
SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query  
FROM pg_stat_activity  
WHERE not pid=pg_backend_pid()  
AND query NOT ILIKE '%pg_stat_activity%'  
AND usename!='rdsadmin';

Obtenez le nombre de connexions pour chaque nom d'utilisateur et d'application :

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;    application_name    | count   
------------------------+-------  
 psql                   |     1  
 PostgreSQL JDBC Driver |     1   
                        |     5  
(3 rows)  
postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;  
 usename  | count  
----------+-------  
 master   |     4   
 user1    |     1  
 rdsadmin |     2  
(3 rows)

Une fois les connexions inactives identifiées, exécutez l’une des requêtes suivantes pour mettre fin à ces connexions :

psql=> SELECT pg_terminate_backend(pid)   FROM pg_stat_activity  
   WHERE usename = 'example-username'  
   AND pid <> pg_backend_pid()  
   AND state in ('idle');

-ou-

SELECT pg_terminate_backend (example-pid);

Si votre application génère un trop grand nombre de connexions, modifiez-la afin d’éviter que la mémoire et les ressources du processeur ne gèrent ces connexions. Vous pouvez soit limiter le nombre de connexions, soit utiliser une solution de regroupement de connexions telle que PgBouncer. Vous pouvez également utiliser le proxy Amazon RDS pour configurer des groupes de connexions.

Exécuter la commande ANALYZE

La commande ANALYZE collecte des statistiques sur le contenu des tables de la base de données et stocke les résultats dans le catalogue de système pg_statistic. Ensuite, le planificateur de requêtes utilise ces statistiques pour déterminer les plans d’exécution les plus efficaces pour les requêtes. Lorsque vous n'exécutez pas fréquemment la commande ANALYZE sur les tables de votre base de données, les requêtes peuvent utiliser davantage de ressources de calcul, en raison de statistiques obsolètes dans le système.

Les statistiques obsolètes apparaissent pour les raisons suivantes :

• La fonction autovacuum n’est pas fréquemment exécutée.
• Vous n'avez pas exécuté l'opération ANALYZE après la mise à niveau majeure de la version.

Autovacuum détecte la présence de tables surchargées dans la base de données et récupère l’espace à des fins de réutilisation. Pour s'assurer que les statistiques des tables sont régulièrement mises à jour, le démon autovacuum exécute la commande ANALYZE lorsque le seuil défini pour les tuples est dépassé.

Pour en savoir plus, consultez les ressources suivantes :

• Comprendre la fonction autovacuum dans les environnements Amazon RDS for PostgreSQL
• Étude de cas : le réglage de la fonction autovacuum dans Amazon RDS for PostgreSQL

Pour savoir quand les fonctions autovacuum et autoanalyze ont été exécutées pour la dernière fois sur les tables, exécutez la requête suivante :

SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;

Pour éviter les problèmes de performance après une mise à niveau majeure de la version du moteur, exécutez la commande ANALYZE pour actualiser la table pg_statistic. Exécutez la commande ANALYZE pour chaque base de données de votre instance de base de données RDS for PostgreSQL.

Pour éviter tous problèmes de performances dus à une utilisation accrue des ressources, vous devez régénérer toutes les statistiques. Pour générer des statistiques pour toutes les tables standard de la base de données actuelle après une mise à niveau vers une version majeure, exécutez la commande suivante sans paramètres :

ANALYZE VERBOSE

Vérifier les journaux d'erreurs de PostgreSQL

Utilisez Amazon RDS pour activer la journalisation des requêtes pour PostgreSQL. Consultez ensuite les journaux d’erreurs de PostgreSQL pour vérifier que vous avez défini vos paramètres log_min_duration_statement et log_statement sur des valeurs appropriées. Pour en savoir plus, consultez la page Création de rapports et journalisation des erreurs sur le site Web de PostgreSQL.

Réduire l'utilisation du processeur

Après avoir identifié les requêtes à l’origine d’une utilisation élevée du processeur, appliquez les méthodes suivantes pour réduire davantage l’utilisation du processeur :

• Utilisez EXPLAIN et EXPLAIN ANALYZE pour identifier les moyens d'ajuster les plans de requête. Pour en savoir plus, consultez la page Utilisation de EXPLAIN sur le site Web de PostgreSQL.
• Si une requête est exécutée de manière répétée, utilisez des instructions préparées pour réduire la pression sur votre processeur. Les instructions préparées qui s'exécutent de manière répétée mettront le plan de requête en cache. Lorsque le plan est déjà en cache pour d'autres exécutions, il reste moins de temps pour planifier la requête.

Informations connexes

Bonnes pratiques d’utilisation de PostgreSQL