Forums PostgreSQL.fr

mortimer.pw

Membre

Fragmentation des tables et indexes

Bonjour à tous,

Je travaille avec un moteur 10.3 sous Cent-OS 7.5.

Je souhaite surveiller la fragmentation des tables et indexes "sensibles" de notre application.

J'ai mis en place l'extension PGSTATTUPLE.

J'ai trouvé la requête ci-dessous sur le sujet :

SELECT pn.nspname AS SCHEMA,pc2.relname AS TABLE,PG_SIZE_PRETTY(PG_RELATION_SIZE(pn.nspname||'.'||pc2.relname))AS TBL_SIZE,dead_tuple_percent AS PCT_DEAD_LINE,
pc.relname AS INDEX,PG_SIZE_PRETTY(index_size) AS INDEX_SIZE,leaf_pages AS NB_LEAF,empty_pages AS EMPT_LEAF,deleted_pages AS DEL_LEAF,avg_leaf_density AS LEAF_DENS,leaf_fragmentation AS FRAG_LEAF
FROM pg_class pc,pg_class pc2,pg_index pi,pg_namespace pn,
PGSTATTUPLE(pc2.oid),PGSTATINDEX(pn.nspname||'.'||pc.relname)
WHERE pc.relkind='i'
AND pc.oid=pi.indexrelid
AND pi.indrelid=pc2.oid
AND pc.relnamespace=pn.oid
AND pn.nspname||'.'||pc2.relname IN('go.table1')
ORDER BY pn.nspname,pc2.relname,pc.relname;

Je l'ai exécuté sur une première table :

schema |    table     | tbl_size | pct_dead_line |              index               | index_size | nb_leaf | empt_leaf | del_leaf | leaf_dens | frag_leaf
-------+--------------+----------+---------------+----------------------------------+------------+---------+-----------+----------+-----------+-----------
go    |  table1      | 616 MB   |          2.16 | pk_table1                        | 1088 MB    |  103738 |         0 |    32743 |     54.05 |     14.04

J'ai ensuite regardé les stats sur la table :

go=# select * from pg_stat_all_tables where schemaname='go' and relname in ('table1');

relid | schemaname |   relname    | seq_scan | seq_tup_read | idx_scan  | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum |        last_autovacuum
       | last_analyze |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+--------------+----------+--------------+-----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------+------------------------
-------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
16853 |  go        |   table1     |      201 |    549632316 |       117 |           115 |   1505074 |         0 |   1432601 |             0 |    4177689 |     282570 |              114490 |             | 2018-11-12 06:01:53.281
133+03 |              | 2018-11-17 06:00:12.75971+03  |            0 |                2 |             0 |                 6

J'ai fait un Vacuum :

go=# vacuum (full, verbose, analyze) go.table1;
INFO:  vacuuming "go.table1"
INFO:  "table1": found 123447 removable, 4457080 nonremovable row versions in 78860 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 2.85 s, system: 1.26 s, elapsed: 5.71 s.
INFO:  analyzing "go.table1"
INFO:  "table1": scanned 30000 of 63469 pages, containing 2107178 live rows and 0 dead rows; 30000 rows in sample, 4457522 estimated total rows
VACUUM

J'ai regardé à nouveau les stats :

go=# select * from pg_stat_all_tables where schemaname='go' and relname in ('table1');

relid | schemaname |   relname    | seq_scan | seq_tup_read | idx_scan  | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum |        last_autovacuum
       | last_analyze |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+--------------+----------+--------------+-----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------+------------------------
-------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
16853 |  go        |   table1     |      203 |    558669923 |       117 |           115 |   1505074 |         0 |   1432601 |             0 |    4457522 |          0 |                   0 |             | 2018-11-12 06:01:53.281
133+03 | 2018-11-19 13:39:05.076427+03 | 2018-11-17 06:00:12.75971+03  |            0 |                2 |             1 |                 6

Et j'ai regardé à nouveau la fragmentation :

schema |    table     | tbl_size | pct_dead_line |              index               | index_size | nb_leaf | empt_leaf | del_leaf | leaf_dens | frag_leaf
--------+--------------+----------+---------------+----------------------------------+------------+---------+-----------+----------+-----------+-----------
go     |  table1      | 496 MB   |             0 | pk_table1                        | 458 MB     |   57568 |         0 |        0 |     90.76 |         0

