Santé des index dans PostgreSQL vu par un développeur Java

Bonjour.

Je m'appelle Vania et je suis développeur Java. Il se trouve que je travaille beaucoup avec PostgreSQL – je m'occupe de la configuration de la base de données, de l'optimisation de la structure, de la performance et je joue un peu au DBA pendant le week-end.

Récemment, j'ai remis en ordre plusieurs bases de données dans nos microservices et j'ai écrit une bibliothèque Java pg-index-health, qui facilite ce travail, fait gagner du temps et aide à éviter certaines erreurs courantes commises par les développeurs. C'est justement de cette bibliothèque dont je vais parler aujourd'hui.

Avertissement

La version principale de PostgreSQL avec laquelle je travaille est la 10. Toutes les requêtes SQL que j'utilise ont également été vérifiées sur la version 11. La version minimale prise en charge est 9.6.

Contexte

Tout a commencé il y a presque un an avec une situation étrange pour moi : la création concurrentielle d'un index a échoué avec une erreur. L'index lui-même est resté dans un état non valide dans la base. L'analyse des logs a montré un manque de temp_file_limit. Et c'est parti... En creusant un peu plus, j'ai découvert un tas de problèmes dans la configuration de la base de données et, retroussant mes manches, avec des yeux pétillants, je me suis mis à les corriger.

Le premier problème - la configuration par défaut

Peut-être que la métaphore de Postgres, qui peut être lancé sur une cafetière, commence à bien lasser tout le monde, mais... la configuration par défaut soulève vraiment plusieurs questions. Au minimum, il faut faire attention à maintenance_work_mem, temp_file_limit, statement_timeout et lock_timeout.

Dans notre cas maintenance_work_mem était par défaut 64 Mo, tandis que temp_file_limit environ 2 Go - nous manquions tout simplement de mémoire pour créer un index sur une grande table.

C'est pourquoi, dans pg-index-health j'ai rassemblé un certain nombre de paramètres clés, selon moi, qu'il vaut la peine de configurer pour chaque base de données.

Le deuxième problème - les index dupliqués

Nos bases vivent sur des disques SSD, et nous utilisons HA-une configuration avec plusieurs centres de données, un hôte principal et n-un nombre variable de répliques. L'espace disque est une ressource très précieuse pour nous ; il est tout aussi important que la performance et la consommation de CPU. Donc, d'un côté, nous avons besoin d'index pour des lectures rapides, mais de l'autre, nous ne voulons pas voir d'index superflus dans la base de données, car ils consomment de l'espace et ralentissent la mise à jour des données.

Et donc, après avoir restauré tous les index non valides et après avoir regardé les présentations d'Oleg Bartunov, j'ai décidé de faire un « grand » nettoyage. Il s'est avéré que les développeurs n'aiment pas lire la documentation de la base de données. Ils n'aiment vraiment pas. Cela entraîne deux erreurs typiques : un index créé manuellement sur la clé primaire et un index similaire « manuel » sur une colonne unique. Le fait est qu'ils ne sont pas nécessaires – Postgres s'en occupera tout seul. Ces index peuvent être supprimés sans hésitation, et un diagnostic a été mis en place pour cela. index_dupliqués.

Problème trois – index qui se chevauchent

La plupart des développeurs débutants créent des index sur une seule colonne. Progressivement, après avoir goûté à cette tâche, les gens commencent à optimiser leurs requêtes et à ajouter des index plus complexes comprenant plusieurs colonnes. C'est ainsi qu'apparaissent les index sur les colonnes A, A+B, A+B+C etc. Les deux premiers de ces index peuvent être facilement supprimés, car ils sont des préfixes du troisième. Cela économise également une quantité considérable d'espace disque et un diagnostic est disponible pour cela. index_recroisés.

Problème quatre – clés étrangères sans index

Postgres permet de créer des contraintes de clé étrangère sans spécifier d'index de support. Dans de nombreuses situations, cela n'est pas un problème et peut même ne pas se manifester... Jusqu'à un certain moment...

C'était notre cas : à un certain moment, un job, s'exécutant selon un emploi du temps et nettoyant la base des commandes de test, a commencé à « saturer » notre hôte master. Le processeur et l'IO s'envolaient, les requêtes ralentissaient et étaient interrompues par des timeouts, le service affichait des erreurs 500. Une analyse rapide pg_stat_activity a montré que les requêtes du type :

supprimer de <table> où id dans (…)

Tout en ayant l'index sur id dans la table cible, il y avait naturellement peu de suppressions selon la condition. On aurait pensé que tout devait fonctionner, mais, hélas, ça ne fonctionnait pas.

