Hey.
Je m'appelle Vanya et je suis développeur Java. Il se trouve que je travaille beaucoup avec PostgreSQL - en configurant la base de données, en optimisant la structure, les performances et en jouant un peu avec DBA le week-end.
Récemment, j'ai rangé plusieurs bases de données dans nos microservices et écrit une bibliothèque Java , ce qui facilite ce travail, me fait gagner du temps et m'aide à éviter certaines erreurs courantes commises par les développeurs. C'est de cette bibliothèque dont nous parlerons aujourd'hui.
Clause de non-responsabilité
La version principale de PostgreSQL avec laquelle je travaille est la 10. Toutes les requêtes SQL que j'utilise sont également testées sur la version 11. La version minimale prise en charge est 9.6.
Préhistoire
Tout a commencé il y a presque un an avec une situation qui m'était étrange : la création concurrentielle d'un indice à l'improviste s'est soldée par une erreur. L'index lui-même, comme d'habitude, est resté dans la base de données dans un état invalide. L'analyse des journaux a montré une pénurie . Et c'est parti... En creusant plus profondément, j'ai découvert tout un tas de problèmes dans la configuration de la base de données et, retroussant mes manches, j'ai commencé à les résoudre avec une étincelle dans les yeux.
Premier problème : configuration par défaut
Tout le monde est probablement fatigué de la métaphore sur Postgres, qui peut être exécuté sur une cafetière, mais... la configuration par défaut soulève vraiment un certain nombre de questions. Au minimum, il convient de prêter attention maintenance_work_mem, temp_file_limit, instruction_timeout и lock_timeout.
Dans ce cas, maintenance_work_mem était la valeur par défaut de 64 Mo, et temp_file_limit quelque chose d'environ 2 Go - nous n'avions tout simplement pas assez de mémoire pour créer un index sur une grande table.
Par conséquent, dans pg-index-santé J'ai collectionné une série , à mon avis, les paramètres qui doivent être configurés pour chaque base de données.
Deuxième problème : index en double
Nos bases de données vivent sur des disques SSD et nous utilisons HA-configuration avec plusieurs centres de données, hôte maître et n-nombre de répliques. L'espace disque est une ressource très précieuse pour nous ; ce n'est pas moins important que les performances et la consommation du processeur. Par conséquent, d'une part, nous avons besoin d'index pour une lecture rapide et, d'autre part, nous ne voulons pas voir d'index inutiles dans la base de données, car ils consomment de l'espace et ralentissent la mise à jour des données.
Et maintenant, après avoir tout restauré et j'en ai assez vu , j’ai décidé d’organiser une « grande » purge. Il s'est avéré que les développeurs n'aiment pas lire la documentation des bases de données. Ils n'aiment pas beaucoup ça. Pour cette raison, deux erreurs typiques surviennent : un index créé manuellement sur une clé primaire et un index « manuel » similaire sur une colonne unique. Le fait est qu'ils ne sont pas nécessaires - Postgres fera tout lui-même. De tels index peuvent être supprimés en toute sécurité et des diagnostics sont apparus à cet effet .
Troisième problème - indices qui se croisent
La plupart des développeurs novices créent des index sur une seule colonne. Peu à peu, après avoir bien expérimenté ce métier, les gens commencent à optimiser leurs requêtes et à ajouter des index plus complexes comprenant plusieurs colonnes. Voici comment apparaissent les index sur les colonnes A, A + B, A + B + C et ainsi de suite. Les deux premiers de ces indices peuvent être rejetés en toute sécurité, puisqu'ils sont des préfixes du troisième. Cela permet également d'économiser beaucoup d'espace disque et il existe des diagnostics pour cela .
Problème quatre - clés étrangères sans index
Postgres vous permet de créer des contraintes de clé étrangère sans spécifier d'index de sauvegarde. Dans de nombreuses situations, cela ne pose pas de problème et peut même ne pas se manifester... Pour le moment...
C'était la même chose chez nous : c'est juste qu'à un moment donné, un travail, exécuté selon un calendrier et effaçant la base de données des commandes de test, a commencé à nous être « ajouté » par l'hôte maître. Le processeur et les E/S ont été gaspillés, les requêtes ont ralenti et ont expiré, le service était de cinq cents. Analyse rapide a montré que des requêtes telles que :
delete from <table> where id in (…)Dans ce cas, bien sûr, il y avait un index par identifiant dans la table cible, et très peu d'enregistrements étaient supprimés selon la condition. Il semblait que tout devrait fonctionner, mais hélas, ce n’est pas le cas.
Le merveilleux est venu à la rescousse expliquer analyser et a déclaré qu'en plus de supprimer les enregistrements dans la table cible, il existe également une vérification de l'intégrité référentielle, et sur l'une des tables associées, cette vérification échoue analyse séquentielle en raison de l'absence d'un indice approprié. Ainsi est né le diagnostic .
Problème cinq – valeur nulle dans les index
Par défaut, Postgres inclut des valeurs nulles dans les index btree, mais elles n'y sont généralement pas nécessaires. Par conséquent, j'essaie avec diligence de supprimer ces valeurs nulles (diagnostics ), créant des index partiels sur les colonnes nullables par type where <A> is not null. De cette façon, j'ai pu réduire la taille d'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 % (de 4.3 Go en chiffres absolus) en raison de l'exclusion des valeurs nulles des index. D'énormes économies d'espace disque avec des modifications très simples. 🙂
Problème six – manque de clés primaires
En raison de la nature du mécanisme une situation comme celle-ci est possible lorsque la taille de votre table augmente rapidement en raison d'un grand nombre d'enregistrements morts. J'ai naïvement cru que cela ne nous menacerait pas, et que cela n'arriverait pas à notre base, parce que, wow !!!, nous sommes des développeurs normaux... Comme j'étais stupide et naïf...
Un jour, une merveilleuse migration a pris et mis à jour tous les enregistrements dans une grande table activement utilisée. Nous avons obtenu +100 Go à la taille de la table à l'improviste. C'était vraiment dommage, mais nos mésaventures ne se sont pas arrêtées là. Après la fin de l'auto-vide sur cette table 15 heures plus tard, il est devenu clair que l'emplacement physique ne reviendrait pas. Nous ne pouvions pas arrêter le service et rendre VACUUM FULL, nous avons donc décidé d'utiliser . Et puis il s'est avéré que pg_repack ne sait pas comment traiter les tables sans clé primaire ou autre contrainte d'unicité, et notre table n'avait pas de clé primaire. Ainsi est né le diagnostic .
Dans la version bibliothèque 0.1.5 La possibilité de collecter des données à partir d'une multitude de tables et d'index et d'y répondre en temps opportun a été ajoutée.
Problèmes sept et huit : index insuffisants et index inutilisés
Les deux diagnostics suivants sont : и – sont apparus sous leur forme définitive relativement récemment. Le fait est qu’ils ne peuvent pas simplement être pris et ajoutés.
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. En conséquence, il s'avère que certaines tables et index sur certains hôtes ne sont pratiquement pas utilisés et, pour l'analyse, vous devez collecter des statistiques sur tous les hôtes du cluster. Ceci est également nécessaire sur chaque hôte du cluster ; vous ne pouvez pas le faire uniquement sur le maître.
Cette approche nous a permis d'économiser plusieurs dizaines de gigaoctets en supprimant les index qui n'ont jamais été utilisés, ainsi qu'en ajoutant les index manquants aux tables rarement utilisées.
En conclusion
Bien entendu, pour presque tous les diagnostics, vous pouvez configurer . De cette façon, vous pouvez rapidement mettre en œuvre des contrôles dans votre application, empêchant l'apparition de nouvelles erreurs, puis corriger progressivement les anciennes.
Certains diagnostics peuvent être effectués lors de tests fonctionnels immédiatement après le déploiement des migrations de bases de données. Et c'est peut-être l'une des fonctionnalités les plus puissantes de ma bibliothèque. Un exemple d'utilisation peut être trouvé dans .
Il est logique d'effectuer des vérifications des index inutilisés ou manquants, ainsi que de la surcharge, uniquement sur une base de données réelle. Les valeurs collectées peuvent être enregistrées dans ou envoyé au système de surveillance.
J'espère vraiment que pg-index-santé sera utile et demandé. Vous pouvez également contribuer au développement de la bibliothèque en signalant les problèmes que vous rencontrez et en suggérant de nouveaux diagnostics.
Source: habr.com