Aller au contenu

Outils de défragmentation de table

À propos

LicensePostgreSQL
CompatibilitéToutes versions de PostgreSQL

Résumé

Success

Nous recommandons autant que possible d’utiliser VACUUM FULL ou CLUSTER s’il faut vraiment reconstruire une table. C’est le plus sûr et le plus rapide.

Dans les cas où une défragmentation en ligne est nécessaire, nous recommandons exclusivement pgcompacttable. En effet, ce projet est activement maintenu et la méthode utilisée pour la défragmentation comporte intrinsèquement moins de risques de corruption.

L’opération sera forcément beaucoup plus longue qu’un VACUUM.

pg_repack est répandu mais intrinsèquement plus dangereux.

Il est chaudement conseillé de tester ces outils sur une copie physique de l’instance avant passage en production.

Utilité

Le modèle MVCC de PostgreSQL a tendance à mener à de la fragmentation dans les tables. Un autovacuum normalement paramétré doit permettre de compenser cela. Si cela ne suffit pas, les ordres VACUUM FULL ou CLUSTER permettent de reconstruire une table.

Comme ces ordres impliquent un verrouillage complet de la table, et interdisent toute lecture ou écriture dessus, il s’ensuit une indisponibilité souvent longue. De plus, la place disque exigée est parfois excessive car la table est réécrite à côté de l’ancienne et ses index recréés.

Nous présentons ici plusieurs outils pour réaliser cette défragmentation sans interruption de service.

pgcompacttable

AuteurData Egret
Site du projethttps://github.com/dataegret/pgcompacttable
LangagePerl
Paquets PGDGAucun
CompatibilitéPostgreSQL 9.2 minimum

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 ordres SQL (UPDATE) pour déplacer les données dans les tables, et des commandes REINDEX pour traiter les index associés.

pgcompacttable est donc plus sûr et moins consommateur de place, mais plus lent, que pg_repack.

Ce mode de fonctionnement est intéressant car il ne contourne pas le moteur de PostgreSQL en le laissant faire lui-même les modifications. Les risques de corruptions des tables et index deviennent nuls. Le besoin en place est limité (l’autovacuum procèdera au nettoyage). Les I/O sont relativement limitées.

Il dispose de nombreuses options pour inclure ou exclure des tables (-t/-T), schémas (-n / -N) ou bases de données (-d), ainsi que de filtrer les tables par leur nom (--tables-like 'LIKE expression').

L’estimation de la fragmentation est réalisée avec l’extension pgstattuple (fournie avec PostgreSQL) qui est indispensable pour l’utilisation de l’outil.

Les tables sélectionnées ne seront défragmentées que si la fragmentation atteint 20 % de la taille de la table (sauf à utiliser --force).

Un VACUUM est effectué au préalable (sauf avec --no-initial-vacuum).

Par défaut, une ré-indexation de la table suit la réorganisation (c’est en général nécessaire). Elle peut également être lancée avant la réorganisation ou tout simplement ignorée. Les contentions lors de la réindexation sont réduites grâce au mode concurrent (CONCURRENTLY). Des options sont disponibles pour relancer la réindexation au cas où elle échoue à cause d’un verrou non obtenu. Elles permettent aussi 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).

Il est également possible d’afficher les requêtes de ré-indexation afin de les lancer manuellement (--print-reindex-queries).

Le contrôle de l’agressivité de l’outil est configurable via le temps d’attente entre les passes de défragmentation, ou bien en le lançant avec l’utilitaire Linux ionice -c3.

Si on peut interrompre l’outil n’importe quand, son démarrage est toutefois lent : il opère un VACUUM puis une requête assez lourde impliquant pgstattuple pour rechercher les blocs à défragmenter.

Ce projet est actif.

Pré-requis et installation

  • C’est un simple script Perl de Data Egret, à télécharger depuis la page Github.

  • Son fonctionnement dépend de quelques paquets Perl :

apt install libdbi-perl libdbd-pg-perl

yum install perl-Time-HiRes perl-DBI perl-DBD-Pg
  • Installer pgstattuple dans la base concernée :
CREATE EXTENSION pgstattuple ;

Utilisation

On utilisera les paramètres habituels pour se connecter à la base de données (-h, -p, -U, -d). Les variables d’environnement PG* ne fonctionnent pas.

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é de la réorganisation
  • -T : exclusion des objets de la table spécifiée de la réorganisation.

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 une fois la réorganisation réalisée, et en cas d’échec l’outil peut rééssayer (100 fois au besoin, par défaut).

Il est possible de désactiver la réindexation post-réorganisation avec l’option --no-reindex.

Documentation

./pgcompacttable --man

