View a markdown version of this page

Résolution initiale des problèmes de performances courants de PostgreSQL dans RDS pour PostgreSQL - Amazon Relational Database Service

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 RDS pour PostgreSQL

Ce guide couvre les quatre problèmes de performances les plus courants qui affectent RDS pour PostgreSQL bases de données : gonflement des tables et des index, épuisement des ressources de requêtes parallèles, pression de connexion et d'authentification élevée et réglage de l'autovacuum. Utilisez ce guide comme liste de diagnostic de première étape 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 pourriez observer, fournit des requêtes de diagnostic pour confirmer la cause première et recommande des mesures de correction 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, avant de subir une dégradation soudaine des performances même si le code de l'application et les modèles de requêtes 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 changements de plan ou des conflits de ressources :

  • L'accumulation de ballonnement est une modification de la charge de travail. Le contrôle de simultanéité multiversion (MVCC) de PostgreSQL conserve les anciennes versions des 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 affichées par le planificateur le sont.

  • Les nouvelles valeurs de paramètres correspondent à 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 l'autovacuum fonctionne. Déclencheurs d'aspiration automatique ANALYZE sur la base du nombre de lignes insérées ou mises à jour, et non sur la question de savoir si la distribution des données a changé de manière significative. Si votre application passe à l'interrogation 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 été exécuté récemment.

  • La croissance globale de la base de données est une modification de la charge de travail. Au fur et à mesure que les tables s'agrandissent, le volume de pages de données que les requêtes doivent analyser augmente. Les requêtes qui ont donné de bons résultats 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. Moniteur FreeStorageSpace 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 ballonnements, les nouvelles plages de valeurs de paramètres, la croissance globale de la base de données et les statistiques obsolètes comme les causes profondes 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 performances pour la première fois :

  1. Vérifiez pg_stat_activity. Examinez le nombre de connexions, les sessions de transaction inactives et les requêtes de longue durée. Pour plus d'informations, voir Concepts essentiels pour le réglage de RDS pour PostgreSQL.

  2. Vérifiez s'il n'y a pas de ballonnement. Recherchez le haut n_dead_tup dans pg_stat_user_tables et pensez à utiliser pgstattuple pour une mesure précise. Pour plus d'informations, voir Supprimer le gonflement des tables avec pg_repack.

  3. Vérifiez pg_stat_user_tables. Recherchez le haut n_dead_tup valeurs et périmé last_autovacuum horodatages. Pour plus d'informations, voir Utilisation de PostgreSQL Autovacuum sur Amazon RDS.

  4. Révision EXPLAIN ANALYZE sur les 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. Vérifiez CloudWatch et des indicateurs de performance. Passez en revue l'utilisation du processeur, le nombre de connexions, les IOPS et la mémoire libérable. Pour plus d'informations, voir Surveillance d'Amazon RDS. Pour connaître les événements d'attente courants et les mesures correctives à prendre, voir Événements d'attente RDS pour PostgreSQL.

  6. Passez en revue votre groupe de paramètres de base de données. Vérifiez max_parallel_workers_per_gather et réglages de l'aspirateur automatique. Pour plus d’informations, consultez Utilisation de paramètres sur votre instance de base de données RDS pour PostgreSQL.

Gonflement des tableaux 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'autovacuum 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 obsolètes

  • Haut dead_tuple_count dans les statistiques du tableau

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 à l'engorgement, vous pouvez utiliser le pg_repack extension pour réorganiser les tables et les index avec un minimum de verrouillage. Pour plus d'informations, voir Supprimer le gonflement des tables avec pg_repack et Supprimez les blocages d'Amazon Aurora et de RDS pour PostgreSQL avec pg_repack.

Important

Plutôt que de vous fier à une maintenance manuelle, assurez-vous que l'aspirateur automatique est activé et adapté à votre charge de travail. Voir Réglage de l'aspirateur automatique pour 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 pour les analyses séquentielles et les agrégations de grande envergure. Cependant, chaque travailleur parallèle est un processus backend complet qui compte max_worker_processes (et la sous-limite max_parallel_workers) et alloue ses propres work_mem. Une seule requête avec 4 opérateurs parallèles peut consommer des centaines de mégaoctets de mémoire et un processeur important. Dans des conditions de simultanéité élevée, un parallélisme excessif peut épuiser rapidement le processeur et la mémoire.

