Discussion :

[davidjo_20165]

bonjour a tous

Question d'un débutant en Postgresql si Je procède a faire une opération du maintenance VACUUM FULL aurait 'il nécessaire de faire un REINDEX?

aussi j'ai lit plusieurs articles qui dit que cette opération est très coûteuse et que n'est très rarement nécessaire

Alors dans quel cas je doit décider de le faire sur mes bases de production

Merci d'avance pour vos lumières

[Arkhena]

Bonjour,

Il n'est pas forcément nécessaire de réindexer en permanence tous vos index. Je vous conseille d'aller voir la page du wiki sur la maintenance des index: https://wiki.postgresql.org/wiki/Index_Maintenance et de regarder les requêtes fournies ou que vous trouverez en suivant les liens sur cette page.

Cordialement,

Arkhena

[SQLpro]

bonjour a tous

Question d'un débutant en Postgresql si Je procède a faire une opération du maintenance VACUUM FULL aurait 'il nécessaire de faire un REINDEX?

VACUUM fait le ménage des lignes fantômes du fait du fonctionnement de MVCC. En effet la gestion du verrouillage optimiste de PG est effectué dans les pages des tables. En gros, chaque utilisateur travaille sur une version de chacune des lignes des tables et en cas de mise à jour (INSERT, UPDATE ou DELETE) il y a ajout d'une nouvelle version de ligne (a lire : This is why an UPDATE actually creates a new row and why DELETE doesn’t really remove the row). Au bout d'un certain temps, les versions des lignes obsolètes font enfler la base d'octets inutiles. Pour pallier à ce problème, PG fait passer une technicienne de surface nommée AUTO_VACUUM.

Le VACUUM réalise ce nettoyage mais ne compacte pas les lignes vide qui constitue des octets résiduel dans les pages (les lignes des tables et index étant stockées dans des pages de 8 Ko). À terme, ceci fait "enfler" la base et dégrade les performances.
De la même manière, les index se fragmentent naturellement à l'usage, car comme l'index est un tri de données, l'insertion d'une nouvelle entrée dans une page pleine provoque un "split" de page, c'est à dire la création d'une nouvelle page avec report de la moitié des lignes dans la nouvelle page, ce qui représente une fragmentation nouvelle.
Il n'est donc pas rare que les index (surtout) et les tables (parfois) s'avère très fragmentés ce qui pose des problématique de volumétrie et de temps de réponse.

• Volumétrie des fichiers, et donc, volumétrie de la mise en cache donc baisse du ratio de mise en page et du temps de survie des pages en cache
• Temps de réponse augmenté essentiellement pour le scan, une fragmentation de 50% voulant dire un scan deux fois plus long.

Autrefois le monde PG indiquait que cette fragmentation n'était pas importante et que l'on pouvait vivre avec... Mais ça c'était autrefois. Depuis peu, PG s'est mis à faire du scan d'index ! Et là ça change tout... Une fragmentation de 50 % veut dire un scan 2 fois plus long, 75 % 4 fois plus long et 90 % 10 fois plus. Il n'est pas rare de trouver des index fragmentés à 98 % !!!

Conclusion la réindexation devient maintenant une problématique de maintenance primordiale. Il faut donc, utiliser une méthode de défragmentation ou de reconstruction....

A +

[Arkhena]

Bonjour,

Dans la mesure du possible, on évite vacuum full. Ce ne doit pas être une tâche de maintenance régulière. Un vaccum régulier, ou mieux, un autovacuum bien configuré reste préférable. D'autre part, vacuum full ayant évolué avec les nouvelles versions de PostgreSQL, (à partir de 8.4, et plus encore avec la 9.0), le reindex n'est plus nécessaire.

Toutes les autres affirmations ne sont que péremptoires et ne reposent sur aucune documentation valable ou cas concret.

Cordialement,

Arkhena

[SQLpro]

...le reindex n'est plus nécessaire.

Ha oui ? Alors explique moi ce qui se passe dans cet exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE T_TEST_BLOAT (ID SERIAL PRIMARY KEY, DATA VARCHAR(256));
 
CREATE INDEX X ON T_TEST_BLOAT (DATA);
 
INSERT INTO T_TEST_BLOAT (DATA) 
SELECT 'toto'
FROM   generate_series(1, 100000);

Pour visualiser les fragmentations des index, contrib pgstattuple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE EXTENSION pgstattuple;

Etat de départ

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM pgstatindex('X') --> index_size : 2 809 856

Suppression de 10% des lignes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DELETE FROM T_TEST_BLOAT WHERE MOD(ID, 10) = 1;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM pgstatindex('X')

