10 erreurs courantes de PostgreSQL et comment les éviter

Beaucoup de choses peuvent mal tourner avec une installation de PostgreSQL. Pire encore, de nombreux problèmes peuvent passer inaperçus au fur et à mesure que le problème s’accumule sur une période de temps, puis frapper soudainement avec un impact majeur qui le place au premier plan de l’attention de tous. Qu’il s’agisse d’une baisse flagrante des performances ou d’une augmentation spectaculaire de la consommation des ressources et des coûts de facturation, il est important d’identifier ces problèmes le plus tôt possible ou, mieux encore, de les éviter en configurant votre implémentation en fonction de la charge de travail souhaitée.

S’appuyant sur l’expérience de Percona dans l’aide d’innombrables boutiques PostgreSQL au fil des ans, nous avons compilé une liste des erreurs les plus courantes. Même si vous pensez avoir correctement configuré votre installation PostgreSQL, vous pouvez toujours trouver cette liste utile pour valider votre configuration.

Erreur #1 : exécuter la configuration par défaut

PostgreSQL fonctionne dès la sortie de la boîte, mais il n’est pas très bien configuré pour vos besoins. La configuration par défaut est très basique et n’est pas adaptée à une charge de travail spécifique. Cette configuration excessivement conservatrice permet à PostgreSQL d’exécuter n’importe quel environnement, dans l’espoir que les utilisateurs le configureront selon leurs besoins.

La pgtune L’outil offre un sous-ensemble de configurations basées sur les ressources matérielles et le type de charge de travail. C’est un bon point de départ pour configurer votre cluster PostgreSQL en fonction des besoins de votre charge de travail. De plus, vous devrez peut-être configurer les variables de rétention autovacuum, log, checkpoint et WAL (write-ahead log).

Il est vraiment important que votre serveur soit configuré de manière optimale pour tout besoin futur immédiat afin d’éviter tout redémarrage inutile. Jetez donc un œil à tous les GUC avec le contexte “postmaster” dans la vue du catalogue pg_settings.

SELECT name, setting, boot_val
FROM   pg_settings
WHERE  context="postmaster";

Ceci est particulièrement critique lors de la configuration d’un cluster haute disponibilité (HA), car tout temps d’arrêt du serveur principal dégradera le cluster et entraînera la promotion d’un serveur de secours au rôle de serveur principal.

Erreur #2 : conception et architecture de base de données non optimisées

Ce point ne peut pas être assez souligné. J’ai personnellement vu des organisations payer plus de cinq fois le coût dont elles avaient besoin, simplement à cause d’une conception et d’une architecture de base de données non optimisées.

L’un des meilleurs conseils ici est de regarder ce dont votre charge de travail a besoin maintenant et dans un avenir proche, plutôt que ce qui pourrait être nécessaire dans six mois à un an. Regarder trop loin signifie que vos tables sont conçues pour des besoins futurs qui ne seront peut-être jamais réalisés. Et ce n’est qu’un aspect.

Parallèlement à cela, la dépendance excessive à l’égard du mappage objet-relationnel (ORM) est également une cause majeure de mauvaises performances. Les ORM sont utilisés pour connecter des applications à des bases de données à l’aide de langages de programmation orientés objet, et ils devraient simplifier la vie de vos développeurs au fil du temps. Cependant, il est essentiel que vous compreniez ce qu’un ORM fournit et quel type d’impact sur les performances il introduit. Sous le capot, un ORM peut exécuter plusieurs requêtes, qu’il s’agisse de combiner plusieurs relations, d’effectuer des agrégations ou même de diviser les données de requête. Dans l’ensemble, vous bénéficierez d’une latence plus élevée et d’un débit plus faible sur vos transactions lorsque vous utilisez un ORM.

Au-delà des ORM, améliorer l’architecture de votre base de données consiste à structurer les données afin que vos opérations de lecture et d’écriture soient optimales pour les index comme pour les relations. Une approche qui peut aider consiste à dénormaliser la base de données, car cela réduit la complexité des requêtes SQL et les jointures associées afin que vous puissiez extraire des données à partir de moins de relations.

Au final, les performances sont pilotées par un processus simple en trois étapes de « définition, mesure et optimisation » dans votre environnement pour votre application et votre charge de travail.

Erreur #3 : Ne pas régler la base de données pour la charge de travail

Le réglage d’une charge de travail nécessite des informations sur la quantité de données que vous avez l’intention de stocker, la nature de l’application et le type de requêtes à exécuter. Vous pouvez toujours ajuster et comparer votre configuration jusqu’à ce que vous soyez satisfait de la consommation de ressources sous une charge importante.

Par exemple, l’intégralité de votre base de données peut-elle tenir dans la RAM disponible de votre machine ? Si oui, alors vous voudriez évidemment augmenter la valeur de shared_buffers pour cela. De même, la compréhension de la charge de travail est essentielle pour configurer le point de contrôle et les processus de vide automatique. Par exemple, vous les configurerez de manière très différente pour une charge de travail d’ajout uniquement par rapport à une charge de travail mixte de traitement des transactions en ligne qui répond au benchmark de type C du Transaction Processing Performance Council.

