View a markdown version of this page

Résolution initiale des problèmes de performances courants de PostgreSQL dans - Amazon Relational Database Service
Liste de contrôle pour un diagnostic rapideExpansion des tables et des indexÉpuisement des ressources de requêtes parallèlesPression de connexion et d'authentification élevéeUtilisation des événements d'attente de Performance Insights pour le dépannageRéglage automatique de l'aspirateurInformations connexes

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 :

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érifiezpg_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 .

  2. 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.

  3. Vérifiezpg_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 .

  4. 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.

  5. 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. Pour les événements d'attente courants et les mesures correctives, voir Événements d'attente .

  6. 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.

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_repackextension 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 .

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 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-limitemax_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:BgWorkerStartupIPC:ExecuteGather, etIPC:ParallelFinish. Pour plus d'informations sur ces événements d'attente, voir d'attente.

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.

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.

  • Délais d'établissement de la connexion lents

  • FATAL: too many connections for roleou 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.

  • Setidle_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 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:BgWorkerStartupIPC: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 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