Les symptômes courants incluent des pics de processeur soudains, une utilisation élevée de la mémoire par requête et une utilisation élevée DatabaseConnections dans 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 IPC:parallel événements d'attente.

Pour la plupart des charges de travail de production OLTP et à haute simultanéité, désactivez le parallélisme automatique en 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

L'interruption des connexions (ouverture et fermeture fréquentes de connexions à la base de données sans regroupement) entraîne une surcharge d'authentification et peut épuiser les créneaux de connexion disponibles. Les connexions inactives qui restent ouvertes consomment également des emplacements sans effectuer de travail utile.

Symptômes

  • Élevé total_auth_attempts dans la surveillance de Performance Insights. Pour plus d'informations, voir Non-native compteurs pour RDS pour PostgreSQL.

  • Délais d'établissement de connexion lents

  • FATAL: too many connections for role ou remaining connection slots are reserved erreurs

  • Les pics de processeur sont corrélés à la perte de 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 ou idle in transaction connexions relatives à 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'autovacuum de récupérer les tuples morts.

Correction

  • Déployez un pool de connexions. Utiliser PgBouncer ou Serveur proxy Amazon RDS pour réduire le nombre de connexions directes à votre base de données. Le regroupement de connexions réutilise les connexions existantes au lieu 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 lors d'une transaction au-delà de la durée spécifiée. Cela empêche les transactions inactives de longue durée de se verrouiller et de bloquer l'aspiration automatique.

  • Passez en revue la gestion des connexions de l'application. Assurez-vous que votre application ferme les connexions rapidement et ne laisse pas les transactions ouvertes plus longtemps que nécessaire.

Note

Les outils de traitement des requêtes parallèles consomment du processeur et de la mémoire. Si vous observez un épuisement des ressources parallèlement à 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 où votre base de données passe du temps. Lorsque vous étudiez des problèmes de performances, les événements d'attente vous aident à déterminer si le goulot d'étranglement provient du processeur, I/O, verrouillage, communication réseau ou interprocessus. Les catégories d'événements d'attente courantes qui apparaissent lors des numéros décrits dans ce guide sont les suivantes :

  • CPU — La session est active sur le processeur ou en attente de processeur. Les événements d'attente CPU élevés sont souvent en corrélation avec un parallélisme excessif ou des plans de requêtes inefficaces scannant des tableaux gonflés.

  • IPC (communication inter-processus) — Les événements d'attente tels que IPC:BgWorkerStartup, IPC:ExecuteGather, et IPC:ParallelFinish indiquer la surcharge de coordination des requêtes parallèles.

  • IO — Les événements d'attente tels que IO:DataFileRead indiquent que les requêtes lisent des données depuis le stockage car les pages requises ne sont pas en mémoire partagée. Cela est courant lorsque les tables gonflées dépassent le cache de la mémoire tampon.

  • Verrouiller — Les événements d'attente tels que Lock:transactionid et Lock:tuple indiquer un conflit entre les sessions. Idle-in-transaction les connexions peuvent contenir des verrous qui bloquent les autres requêtes et la mise sous vide automatique.

  • Cliente — Les événements d'attente tels que Client:ClientRead indique que la base de données attend que l'application envoie des données. Les événements d'attente client élevés 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 performance et les actions correctives recommandées, voir Événements d'attente RDS pour PostgreSQL.

Réglage de l'aspirateur automatique

Autovacuum est le processus d'arrière-plan qui récupère les tuples morts, empêche le gonflement des tables et des index, met à jour les statistiques du planificateur et protège contre le contournement des identifiants de transaction. Les paramètres d'aspiration automatique par défaut sont conservateurs et conçus pour les petites bases de données. High-write les charges de travail de production nécessitent presque toujours un réglage.

Lorsque Autovacuum ne peut pas gérer votre charge de travail d'écriture, le gonflement s'accumule, les statistiques du planificateur deviennent obsolètes et le risque de contournement des identifiants de transaction augmente. Si age(relfrozenxid) approche les 2 milliards, la base de données s'arrête pour empêcher la corruption des données.

Pour obtenir des conseils détaillés sur le réglage des paramètres de l'aspiration automatique, la surveillance de l'activité du vide et la configuration des remplacements par table, voir Utilisation de PostgreSQL Autovacuum sur Amazon RDS.

Informations connexes