Partitionner une table et répartion des données

Version imprimable

18/05/2015, 13h33
SEPHIRA

Partitionner une table et répartion des données
Bonjour,

Je souhaiterais optimiser l'organisation d'une base de données comptables.
Je viens de découvrir la possibilité de partitionner les données d'une table qui pourrait être répondre à ce besoin.

J'ai une table ECRITURE contenant toutes les écritures comptables depuis 2006.
La table possède un champ codeExercice représentant un exercice comptable par année : chaque exercice représente environ 1million de lignes.
J'ai actuellement à ma disposition 3 grappes de disques configurées en RAID1 sur un serveur 4cores, 8Go de RAM.

Je pensais procéder comme suit :
- Créer une fonction de partition basée sur le codeExercice et anticiper sur les codes exercices à venir:
  - 2006,2007,2008,...2014, 2015, 2016, ...., 2029, 2030
- Associer à chaque partition un fileGroup différent (un fichier lié à chaque fois)
  - ecriture_2006.ndf, ecriture_2007.ndf, ..., ecriture_2030.ndf
- Répartir les fichiers de base de données comme suit :
  - fichier de log sur la grappe 1
  - fichier ecriture_2006.ndf sur la grappe 2
  - fichier ecriture_2007.ndf sur la grappe 3
  - fichier ecriture_2008.ndf sur la grappe 2
  - fichier ecriture_2009.ndf sur la grappe 3
  ...
  - fichier ecriture_2029.ndf sur la grappe 3
  - fichier ecriture_2030.ndf sur la grappe 2
En procédant ainsi, je pense optimiser les lectures/écritures des disques de manière uniforme.
Car au niveau du logiciel comptable on accède qu'en général au exercice N et N-1.

Serait-ce une bonne démarche ou bien suis-je à coté de la plaque ?
Est ce que le nombre de lignes est "suffisant" pour que le partitionnement soit nécessaire et bénéfique.

Par avance merci,
18/05/2015, 14h34
SQLpro

De manière générale c'est pas mal, mais pensez à :
1) mettre vos partitions ancienne en READ ONLY via le FG
2) prévoir la création des nouveaux groupe de fichier, fichier et partitionnement via une tâche à récurrence annuelle dans l'Agent SQL
3) bien dimensionner vos fichiers au départ pour n'avoir plus aucune opérations de croissance

Voir le chapitre 10 de notre livre à ce sujet : http://livre.fnac.com/a6434611/Frede...QL-Server-2014

A +
18/05/2015, 16h53
SEPHIRA

Merci bcp de votre réponse,

L'idée de créer par anticipation le partitionnement et les fichiers (jusqu'à l'exercice 2030 par ex) est-elle intéressante ou proscrite (sachant d'évidement il faudra que je dimensionne tous ces fichiers).
Je pars sur l’hypothèse d'ici 2030, le serveur aura évolué ou été changé et que les stratégies de stockage seront peut être différentes.
18/05/2015, 17h22
Boubou2020

Pour plus d'amélioration IO disque , chaque Groupe de fichier devrait être placé sur un disque indépendant des autres
18/05/2015, 17h55
SQLpro

Citation:

Envoyé par abdallah_mehdoini

Pour plus d'amélioration IO disque , chaque Groupe de fichier devrait être placé sur un disque indépendant des autres

Oui et non. Pour les données actives, par exemple si partitionnement par mois et donc nombreuses écritures sur les 3 derniers mois, alors oui. Sur les partitions mortes alors pas d'intérêt.

A +
18/05/2015, 18h08
SEPHIRA

Citation:

Envoyé par SQLpro

Oui et non. Pour les données actives, par exemple si partitionnement par mois et donc nombreuses écritures sur les 3 derniers mois, alors oui. Sur les partitions mortes alors pas d'intérêt.

A +

Effectivement, dans mon cas, les partitions liées aux exercices antérieurs sont quasi mortes (l'exercice comptable est clôturé).
Ne subsistera que quelques mises à jours éventuelles mais très rares (opération comptable de lettrage notamment).
Seul les exercices N et N-1 sont vraiment actifs (lecture/écriture), d'où suite à ma 1ère analyse, la suffisance de 2 grappes de disques pour les données et une grappe pour le journal...
19/05/2015, 11h41
bvesan

Bonjour,
Pour répondre à la question de la création des partitions par anticipations, je pense que c'est une bonne idée.
L'absence d'une partition n'empêchera pas l'insertion de données, mais créer une partition lorsque les données sont présente va avoir pour conséquence une migration de données. Cette opération peut être très lourde en IOs et générer un très gros volume transactionnel.

La partie Indexes n'a pas encore été abordée dans le sujet. N'oubliez pas qu'il sera sans doute intéressant de partitionner aussi vos indexes (selon le codeExercice a priori, mais peut-être plutôt selon toute autre colonne discriminante).
19/05/2015, 15h06
SQLpro

Citation:

Envoyé par bvesan

Bonjour,
Pour répondre à la question de la création des partitions par anticipations, je pense que c'est une bonne idée.

Tout dépend du moment ou tu anticipes... Par exemple avec l'Agent SQL tu peut anticiper la création d'un nouvelle partition 1, jour, une semaine ou un mois avant la date fatidique... Par exemple, je le fais 48h avant en général et dans la semaine qui précède on teste par un autre travail de l'agent s'il y a de la place et quelle place il faut en auditant le contenu réel des 3 dernières partitions et en remontant une alerte q'il n'y a pas la place.
En anticipant trop cela coûte cher en espace vide utilisé pour tes partition, car faire des partitions avec des fichiers an autogrowth c'est stupide !!!

Citation:

L'absence d'une partition n'empêchera pas l'insertion de données, mais créer une partition lorsque les données sont présente va avoir pour conséquence une migration de données. Cette opération peut être très lourde en IOs et générer un très gros volume transactionnel.

La partie Indexes n'a pas encore été abordée dans le sujet. N'oubliez pas qu'il sera sans doute intéressant de partitionner aussi vos indexes (selon le codeExercice a priori, mais peut-être plutôt selon toute autre colonne discriminante).

A +