Forums PostgreSQL.fr

gde

Membre

gérer une table avec mise à jour fréquent

Bonjour,

Je débute un peu dans l'administration de postgresql et je souhaite connaitre les recommandations sur un cas un peu particulier.

J'ai une table qui est plutot petite par rapport au reste de la base de donnée (11Mo pour la table ) mais elle subit énormément d'update (~5000 lignes mise à jours par minutes). J'ai constaté que l'autovacuum passait dessus assez régulièrement. J'ai d'ailleurs passé l'autovacuum_vacuum_scale_factor à 0.1 sur cette table.
En ce moment, j'ai un vacuum et analyze qui passe sur cette table toutes les 3 minutes environs et le vacuum n'est pas très long (moins 1 seconde).
Je pense que le paramétrage sur cette table est correct pour l'autovacuum.

Par contre, l'index m'inquiète car il dégénère rapidement. En moins d'une journée, l'espace de l'index dépasse celui de la table. J'ai configurer un passage de reindexdb quotidiennement sur cette table, mais en regardant les logs, j'ai remarqué que celà entrainais des deadlocks. Au vue de la fréquence de mise à jours, ça me semble pas trop surprenant d'avoir des deadlocks.

Je ne sais pas si je peux faire mieux (surtout pour éviter les deadlocks).
Mais, de manières générale, sur quels paramètres devrais-je porter plus d'attention pour une table comme celle-ci qui se met à jours aussi fréquemment ?

merci de vos conseils

Marc Cousin

Membre

Re : gérer une table avec mise à jour fréquent

Bonjour,

Quelle version de PostgreSQL, pour commencer ? (il y a des choses qui ne seraient disponibles pour ce genre de cas de figure que dans des versions récentes)

---

Marc.

gde

Membre

Re : gérer une table avec mise à jour fréquent

oui effectivement, j'ai oublié ce gros détail.

On utilise actuellement la 9.0

Marc Cousin

Membre

Re : gérer une table avec mise à jour fréquent

Ok. Donc la première chose que j'essayerais de faire à votre place, c'est de regarder si je peux tout faire avec des mises à jour HOT (Heap Only Tuple). Grosso modo, ça veut dire voir si mes updates passés sur la table ont lieu sur des colonnes indexées ou non. Si les colonnes mises à jour ne sont pas dans des index, PostgreSQL peut faire les mises à jour «sur place» (HOT), donc sans faire grossir ni la table, ni l'index, et sans besoin de vacuum. Ça veut aussi dire qu'il est peut-être judicieux de virer des index… surtout sur une table aussi petite.

Sinon, le paramétrage de vacuum est probablement bon pour cette table. Pour ce qui est des index, si la table est de 11Mo, et que les index vont à 11Mo, laissez les grossir

Plus sérieusement, il se peut que vos index grossissent un (long) moment, et finissent par plafonner. La réutilisation des blocs est plus difficile pour les index (il faut qu'un bloc soit entièrement libre pour pouvoir être réutilisé), ce qui fait qu'ils ont davantage tendance à grossir. Après, il se peut que vous ayez une façon de modifier l'index qui fasse grossir indéfiniment l'index, mais c'est  quand même assez rares (il faut remplir les pages d'index à raz-bord pour déclencher la création de nouvelles pages, puis les vider à presque 0, mais pas tout à fait, pour que ça se produise, pour simplifier).

Un reindex ne fait pas de mal de temps en temps, mais tous les jours, il est probable que c'est trop agressif.

Dernière modification par Marc Cousin (05/03/2012 18:25:21)

---

Marc.

gde

Membre

Re : gérer une table avec mise à jour fréquent

Merci pour ces conseils,

J'ai constaté que les mise à jours "HOT" réprésentent 90% des updates.
Je vais envisager cette piste de supprimer les index.

Marc Cousin

Membre

Re : gérer une table avec mise à jour fréquent

Si sur cette table, c'est 90% des updates, c'est déjà plutôt bien. De toutes façons, il y a toujours une phase «initiale» (et après un cluster ou vacuum full) où on ne peut à nouveau plus faire de HOT. Il faut en effet qu'il reste de la place dans le même bloc pour faire la mise à jour HOT. Il y a donc toujours une phase initiale où les blocs doivent se «pourrir» un peu (avoir un peu d'espace libre) pour que le HOT se mette en route.

---

Marc.

rjuju

Administrateur

Re : gérer une table avec mise à jour fréquent

On ne peut pas jouer avec le paramètre FILLFACTOR de la table pour éviter le "temps de pourrissage" de la table quitte à sacrifier un peu d'espace disque ?
Même si dans ce cas précis ça ne serait sans doute pas très utile s'il n'y a pas de vacuum full ni cluster.

---

Julien.
https://rjuju.github.io/

Marc Cousin

Membre

Re : gérer une table avec mise à jour fréquent

Si, tu peux jouer avec le fillfactor. Mais autant dans certains cas abaisser le fillfactor d'un index peut te donner des gros gains en mise à jour (c'est cher de casser une page d'index en deux), autant pour une table j'ai jamais vu de gain. Surtout pour une table aussi petite, ça va se faire tout seul assez vite. Et oui, je suis assez flemmard en ce qui concerne les clauses de stockage sur les tables… si le moteur peut se débrouiller sans moi, qu'il se débrouille, je sirote mon thé pendant ce temps

---

Marc.