Il existe de nombreux outils utiles qui fournissent des informations sur les performances des requêtes. Vous pourriez vérifier mon article de blog sur les performances des requêtes, qui traite de certaines des options open source disponibles, ou consultez ma présentation sur YouTube.

Chez Percona, nous avons deux outils qui vous aideront énormément à comprendre les modèles de performances des requêtes :

• PMM – Percona Monitoring and Management est un projet gratuit et entièrement open source qui fournit une interface graphique avec des statistiques système détaillées et des analyses de requêtes. N’hésitez pas à essayer le Démo PMM qui s’adresse à MySQL, MongoDB et PostgreSQL.
• pg_stat_monitor – Il s’agit d’une version améliorée de pg_stat_statements qui fournit des informations plus détaillées sur les modèles de performances des requêtes, le plan de requête réel et le texte de la requête avec les valeurs des paramètres. Il est disponible sur Linux à partir de notre page de téléchargement ou sous forme de packages RPM à partir des référentiels yum de la communauté PostgreSQL.

Erreur #4 : Mauvaise gestion des connexions

La configuration des connexions semble anodine à première vue. Cependant, j’ai vu des cas où une très grande valeur pour max_connections a provoqué des erreurs de mémoire insuffisante. La configuration de max_connection nécessite donc une certaine attention.

Le nombre de cœurs, la quantité de mémoire disponible et le type de stockage doivent être pris en compte lors de la configuration de max_connections. Vous ne voulez pas surcharger les ressources de votre serveur avec des connexions qui ne seront peut-être jamais utilisées. Ensuite, il y a les ressources du noyau qui sont également allouées par connexion. Le PostgreSQL documentation du noyau a plus de détails.

Lorsque les clients exécutent des requêtes qui prennent très peu de temps, un pooler de connexions améliore considérablement les performances, car la surcharge de génération d’une connexion devient importante dans ce type de charge de travail.

Erreur #5 : L’aspirateur ne fonctionne pas correctement

J’espère que vous n’avez pas désactivé l’autovacuum. Nous avons vu dans de nombreux environnements de production que les utilisateurs ont complètement désactivé l’autovacuum, généralement en raison d’un problème sous-jacent. Si l’autovacuum ne fonctionne pas vraiment dans votre environnement, il ne peut y avoir que trois raisons :

1. Le processus de vide ne se déclenche pas, ou du moins pas aussi fréquemment qu’il le devrait.
2. L’aspiration est trop lente.
3. L’aspirateur ne nettoie pas les rangs morts.

1 et 2 sont directement liés aux options de configuration. Vous pouvez voir les options liées au vide en interrogeant la vue pg_settings.

 
SELECT  name
        , short_desc
        , setting
        , unit
        , CASE
            WHEN context="postmaster" THEN 'restart'
            WHEN context="sighup"     THEN 'reload'
            ELSE context
          END "server requires"
FROM    pg_settings
WHERE   name LIKE '%vacuum%';

La vitesse peut potentiellement être améliorée en ajustant autovacuum_work_mem et le nombre de travailleurs parallèles. Le déclenchement du processus de vide peut être réglé via la configuration de facteurs d’échelle ou de seuils.

Lorsque le processus de vide ne nettoie pas les tuples morts, cela indique que quelque chose retient les ressources clés. Les coupables pourraient être un ou plusieurs d’entre eux :

• Requêtes ou transactions de longue durée.
• Serveurs de secours dans un environnement de réplication avec l’option hot_standby_feedback activée.
• Une valeur supérieure à la valeur requise de vacuum_defer_cleanup_age.
• Des emplacements de réplication qui maintiennent la valeur xmin et empêchent le vide de nettoyer les tuples morts.

Si vous souhaitez gérer manuellement le vide d’une relation, suivez la loi de Pareto (alias la règle des 80/20). Réglez le cluster sur une configuration optimale, puis réglez spécifiquement pour ces quelques tables. N’oubliez pas que autovacuum ou toast.autovacuum peuvent être désactivés pour une relation spécifique en spécifiant l’option de stockage associée lors de l’instruction create ou alter.

Erreur #6 : Connexions malveillantes et transactions de longue durée

Un certain nombre de choses peuvent retenir votre cluster PostgreSQL en otage, et les connexions malveillantes en font partie. En plus de conserver des emplacements de connexion qui pourraient être utilisés par d’autres applications, les connexions malveillantes et les transactions de longue durée conservent des ressources clés qui peuvent semer le chaos dans tout le système. Dans une moindre mesure, dans un environnement de réplication avec hot_standby_feedback activé, les transactions de longue durée sur le serveur de secours peuvent empêcher le vide sur le serveur principal de faire son travail.

Pensez à une application boguée qui ouvre une transaction et cesse de répondre par la suite. Il peut s’agir de conserver des verrous ou simplement d’empêcher le vide de nettoyer les tuples morts car ceux-ci restent visibles dans de telles transactions. Et si cette application devait ouvrir un grand nombre de transactions de ce type ?

