Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
#1 01/04/2014 17:51:42
- obernhard
- Membre
taille de Table et espace libre croissant malgré deletes régulier
Bonjour,
Je travaille sous postgresql 9.3 et j'observe le phénomène suivant que je ne parviens pas à expliquer (et à résoudre autre que par un vacuum full sur table) :
J'ai une table présentant la structure suivante :
monitor=# \d pg_snap_stat_all_tables
Table "public.pg_snap_stat_all_tables"
Column | Type | Modifiers
-------------------+-----------------------------+-----------
snapid | integer | not null
begin_snaptime | timestamp without time zone |
end_snaptime | timestamp without time zone |
datid | oid | not null
datname | name | not null
relid | oid | not null
schemaname | name | not null
relname | name | not null
seq_scan | bigint |
seq_tup_read | bigint |
idx_scan | bigint |
idx_tup_fetch | bigint |
n_tup_ins | bigint |
n_tup_upd | bigint |
n_tup_del | bigint |
n_tup_hot_upd | bigint |
n_live_tup | bigint |
n_dead_tup | bigint |
last_vacuum | timestamp without time zone |
last_autovacuum | timestamp without time zone |
last_analyze | timestamp without time zone |
last_autoanalyze | timestamp without time zone |
vacuum_count | bigint |
autovacuum_count | bigint |
analyze_count | bigint |
autoanalyze_count | bigint |
Indexes:
"pk_pg_snap_stat_all_tables" PRIMARY KEY, btree (snapid, datid, datname, relid, schemaname, relname)
Foreign-key constraints:
"ref_snapid" FOREIGN KEY (snapid) REFERENCES pg_snap_snapshots(snapid)
(La pk n'est pas optimale, mais c'est un point de détail que je reverrai plus tard). Je met en place un system de snapshots à intervalles de temps réguliers des différentes tables pg_stat_xxxx et je stocke ces snapshots dans des tables pg_snap_xxxx.
L'alimentation de ma table s'effectue à intervalle de temps régulier en suivant le protocole suivant :
1) insertion des records en provenance de la table pg_stat_all_tables => celà génère un snapshot d'à peu près 3000 lignes
2) delete de tous les records antérieurs aux 30 derniers snapshots réalisés
Je m'attend bien évidemment à avoir des dead tuples et par voie de conséquence, une taille de table qui grossit, malgré les suppressions régulières de records.
Par conséquence le vacuum est activé au niveau base mais également au niveau table afin de m'assurer que les dead tuples sont bien supprimés.
Et c'est effectivement le cas, je maintiens un nombre de dead tuples avoisinant les 4000 records.
Problème :
1) La taille de la table ne cesse de croître alors que le nombre de dead tuples reste stable
2) l'espace libre non utilisé ne cesse de croître : il n'est donc pas réutilisé.
Auriez-vous une piste permettant d'expliquer que l'espace libre n'est pas réutilisé ?
Je précise qu'un full vacuum sur la table entraine la libération de l'intégralité de l'espace libre non occupé.
Ce que je ne comprend pas, c'est la non réutilisation de l'espace libre libéré par un vacuum classique, sachant que celui-ci semble fonctionner correctement puisque le nombre de dead tuples stagne.
Par avance merci pour vos avis éclairés.
Hors ligne
#2 01/04/2014 22:52:09
- gleu
- Administrateur
Re : taille de Table et espace libre croissant malgré deletes régulier
Il peut y avoir des très nombreuses raisons. Par exemple, vous pouvez avoir des transactions ouvertes pendant le VACUUM, ce qui empêche la libération des lignes mortes. Vous pouvez aussi avoir tellement d'insertions que vous ne le voyez pas ré-utiliser l'espace libre avant de faire de nouveau grossir la table. Ce que vous avez supprimé ne permet pas d'avoir suffisamment d'espace contigue pour y stocker une des nouvelles lignes. Etc, etc.
Seulement 4000 lignes mortes, chiffre stable, ça me donne plutôt l'impression d'un système sain, qui voit des VACUUM exécutés de temps en temps.
Guillaume.
Hors ligne
#3 03/04/2014 16:01:12
- obernhard
- Membre
Re : taille de Table et espace libre croissant malgré deletes régulier
Il peut y avoir des très nombreuses raisons. Par exemple, vous pouvez avoir des transactions ouvertes pendant le VACUUM, ce qui empêche la libération des lignes mortes. Vous pouvez aussi avoir tellement d'insertions que vous ne le voyez pas ré-utiliser l'espace libre avant de faire de nouveau grossir la table. Ce que vous avez supprimé ne permet pas d'avoir suffisamment d'espace contigue pour y stocker une des nouvelles lignes. Etc, etc.
Seulement 4000 lignes mortes, chiffre stable, ça me donne plutôt l'impression d'un système sain, qui voit des VACUUM exécutés de temps en temps.
Bonjour Merci pour votre réponse. J'y répond tardivement car j'ai pris le temps d'investiguer le problème avec un de mes collègues et nous avons établi le diagnostic suivant :
Tout d'abord j'ai omis de vous préciser que j'utilise la contrib http://pgxn.org/dist/worker_spark/ qui reprend le code de la contrib worker_spi.
Cette contrib me permet de déclarer un worker s'executant à la fréquence de mon choix et qui execute la fonction que je fournis en paramètre.
La fonction que j'ai écrite réalise des snapshots de la plupart des vues fournissant des métriques de fonctionnement de postgresql. Pour les vues cumulatives, cette fonction effectue également un snapshot différentiel entre les deux snapshots les plus récents (j'ai également introduit la contrib pg_proctab afin de collecter des métriques OS pour chaque backend de la base.
Venant-on à la source du problème : Lorsque ma fonction s'execute au travers du worker, les tuples insérés, deleté, updatés ne sont pas mis à jour. Alors qu'ils le sont lorsque j'execute ma fonction directement sans passer par le background worker. Du coup, l'auto analyze ne se déclanche jamais pour les tables populées au travers de la fonction executée par le background worker.
Je ne sais pas pourquoi la modification des tuples nécessaire au déclanchement de l'auto analyse ne se fait lorsque la fonction est appelée par le background worker. Le seul contournement que j'ai pu faire en l'état, consiste à executer les ordres sql d'insertion, mise à jour, suppression au travers d'un dblink sur la base elle meme. Dans ce cas, les modifications sur les tuples sont bien effectuées et l'auto analyze se déclanche, suivi de l'auto vacuum.
J'ai vu que vous aviez également codé un background worker pour collecter des snapshots sur la vue pg_stat_bg_writer. Auriez-vous rencontré un problème similaire, et sinon auriez-vous une explication concernant l'absense de mise à jour des tuples lorsque la fonction est executée par le background worker ?
Par avance Merci.
Hors ligne
#4 03/04/2014 17:47:45
- obernhard
- Membre
Re : taille de Table et espace libre croissant malgré deletes régulier
Il peut y avoir des très nombreuses raisons. Par exemple, vous pouvez avoir des transactions ouvertes pendant le VACUUM, ce qui empêche la libération des lignes mortes. Vous pouvez aussi avoir tellement d'insertions que vous ne le voyez pas ré-utiliser l'espace libre avant de faire de nouveau grossir la table. Ce que vous avez supprimé ne permet pas d'avoir suffisamment d'espace contigue pour y stocker une des nouvelles lignes. Etc, etc.
Seulement 4000 lignes mortes, chiffre stable, ça me donne plutôt l'impression d'un système sain, qui voit des VACUUM exécutés de temps en temps.
Nous avons également testé avec votre contrib, et à moins que nous soyons passé à côté de quelque chose, nous faisons le meme constat : autant les stats concernant les scans sont populées, autant les infos concernant les tuples insérés, modifiés, deletés ne sont pas populées, ce qui, à moins d'avoir mal compris ce qui déclanche un auto analyse (puis par voie de conséquence un auto vacuum si besoin de nettoyer), ne permet en aucun cas de déclancher un auto analyze, et donc pas d'auto vacuum.
par avance merci.
Olivier
Dernière modification par obernhard (03/04/2014 17:50:27)
Hors ligne
#5 03/04/2014 18:18:36
- obernhard
- Membre
Re : taille de Table et espace libre croissant malgré deletes régulier
Il peut y avoir des très nombreuses raisons. Par exemple, vous pouvez avoir des transactions ouvertes pendant le VACUUM, ce qui empêche la libération des lignes mortes. Vous pouvez aussi avoir tellement d'insertions que vous ne le voyez pas ré-utiliser l'espace libre avant de faire de nouveau grossir la table. Ce que vous avez supprimé ne permet pas d'avoir suffisamment d'espace contigue pour y stocker une des nouvelles lignes. Etc, etc.
Seulement 4000 lignes mortes, chiffre stable, ça me donne plutôt l'impression d'un système sain, qui voit des VACUUM exécutés de temps en temps.
Après avoir relu la doc, il nous semble que le custom background process ne transmet pas les informations au statistic collector.
Au lieu de passer par des dblink, je me contente de lancer des commandes analyze dans ma fonction. De sorte que l'auto vacuum se déclanche comme souhaité.
Comme en plus je collecte et centralise pour chaque base du cluster, les stats en provenance des tables pg_stat_all_tables/indexes/sequences et pg_statio_xxxx, j'étais déjà obligé de passer par un dblink pour effectuer la centralisation. Utiliser Analyze table me simplifie donc la tâche.
Par contre, j'aimerais bien avoir votre avis sur cet apparent problème de non transfert des stats au statistic collector.
Hors ligne
#6 03/04/2014 22:59:34
- gleu
- Administrateur
Re : taille de Table et espace libre croissant malgré deletes régulier
Les background workers utilisent SPI pour exécuter des requêtes. Ils ont une connexion directe à la base de données de leur choix. En gros, ils sont leur propre backend process. De ce fait, les actions de leur requêtes ne sont pas comptabilisées dans les statistiques d'activité. C'est généralement un plus car ça évite de mélanger l'activité système (ce qu'est un background worker) et l'activité utilisateur (qui passe par une connexion standard, généralement de la libpq vers un backend process). Seule est comptabilisée l'activité utilisateur.
Si j'ai bien compris, c'est le background worker qui effectue la purge dans votre cas. Dans ce cas, à lui de voir s'il dépasse la limite (soit une limite propre soit celle de l'autovacuum) pour qu'il lance de lui-même les actions de maintenance appropriées.
Guillaume.
Hors ligne