Pour la fragmentation des tables, est-ce bien lorsque le PCT_DEAD_LINE dépasse un certain seuil qu'il faut décider d'un vacuum full sur la table ?
Pour les indexes, est-ce bien en fonction des LEAF_DENS et FRAG_LEAF ?

J'ai fait un Vacuum Full mais je ne vois pas d'info dans la colonne LAST_VACUUM, est-ce normal ?
J'ai regagné 120 Mo mais un Reindex n'était-il pas plus approprié ? La table était fragmentée à un peu plus de 2% seulement, alors que la densité des feuilles de l'indexe était à 54% ?

Globalement est-ce la bonne démarche ?

D'avance merci pour vos retours.

mortimer.pw

Membre

Re : Fragmentation des tables et indexes

Bonjour,
Personne pour me conseiller, svp ?
Merci.

gleu

Administrateur

Re : Fragmentation des tables et indexes

Pour la fragmentation des tables, est-ce bien lorsque le PCT_DEAD_LINE dépasse un certain seuil qu'il faut décider d'un vacuum full sur la table ?

Il faut éviter à tout prix les VACUUM FULL (à moins de pouvoir bloquer sa prod pendant un long moment). Mais en théorie, oui, lorsque PCT_DEAD_LINE dépasse un certain niveau, un VACUUM suffit. Et pour un niveau plus important, un VACUUM FULL peut être envisageable.

Pour les indexes, est-ce bien en fonction des LEAF_DENS et FRAG_LEAF ?

Je ne prends en compte que le deuxième.

J'ai fait un Vacuum Full mais je ne vois pas d'info dans la colonne LAST_VACUUM, est-ce normal ?

Oui. Un VACUUM FULL n'est pas un VACUUM vu qu'il s'agit de reconstruire entièrement la table. La colonne last_vacuum n'est renseignée que pour les VACUUM non FULL.

J'ai regagné 120 Mo mais un Reindex n'était-il pas plus approprié ? La table était fragmentée à un peu plus de 2% seulement, alors que la densité des feuilles de l'indexe était à 54% ?

Une fragmentation à 2% ne nécessite pas forcément de VACUUM. Un VACUUM FULL n'a aucun intérêt. Un REINDEX aurait été plus intéressant.

Globalement est-ce la bonne démarche ?

Globalement, oui, si on enlève la partie sur le VACUUM FULL.

---

Guillaume.

mortimer.pw

Membre

Re : Fragmentation des tables et indexes

Bonjour Guillaume,
Merci d'avoir pris le temps d'apporter des réponses à mes questions.
Du coup, j'ai une autre interrogation, je pensais avoir lu que dans les fonctionnalités du Vacuum, il y avait le nettoyage des indexes ?
C'est juste "un marquage" des lignes plus utilisées ? La réindexation doit être déclenchée manuellement ?

gleu

Administrateur

Re : Fragmentation des tables et indexes

C'est plus un nettoyage des enregistrements compris dans les index. Ils sont supprimés de l'index, donc non pris en compte lors d'une recherche par exemple, ce qui permet à la recherche d'aller plus vite, mais toujours présent dans le fichier. PostgreSQL peut réutiliser de la place libérée dans un index mais moins facilement/rapidement que pour une table. Il faut en effet qu'un bloc complet soit libéré pour qu'il puisse être réutilisé (alors qu'il suffit d'un enregistrement libéré dans un bloc d'une table pour que cette place puisse être réutilisée).

Quant à la réindexation, elle doit être déclenchée manuellement, pour exactement la même raison qu'un VACUUM FULL. Un REINDEX va bloquer les écritures sur la table de cet index, et potentiellement certaines lectures (celles qui voudraient utiliser l'index en cours de réindexation). Donc un gros risque de contention, donc pas d'automatisation par PostgreSQL. Vous pouvez évidemment faire votre propre script de réindexation automatique... à vos risques et périls

---

Guillaume.

mortimer.pw

Membre

Re : Fragmentation des tables et indexes

Bonjour Guillaume.
Ok, merci pour les explications.
Bon week-end.