Supervision

Surveiller la volumétrie des journaux

En effet, une part substantielle de la table va être réécrite, et l’autovacuum va probablement se déclencher.

En cas d’arrêt par Ctrl-C, contrôler :

  • que les index temporaires ont bien été effacés (leur nom commence par pgcompact_index_)
  • que la requête avec pgstattuple ne tourne plus (dans pg_stat_activity ou avec pg_activity)
  • que les advisory lock utilisés sont bien tous fermés (SELECT * FROM pg_locks WHERE locktype = 'advisory')
  • au besoin tuer les sessions avec SELECT pg_terminate_backend (<n° pid>).

Ordres générés

Si les traces sont assez verbeuses, on y trouvera entre autres ce qui suit :

  • la désactivation des triggers dans la session (en effet, les modifications n’altéreront pas les données, les triggers ne doivent pas se déclencher) :
LOG:  statement: set session_replication_role to replica;
  • la création d’une 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 $$
)
…

  • des tentatives d’acquisition 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'

  • des VACUUM explicites (pour récupérer tout espace disponible en début de table et éliminer le maximum de lignes mortes) :

STATEMENT:  VACUUM "public"."frag"
  • De très nombreux appels de la fonction pour déplacer un ensemble de lignes :
LOG:  execute <unnamed>: 
            SELECT public.pgcompact_clean_pages_8185($1,$2,$3,$4,$5)

2019-06-28 17:04:48 CEST [8186]: [110-1] user=postgres,db=frag,app=[unknown],client=::1 DETAIL:  parameters: $1 = '"public"."frag"', $2 = 'f', $3 = '73529', $4 = '5', $5 = '316'
2019-06-28 17:04:48 CEST [8186]: [111-1] user=postgres,db=frag,app=[unknown],client=::1 LOG:  duration: 25.116 ms

Exemple 1

Dans cet exemple nous compacterons une table nommée frag ainsi :

$ ionice -c3 ./pgcompacttable -p 5432 -d mabase -U monuser -t frag

L’outil fournit une estimation du compactage et procède à celui-ci :

[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:04:40 2019] (frag) It is recommended to set ionice -c 3 for pgcompacttable: ionice -c 3 -p 8186
[Fri Jun 28 17:04:40 2019] (frag) Handling tables. Attempt 1
[Fri Jun 28 17:04:48 2019] (frag:public.frag) Statistics: 73530 pages (238113 pages including toasts and indexes), it is expected that ~88.370% (64977 pages) can be compacted with the estimated space saving being 507.640MB.
[Fri Jun 28 17:05:48 2019] (frag:public.frag) Progress: 67%,  44130 pages completed.
...
[Fri Jun 28 17:06:25 2019] (frag:public.frag) Processing results: 3677 pages left (11931 pages including toasts and indexes), size reduced by 545.727MB (1.725GB including toasts and indexes) in total.
[Fri Jun 28 17:06:25 2019] (frag) Processing complete.

