Outil de réglage des performances de PostgreSQL

Bien connu pour sa fiabilité, la robustesse de ses fonctionnalités et ses hautes performances, PostgreSQL est un système open source de gestion de bases de données hautement évolutif. Vu le volume de données et d’utilisateurs que PostgreSQL peut prendre en charge, son réglage est essentiel pour qu’il soit performant. Pour optimiser efficacement les performances, le réglage des bases de données implique souvent des opérations de surveillance afin d’accroître la visibilité sur les tendances des requêtes.

Le réglage de PostgreSQL avec un outil adapté facilite les initiatives de collecte d’informations supplémentaires grâce aux fonctionnalités suivantes :

• Surveillance : le réglage consiste dans un premier temps à collecter des informations via des opérations de surveillance pour vous permettre d’identifier plus facilement les ressources affectées. Ces informations concernent notamment le processeur, les E/S, la mémoire, le réseau et d’autres facteurs clés.
• Dépannage et diagnostics : examinez la configuration et l’authentification, ainsi que les journaux de ressources et WAL (write-ahead log), pour mieux comprendre l’origine des problèmes. 
• Réglage des requêtes : identifiez les requêtes lentes et réécrivez-les en appliquant les pratiques conseillées.
• Établissement de rapports et consignation : les rapports et les journaux vous permettent d’identifier plus facilement la source d’un problème et le moment auquel il s’est produit pour pouvoir procéder à un réglage efficace.

Lors du réglage de PostgreSQL, voici les causes fréquentes des problèmes de performance, ainsi que les éventuelles opportunités d’amélioration considérable des performances des bases de données que présente le réglage :

• Vérifiez vos index : observez le nombre d’index que vous avez définis dans votre table et déterminez si les avantages qu’ils présentent pour les requêtes l’emportent sur le temps système lié au stockage et aux instructions INSERT. Puisque chaque système est différent, c’est vous qui déterminez le nombre d’index.
• Évitez d’utiliser des contraintes de clé UNIQUE et de clé étrangère : si une contrainte UNIQUE est définie, l’instruction INSERT devra éventuellement consulter un index pour déterminer si la ligne existe déjà, ce qui risque de ralentir votre instruction INSERT. De la même façon, en présence d’une contrainte de clé étrangère, chaque instruction INSERT devra effectuer des opérations de lecture dans votre table référencée, au risque de diminuer les performances. Envisagez plutôt de dénormaliser vos données.
• Repensez l’utilisation que vous faites de vos disques : les données stockées dans le journal WAL risquent de ralentir les disques et donc les performances des instructions INSERT. En outre, pour éviter les goulots d’étranglement, vous pouvez améliorer le débit en utilisant un disque séparé (espace disque logique) pour le journal WAL et les données.

En utilisant un outil de réglage des requêtes Postgres, vous pouvez plus facilement déterminer si une table ou des index ne respectent pas les pratiques conseillées pour PostgreSQL. SolarWinds Database Performance Analyzer (DPA) intègre un conseiller de réglage des tables qui répertorie les infractions et les étapes suivantes recommandées. Parmi les pratiques conseillées de SolarWinds DPA en matière de réglage de tables que vous pouvez envisager, on compte les suivantes :

• Déterminer si une clé étrangère est indexée. En indexant chaque clé étrangère, vous pouvez améliorer les performances des requêtes joignant les deux tables.
• Identifier les index qui se chevauchent et, en les examinant, déterminer s’il est possible d’en supprimer.
• Supprimer les dernières colonnes. Puisque les index larges (au moins cinq colonnes) nécessitent davantage de stockage, ils risquent d’augmenter le coût de maintenance des index. 
• Ajouter des index définis à la table. Toutefois, si la table est très petite, un index risque de ne pas être plus efficace qu’une analyse intégrale de la table.
• Vérifier que la table comporte une clé primaire. Une telle clé permet d’identifier un enregistrement de manière unique et elle est essentielle pour garantir l’intégrité des données. Si aucune colonne ou combinaison de colonnes ne fournit de valeur unique, vous pouvez ajouter une clé primaire artificielle, représentant par exemple une colonne ID.

Le réglage des bases de données facilite l’optimisation des environnements de bases de données pour permettre à vos applications d’accéder rapidement aux données. Le réglage de bases de données affecte également votre capacité à maintenir les performances des bases de données et des applications, l’intégrité des données et les contrôles.

Puisque PostgreSQL est une base de données open source, il est parfois difficile d’optimiser les performances et cette opération peut être chronophage. Lorsque vous développez des applications et des requêtes de bases de données PostgreSQL, vous devez vous assurer que le processus d’optimisation ne sera ni compliqué, ni chronophage.

La fonction de réglage des bases de données PostgreSQL et les outils associés peuvent vous fournir les informations dont vous avez besoin pour optimiser le réglage et l’efficacité des implémentations.

Avec un outil de réglage de PostgreSQL, les administrateurs de bases de données peuvent observer les tendances des performances des requêtes, afficher les modifications apportées au code et aux requêtes, et mieux comprendre les impacts à partir des bases de données de test et de production, grâce à des fonctionnalités puissantes conçues pour identifier rapidement les problèmes.

