Mise en oeuvre des index CLUSTER

Version imprimable

25/05/2018, 15h07
aras-vbo

Mise en oeuvre des index CLUSTER

Bonjour,

Pour des raisons de performance, on m'a conseillé la mise en place de CLUSTER sur certaines tables sous Postgresql 9.6.
J'aimerai avoir le retour d'utilisateurs qui auraient déjà mis en place ce mécanisme de réorganisation :
Quelle doit-être la fréquence de réorganisation ?
Sous quelle forme doit-elle se faire (trigger, tâche cron...) ?
Quelle est la charge pour le serveur ?
Quelles sont les tâches à réaliser avant et/ou après la clusterisation d'une table ? La documentation conseille un ANALYSE.
Existe-t-il des outils de simulation/monitoring qui prennent en charge CLUSTER ?

Merci par avance

Vincent
28/05/2018, 08h54
SQLpro

Les tables structurées sous forme de cluster ont été inventés à l'origine dans Ingres et exploitées pleinement dans Sybase SQL Server puis dans Microsoft SQL Server dont c'est le cœur du moteur de stockage. Il n'y a pas plus rapide pour l'accès aux données de la table par sa clef primaire. Aujourd'hui SQL Server est l'un des SGBDR les plus rapide au monde, grâce à cette technologie.
Un petit exemple :
https://www.periscopedata.com/blog/c...ver-and-oracle

Encore faut-il respecter certaines conditions pour que cette organisation en cluster soit extrêmement efficace :
la clef de l'index cluster doit être :
1) monocolonne
2) jamais NULL
3) la plus petite possible (int, bigint)
4) invariante (aucun UPDATE)
5) monotone (les valeurs successives données dans le temps doivent être en croissance ou décroissance)
Ce qui revient à dire qu'il faut un auto incrément et rien d'autre.

Avec cela vous pouvez faire des optimisatiuon de x2 à x30 pour certaines grandes tables.

A +

Bonjour SQLpro,

Merci de votre réponse.

La principale table avec un index CLUSTER est celle-ci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 CREATE TABLE SPECTRUM_S.SPECTRUM_T_SPM( SPM_ID SERIAL NOT NULL , SPM_FITS_FILE VARCHAR (100) NOT NULL , SPM_HELIO_CORRECTION HELIOCORRECTION_D NOT NULL , SPM_TELLURIC_CORRECTION CHAR (50) NOT NULL , SPM_COSMIC_REMOVAL CHAR (50) NOT NULL , SPM_NORMALISATION CHAR (50) NOT NULL , SPM_DISPLAY BOOL NOT NULL , OBJ_ID INT NOT NULL , TLS_ID INT NOT NULL , TLP_ID INT NOT NULL , SPM_EXPOSURE_START_DATETIME TIMESTAMP NOT NULL , EXPOSURE_TIME INTERVAL SECOND NOT NULL , CONSTRAINT SPM_T_PK PRIMARY KEY (OBJ_ID,SPM_ID) ) WITHOUT OIDS; ALTER TABLE SPECTRUM_S.SPECTRUM_T_SPM ADD CONSTRAINT SPM_T_FK1 FOREIGN KEY (OBJ_ID) REFERENCES CATALOG_S.OBJECT_T_OBJ (OBJ_ID); ALTER TABLE SPECTRUM_S.SPECTRUM_T_SPM ADD CONSTRAINT SPM_T_FK2 FOREIGN KEY (TLS_ID,TLP_ID) REFERENCES SITE_S.TELESCOPE_SITE_T_TLS (TLS_ID,TLP_ID);
L'index mis en place est celui, par défaut, de la clé primaire (OBJ_ID,SPM_ID), composé de 2 colonnes.
Le CLUSTER serait surtout intéressant pour accéder plus rapidement aux spectres (SPM_ID) par objet (OBJ_ID). Sur les conseils de fsmrel, j'ai réorganisé les clés primaires composites des tables concernées en plaçant systématiquement la clé OBJ_ID en premier.

En regardant chaque point que vous indiquez, je me rend compte que certains ne seront pas forcément respectés (notamment le monocolonne).
Je me pose donc les questions suivantes :
- la clusterisation est-elle intéressante sur une table de quelques dizaines voire centaines de milliers de lignes seulement ?
- N'est ce pas une contrainte inutile sachant qu'il faudra relancer la commande régulièrement pour maintenir les performances optimales ?
- N'ai-je pas intérêt à créer un index dédié sur cette table sur OBJ_ID destiné à la clusterisation et ne pas prendre l'index par défaut de la table ?

Vincent

29/05/2018, 07h48
SQLpro

Citation:

Envoyé par aras-vbo

Je me pose donc les questions suivantes :
- la clusterisation est-elle intéressante sur une table de quelques dizaines voire centaines de milliers de lignes seulement ?

Tout index fait passer le coût des recherches de linéaire à logarithmique. Avec une très petite cardinalité (quelques lignes), l'index se révèle plus couteux qu'une lecture de la table par balayage, tout simplement parce que la structure d'un index nécessite au minimum deux pages (une page racine et une page de feuille) alors que la table n'en nécessite qu'une. Avec une faible cardinalité, l'index n'apporte pas un gain sensible. mais avec une cardinalité importante, la différence est spectaculaire... Mais la question est : que vous lisiez une page ou deux, le temps de lecture sera insignifiant. mais que vous lisiez 3 pages ou 10000, la sera la différence...

Citation:

- N'est ce pas une contrainte inutile sachant qu'il faudra relancer la commande régulièrement pour maintenir les performances optimales ?

Tous les index quel qu'ils soient vont se fragmenter, soit parce que les valeurs de la clef ne sont pas monotone, soit parce que les valeurs de la clef peuvent changer, soit parce qu'il y a dans la tables de colonnes de taille variable associées à des UPDATE fréquents. Ceci n'est pas propre à l'index cluster. Donc, il faudra les défragmenter. Il n'y a guère que les tables en "heap" qui ont peu besoin d'être défragmentées (et encore, lire par balayage une table fragmentée coute plus cher et consomme plus d'espace en cache)...

Citation:

- N'ai-je pas intérêt à créer un index dédié sur cette table sur OBJ_ID destiné à la clusterisation et ne pas prendre l'index par défaut de la table ?

Peut être, mais il faut faire des essais !

Citation:

Vincent

A +