Le plus souvent, vous pouvez vous débarrasser de ces transactions en configurant idle_in_transaction_session_timeout sur une valeur adaptée à vos requêtes. Bien sûr, gardez toujours à l’esprit le comportement de votre application chaque fois que vous commencez à régler le paramètre.

Au-delà du réglage de idle_in_transaction_session_timeout, surveillez pg_stat_activity pour toute requête de longue durée ou toute session qui attend des événements liés au client plus longtemps que prévu. Gardez un œil sur les horodatages, les événements d’attente et les colonnes d’état.

 
backend_start    | 2022-10-25 09:25:07.934633+00
xact_start       | 2022-10-25 09:25:11.238065+00
query_start      | 2022-10-25 09:25:11.238065+00
state_change     | 2022-10-25 09:25:11.238381+00
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction

En dehors de cela, les transactions préparées (en particulier les transactions préparées orphelines) peuvent également conserver des ressources système clés (verrous ou valeur xmin). Je recommanderais de mettre en place une nomenclature des opérations préparées pour définir leur ancienneté. Disons qu’une transaction préparée avec un âge maximum de 5 minutes peut être créée comme PREPARE TRANSACTION 'foo_prepared 5m'.

 
SELECT  gid
        , prepared
        , REGEXP_REPLACE(gid, '.* ', '') AS age
FROM    pg_prepared_xacts
WHERE   prepared + CAST(regexp_replace(gid, '.* ', '') AS INTERVAL) < NOW();

Cela fournit un schéma permettant aux applications de définir l’âge de leurs transactions préparées. Une tâche cron ou une tâche planifiée pourrait alors surveiller et annuler toutes les transactions préparées qui restent actives au-delà de leur âge prévu.

Erreur #7 : Sur-indexation ou sous-indexation

Il n’y a sûrement rien de mal à sur-indexer une relation. Ou est-il? Pour obtenir les meilleures performances de votre instance PostgreSQL, il est impératif que vous compreniez comment PostgreSQL gère les index.

Il existe plusieurs types d’index dans PostgreSQL. Chacun a un cas d’utilisation différent et chacun a ses propres frais généraux. B-tree est le type d’index le plus couramment utilisé. Il est également utilisé pour les clés primaires. Les dernières versions majeures ont vu de nombreuses améliorations liées aux performances (et au déblocage) dans les index B-tree. Voici l’un des mes articles de blog qui traite des roulements de version en double dans PostgreSQL 14.

Lorsqu’un parcours d’index est exécuté sur une relation, pour chaque tuple correspondant, il accède au tas pour récupérer à la fois les données et les informations de visibilité, de sorte que seule la version visible par la transaction en cours soit choisie. La sur-indexation entraînera la mise à jour d’un plus grand nombre d’index, consommant ainsi plus de ressources sans récolter les avantages souhaités.

De même, la sous-indexation entraînera plus d’analyses de tas, ce qui entraînera potentiellement plus d’opérations d’E/S et donc une baisse des performances.

L’indexation ne concerne pas seulement le nombre d’index que vous avez sur une relation. C’est à quel point ces index sont optimisés pour les cas d’utilisation souhaités. Idéalement, vous voudriez effectuer une analyse d’index uniquement à chaque fois, mais il y a des limites. Bien que les index B-tree prennent en charge les analyses d’index uniquement pour tous les opérateurs, les index GiST et SP-GiST ne les prennent en charge que pour certains opérateurs. Voir le Documentation pour plus de détails.

Suivre une simple liste de contrôle peut vous aider à valider que votre système est configuré de manière optimale pour les index :

• Assurez-vous que la configuration est correctement définie (par exemple, le coût de la page aléatoire est adapté à votre matériel).
• Vérifiez que les statistiques sont à jour, ou au moins que les commandes analyze ou vacuum s’exécutent sur les relations avec les index. Cela garantira que les statistiques sont plus ou moins à jour afin que le planificateur ait une meilleure probabilité de choisir un balayage d’index.
• Créez le bon type d’index (arbre B, hachage ou autre type).
• Utilisez des index sur les colonnes de droite. N’oubliez pas d’inclure des colonnes non indexées pour éviter l’accès au tas. Tous les types d’index ne permettent pas de couvrir les index, alors consultez la documentation.
• Débarrassez-vous des index inutiles. Voir pg_station_user_indexes pour plus d’informations sur les index et les accès aux blocs.
• Comprenez l’impact de la couverture des index sur des fonctionnalités telles que la déduplication, les rotations de version en double et les analyses d’index uniquement.

Regarde ça page wiki sur la maintenance des index pour des requêtes plus utiles.

Erreur #8 : Sauvegardes et HA inadéquates

La haute disponibilité ne consiste pas seulement à maintenir un service opérationnel. Il s’agit également de s’assurer que le service répond dans les critères d’acceptation définis et qu’il respecte les cibles RPO (objectif de point de récupération) et RTO (objectif de temps de récupération). Pour faire correspondre les exigences de disponibilité et le nombre de neuf que vous ciblez, reportez-vous à ce page wiki pour les calculs de pourcentage.