Forums PostgreSQL.fr

bebert73

Membre

choix d'un fillfactor

bonjour,

quelles sont les valeurs à mettre dans fillfactor pour des index ou des tables souvent mises à jour ?

je pense que j'ai bien compris à quoi servait le fillfactor (j'ai notamment lu le wiki de gleu qui traite aussi de ce sujet, http://wiki.guillaume.lelarge.info/doku … resql_8.2).

en gros, quand la table est fréquemment mise à jour, il est conseillé de baisser le fillfactor...mais baisser à combien ? on fait ça au pif ? j'ai l'impression que c'est très empirique...

Tom Lane notamment dit "I'm inclined to think we should allow much lower fillfactors, maybe down to 10%. A really low fillfactor could be a good idea in a heavily updated table --- at least, I don't think we have any evidence to prove that it's not sane to want a fillfactor below 50%." (http://archives.postgresql.org/pgsql-ha … g00147.php)

Très concrètement, si vous créez une table, et que vous savez que cette table aura quelques centaines de milliers de lignes, et sera souvent mise à jour, que mettez-vous comme fillfactor sur la table ? Et si dans cette table vous mettez un index sur une colonne souvent mise à jour, que mettez vous comme fillfactor sur cet index ?

Autres questions :
- y-a-t-il un intérêt à baisser le fillfactor pour une table qui est peu mise à jour, mais avec beaucoup d'INSERT (donc beaucoup d'INSERT, peu de UPDATE)

- à part consommer de l'espace disque supplémentaire, baisser le fillfactor présente-t-il d'autres inconvénients ? (notamment sur une table sur laquelle il y a aussi beaucoup de SELECT, baisser le fillfactor ne ralentit-il pas les requêtes ? je sais pas, j'imagine que si on dispatche les données sur un nombre plus importants de pages, ça signifie aussi qu'il faut parcourir un nombre plus important de pages lors des requêtes, non ? je me trompe ?)

Dernière modification par bebert73 (03/06/2011 08:16:02)

gleu

Administrateur

Re : choix d'un fillfactor

Très concrètement, si vous créez une table, et que vous savez que cette table aura quelques centaines de milliers de lignes, et sera souvent mise à jour, que mettez-vous comme fillfactor sur la table ? Et si dans cette table vous mettez un index sur une colonne souvent mise à jour, que mettez vous comme fillfactor sur cet index ?

Aucune idée. Il faut tester.

y-a-t-il un intérêt à baisser le fillfactor pour une table qui est peu mise à jour, mais avec beaucoup d'INSERT (donc beaucoup d'INSERT, peu de UPDATE)

Aucun intérêt.

à part consommer de l'espace disque supplémentaire, baisser le fillfactor présente-t-il d'autres inconvénients ? (notamment sur une table sur laquelle il y a aussi beaucoup de SELECT, baisser le fillfactor ne ralentit-il pas les requêtes ? je sais pas, j'imagine que si on dispatche les données sur un nombre plus importants de pages, ça signifie aussi qu'il faut parcourir un nombre plus important de pages lors des requêtes, non ? je me trompe ?

Non, c'est bien ça. Petit FILLFACTOR veut dire grosse table, donc un shared_buffers plus important, un parcours séquentiel plus long, etc.

---

Guillaume.

Marc Cousin

Membre

Re : choix d'un fillfactor

Et le FILLFACTOR sera surtout intéressant à paramétrer sur une table qui recevra des mises à jour HOT. pour les autres, ça n'a pas trop d'intérêt. Et les mises à jour HOT, c'est les mises à jour qui ne touchent pas aux colonnes indexées.

---

Marc.

bebert73

Membre

Re : choix d'un fillfactor

ok merci c'est plus clair

Donc en résumé les points à retenir (vous me rectifiez si je me trompe) :

- pour une table sur laquelle on fait principalement des SELECT, des INSERT ainsi que des UPDATE sur des colonnes indexées : il faut laisser le FILLFACTOR par défaut (le baisser entraînera même une augmentation des lectures séquentielles)

- pour une table sur laquelle on fait des UPDATE de colonnes non indexées : on peut baisser le fillfactor, et il faut faire des tests pour déterminer le fillfactor optimal

Par contre qu'en est-il du fillfactor sur les index ? Si j'applique le même raisonnement, j'ai envie de dire que baisser le fillfactor n'est intéressant que pour des index appliqués sur des colonnes fréquemment mises à jour. C'est bien cela ?

Autrement dit, soit une table qu'on met souvent à jour mais uniquement sur une colonne indexée :
- on laisse le fillfactor par défaut sur la table
- on baisse le fillfactor sur l'index de la colonne fréquemment mise à jour

Exact ?

Marc Cousin

Membre

Re : choix d'un fillfactor

Pour les index, c'est un peu différent. Enfin la raison est un peu différente.

Les index, c'est un B-Tree, un arbre balancé (enfin la plupart du temps, on ne va pas parler des gin, gist, hash).

Dans l'implémentation PostgreSQL, on ne stocke des valeurs que dans les 'feuilles' de l'arbre. Quand une feuille est pleine, on doit la couper en deux. Pour cela, il faut créer une nouvelle feuille, la chaîner avec la précédente (il y a une liste chaînée bidirectionnelle entre les feuilles du btree), modifier les pointeurs (en rajouter un) du nœud pointant sur la feuille, voire couper aussi ce nœud, etc…

Les split de page sont problématiques principalement pour deux raisons: ils sont lents au moment de l'insertion (beaucoup plus d'entrées sorties, de verrouillage, etc…), et ils font que l'index n'est plus physiquement trié: la nouvelle page peut être un peu n'importe où sur le disque. Son parcours complet devient donc plus lent. Pour un split ce n'est pas bien grave, mais si c'est des milliers, ça peut le devenir. On peut d'ailleurs suivre tout ça avec le contrib pgstattuple.