--> index_size : 2809856 (inchangée)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

VACUUM;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM pgstatindex('X')

--> index_size : 2809856 (inchangée alors que 10 % des lignes ont été supprimées)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE T_TEST_BLOAT SET DATA = 'rantanplan' WHERE MOD(ID, 10) = 2;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM pgstatindex('X')

--> index_size : 3 186 688 (augmentation !)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

VACUUM;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM pgstatindex('X') --> index_size : 3186688 (inchangé)

VACUUM inefficace !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE T_TEST_BLOAT SET DATA = NULL WHERE MOD(ID, 10) = 3;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM pgstatindex('X')

--> index_size : 3489792 (augmentation)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

VACUUM

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM pgstatindex('X')

--> index_size : 3489792 (inchangé)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

REINDEX INDEX X;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SELECT * FROM pgstatindex('X')

--> index_size : 2121728 (diminué)

Donc, selon vous le REINDEX ne sert a rien ?

Pour info : SELECT version() --> "PostgreSQL 9.5.3, compiled by Visual C++ build 1800, 64-bit"
Évidemment, sous MS Windows 10... Alors on va encore me dire que sous Linux.... !

A +

[Arkhena]

Bonjour,

Il n'est jamais aisé de comprendre le ton d'une conversation écrite sur un forum, mais j'ai l'impression que vous êtes légèrement agressif...

Afin d'apaiser la situation et de revenir sur le sujet qui préocuppe ce post, je propose donc de recentrer le débat sur la question initiale de davidjo_20165:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

si Je procède a faire une opération du maintenance VACUUM FULL aurait 'il nécessaire de faire un REINDEX?

La routine de maintenance conseillée est l'autovacuum. Cette tâche s'acquittera également de la maintenance des statistiques.
Ensuite, si vous constatez des soucis de performances et que votre base de données subi des grosses suppressions de données, vous pourrez vous poser la question de regarder le taux de fragmentation de vos index. Si vous constatez alors qu'un ou plusieurs de vos index est(sont) fragmenté(s), il faudra voir pour mettre en place une réindexation des index qui se dégradent avec une périodicité suffisante.
Pour ce qui concerne le vacuum full, c'est la même chose, si votre base de données subit de grosses suppressions de données, vous pouvez vous poser la question de lancer un vacuum full.

Cordialement,

Arkhena

[SQLpro]

Bonjour,

Il n'est jamais aisé de comprendre le ton d'une conversation écrite sur un forum, mais j'ai l'impression que vous êtes légèrement agressif...

Remettons les choses dans leur contexte...

.. le reindex n'est plus nécessaire.

Toutes les autres affirmations ne sont que péremptoires et ne reposent sur aucune documentation valable ou cas concret.

Donc ce que j'affirmais était péremptoire selon vous et le REINDEX absolument pas nécessaire. Or je viens de prouver le contraire dans l'exemple que je vous ai donné et vous me trouvez agressif ???

Afin d'apaiser la situation et de revenir sur le sujet qui préocuppe ce post, je propose donc de recentrer le débat sur la question initiale de davidjo_20165:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

si Je procède a faire une opération du maintenance VACUUM FULL aurait 'il nécessaire de faire un REINDEX?

Donc, la réponse est bien celle que j'ai donné : oui, dans bien des cas, après avoir mesuré la fragmentation des index...

La routine de maintenance conseillée est l'autovacuum. Cette tâche s'acquittera également de la maintenance des statistiques.
Ensuite, si vous constatez des soucis de performances et que votre base de données subi des grosses suppressions de données, vous pourrez vous poser la question de regarder le taux de fragmentation de vos index. Si vous constatez alors qu'un ou plusieurs de vos index est(sont) fragmenté(s), il faudra voir pour mettre en place une réindexation des index qui se dégradent avec une périodicité suffisante.
Pour ce qui concerne le vacuum full, c'est la même chose, si votre base de données subit de grosses suppressions de données, vous pouvez vous poser la question de lancer un vacuum full.

Cordialement,

Arkhena

Les statistiques n'ont rien à voir avec l'accroissement du volume et ne pourrons pas diminuer le temps de parcours d'index fragmentés....

De plus l'AUTOVACUUM provoque de nombreux blocages et verrous mortels dans les DB un peu chargées. Raisons pour laquelle les utilisateurs des grosses BD PG arrêtent le service des données afin de procéder à ce genre de maintenance... Même si l'AUTOVACCUM a été notablement amélioré avec la 9.6, ça reste un problème !

A +

[davidjo_20165]

merci bien SQLPRO