Forums PostgreSQL.fr

mortimer.pw

Membre

Vacuum full réalisé ou pas ?

Bonjour,
J'ai un serveur de prod en 9.2.3 sous CentOS 5.4.
Tout les dimanche est programmé un "vacuumdb -a -f -z", suivi d'un "reindexdb -a" puis d'un "reindexdb -s".
En regardant les logs, je vois ces traces :
2013-09-29 01:45:02 EAT [14674]: [1-1] user=[unknown],db=[unknown]  LOG:  connection received: host=[local]
2013-09-29 01:45:02 EAT [14674]: [2-1] user=postgres,db=geo [local] LOG:  connection authorized: user=postgres database=geo
2013-09-29 02:17:54 EAT [14674]: [3-1] user=postgres,db=geo [local] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14674.0", size 1073741824
2013-09-29 02:17:54 EAT [14674]: [4-1] user=postgres,db=geo [local] STATEMENT:  VACUUM (FULL, ANALYZE);
2013-09-29 02:17:54 EAT [14674]: [5-1] user=postgres,db=geo [local] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14674.1", size 217432064
2013-09-29 02:17:54 EAT [14674]: [6-1] user=postgres,db=geo [local] STATEMENT:  VACUUM (FULL, ANALYZE);
2013-09-29 02:46:00 EAT [14674]: [7-1] user=postgres,db=geo [local] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14674.2", size 1073741824
2013-09-29 02:46:00 EAT [14674]: [8-1] user=postgres,db=geo [local] STATEMENT:  VACUUM (FULL, ANALYZE);
2013-09-29 02:46:00 EAT [14674]: [9-1] user=postgres,db=geo [local] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14674.3", size 505937920
2013-09-29 02:46:00 EAT [14674]: [10-1] user=postgres,db=geo [local] STATEMENT:  VACUUM (FULL, ANALYZE);
2013-09-29 03:03:44 EAT [14674]: [11-1] user=postgres,db=geo [local] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14674.4", size 1073741824
2013-09-29 03:03:44 EAT [14674]: [12-1] user=postgres,db=geo [local] STATEMENT:  VACUUM (FULL, ANALYZE);
2013-09-29 03:03:45 EAT [14674]: [13-1] user=postgres,db=geo [local] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14674.5", size 167059456
2013-09-29 03:03:45 EAT [14674]: [14-1] user=postgres,db=geo [local] STATEMENT:  VACUUM (FULL, ANALYZE);
2013-09-29 03:20:14 EAT [14674]: [15-1] user=postgres,db=geo [local] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14674.6", size 583753728
2013-09-29 03:20:14 EAT [14674]: [16-1] user=postgres,db=geo [local] STATEMENT:  VACUUM (FULL, ANALYZE);
2013-09-29 03:26:11 EAT [14674]: [17-1] user=postgres,db=geo [local] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14674.7", size 441016320
2013-09-29 03:26:11 EAT [14674]: [18-1] user=postgres,db=geo [local] STATEMENT:  VACUUM (FULL, ANALYZE);
2013-09-29 03:26:24 EAT [14674]: [19-1] user=postgres,db=geo [local] LOG:  duration: 6081940.220 ms  statement: VACUUM (FULL, ANALYZE);
2013-09-29 03:26:24 EAT [14674]: [20-1] user=postgres,db=geo [local] LOG:  disconnection: session time: 1:41:21.943 user=postgres database=geo host=[local]
Lorsque je fais la requête suivante (sur la plus grosse de mes tables) :
select * from pg_stat_user_tables where relname='ptg_pers';
J'obtiens en réponse :
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 | last_vacuum |
last_autovacuum |         last_analyze          |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+----------+----------+--------------+-----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------+-
----------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
16800 | geo        | ptg_pers |    82518 | 212479893776 | 275806381 |   19986180460 |  22160277 |  37318526 |   6406037 |         55565 |   15752700 |    1258080 |             |
                | 2013-09-29 03:26:23.178792+03 | 2013-09-27 13:41:29.194096+03 |            0 |                0 |            27 |                23
