pgcompacttable¶
Le modèle MVCC de PostgreSQL a tendance à mener à de la fragmentation dans les fichiers des tables. Si elle prend de l’ampleur, les commandes VACUUM FULL et CLUSTER corrigent cela, mais elles impliquent un verrouillage complet de la table, une indisponibilité et un espace disque suffisant.
L’outil pgcompacttable propose de réduire la fragmentation des tables sans blocage.
Attention : L’utilisation de cet outil ne doit se faire qu’en dernière option.
VACUUM FULLetCLUSTERsont les seules manières totalement fiables de supprimer toute fragmentation.
Principe¶
Cet outil fonctionne en ligne de commande et permet de diminuer la fragmentation dans les tables et index sans poser de verrous exclusifs. Il utilise des commandes SQL pour déplacer les données dans les tables et des commandes REINDEX pour traiter les index associés. Ce mode de fonctionnement est intéressant car il limite les risques de corruption des tables et index en laissant PostgreSQL se charger de leurs modifications.
Il dispose de nombreuses options pour sélectionner les tables à traiter. Il est par exemple possible d’inclure ou d’exclure des tables (-t/-T), schémas ou bases de données, ainsi que de filtrer les tables par leur nom (--tables-like 'LIKE expression').
Par défaut, les tables sélectionnées ne seront défragmentées que si la fragmentation atteint 20 % de la taille de la table. Il est cependant possible de forcer le traitement (--force).
Un VACUUM préalable est effectué par défaut (sauf --no-initial-vacuum).
L’estimation de la fragmentation est réalisée avec l’extension pgstattuple, qui est indispensable pour l’utilisation de l’outil : cela implique que toute la table sera lue dès le départ.
Par défaut, une réindexation de la table est réalisée après la réorganisation de celle-ci. Elle peut également être lancée avant la réorganisation ou tout simplement ignorée (--no-reindex). On peut en effet préférer traiter les index soi-même, plus tard ou de manière plus traditionnelle (le script peut les afficher avec --print-reindex-queries).
REINDEX CONCURRENTLY est activé par défaut mais peut être désactivé (--reindex-replace). Des options sont disponibles pour relancer la réindexation en cas d’échec à cause d’un verrou non obtenu (--reindex-*). Elles permettent notamment de contrôler le nombre de tentatives de réindexation, le temps d’attente entre les tentatives et le temps d’attente avant qu’une réindexation n’échoue (lock_timeout).
L’agressivité de l’outil est configurable via un temps d’attente entre les passes de défragmentation. Il est recommandé d’utiliser ionice -c 3 pour ne pas gêner les I/O.
Avantages¶
- Fonctionne pour les versions PostgreSQL 9.2 et supérieures.
- Permet de réorganiser une table ou une base sans interruption de service.
- Réduit le besoin en espace par rapport à un
VACUUM FULL. - Réduit les entrées-sorties car il ne réécrit pas la table entière.
- Utilise des
UPDATEpour modifier les données. - Le développement est actif.
Inconvénients¶
Il s’agit d’un outil tiers pour PostgreSQL. S’il est possible de l’utiliser en production, son bon fonctionnement n’est cependant pas garanti par la communauté.
Un bug n’est jamais impossible. L’outil doit être testé avant toute utilisation en production. Cependant, le risque de corruption semble faible puisque l’outil utilise de simples commandes UPDATE pour déplacer les lignes. Ne pas lancer sans surveillance.
Le plus sûr reste, si possible, d’utiliser les outils fournis par la communauté : VACUUM FULL et CLUSTER.
L’outil peut être lancé et interrompu, mais en raison du VACUUM et de la requête pgstattuple nécessaires, le relancer est chronophage et consommateur d’I/O avant même toute altération de la table.
Installation¶
Il s’agit d’un script Perl de Data Egret, à télécharger sur https://github.com/dataegret/pgcompacttable.
Il est nécessaire d’installer quelques paquets Perl :
# RedHat et assimilés
yum install perl-Time-HiRes perl-DBI perl-DBD-Pg
# Debian et assimilés
apt install libdbi-perl libdbd-pg-perl
L’installation de l’extension (standard) pgstattuple dans la base concernée est nécessaire :
Connexion¶
Les paramètres habituels sont utilisés pour se connecter à la base de données (-h, -p, -U, -d). Les variables d’environnement PG* ne fonctionnent pas.
On peut sélectionner les objets à réorganiser :
-a: réorganisation de tous les objets dans toutes les bases de données,-d: réorganisation de tous les objets dans la base de données spécifiée,-n: réorganisation de tous les objets dans le schéma de la base de données spécifiée,-t: réorganisation de tous les objets dans la table du schéma de la base de données spécifiée,-N: exclusion des objets du schéma spécifié,-T: exclusion des objets de la table spécifiée.
Par défaut :
- seules les tables présentant plus de 20 % de bloat (par défaut) sont réorganisées (sauf
--force), - les index sont reconstruits de façon concurrente après la réorganisation, et en cas d’échec, l’outil peut réessayer (jusqu’à 100 fois par défaut).
On peut désactiver la réindexation post-réorganisation avec l’option --no-reindex.
Attention : Si on peut interrompre l’outil à tout moment, son démarrage est toutefois lent : il effectue un VACUUM, puis exécute une requête assez lourde impliquant pgstattuple pour rechercher les blocs à défragmenter.
La documentation complète est accessible avec :
Sécurité et vérifications diverses¶
- Surveiller la volumétrie des journaux : une part importante de la table peut être réécrite !
Notamment en cas de Ctrl-C :
- Vérifier que les index temporaires ont bien été effacés.
- Vérifier que la requête
pgstattuplene tourne plus (danspg_stat_activityou avecpg_activity). - Vérifier que les verrous “advisory” sont bien tous fermés (
SELECT * FROM pg_locks WHERE locktype = 'advisory'). - Au besoin, tuer la session avec
SELECT pg_terminate_backend(<pid>).
Ordres¶
Si les traces sont assez verbeuses, on y trouvera des informations comme :
Désactivation des triggers dans la session :¶
Création de fonction utilitaire :¶
LOG: statement:CREATE OR REPLACE FUNCTION public.pgcompact_clean_pages_8185(
CREATE OR REPLACE FUNCTION public.pgcompact_clean_pages_8185(
i_table_ident text,
i_column_ident text,
i_to_page integer,
i_page_offset integer,
i_max_tupples_per_page integer)
RETURNS integer
LANGUAGE plpgsql AS $$ )
Tentative de verrou :¶
LOG: execute <unnamed>:
SELECT pg_try_advisory_lock(
'pg_catalog.pg_class'::regclass::integer,
(quote_ident($1)||'.'||quote_ident($2))::regclass::integer)::integer;
DETAIL: parameters: $1 = 'public', $2 = 'frag'
VACUUM explicite :¶
Appels de la fonction pour déplacer un ensemble de lignes :¶
Exemple (compactage d’une table fragmentée)¶
L’outil fournit une estimation et procède au compactage :
[Fri Jun 28 17:04:40 2019] (frag) Connecting to database
[Fri Jun 28 17:04:40 2019] (frag) Postgres backend pid: 8186
...
[Fri Jun 28 17:06:25 2019] (frag:public.frag) Processing complete.
Avec l’option --print-reindex-queries on obtient aussi le script des REINDEX :
[Fri Jun 28 17:06:23 2019] (frag:public.frag) Reindex: public.frag_pkey, initial size 27422 pages(214.234MB), has been reduced by 94% (203.500MB), duration 0 seconds, attempts 0.
Exemple (pgbench_accounts, nettoyage forcé)¶
$ ./pgcompacttable -p 13002 -h monolithe -d pgbench_1000 -U postgres -t pgbench_accounts --verbose --no-reindex --print-reindex-queries --no-initial-vacuum
L’outil affiche le traitement effectué :