Partitionnement de grosses tables

[apad]

Bonjour

Je travaille sur la conception d'une base de données avec de gros volumes à gérer. J'ai deux types de table :
1) table avec une grande quantité d'enregistrements.
2) table avec une grande quantité d'information par enregistrement (champ LOB)
Je souhaite partitionner mes tables. Mes tables n'ont pas de données historisables donc une partition de type RANGE n'est pas adaptée. Mes tables ne contiennent pas non plus de champs avec une liste de valeur finie donc une partition de type LIST n'est pas adaptée. Il me reste donc la possibilité de faire une partition de type HASH.

Mon problème est de savoir combien de partition créer par table. Quelles questions dois-je me poser pour définir le nombre de partition idéal à ma situation ?

Table 1 : 70 millions d'enregistrements pour une taille estimée de 5 Go
Dois-je créer 100 partitions de 50 Mo mais avec 700 000 enregistements par partition ? ou plutôt 1000 partitions de 5 Mo avec 70 000 enregistrements ?

Table 2 : 47 000 enregistrements pour une taille estimée de 2,5 Go
Dois-je créer 50 partitions de 50 Mo mais avec moins de 1000 enregistrements par partition ou 5 partitions de 500 Mo avec 9400 enregistrements ?

Bref vous avez compris, je ne sais pas où positionner le curseur ...
Vos conseils seront bienvenus.

Cordialement

[orafrance]

attention, pour fonctionner correctement le hash doit porter sur une tuple unique et le nombre de partition doit être une puissance de 2 (2, 4, 8, 16, 32, etc...)

Sinon, c'est surtout le nombre d'enregistrements qui comptent et là ça dépend de la vélocité de ton instance.

[remi4444]

Moi je ferais des partitions encore plus grandes (~ 200 Mo voire plus) lorsque le nombre de partitions est déraisonablement élevé, les index partitionné commencent à faire dégrader les performances.

remarque: tu n'es pas obligé de baser une partition "range" sur une date, tu peux prendre d'autre clef si tu veux.

[orafrance]

Envoyé par remi4444

les index partitionné commencent à faire dégrader les performances.

euh... index global pourquoi pas mais partitionné j'vois pas pourquoi ?

[remi4444]

Je parle performance sur les recherches dans des cas limites de jointures entre plusieurs tables très partitionnées, oracle est parfois obligé d'aller regarder dans tous les morceaux de son index lorsque celui ci est partitionné, et lorqu'il faut faire une jointure entre 2 tables de 1000 partitions, ça commence à faire . La solution dans ces cas là est justement de faire un index global.

[apad]

Envoyé par Fred_D

attention, pour fonctionner correctement le hash doit porter sur une tuple unique et le nombre de partition doit être une puissance de 2 (2, 4, 8, 16, 32, etc...)

J'ai relu différentes doc Oracle (Oracle Database Concept, Database Administrator’s Guide et SQL References). Je ne vois nulle part que le nombre de partition doit être une puissance de 2 ni que la clé de partitionnement doit porter sur un tuple unique. Peux-tu m'indiquer tes sources sur ce point ?

Envoyé par Fred_D

Sinon, c'est surtout le nombre d'enregistrements qui comptent et là ça dépend de la vélocité de ton instance.

En fait, j'ai 2 problèmes à résoudre : la performance et le processus de sauvegarde/restauration.
Pour la performance, le nombre d'enregistrement par partition est effectivement important.
Dans l'exemple que j'ai cité précédemment, les volumes restaient gérables. Mais j'ai une table qui contiendra à terme près de 3 TeraOctets de données. Ca devient compliquer de sauvegarder (et restaurer) de tel volume donc le partitionnement me permet de segmenter. Je dois donc trouver un compromis entre un nombre de partition acceptable (<1000) et une taille de partition acceptable. Le problème ici c'est de trouver qu'elle est la taille acceptable pour une partition. Dois-je faire 100 partitions de 30 Go ou 1000 partitions de 3 Go ?

[apad]

Envoyé par remi4444

Moi je ferais des partitions encore plus grandes (~ 200 Mo voire plus) lorsque le nombre de partitions est déraisonablement élevé, les index partitionné commencent à faire dégrader les performances.

Toute la difficulté est de savoir ce qui se cache derrière le terme "déraisonablement élevé". J'ai contacté ORACLE à ce sujet. Il semble que 1000 partitions soit la limite max qu'il est conseillé de ne pas dépasser.

Envoyé par remi4444

remarque: tu n'es pas obligé de baser une partition "range" sur une date, tu peux prendre d'autre clef si tu veux.

Oui tu as raison, mon raccourci RANGE = DATE n'était pas pertinent.

[remi4444]

Moi je pense qu'à partir de 200 partions, ça commence à devenir difficile à gérer mais évidement si tu as une table de plusieurs Téra (tu l'avais pas dit sur ton premier messages) tu ne va pas y couper, il te faudra beaucoup de partitions, mais pas des partitions de 5M ! D'un autre coté, des partitions ne doivent pas etre trop grosses (~ moins de 5Go à mon avis). Tu vas avoir une table hors normes et dans ce cas, là c'est clair qu'il faut faire valider ton architecture par le support oracle.

Remarque: Si ce sont les lob qui occupent beaucoup de place, il faut absoluement que tu les mettent dans des tablespaces séparés des tables, ça te résoudra bien des problèmes....