Les outils de réglage de PostgreSQL fournissent des vues « avant et après » des éléments clés des bases de données pour vous permettre de comparer les performances, de comprendre les tendances et d’identifier des problèmes comme les suivants :

• Indicateurs sur l’intégrité des bases de données et du système, notamment au niveau du système d’exploitation, et indicateurs ciblant particulièrement les systèmes de gestion de bases de données
• Instantanés actuels et données historiques
• Informations sur les modifications apportées au code et aux requêtes
• Informations sur les impacts dans les bases de données de test et de production
• Alertes lorsqu’un système tombe en panne ou lors du dépassement de plusieurs seuils de latence, de valeurs de comptage ou d’utilisation des ressources

En utilisant un outil de réglage des performances de PostgreSQL avec un analyseur de requête comme SolarWinds Database Performance Analyzer (DPA), vous pourrez afficher plus facilement les indicateurs critiques de Postgres via l’analyse multidimensionnelle des requêtes, afin d’identifier les problèmes de performance.

SolarWinds Database Performance Analyzer (DPA) est une solution essentielle pour régler efficacement les requêtes PostgreSQL et fournir des indicateurs complets permettant de comprendre l’utilisation de ressources, comme le processeur, le disque, la mémoire et bien plus. Ces indicateurs, liés notamment à la mémoire, au disque, au réseau, à la session, à l’attente, à la ligne, à l’exécution de la commande VACUUM, au point de contrôle, à la réplication et à l’expulsion de données du cache rendent l’environnement plus clair pendant les ralentissements et facilitent l’identification de la cause première. Vous pouvez accéder à ces indicateurs dans DPA de manière intuitive en sélectionnant une instance de base de données, en accédant à l’onglet des ressources pour afficher des tableaux proposant des plages temporelles réglables, puis en survolant des points de données pour consulter des annotations ou des informations détaillées sur des événements VMware. Outre les indicateurs, le conseiller DPA spécialisé dans le réglage des tables identifie les tables affectées par les requêtes inefficaces et fournit des informations sur les performances des requêtes et les index. Ces informations agrégées favorisent la prise de décisions informées pour optimiser les performances de PostgreSQL.

Le conseiller spécialisé dans le réglage des tables (Table Tuning Advisor) est un outil conçu pour optimiser la performance des bases de données en analysant et en corrigeant les requêtes inefficaces. Ces requêtes lisent souvent un volume important de données, mais renvoient peu de résultats, ce qui entraîne des E/S superflues et sollicite trop de ressources. Le conseiller facilite le réglage de ces requêtes en communiquant des informations justifiant ou non l’ajustement de requêtes, d’index ou de plans.

Il fournit une vue agrégée de la charge de travail d’une table permettant d’identifier les requêtes SQL coûteuses et de planifier des étapes d’optimisation d’éléments spécifiques. Le conseiller analyse les tendances d’utilisation des tables, la conception des index et l’activité transactionnelle pour influencer des décisions stratégiques.

Pour accéder au conseiller, vous pouvez utiliser la combinaison de touches super-tabulation du « réglage »ou l’activer dans la page Query Performance Analysis (Analyse des performances des requêtes). Il comporte trois sections principales : Inefficient SQL (Requêtes SQL inefficaces), SQL and Plan Details (Détails du plan et des requêtes SQL) et Table and Index Information (Informations sur les tables et les index).

Le conseiller met en évidence les étapes inefficaces du plan, comme les analyses complètes des tables, en suggérant l’amélioration de processus particuliers. Il affiche également toutes les requêtes à destination d’une table en les hiérarchisant en fonction de leur impact sur la charge de travail.

L’outil tient également compte de l’état actuel des index de tables en suggérant d’ajouter éventuellement de nouveaux index ou d’en modifier, en fonction du volume d’activité des données et d’autres métadonnées, pour s’assurer que tous les changements effectués optimisent les performances sans générer de nouveaux problèmes.

Lorsque vous exécutez PostgreSQL sur une plateforme de Cloud computing, vous devez impérativement optimiser et régler les performances puisque vous payez pour bénéficier de ressources de calcul et que des requêtes inefficaces et mal rédigées peuvent être coûteuses et affecter les performances des applications frontales.

DPA propose une approche globale de gestion des performances de PostgreSQL qui comporte les éléments suivants :

• Vues en temps réel et historiques
• Collecte de données à la seconde près, 24/7
• Implémentation simple et sans agent avec moins de 1 % de frais généraux
• Rapports ad-hoc ou planifiés propres à Postgres
• Détection des anomalies alimentée par l’apprentissage automatique
• Analyse multidimensionnelle des temps d’attente des requêtes
• Conseillers spécialisés dans les requêtes pour identifier les problèmes
• Indicateurs sur le système et PostgreSQL
• Exploration du texte SQL et exécution de plans en temps réel
• Modèle d’alerte par e-mail personnalisé avec la fonction glisser-déplacer
• API RESTful pour l’intégration de l’automatisation

Vous pouvez installer DPA sur un serveur physique ou une machine virtuelle, dans Azure, ou en tant que service dans AWS.