Vous n'êtes pas identifié(e).
Pages : 1
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.
Hors ligne
Bonjour,
Personne pour me conseiller, svp ?
Merci.
Hors ligne
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.
Hors ligne
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 ?
Hors ligne
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.
Hors ligne
Bonjour Guillaume.
Ok, merci pour les explications.
Bon week-end.
Hors ligne
Bonjour
Merci Mortimer et Guillaume pour ce post très intéressant, à partir de quel PCT_DEAD_LINE nous devons faire un vacuum full ?
Bonne journée
Hors ligne
Bonjour
Merci Mortimer et Guillaume pour ce post très intéressant, à partir de quel PCT_DEAD_LINE nous devons faire un vacuum full ?
Bonne journée
Normalement, on ne fait jamais de vacuum full.
S'il y en a besoin c'est que l'autovacuum et/ou les batches de vacuum sont mal tunés.
Hors ligne
Le nombre de lignes (ou son pourcentage) n'est pas une info suffisante. Il est plus justifié de parler en octets, et cela dépend aussi de la quantité de RAM disponible. Bref, encore une fois, difficile de donner une valeur comme ça.
Guillaume.
Hors ligne
Je suis plutôt du même avis qu'Hervé, partir sur une approche basée sur faire des VACUUM FULL plutôt que corriger la configuration autovacuum ou des tâches planifiées VACUUM me parait une mauvaise idée.
Julien.
https://rjuju.github.io/
Hors ligne
ok merci pour vos réponses
je vais travailler sur une optimisation du vacuum
Bonne après midi
Hors ligne
Pages : 1