Pas d' autovacuum, juste un autoanalyze.
Les paramètres de mon postgresql.conf sont :
#vacuum_cost_delay = 0ms
#vacuum_cost_page_hit = 1
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200
#track_counts = on
#autovacuum = on
log_autovacuum_min_duration = 300
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1
N'y aurait-il jamais d' autovacuum ?
Merci pour vos réponses.

dverite

Membre

Re : Vacuum full réalisé ou pas ?

Cette ligne:

2013-09-29 03:26:24 EAT [14674]: [19-1] user=postgres,db=geo [local] LOG:  duration: 6081940.220 ms  statement: VACUUM (FULL, ANALYZE);

indique que le vacuum full a bien été fini.

N'y aurait-il jamais d' autovacuum ?

autovacuum_count à 0 indique que non, autovacuum n'a rien fait sur cette table.

Avec le autovacuum_vacuum_scale_factor par défaut à 0.2, il faut que plus de 20% des lignes aient modifiées/effaçées pour qu'autovacuum s'occupe de la table (à strictement parler il y a aussi autovacuum_vacuum_threshold mais sur une grosse table son influence est insignifiante).

Et dans le cas présent où un VACUUM FULL est fait régulièrement, logiquement ces  20% modifiés sont comptés depuis le dernier VACUUM FULL. Si bien que si moins de 20% de la table change d'un dimanche à l'autre, il est normal que l'autovacuum n'ait jamais l'opportunité de s'en occuper.

Dernière modification par dverite (03/10/2013 17:32:46)

---

@DanielVerite
http://blog-postgresql.verite.pro/

gleu

Administrateur

Re : Vacuum full réalisé ou pas ?

J'ajoute juste une information. Un VACUUM avec l'option FULL n'est pas comptabilisé dans le last_vacuum et dans le vacuum_count. La raison est que la table est complètement reconstruite, c'est donc une nouvelle table.

---

Guillaume.

mortimer.pw

Membre

Re : Vacuum full réalisé ou pas ?

Bonjour,
Merci messieurs pour les réponses.
L'analyze lui s'exécute bien chaque semaine et sur toutes les tables, alors que certaines ne subissent aucune modification, pour quelle raison ?
Faut-il diminuer le scale_factor du vacuum à 0.1 ? ou faut-il gérer plus finement au niveau des tables ?
Si les compteurs last_vacuum et vacuum_count ne sont pas incrémentés, y-a-t'il un autre endroit où trouver l'information que le vacuum full s'est exécuté en dehors des logs ?
Comment mesurer ces 20% par semaine ? en surveillant, entre 2 dimanches, les compteurs n_tup_upd, n_tup_del et n_tup_hot_upd ? ou n_live_tup et n_dead_tup ?
Si moins de 20% de la table change chaque semaine, un vacuum est-il nécessaire ? ou est-ce que au fil des semaines la table (et ses index) se "dégrade" ?
Je crois que je vais devoir passer DBA à plein temps, lol.

Dernière modification par mortimer.pw (04/10/2013 08:08:36)

gleu

Administrateur

Re : Vacuum full réalisé ou pas ?

> L'analyze lui s'exécute bien chaque semaine et sur toutes les tables, alors que certaines ne subissent aucune modification, pour quelle raison ?

Au même moment pour toutes les tables ?

> Faut-il diminuer le scale_factor du vacuum à 0.1 ? ou faut-il gérer plus finement au niveau des tables ?

Si vous voulez plus de VACUUM, oui. Est-ce que c'est nécessaire, aucune idée.

> Si les compteurs last_vacuum et vacuum_count ne sont pas incrémentés, y-a-t'il un autre endroit où trouver l'information que le vacuum full s'est exécuté en dehors des logs ?

Non, ce n'est pas tracé et ça ne le sera pas.

> Comment mesurer ces 20% par semaine ? en surveillant, entre 2 dimanches, les compteurs n_tup_upd, n_tup_del et n_tup_hot_upd ? ou n_live_tup et n_dead_tup ?

Les 20% de la table sont comparés à n_dead_tup.

> Si moins de 20% de la table change chaque semaine, un vacuum est-il nécessaire ?