[orafrance]

Envoyé par apad

J'ai relu différentes doc Oracle (Oracle Database Concept, Database Administrator’s Guide et SQL References). Je ne vois nulle part que le nombre de partition doit être une puissance de 2 ni que la clé de partitionnement doit porter sur un tuple unique. Peux-tu m'indiquer tes sources sur ce point ?

pour le nombre c'est un consultant de chez Oracle avec beaucoup de métier qui me l'a dit, je vais essayer de trouver mieux

Pour l'unicité de la clé il suffit de faire un test pour s'en convaincre, si tu peux avoir des doublons alors le hash sera faux et les lignes ne seront pas correctement réparties

Envoyé par apad

En fait, j'ai 2 problèmes à résoudre : la performance et le processus de sauvegarde/restauration.
Pour la performance, le nombre d'enregistrement par partition est effectivement important.
Dans l'exemple que j'ai cité précédemment, les volumes restaient gérables. Mais j'ai une table qui contiendra à terme près de 3 TeraOctets de données. Ca devient compliquer de sauvegarder (et restaurer) de tel volume donc le partitionnement me permet de segmenter. Je dois donc trouver un compromis entre un nombre de partition acceptable (<1000) et une taille de partition acceptable. Le problème ici c'est de trouver qu'elle est la taille acceptable pour une partition. Dois-je faire 100 partitions de 30 Go ou 1000 partitions de 3 Go ?

Pour la sauvegarde tu peux compresser tes partitions et mettre tes tablespaces OFFLINE pour archiver les datafiles... évidemment : compression = plus d'écriture (pour les perfs) et OFFLINE = ni écriture ni lecture

Note que le poids d'une ligne n'a aucune incidence pour le partitionnement.

[apad]

Envoyé par remi4444

Remarque: Si ce sont les lob qui occupent beaucoup de place, il faut absoluement que tu les mettent dans des tablespaces séparés des tables, ça te résoudra bien des problèmes....

Ta remarque est intéressante car j'ai ce cas dans ma base.
Je ne vais pas détailler toutes mes tables mais reprenons l'exemple de ma Table 2 : 47 000 enregistrements pour une taille estimée de 2,5 Go
Ce qui prend de la place c'est un BLOB. Si je mets mon LOB dans un tablespace séparé, ai-je toujours besoin de partitionner ma table pour 47 000 lignes ?

Envoyé par remi4444

D'un autre coté, des partitions ne doivent pas etre trop grosses (~ moins de 5Go à mon avis)

Ce seuil de 5 Go, c'est l'expérience qui te le dicte ou c'est quelque part dans une doc de référence ?

[orafrance]

Note:125314.1:

Performance Considerations for Hash Partitioning
------------------------------------------------

Hash partitions are mostly used when we do not know beforehand how much data
will map into a given range and sizes of range partitions would differ quite substantially.
Hash partitioning is an effective means of distributing data, because Oracle hashes
the data into a number of partitions, each of which can reside on a separate device.
Thus, data is evenly spread over as many devices as required to maximize I/O throughput.
The number of partitions should be a power of two (2, 4, 8, and so on) to obtain the most
even data distribution. Local indexes on hash partitions are equipartitioned with the
table data, enabling hash partitioning to be more effective in parallel index access
and PDML. Additionally, Oracle recommends hash partitioning on high cardinality key
columns, preferably unique keys.

[apad]

Envoyé par Fred_D

Pour l'unicité de la clé il suffit de faire un test pour s'en convaincre, si tu peux avoir des doublons alors le hash sera faux et les lignes ne seront pas correctement réparties .

Je crains de ne pas comprendre le point sur les doublons. Je vais prendre un exmple ça sera plus facile.
J'ai une table avec 3 champs : PERE_ID, FILS_ID et VALUE. La clé primaire de ma table est composée de PERE_ID et FILS_ID. Puis-je partitionner sur PERE_ID uniquement ? Dans ma table, je n'ai pas de doublon mais si je prend le champ PERE_ID seul il y a forcement redondance de valeur.

Envoyé par Fred_D

... mettre tes tablespaces OFFLINE pour archiver les datafiles [...] OFFLINE = ni écriture ni lecture.

Bien entendu, je ne souhaitais pas t'assomer dès le premier post avec toutes les contraintes inhérentes à mon projet mais je suis en mode haute dispo (24/24 7/7) donc le OFFLINE je vais éviter.

[apad]

Envoyé par Fred_D

Note:125314.1

Merci pour la source. J'en prends bonne note.

[remi4444]

Envoyé par apad

Ta remarque est intéressante car j'ai ce cas dans ma base.
Je ne vais pas détailler toutes mes tables mais reprenons l'exemple de ma Table 2 : 47 000 enregistrements pour une taille estimée de 2,5 Go
Ce qui prend de la place c'est un BLOB. Si je mets mon LOB dans un tablespace séparé, ai-je toujours besoin de partitionner ma table pour 47 000 lignes ?

Non, c'est tout l'interret de la séparation des lob qui sont des données de natures très différentes du reste.

Envoyé par apad

Ce seuil de 5 Go, c'est l'expérience qui te le dicte ou c'est quelque part dans une doc de référence ?

C'est juste un avis selon expérience, ça dépend beaucoup de la puissance de ta machine et de tes disques. Il faudrait voir si il y a des préconisations oracle précises en la matière.