Diminuer le FILLFACTOR (il est par défaut à 90), ça permet que l'index garde plus longtemps sa structure physique séquentielle, et ça limite l'occurrence des splits. Par contre, évidemment, on le paye en ayant un index beaucoup moins compact sur disque, donc un moins bon hit ratio, etc… Sachant qu'en 9.0 on peut reconstruire n'importe quel index sans verrouillage (CREATE INDEX CONCURRENTLY, puis suppression de l'ancien index), sauf ceux qui sont liés à une définition de contrainte unique ou primary key, et que cette limitation a sauté en 9.1.

Évidemment, le split n'arrive que pour des index où on insère 'au milieu de l'index'. Pour une clé technique auto-incrémentée, cela n'arrive jamais (on insère toujours au même bord de l'index), c'est une des raisons pour lesquelles elles sont d'ailleurs peu coûteuses comparativement aux autres index à l'insertion.

---

Marc.

bebert73

Membre

Re : choix d'un fillfactor

donc si j'ai bien compris, pour un index, diminuer le fillfactor n'a pas d'autres incidence négative que l'augmentation de l'espace disque (contrairement au fillfactor sur les tables, ou le fait d'augmenter le nombre de pages entraîne une baisse de perf sur les lectures séquentielles) ? Exact ?

Dans ce cas, si on n'a pas de problèmes d'espace disque, peut-on considérer que c'est une bonne pratique de baisser systématiquement le fillfactor des index à 50 par exemple ? (hormis bien sur pour les index sur les clés auto-incrémentées où ça n'a aucun intérêt)

et en plus de ça lancer de temps en temps un script qui reconstruit les index

Marc Cousin

Membre

Re : choix d'un fillfactor

Non, ce n'est pas une bonne pratique systématiquement. Les valeurs par défaut (90 pour un index, 100 pour une table) sont les meilleurs la plupart du temps.

N'optimisez que si vous avez un problème de performance. Là, à mon avis, c'est trop tôt

Si dans quelques semaines à quelques mois, vous vous rendez compte que vous avez les problèmes que j'ai évoqué plus haut, alors il sera temps de baisser le fillfactor et de reconstruire l'index. Pour le moment, partez de l'hypothèse que 90 c'est très bien.

---

Marc.

bebert73

Membre

Re : choix d'un fillfactor

ok merci, c'est compris

SQLpro

Membre

Re : choix d'un fillfactor

Un bon FillFactor dépend de 3 choses :
1) du pourcentage de données mises à jour (INSERT/UPDATE/DELETE) par rapport au volume global avant reconstruction de l'index
2) du volume des données
3) de la nature des données.

