Du SQL, notamment en lien avec un éléphant bleu et libre
Un des défauts choix de l’implémentation de PostgreSQL est le numéro de transactions sur 32 bits, qui oblige à nettoyer périodiquement les vieux numéros de transaction en marquant les lignes comme « gelées ». Cette opération se déroule lors d’un VACUUM FREEZE (doc), et elle est parfois assez embêtante.
Que se passe-t-il quand on croit bien faire en le désactivant ?
Plongée dans le tuning mémoire d’une instance PostgreSQL, du basique work_mem aux entrailles du kernel Linux.
samedi 1 septembre 2018
Dans les améliorations de performances de la v11, l’une d’elles peut faire gagner beaucoup de temps lors des mises à jour applicatives, voire peut en rendre certaines simplement possibles :
Many other useful performance improvements, including making
ALTER TABLE .. ADD COLUMNwith a non-null column default faster
Les détails sont chez Brandur. En résumé, lors de l’ajout d’une colonne avec une valeur par défaut, PostgreSQL pré-v11 devait réécrire la table pour la rajouter physiquement. Ce qui prend son temps si ladite table fait 1 To. Et l’application utilisatrice est bloquée, ce qui peut être intolérable. Les contournements, comme l’ajout d’une colonne nullable puis la mise à jour progressive par batchs, introduisent un état transitoire un peu bancal.
Vient la v11, qui se contente de rajouter l’information que la colonne a été ajoutée, et que si elle ne se trouve pas dans la ligne, il faut prendre la valeur par défaut.
Certains types de requêtes sont condamnées au seq scan (parcours de table complet), par exemple des requêtes décisionnelles portant sur un historique assez long. Mais on n’a pas forcément envie de parcourir tout l’historique non plus, et les index ne peuvent pas tout. Les tables énormes sont également peu maniables (VACUUM FULL impossible, difficulté à les répartir sur des disques différents…). Pour accélérer malgré tout ces requêtes, tout en se simplifiant l’administration, il est plus confortable de tronçonner la table en partitions, stockées parfois dans différents tablespaces, par exemple en données mensuelles que l’on pourra au besoin parcourir intégralement.
C’est le but du tout nouveau partitionnement déclaratif de PostgreSQL 10. Il y avait déjà un système de partitionnement par héritage, mais peu pratique et peu utilisé.
Mais jusqu’où peut-on monter dans le nombre de partitions ? (Spoiler : pas trop haut).
Le modèle MVCC de PostgreSQL a un avantage : une session ne s’embête pas à supprimer physiquement les données qu’elle a modifiées, et gagne ainsi du temps. De plus, ces lignes pourraient encore être utiles à une autre transaction. L’inconvénient, c’est qu’il va bien falloir s’en occuper plus tard — en théorie à un moment moins chargé.
Il faut donc effectuer régulièrement un VACUUM sur les tables modifiées pour éviter le bloat, la boursouflure, le gonflement, l’embonpoint né de ces lignes mortes qu’aucune transaction ne peut plus voir. Dans les versions antiques de PostgreSQL, le VACUUM devait être passé manuellement ; à présent il est déclenché en arrière-plan par le démon autovacuum.
La configuration par défaut suffit en général, mais quand il faut y toucher, les nombreux paramètres ne rendent pas la chose très lisible. Il y a bien sûr la doc que l’on peut déjà trouver en ligne, l’officielle ou chez Dalibo, ou encore ce billet chez 2nd Quadrant. Mais rien ne vaut un bon test.
Parmi toutes la pléthore de nouveautés de PostgreSQL 10, une un peu planquée est liée à la prise en compte de la corrélation des données par l’optimiseur.
Ce qui suit décrit le strict minimum pour mettre en place deux instances en réplication maître/esclave avec PostgreSQL 9.6.
Bientôt ici :
Pour le moment on en est au choix du thème…
© SQL & éléphant, after the WP Dusk To Dawn theme Propulsé par Dotclear