Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
# Résolution initiale des problèmes de performances courants de PostgreSQL dans
Ce guide couvre les quatre problèmes de performances les plus courants qui affectent : le gonflement des tables et des index, l'épuisement des ressources des requêtes parallèles, la pression élevée en matière de connexion et d'authentification, et le réglage automatique. Utilisez ce guide comme liste de contrôle diagnostique de première passe lorsque vous constatez une dégradation des performances, avant de commencer une enquête plus approfondie.
Chaque section décrit les symptômes que vous pouvez observer, fournit des requêtes de diagnostic pour confirmer la cause première et recommande des mesures correctives spécifiques.
**Comprendre les régressions de performance « rien n'a changé »**
Les charges de travail PostgreSQL s'exécutent souvent sans problème pendant des semaines, voire des mois, puis subissent une dégradation soudaine des performances, même si le code de l'application et les modèles de requête semblent inchangés. Cela est dû au fait que l'environnement de base de données n'est jamais vraiment statique : plusieurs facteurs invisibles évoluent au fil du temps et peuvent entraîner des modifications du plan ou une contention des ressources :
**L'accumulation de ballonnement est une modification de la charge de travail.** Le contrôle de concurrence multiversion (MVCC) de PostgreSQL conserve les anciennes versions de lignes jusqu'à ce qu'Autovacuum les récupère. Lorsque les tuples morts s'accumulent plus rapidement que l'autovacuum ne peut les traiter, les tables et les index s'agrandissent physiquement. Le planificateur de requêtes peut ensuite passer d'analyses d'index efficaces à des analyses séquentielles, car les estimations de coûts changent à mesure que la taille de la table augmente. Votre code SQL n'a pas changé, mais les données que le planificateur voit ont changé.
**Les nouvelles valeurs de paramètres constituent une modification de la charge de travail.** Une requête paramétrée qui fonctionne correctement pour une plage de valeurs peut ne pas fonctionner correctement lorsque l'application commence à utiliser une plage différente. PostgreSQL peut réutiliser un plan d'exécution générique qui ne tient pas compte de l'asymétrie des données dans la nouvelle plage, ou les statistiques du planificateur peuvent ne pas refléter avec précision la distribution de ces valeurs. Lorsque le gonflement est également présent, l'impact s'aggrave : un plan sous-optimal analyse désormais beaucoup plus de données mortes.
**Les statistiques peuvent être périmées même lorsque Autovacuum fonctionne.** Autovacuum `ANALYZE` se déclenche en fonction du nombre de lignes insérées ou mises à jour, et non en fonction de la modification significative de la distribution des données. Si votre application passe à la recherche d'une plage de valeurs ou d'une fenêtre temporelle différente, les estimations de coûts du planificateur peuvent être inexactes, même si Autovacuum a récemment été utilisé.
**La croissance globale de la base de données est une modification de la charge de travail.** À mesure que les tables augmentent au fil du temps, le volume de pages de données que les requêtes doivent scanner augmente. Les requêtes qui fonctionnent bien sur des tables plus petites peuvent développer une latence à mesure que la taille de la table augmente, même si la logique de requête et les index restent inchangés. Surveillez pour suivre les tendances de croissance du stockage.
Lorsque vous étudiez des régressions de performances où « rien n'a changé », considérez l'accumulation de surcharges, les nouvelles plages de valeurs de paramètres, la croissance globale de la base de données et les statistiques périmées comme les causes premières les plus probables. Suivez les étapes de diagnostic décrites dans ce guide pour vérifier quel facteur s'applique.
Pour plus d’informations, consultez les ressources suivantes :
[Activités de maintenance des bases de données PostgreSQL dans Amazon RDS et Amazon AWS Aurora](https://docs.aws.amazon.com/prescriptive-guidance/latest/postgresql-maintenance-rds-aurora/introduction.html) (directives prescriptives)
[Optimisation des performances AWS des requêtes PostgreSQL](https://docs.aws.amazon.com/prescriptive-guidance/latest/postgresql-query-tuning/introduction.html) (directives prescriptives)
[Réglage des paramètres PostgreSQL dans Amazon RDS et Amazon Aurora](https://docs.aws.amazon.com/prescriptive-guidance/latest/tuning-postgresql-parameters/introduction.html)
[Mesures au niveau de l'instance Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-metrics.html#rds-cw-metrics-instance) (surveillez les tendances de croissance `FreeStorageSpace` du stockage)
## Liste de contrôle pour un diagnostic rapide
Suivez les étapes de triage ordonnées suivantes lorsque vous étudiez un problème de performance pour la première fois :
1. **Vérifiez`pg_stat_activity`.** Examinez le nombre de connexions, les sessions d'inactivité pendant les transactions et les requêtes de longue durée. Pour plus d'informations, voir .
1. **Vérifiez qu'il n'y a pas de ballonnement.** Recherchez une valeur élevée `n_dead_tup` `pg_stat_user_tables` et envisagez de l'utiliser `pgstattuple` pour une mesure précise. Pour plus d'informations, consultez [des tables avec pg\_repack](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pg_repack.html).
1. **Vérifiez`pg_stat_user_tables`.** Recherchez les `n_dead_tup` valeurs élevées et les `last_autovacuum` horodatages périmés. Pour plus d'informations, consultez [Utilisation de l'aspirateur automatique PostgreSQL sur Amazon RDS Utilisation de l'aspirateur automatique PostgreSQL sur](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html) .
1. **Révision `EXPLAIN ANALYZE` des requêtes lentes.** Recherchez des plans parallèles et des scans séquentiels sur de grandes tables. Pour plus d'informations, voir Meilleures [pratiques pour les requêtes parallèles dans RDS pour PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.ParallelQueries.html).
1. **Indicateurs CloudWatch de vérification et de Performance Insights.** Vérifiez l'utilisation du processeur, le nombre de connexions, le nombre d'IOPS et la mémoire disponible. Pour plus d'informations, consultez la section [Surveillance d'Amazon RDS.](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MonitoringOverview.html) Pour les événements d'attente courants et les mesures correctives, voir Événements d'attente .
1. **Passez en revue votre groupe de paramètres de base de données.** Réglages `max_parallel_workers_per_gather` de vérification et d'aspiration automatique. Pour plus d'informations, consultez la section [Réglage des paramètres PostgreSQL dans Amazon RDS et Amazon Aurora](https://docs.aws.amazon.com/prescriptive-guidance/latest/tuning-postgresql-parameters/introduction.html).
## Expansion des tables et des index
Le gonflement des tables et des index se produit lorsque des tuples morts s'accumulent dans vos tables plus rapidement que l'aspirateur automatique ne peut les récupérer. Au fil du temps, cela entraîne une dégradation progressive des performances des requêtes, une augmentation de l'utilisation du stockage et des plans de requêtes sous-optimaux.
### Symptômes
+ Dégradation progressive des performances des requêtes au fil des semaines ou des mois
+ L'utilisation du stockage augmente malgré un volume de données stable
+ Le planificateur de requêtes choisit des analyses séquentielles plutôt que des analyses d'index en raison de statistiques périmées
+ Statistiques élevées `dead_tuple_count` dans les tableaux
### Diagnostic
Vous pouvez estimer le gonflement de toutes les tables en interrogeant le catalogue du système. Cette approche ne nécessite aucune extension :
```
SELECT schemaname, relname,
n_dead_tup,
n_live_tup,
ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;
```
Pour remédier à la surcharge, vous pouvez utiliser l'`pg_repack`extension pour réorganiser les tables et les index avec un minimum de verrouillage. Pour plus d'informations, consultez [Suppression de la surcharge sur les tables avec pg\_repack Suppression de la surcharge sur les tables avec pg\_repack](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pg_repack.html) .
**Important**
Plutôt que de vous fier à une maintenance manuelle, assurez-vous que l'aspirateur automatique est activé et correctement adapté à votre charge de travail. Consultez [Réglage automatique de l'aspirateur](#PostgreSQL.InitialTroubleshooting.Autovacuum) les recommandations de réglage.
## Épuisement des ressources de requêtes parallèles
PostgreSQL peut exécuter des requêtes en parallèle afin d'améliorer les performances lors d'analyses et d'agrégations séquentielles de grande envergure. Cependant, chaque parallel worker est un processus backend complet qui compte `max_worker_processes` (et définit la sous-limite`max_parallel_workers`) et alloue le sien. `work_mem` Une seule requête avec 4 travailleurs parallèles peut consommer des centaines de mégaoctets de mémoire et une quantité importante de CPU. En cas de forte simultanéité, un parallélisme excessif peut épuiser rapidement le processeur et la mémoire.
Les symptômes courants incluent des pics soudains du processeur, une utilisation élevée de la mémoire par requête et une `DatabaseConnections` augmentation du volume CloudWatch sans modification de l'application. Vous pouvez également observer des événements d'attente tels que `IPC:BgWorkerStartup``IPC:ExecuteGather`, et`IPC:ParallelFinish`. Pour plus d'informations sur ces événements d'attente, voir [d'attente](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rpg-ipc-parallel.html).
Pour la plupart des charges de travail de production OLTP et à haute simultanéité, désactivez le parallélisme automatique en le définissant `max_parallel_workers_per_gather = 0` dans votre groupe de paramètres de base de données. Vous pouvez ensuite activer le parallélisme de manière sélective pour des sessions d'analyse ou de reporting spécifiques en définissant le paramètre par session ou par rôle.
Pour obtenir des conseils détaillés sur le diagnostic et le contrôle du comportement des requêtes parallèles, voir [Meilleures pratiques pour les requêtes parallèles dans RDS pour PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.ParallelQueries.html).
## Pression de connexion et d'authentification élevée
La perte de connexion (ouverture et fermeture fréquentes de connexions à la base de données sans regroupement) entraîne une surcharge d'authentification et peut épuiser les emplacements de connexion disponibles. Les connexions inactives qui restent ouvertes consomment également des emplacements sans effectuer de travail utile.
### Symptômes
+ Haut niveau `total_auth_attempts` de surveillance des Performances Insights. Pour plus d'informations, consultez la section , compteurs pour RDS pour [Non-native PostgreSQL.](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights_Counters.html#USER_PerfInsights_Counters.PostgreSQL.NonNative)
+ Délais d'établissement de la connexion lents
+ `FATAL: too many connections for role`ou des `remaining connection slots are reserved` erreurs
+ Les pics du processeur sont corrélés au taux de désabonnement des connexions
### Diagnostic
Exécutez la requête suivante pour vérifier l'état actuel de votre connexion :
```
SELECT
setting::int AS max_connections,
(SELECT count(*) FROM pg_stat_activity) AS current_connections,
(SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS idle_connections,
(SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_settings
WHERE name = 'max_connections';
```
Un nombre élevé de `idle in transaction` connexions `idle` ou par rapport à `max_connections` indique que les connexions ne sont pas libérées correctement. Idle-in-transaction les connexions sont particulièrement problématiques car elles bloquent les verrous et empêchent l'aspirateur automatique de récupérer les tuples morts.
### Correction
+ **Déployez le regroupement de connexions.** Utilisez PgBouncer ou Amazon RDS Proxy pour réduire le nombre de connexions directes à votre base de données. Le regroupement de connexions réutilise les connexions existantes plutôt que d'en créer de nouvelles pour chaque demande.
+ **Set`idle_in_transaction_session_timeout`.** Ce paramètre met automatiquement fin aux sessions qui restent inactives dans une transaction au-delà de la durée spécifiée. Cela empêche les transactions inactives de longue durée de bloquer les verrous et de bloquer l'aspiration automatique.
+ **Passez en revue la gestion des connexions aux applications.** Assurez-vous que votre application ferme rapidement les connexions et ne maintient pas les transactions ouvertes plus longtemps que nécessaire.
**Note**
Les serveurs de requêtes parallèles consomment du processeur et de la mémoire. Si vous constatez un épuisement des ressources associé à une activité de requête parallèle, consultez [Épuisement des ressources de requêtes parallèles](#PostgreSQL.InitialTroubleshooting.ParallelQuery) pour obtenir des conseils sur le contrôle de l'utilisation des travailleurs parallèles.
## Utilisation des événements d'attente de Performance Insights pour le dépannage
Performance Insights capture les événements d'attente qui indiquent les domaines dans lesquels votre base de données passe du temps. Lorsque vous étudiez les problèmes de performances, les événements d'attente vous aident à déterminer si le goulot d'étranglement est dû au processeur, au verrouillage I/O, au réseau ou à la communication entre processus. Les catégories d'événements d'attente les plus courants qui apparaissent lors des problèmes décrits dans ce guide sont les suivantes :
+ **CPU** — La session est active sur le processeur ou attend le processeur. Les temps d'attente élevés du processeur sont souvent liés à un parallélisme excessif ou à des plans de requêtes inefficaces qui analysent des tables surchargées.
+ **IPC (communication inter-processus)** — Des événements d'attente tels que `IPC:BgWorkerStartup``IPC:ExecuteGather`, et indiquent une surcharge de coordination des requêtes `IPC:ParallelFinish` parallèles.
+ **IO** — Des événements d'attente `IO:DataFileRead` indiquent par exemple que les requêtes lisent des données depuis le stockage parce que les pages requises ne se trouvent pas dans la mémoire partagée. Cela est courant lorsque les tables surchargées dépassent le cache tampon.
+ **Verrouiller** : attendez des événements tels que `Lock:transactionid` et `Lock:tuple` indiquent des conflits entre les sessions. Idle-in-transaction les connexions peuvent contenir des verrous qui bloquent les autres requêtes et aspirent automatiquement.
+ **Client** : événements d'attente tels que `Client:ClientRead` ceux indiquant que la base de données attend que l'application envoie des données. Les périodes d'attente élevées des clients peuvent indiquer une perte de connexion ou une latence du réseau.
Pour une référence complète des événements d'attente qui indiquent généralement des problèmes de performances et les mesures correctives recommandées, consultez la section Événements d' [RDS pour](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.concepts.summary.html) PostgreSQL.
## Réglage automatique de l'aspirateur
Autovacuum est le processus d'arrière-plan qui permet de récupérer les tuples morts, d'empêcher le gonflement des tables et des index, de mettre à jour les statistiques du planificateur et de protéger contre le contournement des identifiants de transaction. Les paramètres d'autovacuum par défaut sont prudents et conçus pour les petites bases de données. High-write les charges de travail de production doivent presque toujours être ajustées.
Lorsque Autovacuum ne parvient pas à suivre le rythme de votre charge d'écriture, la charge de travail s'accumule, les statistiques du planificateur deviennent obsolètes et le risque d'encapsulation des identifiants de transaction augmente. Si elle `age(relfrozenxid)` approche les 2 milliards, la base de données s'arrête pour empêcher la corruption des données.
## Informations connexes
+ [Meilleures pratiques pour les requêtes parallèles dans RDS pour PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.ParallelQueries.html)
+ [Gestion des connexions mortes dans PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.DeadConnectionHandling.html)
+ [Utilisation de PostgreSQL Autovacuum sur Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html)
+ [Tâches DBA courantes pour RDS pour PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html)
+ [Utilisation de PostgreSQL Autovacuum sur Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html)
+ [Présentation d'autovacuum dans les environnements Amazon RDS pour PostgreSQL](https://aws.amazon.com/blogs/database/understanding-autovacuum-in-amazon-rds-for-postgresql-environments/)