Par exemple au début de la vie de la base, les tables ont peu de données et le nombre de MAJ étant assez constant par unité de temps, la fragmentation va être importante, c'est pourquoi un FILL FACTOR assez faible est intéressant.
Si l'index est organisé en CLUSTER et que les données sont chrono ordonnées ce qui est le cas des auto incréments (SERIAL, SEQUENCE) ou de colonnes horodatées, alors la fragmentation sera très faible, voire voisine de zéro, sauf si certaines colonnes de la table sont en taille variables et que des UPDATE sur ces colonnes sont fréquents (déplacement de la ligne du fait de l'augmentation de la taille des données stockées) ou bien en cas de suppression.

Autrement dit, il est difficile de dire quel est le FILL FACTOR optimum sans auditer la fragmentation.
Ce que je peut vous dire, c'est qu'en début de vie d'une base un FF de 60 à 75 % avec défragmentation tous les jours peut être envisagé, sauf pour les CLUSTER. En fin de vie un FF de 80 à 95 % est souvent tout à fait approprié.

L'augmentation du volume de l'index n'est pas très important pour les performances, car le but des index est plus d'être lu en SEEK qu'en SCAN ce qui minimise les entrées de page d'index en mémoire de toute façons.

A noter, le split de pages évoqué par Marc Cousin n'est pas le seul mécanisme qui fragmente naturellement l'index. La suppression de ligne (DELETE) induit des slots fantômes et la mise à jour (UPDATE) est susceptible d'engendrer le déplacement des lignes (donc un SPLIT + 1 SLOT fantôme).
Pour de plus amples explications sur les phénomènes de fragmentation des index, lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/optimisati … exVLDB.pdf (en particulier chapitre 2)
Certes c'est pour SQL Server, mais les structures d'index et les algo étant similaires, les effets sont les mêmes sous PG.

A +

Dernière modification par SQLpro (05/06/2011 19:44:36)

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *

bebert73

Membre

Re : choix d'un fillfactor

ok merci, je vais lire ça à l'occasion

je retiens de tout ça qu'il vaut mieux laisser le fillfactor d'un index à sa valeur par défaut tant qu'on n'a pas de problèmes de performances, vu que de toutes façon on peut l'ajuster par la suite si le besoin s'en fait sentir, en reconstruisant l'index

à+

Marc Cousin

Membre

Re : choix d'un fillfactor

Quelques notes importantes quand même:
- Il n'y a pas d'index «cluster» (aussi appelés IOT sous Oracle) sous postgresql, il n'y a que des index «normaux» (en ce qui concerne les btree…)
- Il n'y a pas de problématique de déplacement de ligne ou de fragmentation de ligne (je présume que c'est identique au problème de chaînage sous Oracle) sous PostgreSQL, étant donnée la façon différente de faire les mises à jour. Ou on peut le voir autrement, en se disant que les lignes sont en permanence déplacées. Mais elles ne sont pas chaînées, sauf dans le même bloc, et de façon temporaire.
- Le post de Tom Lane au début est, je pense, antérieur à HOT, qui modifie pas mal l'algorithme initial, et donc la pertinence d'avoir des fillfactors vraiment très bas.