Votre configuration de l'autovacuum dit que non. Maintenant, il est tout à fait possible qu'il faudrait exécuter un VACUUM à 10% pour certaines tables, voire même à 1%, et d'autres pas.

> est-ce que au fil des semaines la table (et ses index) se "dégrade" ?

Oui, avec la fragmentation. D'où l'intérêt de bien configurer son autovacuum. Pareil avec les REINDEX.

> Je crois que je vais devoir passer DBA à plein temps, lol.

Prévoyez une formation, ça aide à progresser rapidement

---

Guillaume.

dverite

Membre

Re : Vacuum full réalisé ou pas ?

L'analyze lui s'exécute bien chaque semaine et sur toutes les tables, alors que certaines ne subissent aucune modification, pour quelle raison ?

Parce que ce n'est tout à fait les mêmes règles qui déclenchent l'auto-analyze par rapport à l'auto-vacuum.
En particulier, l'auto-analyze s'intéresse au nombre de nouvelles lignes insérés alors que l'auto-vacuum ne s'y intéresse pas.

Voir dans la doc  http://doc.postgresql.fr/9.2/maintenance.html ces formules:

limite du vacuum = limite de base du vacuum + facteur d'échelle du vacuum * nombre de lignes

Le nbre de lignes est "le nombre de lignes obsolètes depuis le dernier VACUUM", ça veut dire résultant de DELETEs ou UPDATEs

Pour analyze le même genre de formule:

limite du analyze = limite de base du analyze + facteur d'échelle du analyze * nombre de lignes

sauf qu'ici le nbre de lignes est "le nombre de lignes insérées, mises à jour et supprimées depuis le dernier ANALYZE"

Conclusion: pour une table qui subit essentiellement des d'insertions , l'analyze se déclenchera en automatique mais pas le vacuum. Apparement c'est le cas de la  table montrée dans la sortie de pg_stat_user_tables puisque l'autoanalyze_count est à 23.

Si les performances d'accès à cette table ne se dégradent pas particulièrement au cours de la semaine, il n'y a pas de raison de toucher à son paramétrage. Ca ne peut pas se faire "au fil des semaines" puisque la table est physiquement récréée toutes les semaines.
En revanche si le VACUUM FULL était abandonné, ça changerait complètement le contexte.

---

@DanielVerite
http://blog-postgresql.verite.pro/

mortimer.pw

Membre

Re : Vacuum full réalisé ou pas ?

Guillaume,
Oui, l'option -z du vacuumdb, donc les tables sont analysées, le 29/09, entre 1h45 et 3h26.
Pour les indexes, y a t'il les mêmes informations que le n_dead_tup pour les tables ? voir l'évolution de la fragmentation.
Pour la formation, j'en avais fait une il y a quelques années (8.0 ou 8.1), plutôt une introduction à l'administration. Je ne sais pas quelle formation pourrait combler mes lacunes ? plutôt sur le "fonctionnement interne", l'analyse de cas pratiques.
Bon week-end et merci encore.

gleu

Administrateur

Re : Vacuum full réalisé ou pas ?

Si vous faites un ANALYZE ou un VACUUM, que ce soit manuellement ou avec un vacuumdb dans un cron, il va traiter toutes les tables, qu'elles en aient besoin ou pas. Donc c'est normal que la date last_analyze soit renseignée pour toutes les tables.

Pour les indexes, y a t'il les mêmes informations que le n_dead_tup pour les tables ? voir l'évolution de la fragmentation.

Non, et de toute façon, cela fonctionne autrement. PostgreSQL ne peut récupérer que des blocs entiers et pas des morceaux de blocs comme avec les tables.

Pour la formation, j'en avais fait une il y a quelques années (8.0 ou 8.1), plutôt une introduction à l'administration. Je ne sais pas quelle formation pourrait combler mes lacunes ?

Une formation d'administration avancée. Vous devriez trouver votre bonheur chez EDB, 2ndQuadrant ou Dalibo (je ne cite que les sociétés qui participent à la communauté, mais vous pouvez en trouver d'autres sur le web...).

---

Guillaume.