Table partitionnée : comment la structurer?

[Snipah]

Bonjour à tous,

Je réfléchis actuellement sur la meilleure façon de structurer une table partitionée sur une base Oracle 10.2.0.4.
C'est une table dans laquelle seront stockées des données de performance d'un parc de serveurs, les données sont collectées par serveur et par pas de 5 minutes.
=> PERF(collectdate, parc, serveur, valeur).

On veut pouvoir répondre rapidement aux question suivantes :
- perfs sur un intervalle de dates pour un ou plusieurs parcs.
- perfs sur un intervalle de dates pour un ou plusieurs serveurs.
L'intervalle de dates ne peut pas excéder 2 jours.

Environ 1 million de lignes par jour y sont insérées, il existe 20 parcs différents et 4000 serveurs différents. Je souhaite conserver 1 an d'historique, donc environ 360 millions de lignes au total.
Je me suis tourné vers le partitioning pour obtenir de meilleures perfs en selection.

Les solutions sont multiples, et j'ai fait de nombreux tests avec 120 millions de lignes, seulement aucun ne donne de résultat vraiment tranché et au dessus du lot.

Pour comprendre la suite : "moisjour" correspond à une colonne supplémentaire qui correspond à la concaténation du mois et du jour. De type NUMBER, elle servira de clé de partition, de cette manière j'aurai une partition par jour, et je crée les 367 partitions à la création de la table et n'ai pas besoin de retoucher aux partitions au fil du temps.

Voici donc mes essais principaux, j'ai également fait de nombreux mix des ces solutions :

Code :

1
2
3
4
De base :
- Aucune partition sur la TABLE
- INDEX (collectdate, parc)
- INDEX (collectdate, serveur)

Code :

1
2
3
4
PERF_PART_1 : 
- Aucune partition sur la TABLE
- INDEX partitioné : (moisjour, collectdate, parc) PARTITION BY RANGE (moisjour)
- INDEX partitioné : (moisjour, collectdate, serveur) PARTITION BY RANGE (moisjour)

Code :

1
2
3
4
PERF_PART_2 :
- PARTITION BY RANGE (MOISJOUR)
- INDEX LOCAL : (collectdate, parc)
- INDEX LOCAL : (collectdate, serveur)

Code :

1
2
3
PERF_PART_3 :
- PARTITION BY RANGE (MOISJOUR) SUBPARTITION BY HASH (SERVEUR) SUBPARTITIONS 8
- INDEX LOCAL : (collectdate, parc, serveur)

Tout d'abord, la nouvelle colonne "moisjour" vous semble-t-elle justifiée/pertinente? Y a-t-il un autre moyen d'obtenir facilement un partitionnement par jour?

