Discussion :

[Grifos]

Bonjour,
Je teste depuis quelques jours, de partitionner 2 tables de ma BDD afin d'accélérer l'exécution de certaine de mes procédure stockée. Cependant Après de nombreux test je n'arrive pas à trouver la bonne configuration de partitionnement ce qui résulte d'une perte de performance notable au niveau de l'exécution de ma requête.

Je vais poser le problème.
Je possède 2 tables de fortes volumétries
La première table étant la plus petite appelé table A, comportant 25millions de lignes (environ 1GO) et qui augmente de jours en jours.
La seconde table B de 276millions de lignes (environ 11GO)
Les deux tables représente 2 ans de données.

Chacune des lignes des deux tables possède un attribut qui est la date d'insertion dans la table.

J'ai ensuite une procédure stockée qui va sélectionner certaines valeurs comprises dans ces deux table pour une période donnée, généralement 1 mois.

J'ai donc voulu partitionner mes deux tables, en créant une partition par mois et en utilisant la date comme clé de partition(J'incrémenterais chaque mois à l'aide d'un SPLIT) et en créant un index partitionné (primary key ou nouvel index).


Cependant je n'ai pas pus révélé de gains important de performance. J'ai plutôt observé une dégradation des performances.

Je tiens à préciser que toute mes partition sont sur le même disque physique. Je sais qu'il faudrait que chacune des partitions soient sur un disque physique différent, cependant j'ai déjà 25 partitions et l'utilisation d'autant de disques n'est pas envisageable.

Malgré tout j'ai observé des gains de performance au niveau de ma requête dans certains cas.
Voici une de mes requêtes (très, très simplifiée pour l'occasion):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT
PARAM1,
PARAM2,
PARAM3,
PARAM4,
PARAM5,
PARAM6
FROM B
INNER JOIN A
ON B.date = A.date
WHERE A.date BETWEEN '01/03/2009' AND '01/03/2009'

Elle va extraire 1 jours de donnée. En faisant un test je m'aperçois qu'elle s'exécute en 1min15 sur les tables partitionnées, et en 1min30 sur les tables non partitionnées.
Si j'essaie d'extraire 2 jours, la requête s'exécute en 4min sur les tables partitionnée et 1min50 sur les tables non partitionnées.

L'execution plan me le confirme en donnant un 'query cost' plus faible pour la requête sur table partitionné pour l'extraction d'un jours de donnée et l'inverse pour 2 jours.

Je me demande donc si la performance des partitions lors de la récupération de donné a un lien avec la quantité de donnée parcouru. Ou si ce phénomène est simplement dût au fait que les partitions sont sur le même disque.
Ou est ce que finalement le partitionnement n'est pas adapté dans mon cas.

Je vous sollicite donc pour m'aider à résoudre et comprendre mon problème et si jamais il existe une autre solution pour accélérer mes requête, je suis preneur.

Cordialement

[agemis31]

Bonsoir,

Rien à voir avec les partitions, mais si vous avez la possibilité d'utiliser un index sur un int à la place de votre type date (datetime, smalldatetime, date ?),
je pense que ça irait bien plus vite.

@+

[SQLpro]

J'ai plutôt observé une dégradation des performances.

Je tiens à préciser que toute mes partition sont sur le même disque physique. Je sais qu'il faudrait que chacune des partitions soient sur un disque physique différent, cependant j'ai déjà 25 partitions et l'utilisation d'autant de disques n'est pas envisageable.

Tout ceci est parfaitement normal et conforme à ce qui devrait être. En effet un partitionnement n'a d'intérêt que si l'on place les différents fichiers sur autant d'axes physiques différents (disques indépendants) afin de faire du parallélisme. Sans cela les temps sont plus mauvais, car le plan de requêtes est d'un complexité en n (si n partition, la complexité du plan de requête est multiplié par n) et les opérations d'IO sérialisées (donc mise en file d'attente). Autrement dit, le partitionnement n'a de réel intérêt qu'en présence d'un fort volume de table, c'est à dire plusieurs centaines de Go de données dans une même table, le nombre de ligne n'ayant rien à voir !
De plus en faisant 25 partitions, vous ouvrez 25 descripteurs de fichiers au niveau de l'os. Pensez vous que 25 descripteurs de fichiers à maintenir c'est plus ou moins performant qu'un seul ? Ne serait-ce qu'en place occupé par exemple...

Ce qui est bizarre, c'est que vous être conscient de votre défaut et de ne pas accepter la réalité...

Enfin, ce n'est pas parce que vous avez partitionné vos données de table, que les autres index le sont. Il convient aussi d'étudier soigneusement le partitionnement de chaque index de la table...
Cela nécessite une étude technique approfondie des coûts de chacun des ordre SQL INSERT, UPDATE DELETE et SELECT en fonctions de leurs fréquence statistique et du nombre moyen de page à impacter...

Mais avec des tables de 11 Go pour la plus grosse, c'est à priori franchement pas adapté... surtout avec 25 partitions, c'est à dire en moyenne 440 Mo par partition !

Pour ma part je déconseille de partitionner en dessous de 60 Go ety de toute façon uniquement sur des disques physiques indépendant et exclusif (ce seul fichier dessus).

A +

[Grifos]

Merci pour ces éclaircissement.
Le fait est j'étais bien conscient de ces défaut.
J'avais aussi bien géré mes index (qui était tous partitionné) et avait bien vérifié que la requête utilisait bien le partitionnement (via le plan d'exécution).
Ce qui m'intriguais c'était que certaine requête sur les partitions étaient plus rapide que sur la table non partitionné.

Par exemple un simple:

Code :

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

SELECT * FROM A WHERE A.date BETWEEN 20090101 AND 20090104

Il m'était 4 seconde sur du non partitionné et 6 seconde sur du non partitionné.
Et si j'augmentai la taille de la période, l'écart diminuai jusqu'à ce que la requête sur du non partitionné soit plus rapide que sur le partitionné.
Et cela n'a pus qu'entrainer ma confusion.

Mais bon maintenant ceci est résolu et je vous en remercie encore

A+

PS agemis31: mon index était bien sur un int, mais j'ai changé le format de la date pour rendre le code plus explicite