Bref, les problématiques ne sont pas strictement les mêmes entre SQL Server et PostgreSQL (encore heureux, sinon on se demanderait pourquoi on a tant de choix sur les SGBD et on s'ennuierait…). Et la question concernait PostgreSQL…

---

Marc.

bebert73

Membre

Re : choix d'un fillfactor

antérieur à HOT

c'est quoi exactement HOT ?

Et la question concernait PostgreSQL…

petite suggestion du nouvel utilisateur de ce forum que je suis : on se retrouve très souvent dans le cas ou SQLPro fait référence à "comment ça se passe dans MS SQL" (et avec souvent en sous-entendu "dans MS SQL ça marche mieux )...ce qui entraîne en retour des répliques "c'est pas le sujet", dans le meilleur des cas, et parfois même plus agressives..

C'est un forum postgreSQL, donc je suppose qu'à priori on est tous des utilisateurs de PostgreSQL, et très souvent un post qui partait dans la bonne direction finit par dériver complètement dès lors qu'un utilisateur d'une autre base lance un message du genre "mais dans la base X c'est mieux"

par contre les informations restent souvent quand même intéressantes (j'ai par exemple commencé à lire le document pdf sur la maintenance des index, c'est intéressant...ne serait-ce que pour prendre conscience qu'un index doit être maintenu !)

pourquoi ne pas tout simplement ouvrir une rubrique "Généralités sur les SGBD" ? du coup, si quelqu'un a rédigé un document ou connait un lien sur un document qu'il considère avoir un rapport à une question posée, sans toutefois y répondre directement, il pourrait lancer une seconde discussion dans cette rubrique, pour ceux que ça intéresse

dans le même ordre d'idées, je m'apprête à poser une ou deux questions qui concernent le langage SQL, donc qui ne sont pas spécifiques à PostgreSQL... pourquoi ne pas ouvrir aussi une rubrique "SQL" pour ce genre de questions ? Ca éviterait de surcharger la rubrique "Général (questions généralistes)"

Dernière modification par bebert73 (06/06/2011 10:11:09)

Marc Cousin

Membre

Re : choix d'un fillfactor

HOT, c'est pour 'Heap Only Tuples'. L'idée, c'est de créer une nouvelle version d'un enregistrement dans le même bloc que l'enregistrement lui-même (s'il y a la place bien sûr), et chaîner les deux enregistrements. Ça ne se déclenche que quand on ne modifie aucune colonne indexée: il faut que les index continuent à pointer vers le premier enregistrement pour que la liste chaînée reste valable. Ça a un très gros avantage: on n'a pas de maintenance d'index à faire pour cet update, on travaille dans le même bloc, et on peut nettoyer les listes chaînées HOT à chaque fois que le bloc est visité, sans attendre le VACUUM.

Pour ce qui est de la suggestion, tout à fait d'accord sur le diagnostic. Sauf que le message de sqlpro est assez équivoque: les fill factors préconisés sont, je n'en doute pas, des bonnes valeurs pour SQL Server. Mais je doute fortement qu'ils fonctionnent correctement avec PostgreSQL qui est très différent de ce point de vue. Et rien ne dit clairement que les fillfactors préconisés proviennent de préconisations pour SQL Server, ce qui peut embrouiller les personnes lisant le thread. Voire les amener à copier/coller ce genre de préconisations sans prendre le recul nécessaire. J'imagine mal quelqu'un lisant ce thread dans 3 mois faire le tri entre ce qui s'applique à PostgreSQL et ce qui s'applique à SQL Server (les index cluster, les chaînage d'enregistrements…).

Après, je ne sais pas si créer des nouvelles rubriques qui ne concernent pas vraiment PostgreSQL intéressera les admins du forum. En ce qui me concerne, ça ne m'intéresse que modérément, étant donné qu'il y a déjà plein de forums sur ce genre de sujets sur le net, mais mon avis est assez secondaire, je ne suis que contributeur. Et on s'est encore éloignés du sujet…

Dernière modification par Marc Cousin (06/06/2011 10:30:41)

---

Marc.

bebert73

Membre

Re : choix d'un fillfactor

Mais je doute fortement qu'ils fonctionnent correctement avec PostgreSQL qui est très différent de ce point de vue.

c'est sur qu'en lisant le document il faut prendre du recul et être conscient que ça s'applique à une autre base

J'imagine mal quelqu'un lisant ce thread dans 3 mois faire le tri entre ce qui s'applique à PostgreSQL et ce qui s'applique à SQL Server (les index cluster, les chaînage d'enregistrements…).

tout à fait d'accord...d'où l'idée de mettre ça dans une rubrique à part, pour éviter que ça ne pollue le sujet courant

Après, je ne sais pas si créer des nouvelles rubriques qui ne concernent pas vraiment PostgreSQL intéressera les admins du forum. En ce qui me concerne, ça ne m'intéresse que modérément, étant donné qu'il y a déjà plein de forums sur ce genre de sujets sur le net, mais mon avis est assez secondaire, je ne suis que contributeur.

je me suis amusé à compter le nombre de message des 2 premières pages de la rubrique "Général / Questions généralistes" qui sont en fait des questions concernant purement le langage SQL...il y en a une dizaine

Et on s'est encore éloignés du sujet…

ça, ça ne fait pas de doute !

gleu

Administrateur

Re : choix d'un fillfactor

je me suis amusé à compter le nombre de message des 2 premières pages de la rubrique "Général / Questions généralistes" qui sont en fait des questions concernant purement le langage SQL...il y en a une dizaine

Le problème est que ce forum ne serait pas utilisé, ou pas énormément. Beaucoup de questions ne sont pas posées dans les bons forums.

---

Guillaume.

bebert73

Membre

Re : choix d'un fillfactor

oui, ça c'est vrai, en général on a tendance à poser les questions dans la rubrique générale du forum