Le merveilleux explain analyze est venu à la rescousse, et a révélé qu'en plus de la suppression des enregistrements dans la table cible, une vérification de l'intégrité référentielle était également effectuée, et sur l'une des tables associées, cette vérification tombait dans une analyse séquentielle en raison de l'absence de l'index approprié. Ainsi, le diagnostic clés_étrangères_sans_index.

Problème cinq – valeur nulle dans les index

Par défaut, Postgres inclut les valeurs nulles dans les index btree, mais elles ne sont généralement pas nécessaires. C'est pourquoi je m'efforce de supprimer ces nulls (diagnostic index_avec_valeurs_nuls), en créant des index partiels sur les colonnes nullable du type where is not null. De cette façon, j'ai réussi à réduire la taille de l'un de nos index de 1877 Mo à 16 Ko. Et dans l'un des services, la taille de la base de données a diminué au total de 16 % (soit 4,3 Go en chiffres absolus) en raison de l'exclusion des valeurs nulles des index. Une économie d'espace disque colossale avec des améliorations assez simples. 🙂

Problème six – absence de clés primaires

En raison des particularités du mécanisme MVCC dans Postgres il est possible qu'une telle situation survienne, le gonflement, lorsque la taille de votre table augmente rapidement en raison d'un grand nombre d'enregistrements morts. Je pensais naïvement que cela ne nous arriverait pas et qu'avec notre base de données, cela ne se produirait pas, après tout, nous sommes, oh là là!!!, de bons développeurs... Comme j'étais naïf et stupide…

Un beau jour, une migration a décidé de mettre à jour tous les enregistrements dans une grande table souvent utilisée. Nous avons obtenu +100 Go à la taille de la table sans raison. C'était incroyablement frustrant, mais nos mésaventures ne se sont pas arrêtées là. Après que l'autovacuum sur cette table a duré 15 heures, il est devenu clair que l'espace physique ne reviendrait pas. Nous ne pouvions pas arrêter le service et effectuer un VACUUM FULL, donc nous avons décidé d'utiliser pg_repack. Et là, il s'est avéré que pg_repack ne peut pas traiter les tables sans clé primaire ou autre contrainte d'unicité, et notre table n'en avait pas. C'est ainsi qu'est née le diagnostic tables_without_primary_key.

Dans la version de la bibliothèque 0.1.5 la possibilité de collecter des données sur le bloat des tables et des index a été ajoutée et d’y réagir à temps.

Problèmes sept et huit – manque d'index et index inutilisés

Les deux diagnostics suivants — tables_with_missing_indexes et unused_indexes – sont apparus dans leur version finale relativement récemment. En fait, on ne pouvait pas simplement les ajouter comme ça.

Comme je l'ai déjà écrit, nous utilisons une configuration avec plusieurs répliques, et la charge de lecture sur différents hôtes est fondamentalement différente. Au final, cela crée une situation où certaines tables et index sur certains hôtes ne sont pratiquement pas utilisés, et pour l'analyse, il est nécessaire de collecter des statistiques depuis tous les hôtes du cluster. Il est également nécessaire de réinitialiser les statistiques sur chaque hôte du cluster, on ne peut pas le faire uniquement sur le maître.

Cette approche nous a permis d'économiser plusieurs dizaines de gigaoctets en supprimant des index qui n'étaient jamais utilisés, tout en ajoutant des index manquants sur des tables rarement utilisées.

En conclusion

Il va de soi que pour pratiquement tous les diagnostics, il est possible de configurer une liste d'exceptions. Cela permet d'implémenter rapidement des vérifications dans votre application, prévenant ainsi l'apparition de nouvelles erreurs, et de corriger progressivement les anciennes.

Certaines vérifications peuvent être effectuées directement dans les tests fonctionnels immédiatement après l'application des migrations de la base de données. Et c'est probablement l'une des fonctionnalités les plus puissantes de ma bibliothèque. Vous pouvez consulter un exemple d'utilisation dans une démo.

Les vérifications d'index non utilisés ou manquants, ainsi que celles concernant le bloat, ne sont pertinentes que sur une véritable base de données. Les valeurs collectées peuvent être enregistrées dans ClickHouse ou envoyées à un système de surveillance.

J'espère sincèrement que pg-index-health sera utile et recherchée. Vous pouvez également contribuer au développement de la bibliothèque en signalant des problèmes rencontrés et en proposant de nouveaux diagnostics.

Source : habr.com