Forums PostgreSQL.fr

guig

Membre

optimisation de l'efficacité du vacuum

Bonjour,
Je cherche à savoir s'il est possible d'optimiser la taille d'une table sans avoir à exécuter un vacuum full (la table doit restée disponible pour l'utilisateur).
Mes tables ressemblent à ceci:

CREATE TABLE event (
    id integer NOT NULL,
    "time" timestamp(6) without time zone DEFAULT now() NOT NULL,
    info text NOT NULL
);
CREATE TABLE object (
    id integer NOT NULL,
    name text DEFAULT ''::text NOT NULL
);
CREATE TABLE object_data (
    object_id integer NOT NULL,
    event_id integer NOT NULL,
    field1 double precision NOT NULL,
    field2 double precision NOT NULL,
    field3 double precision NOT NULL,
    field4 double precision NOT NULL,
    field5 double precision NOT NULL,
    field6 double precision NOT NULL
);

Fréquemment, dans une application c++, je parcours l'ensemble des event pour mettre à jour les données de chaque object dans la table object_data (implémenté à l'aide d'un "delete where event_id=?" puis d'un "copy de l'ensemble des object pour l'event concerné").
Après 5 à 6 boucles, la taille de la table a augmenté significativement et le vacuum n'a presque aucun effet.

Par exemple, pour un nombre d'entrées d'1 million (1000 events et 1000 objects), la taille de ma table object_data évolue de la façon suivante:
boucle 1: taille=81Mo
boucle 2: taille=161Mo
boucle 3: taille=242Mo
boucle 4: taille=261Mo
boucle 5: taille=322Mo
boucle 6: taille=322Mo
boucle 7: taille=322Mo

Suite à ce test, le vacuum était ensuite sans effet.
Ce comportement vous parait-il normal? est-il évitable?
Est ce qu'une modification du fillfactor peut avoir un effet bénéfique?
Vaut-il mieux laisser l'auto vacuum actif sur cette table ou bien est-il préférable de le désactiver le temps de la mise à jour de la table?
D'autres idées?

Merci d'avance

gleu

Administrateur

Re : optimisation de l'efficacité du vacuum

Fréquemment, dans une application c++, je parcours l'ensemble des event pour mettre à jour les données de chaque object dans la table object_data (implémenté à l'aide d'un "delete where event_id=?" puis d'un "copy de l'ensemble des object pour l'event concerné").

Il serait bon de faire un VACUUM entre les deux. Ça permettrait au COPY de réutiliser les emplacements libérés par les DELETE, à condition qu'il n'y ait pas d'anciennes transactions pouvant toujours voir les lignes supprimées.

Suite à ce test, le vacuum était ensuite sans effet.

C'est-à-dire ? quel effet espérez-vous ?

Ce comportement vous parait-il normal? est-il évitable?

Normal, peut être pas. Pas surprenant, en tout cas. Évitable, tout dépend de ce que vous voulez éviter. En soi, que la table fasse 81 Mo ou 322 Mo, ça n'a pas vraiment une grosse importance.

D'autre part, on a l'impression qu'il y a une stabilisation à 322 Mo. Est-ce vraiment le cas ?

Est ce qu'une modification du fillfactor peut avoir un effet bénéfique?

Une modification du FILLFACTOR est bénéfique pour les performances des mises à jour. Vous n'avez jamais parlé de mise à jour.

Vaut-il mieux laisser l'auto vacuum actif sur cette table ou bien est-il préférable de le désactiver le temps de la mise à jour de la table?

Pour moi, il est préférable de laisser l'autovacuum actif globalement ou table par table tant qu'on n'a pas démontré son côté néfaste dans un cas particulier. Ce n'est pas le cas ici.

En fait, j'en reviens à ma remarque. Quel comportement voudriez-vous ? et pour être clair, une table sans fragmentation, c'est impossible alors qu'une table à la fragmentation contenue, c'est réalisable.

---

Guillaume.

guig

Membre

Re : optimisation de l'efficacité du vacuum

Merci pour votre réponse.

Il serait bon de faire un VACUUM entre les deux. Ça permettrait au COPY de réutiliser les emplacements libérés par les DELETE, à condition qu'il n'y ait pas d'anciennes transactions pouvant toujours voir les lignes supprimées.