L’option --print-reindex-queries fournit le script des REINDEX (utile surtout pour les exécuter manuellement si on a demandé --no-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.
[Fri Jun 28 17:06:23 2019] (frag:public.frag) Reindex queries: public.frag_pkey, initial size 27422 pages (214.234MB), will be reduced by 49% (106.760MB)
[Fri Jun 28 17:06:23 2019] (frag:public.frag) CREATE UNIQUE INDEX CONCURRENTLY pgcompact_index_8185 ON public.frag USING btree (i) TABLESPACE pg_default; --frag
[Fri Jun 28 17:06:23 2019] (frag:public.frag) BEGIN; SET LOCAL statement_timeout TO 1000;
ALTER TABLE "public"."frag" DROP CONSTRAINT "frag_pkey";
ALTER TABLE "public"."frag" ADD CONSTRAINT "frag_pkey" PRIMARY KEY USING INDEX pgcompact_index_8185;
END;; --frag

Exemple 2 (nettoyage forcé)

Cet exemple va au plus rapide : pas de réindexation (mais on affiche le SQL pour réindexer) et pas de VACUUM préalable :

$ ./pgcompacttable -p 13002 -h monolithe -d pgbench_1000 -U postgres \
-t pgbench_accounts  --verbose --no-reindex --print-reindex-queries  --no-initial-vacuum

$ ./pgcompacttable -p 13002 -h monolithe -d pgbench_1000 -U postgres -t pgbench_accounts  --verbose --no-reindex --print-reindex-queries  --no-initial-vacuum

[Fri Feb 25 18:19:04 2022] (pgbench_1000) Connecting to database
[Fri Feb 25 18:19:04 2022] (pgbench_1000) Postgres backend pid: 3091484
[Fri Feb 25 18:19:04 2022] (pgbench_1000) It is recommended to set ionice -c 3 for pgcompacttable: ionice -c 3 -p 3091484
[Fri Feb 25 18:19:04 2022] (pgbench_1000) Handling tables. Attempt 1
[Fri Feb 25 18:19:04 2022] (pgbench_1000:public.pgbench_accounts) Start handling table public.pgbench_accounts
[Fri Feb 25 18:23:03 2022] (pgbench_1000:public.pgbench_accounts) Bloat statistics with pgstattuple: duration 238.368 seconds.
[Fri Feb 25 18:23:03 2022] (pgbench_1000:public.pgbench_accounts) Statistics: 1967097 pages (2515679 pages including toasts and indexes), it is expected that ~16.040% (315530 pages) can be compacted with the estimated space saving being 2.407GB.
[Fri Feb 25 18:23:03 2022] (pgbench_1000:public.pgbench_accounts) Skipping processing: 16.04% space to compact from 20% minimum required.
[Fri Feb 25 18:23:10 2022] (pgbench_1000:public.pgbench_accounts) Reindex queries: public.pgbench_accounts_pkey, initial size 548044 pages (4.181GB), will be reduced by 41% (1.721GB)
[Fri Feb 25 18:23:10 2022] (pgbench_1000:public.pgbench_accounts) CREATE UNIQUE INDEX CONCURRENTLY pgcompact_index_2205715 ON public.pgbench_accounts USING btree (aid) TABLESPACE pg_default; --pgbench_1000
[Fri Feb 25 18:23:10 2022] (pgbench_1000:public.pgbench_accounts) BEGIN; SET LOCAL statement_timeout TO 1000;
ALTER TABLE "public"."pgbench_accounts" DROP CONSTRAINT "pgbench_accounts_pkey";
ALTER TABLE "public"."pgbench_accounts" ADD CONSTRAINT "pgbench_accounts_pkey" PRIMARY KEY USING INDEX pgcompact_index_2205715;
END;; --pgbench_1000
[Fri Feb 25 18:23:10 2022] (pgbench_1000:public.pgbench_accounts) Finish handling table public.pgbench_accounts
[Fri Feb 25 18:23:10 2022] (pgbench_1000) Processing complete.
[Fri Feb 25 18:23:10 2022] (pgbench_1000) Processing results: size reduced by 0.000B (0.000B including toasts and indexes) in total.
[Fri Feb 25 18:23:10 2022] (pgbench_1000) Disconnecting from database
[Fri Feb 25 18:23:10 2022] Processing complete: 1 retries to process has been done
[Fri Feb 25 18:23:10 2022] Processing results: size reduced by 0.000B (0.000B including toasts and indexes) in total.

pgcompact

AuteursSergey Konoplev, Maxim Bogukt
Site du projethttps://github.com/grayhemp/pgtoolkit
LangagePerl

Attention

Cet outil n’est plus maintenu.

Cet outil s’utilise en ligne de commande et comporte beaucoup de similitudes avec pgcompacttable. Il se repose aussi sur des commandes SQL pour réorganiser les tables et des commandes REINDEX pour les index.

Malheureusement, il n’est pas maintenu depuis 2015, c’est pourquoi nous ne recommandons pas son utilisation.

pg_repack

Attention

Nous ne recommandons pas l’utilisation de cet outil qui par sa nature est très intrusif et pourrait être dangereux.

Cela dit, il est utilisé assez largement depuis longtemps et sa maintenance est active.

AuteurData Egret
Site du projethttps://reorg.github.io/pg_repack/
Page Githubhttps://github.com/reorg/pg_repack
LangageC
Paquets PGDGpostgresql-16-repack (Debian & Ubuntu), pg_repack_16 et pg_repack_16-llvmjit (RPM)
CompatibilitéPostgreSQL 9.4 minimum

Le principe de pg_repack est de reconstruire la table à côté de l’ancienne tout en permettant à la production de continuer (c’est-à-dire sans verrouillage complet et long des tables).

pg_repack est donc plus rapide mais plus consommateur de place que pgcompacttable.

Il prend un verrou assez lourd sur la table, crée une table temporaire, ajoute des triggers sur la table originale pour récupérer les lignes modifiées pendant la reconstruction. La copie finie, il échange les tables.

C’est un outil de bas niveau et intrusif qui utilise des triggers et modifie directement le catalogue système, ce qui rendrait catastrophique tout bug : corruption, fichiers orphelins… (voir les issues Github).

Des clés primaires (vraie PK ou UNIQUE NOT NULL) sont obligatoires.

Pour aller plus loin