Ensuite, quelle solution vous semble la meilleure (y compris une solution que je n'aurai pas exposée ici), et pourquoi?

Lorsqu'on a le choix, y a-t-il une différence entre partitionnement BY LIST et BY RANGE?

Des suggestions/conseils divers sur l'utilisation du partitioning?


Voilà tout, en l'absence de nouveaux éléments je sais laquelle je retiendrai, mais les tests n'ayant rien donné de vraiment concluant, j'aurai aimé avoir des avis avant de me lancer.

Merci d'avance pour vos réponses, et merci également d'avoir pris le temps de me lire!
Si vous avez besoin de précisions n'hésitez pas.

[Waldar]

Est-ce qu'un serveur est unique dans son parc ?
Ou plutôt, est-ce que la PK est (collectdate, parc, serveur) ou (collectdate, serveur) ?

Vu la structure relativement courte de la table, je partirai en fait sur une IOT partitionnée par jour.

D'ailleurs la créer comme ça pourrait être performant :
(parc, serveur, collectdate, valeur)
Ça doit permettre d'activer la compression sur une ou deux colonnes.


Au niveau de la partition, je ne rajouterai pas une colonne supplémentaire, je ferai plutôt un petit bloc PL/SQL lancé dans un JOB pour gérer les partitions.

À moins que vous puissiez migrer en 11g où les partitions peuvent se créer toutes seules - mais j'imagine que c'est hors propos !

[Snipah]

Bonjour Waldar, merci pour votre réponse.

Non un serveur n'est pas unique dans son parc, il peut être rattaché à 1 ou n parcs.

Et malheureusement je ne peux pas migrer en 11g.

Pour la colonne supplémentaire, je pensais faire ça parce que le service dans lequel je suis n'a aucune compétence Oracle, donc s'il y a un problème de partition après mon départ ils auront du mal à le régler.

Je vais de ce pas essayer votre proposition, avec et sans colonne supplémentaire pour comparer, et je reviens vers vous pour dire ce qu'il en est. Merci encore.

[Snipah]

Bon, me revoici après une longue série de test. Et j'ai à nouveau du mal à en tirer des conclusions claires. Désolé d'avance si mon post est un peu indigeste!

Donc, je suis à peu près sûr d'une chose, c'est que la solution de la table partitionnée organisée en index donne des résultats bien plus satisfaisants que les tables partitionnées classiques, quelle que soit la requête testée.
Je vous remercie de m'avoir lancé sur cette piste.

Par contre, sur certaines requêtes j'ai la nette impression que les performances sont même en dessous de la table classique.
Ce qui m'amène à m'interroger sur deux choses : mon protocole de test, et la pertinence de la structure de mes tables.

Pour mes tests, je crée mes tables puis les alimente avec 3 mois de données (~120m lignes), puis calcul complet des stats.
Je lance une même requête sur chacune de mes tables, dans SQLPlus avec timing on et autotrace traceonly.
Avant chaque requête je vide le shared_pool et le buffer_cache.
Est-ce une manière correcte de procéder? En conseilleriez vous une autre?

Ensuite la structure de mes tables. Je récapitule, en laissant de côté mes essais moins fructueux.

- Table de base :

Code :

1
2
- (COLLECTDATE,SERVEUR,PARC,VALEUR)
- INDEX PK (PARC,SERVEUR,COLLECTDATE)

- IOT1, partitionnée par jour :

Code :

1
2
3
4
- PARTITION BY RANGE (moisjour)
- (PARC,SERVEUR,MOISJOUR,COLLECTDATE) COMPRESS 3
- INDEX PK (PARC,SERVEUR,MOISJOUR,COLLECTDATE)
- INDEX LOCAL (COLLECTDATE,SERVEUR)

- IOT2, partitionnée par jour (pareil sans moisjour) :

Code :

1
2
3
4
- PARTITION BY RANGE (collectdate)
- (PARC,SERVEUR,COLLECTDATE) COMPRESS 2
- INDEX PK (PARC,SERVEUR,COLLECTDATE)
- INDEX LOCAL (COLLECTDATE,SERVEUR)

La version partitionnée avec moisjour semble un peu plus rapide, en ajoutant bien sûr la condition sur le moisjour en fonction de l'intervalle de temps sélectionné, ce qui semble facile à gérer coté applicatif.

Cette table est plus performantes lorsqu'on précise le parc, le bilan est plus mitigé lorsqu'il lui faut utiliser l'index (collectdate,serveur).

Voici par exemple un tableau de stats pour une requête avec condition sur le serveur + un intervalle de date :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
	        De base         IOT1        IOT2
recursive calls	1038           22269        11094
db block gets	0              0            0
consistent gets	840            8388         9953
physical reads  149            1299         5203
redo size       0              0            0
bytes sent      64989          63337        59927
bytes received	1097	       1097         1097
sorts (memory)	41	       116	    116
sorts (disk)    0              0	    0
rows processed	836	       836	    836
 
Tps exec(s)  	0,3	       1,5	    2,13

Je vais continuer à creuser et tester d'autres solutions et d'autres index (notamment intervertir collectdate et serveur dans l'index de IOT1), mais chaque manip prend beaucoup de temps étant donné les volumétries, je ferai un retour quand ce sera un peu plus approfondi.
Je vais peut être également essayer en ajoutant un partition by hash sur le serveur, à dire vrai je sais pas trop ce que ça peut donner.

Si vous avez des conseils ou des questions n'hésitez pas.
Et encore merci Waldar pour votre aide.

[Waldar]

Il faudrait aussi comparer les plans d'exécution, car on a un nombre beaucoup plus important de lecture sur le disque avec les IOT1 et IOT2.

Je suis à peu près sûr que l'index sur collecdate n'est pas une bonne idée, vu que vous partitionnez par jour et que vous requêtez sur des intervalles de deux jours, ça ne me paraît pas très intéressant.

Idéalement votre plan d'exécution doit d'abord identifier les partitions nécessaires (deux ou trois pour un intervalle de deux jours), puis éventuellement utiliser les index locaux. Je dis éventuellement car avec les IOT la table est déjà dans la PK, c'est donc une opération non nécessaire sur la majorité de vos critères !

J'imagine que la requête avec la table de base est la plus rapide car les restrictions parc / serveur sont plus discriminants que les restrictions au jour.

Gardez à l'esprit qu'avec une IOT les autres index sont moins performants qu'en cas de table classique.

[Snipah]

En fait, l'intervalle dépend complètement de l'utilisateur, ça peut être une heure comme 2 jours.
L'interface par défaut propose les 24 dernières heures, c'est donc probablement le plus utilisé.

Et en effet le plan d'execution se déroule comme ça, par exemple pour une journée et un serveur :

Code :

1
2
3
4
5
6
7
8
9
10
11
|   0 | SELECT STATEMENT       |                          |   466 | 41008 |
1   (0)| 00:00:01 |       |       |
 
|   1 |  PARTITION RANGE SINGLE|                          |   466 | 41008 |
1   (0)| 00:00:01 |    13 |    13 |
 
|*  2 |   INDEX UNIQUE SCAN    | PK_PRF_STK_5MN_PART_IOT  |   466 | 41008 |
1   (0)| 00:00:01 |    13 |    13 |
 
|*  3 |    INDEX RANGE SCAN    | IDXL_PRF_PART_IOT_DTSERV |     1 |       |
1   (0)| 00:00:01 |    13 |    13 |

Par contre, j'admets ne pas bien saisir ce qui est à l'origine du unique scan.
(le range scan est sur l'index local (serveur,collectdate) et le unique sur l'index de clé primaire (parc,serveur,collectdate))

Question sans rapport direct, pensez vous que je devrais ajouter des sous-partitions "by hash" sur la colonne serveur? Je ne suis pas sûr que multiplier les partitions soit réellement bénéfique, et chaque nouvel essai coute beaucoup d'heures de tests!

Merci encore pour votre participation.

[Waldar]

Avec une IOT il n'y a pas de table, juste la clef primaire, c'est donc ici l'équivalent du table scan.

Je n'ai jamais vraiment eu besoin de construire des sous-partitions, maintenant je ne me vois pas vous les déconseiller formellement. J'aurai dit que sur parc ça aurait été plus cohérent, mais si vos requêtes font surtout état du serveur l'index me paraît suffisant car le critère est fortement discriminant.

[Snipah]

Bon, je vais donc partir sur cette solution de table organisée en index, qui offre d'assez loin les meilleures perfs moyennes pour l'ensemble des requêtes "témoins" :

Code :

1
2
3
4
- PARTITION BY RANGE (moisjour)
- (PARC,SERVEUR,MOISJOUR,COLLECTDATE) COMPRESS 3
- INDEX PK (PARC,SERVEUR,MOISJOUR,COLLECTDATE)
- INDEX LOCAL (SERVEUR,COLLECTDATE)

Pour les sous-partitions, je vais faire des tests pour les deux, parc et serveur, histoire de voir ce que ça donne. Je repasserai pour faire un retour là-dessus.

Merci beaucoup pour votre aide!