Je n'ai pas de transaction pouvant voir les lignes supprimées, mais pour des raisons de performance je ne peux pas me permettre de faire un vacuum entre chaque delete et copy, enfin je pense. Pour vous donner quelques détails supplémentaires:
- la table object_data contient les résultats d'un calcul qui est plus rapide (de l'ordre de 10) que l'écriture en base des résultats.
- les actions delete + copy sont faites par bloc d'event
- Pour des raisons de performance, ces écritures de bloc sont fait en parallèle
Du coup je ne vois pas trop comment faire un vacuum dans ces conditions. Si cette stratégie favorise la fragmentation de la table, avez vous des idées de modifications pour réduire la taille de ma table tout en gardant de bonne performance d’écriture?

C'est-à-dire ? quel effet espérez-vous ?

Après le VACUUM la taille de la table n'a pas diminué? Certainement parce que l'auto vacuum était déja passé.

En soi, que la table fasse 81 Mo ou 322 Mo, ça n'a pas vraiment une grosse importance.

Dans mon cas je pense que si. Dans mon exemple, j'ai 1000 event et 1000 object, en réalité les volumétries sont plus important: 10000*10000 potentiellement. Le nombre de colonne de la table est aussi plus important. J'ai plusieurs tables de ce type dans mon schéma. Et surtout, ce schéma peut être multiplié plusieurs centaines de fois.
De plus, j'imagine qu'avoir des tables fragmentées réduit les performances des select, n'est ce pas?

Quel comportement voudriez-vous ? et pour être clair, une table sans fragmentation, c'est impossible alors qu'une table à la fragmentation contenue, c'est réalisable.

En production, j'ai observé des cas où ma table après la première boucle faisait 3Go (ce qui correspondait environ à la taille de mes données plus le HeapTupleHeaderData), après 5 ou 6 boucles, j'étais à 10Go (après un vacuum).
Ceci me pose des problèmes, les requêtes select sont plus lentes et surtout la consommation d'espace disque devient très importantes. Je comprends que je ne peux pas empêcher la fragmentation, je voudrais la contenir au maximum et je ne suis pas sur que ce soit actuellement mon cas. Je suis inquiet du rapport : taille de table possible / taille de table optimale (ici 322/81 => 4).

gleu

Administrateur

Re : optimisation de l'efficacité du vacuum

Après le VACUUM la taille de la table n'a pas diminué?

Un VACUUM ne fait pas diminuer la taille d'une table. Plus exactement, il peut le faire dans un cas très spécifique. Suffisamment spécifique pour être trop rare. Seule l'option FULL permet de gagner de la place sur disque. Avec l'inconvénient dont personne ne veut, à savoir un verrou exclusif sur la table.

De plus, j'imagine qu'avoir des tables fragmentées réduit les performances des select, n'est ce pas?

Oui, si la table a du mal à tenir en mémoire. Là, 322 Mo, non quoi Maintenant, si les tables ont une volumétrie plus importante, ça peut être un soucis. Ceci dit, une table un peu fragmentée aura tendance à améliorer les performances des UPDATE.

En production, j'ai observé des cas où ma table après la première boucle faisait 3Go (ce qui correspondait environ à la taille de mes données plus le HeapTupleHeaderData), après 5 ou 6 boucles, j'étais à 10Go (après un vacuum).

La question est surtout de savoir si cette volumétrie finit par se stabiliser et à quel moment.

Pour revenir aux fondamentaux, un DELETE laissera un emplacement vide dans un bloc de la table. Sans VACUUM, ce bloc ne peut pas être réutilisé. Si vous estimez que la fragmentation ne fait qu'augmenter, c'est que vous n'exécutez pas assez de VACUUM.

---

Guillaume.

guig

Membre

Re : optimisation de l'efficacité du vacuum

La question est surtout de savoir si cette volumétrie finit par se stabiliser et à quel moment.

Oui elle se stabilise.

Pour revenir aux fondamentaux, un DELETE laissera un emplacement vide dans un bloc de la table. Sans VACUUM, ce bloc ne peut pas être réutilisé. Si vous estimez que la fragmentation ne fait qu'augmenter, c'est que vous n'exécutez pas assez de VACUUM.

Ok donc il faut que j'execute un VACUUM à la fin de chaque boucle. Et il ne faut pas que j'évite de relancer une boucle d’écriture avant que le vacuum soit fini, sinon l'espace des lignes supprimées ne peut être réutilisé.
Ainsi je devrais pouvoir maintenir un ratio "taille de table possible / taille de table optimale" inférieur à 2.