Le partitionnement des données est à la mode depuis que récemment MySQL s’est mis à faire ce qu’Oracle, DB2 ou SQL Server font depuis des années. Et ce sujet est devenu populaire. Mais partitionner sans tenir compte des impacts collatéraux peut se révéler bien pire que de ne pas partitionner du tout. Montrons à l’aide d’un exemple les bienfaits et les méfaits du partitionnement des données des tables dans un SGBDR.
1 – L’idée :
L’idée du partitionnement vient d’un constat simple : moins on a de données à traiter, plus vite cela ira. Ainsi en saucissonnant une table en plusieurs plus petite peut-on espérer avoir un gain… C’est vrai, mais pas toujours !On pourra ainsi découper une table en plusieurs plus petites par le biais d’un numéro, d’une date, d’un nom. Par exemple on peut découper une table contenant une date par années, une table contenant des noms par autant de lettre initiales que l’alphabet en comporte, etc….
Bien entendu tout partitionnement de données doit respecter la règle n°11 de Codd (voir http://sqlpro.developpez.com/SGBDR/ReglesCodd/SQL_databases_SGBDR_regles_codd.pdf ) qui signifie que les requêtes et plus généralement les applications doivent demeurer inchangées lorsqu’est entrepris sur une table une mécanique de partitionnement. Autrement dit le partitionnement est un problème de DBA, mais ni de développeur, ni même le modélisateur, qui doivent rester ignorant de ce genre de chose et n’avoir aucun impact sur leurs métiers respectifs, même si l’architecte de données peut l’avoir conçu au cours de la révision du MPD.
Définitions :
Dans la suite de cet article, nous appellerons bornes les valeurs pivots du partitionnement et infopartition les données (une ou plusieurs colonnes) qui permettent de ranger les lignes d’un côté ou de l’autre des bornes.
2- Les conditions :
Le partitionnement n’est possible que dans certaines conditions :
2.1 – l’infopartition n’est pas NULLable.
En effet, la présence du marqueur NULL dans une des colonnes de l’infopartition ne permet pas de résoudre le problème du rangement de la ligne dans une partition. « indéterminisme ».
2.2 Рle partitionnement doit concerner la table tout enti̬re
En effet on ne peut concevoir que certaines colonnes fassent partie d’une partition et pas d’une autre. Dans ce cas, il y a un problème de conception du modèle relationnel à la base. Enfin, si la table est organisée en cluster, alors c’est le cluster qui doit être partitionné
2.3 Рles valeurs des bornes doivent ̻tre connues
Il n’est pas possible de concevoir un partitionnement dynamique dont les bornes seraient calculées par une formule quelconque. Par exemple par une formule mathématique combinant un calcul mathématique ou algébrique…
2.4 – l’infopartition doit être déterministe
Il ne faut pas que les données fluctuent en fonction de critères externe, ce qui revient à dire que les colonnes participant à l’infopartition ne doivent pas être calculées, notamment par des fonctions non déterministes.
Le coût du partitionnement est bénéfique si :
2.5 Рla volum̩trie des donn̩es est cons̩quente
Il ne sert a rien de partitionner des tables si la volumétrie des données est minime. Il ne sert à rien de prévoir un tel partitionnement tant que les données ne sont pas suffisamment volumineuses. La volumétrie étant calculé en nombre d’octets ou bien en page de données et non en nombre de ligne. Ainsi une table de 10 millions de lignes constituées de 3 entiers, soit 12 octets de lignes, c’est à dire 120 Mo au total (ce qui représente dans SQL Server 15000 pages) n’est pas du tout une bonne candidate au partitionnement.
2.6 – les partitions sont gérées par des « storages »
Chaque partition peut résider dans un espace de stockage (ou storage) contrôlable en taille, stratégie de croissance,placement et dynamicité.
Par exemple on aura intérêt à placer chaque partition sur un axe physique différent, si chacune à des chances d’être manipulées à même fréquence, ou encore d’un côté sur une partition en lecture seule et l’autre en lecture écriture si la partition a pour but l’archivage des données.
On doit tout de suite noter que MySQL ne propose aucun moyen de contrôler les « storages » à ce niveau, ce que signifie que le partitionnement dans MySQL s’avère plus un poudre aux yeux que réel gain. En effet en cas de mise à jour simultané de différentes partitions stockées au même endroit, cela conduira à de la contention au niveau du disque…
2.7 Рcertains index sont eux aussi partitionn̩s
Partitionner une table sans se poser la question du partitionnement des index peut s’avérer grandement contre performant pour certaines requêtes.
En effet, l’utilisation d’un index pour une requête conduit souvent à la lecture de la table, notamment si l’index n’est pas couvrant ou le prédicat de filtrage à moitié « sargeable ». Il est donc intéressant de se poser la question de l’alignement des index et de la table au sein du même partitionnement. Mais ce n’est pas toujours le cas !
2.8 Рcertaines tables interrog̩es syst̩matiquement de mani̬re conjointe sont elles aussi partitionn̩s avec le m̻me crit̬re dans les m̻mes storages
Il existe de nombreux cas ou des tables sont utilisées conjointement dans de nombreuses requêtes. Il est donc intéressant si l’on partitionne l’une des tables de se poser la question de partitionner dans les mêmes espaces de stockage l’autre table. Par exemple une table des entête de facture et sa table fille des lignes de facture…
Pour ces deux derniers points (2.7 et 2.8) n’oubliez pas que les données sont stockées dans des pages regroupées par extensions (dans SQL server un bloc de 8 pages) et que les lectures physiques se font par extension et non pas par page, ce qui signifie que la demande de lecture physique d’une ligne d’une table à de grande chance de remontée en mémoire 8 pages (lectures anticipées), qui, si elles sont bien organisées, risquent de contenir déjà les données des autres tables et index nécessaire à l’exécution du plan de requête.
2.9 Рtoutes les requ̻tes inclues toujours le crit̬re de partitionnement dans le filtrage
En effet, si la plupart des requêtes n’incorporent pas le critère de partitionnement de manière systématique, alors cela revient à scruter toutes les partitions et s’avère souvent plus couteux que de le faire sur une table non partitionnée.
De loin, cette dernière condition est la plus importante pour espérer obtenir un bénéfice quelconque du partitionnement.
3 – Principe :
Voici le principe sous MS SQL Server
3.1 Рcr̩ation des espaces de stockage du partitionnement
-- création des storages :
ALTER DATABASE MaBase
ADD FILEGROUP DATA_PART_01;
ALTER DATABASE MaBase
ADD FILEGROUP DATA_PART_02;
ALTER DATABASE MaBase
ADD FILEGROUP DATA_PART_03;
ALTER DATABASE MaBase
ADD FILEGROUP DATA_PART_04;
ALTER DATABASE MaBase
ADD FILEGROUP DATA_PART_05;
-- ajouts de fichiers aux storages :
ALTER DATABASE MaBase
ADD FILE (NAME = 'F_PART_01',
FILENAME = 'E:\DatabasesSQL\Partitions\F_part_01.ndf',
SIZE = 150 GB,
FILEGROWTH = 10 MB)
TO FILEGROUP DATA_PART_01;
ALTER DATABASE MaBase
ADD FILE (NAME = 'F_PART_02',
FILENAME = 'F:\DatabasesSQL\Partitions\F_part_02.ndf',
SIZE = 150 GB,
FILEGROWTH = 10 MB)
TO FILEGROUP DATA_PART_02;
ALTER DATABASE MaBase
ADD FILE (NAME = 'F_PART_03',
FILENAME = 'G:\DatabasesSQL\Partitions\F_part_03.ndf',
SIZE = 150 GB,
FILEGROWTH = 10 MB)
TO FILEGROUP DATA_PART_03;
ALTER DATABASE MaBase
ADD FILE (NAME = 'F_PART_04',
FILENAME = 'H:\DatabasesSQL\Partitions\F_part_04.ndf',
SIZE = 150 GB,
FILEGROWTH = 10 MB)
TO FILEGROUP DATA_PART_04;
ALTER DATABASE MaBase
ADD FILE (NAME = 'F_PART_05',
FILENAME = 'I:\DatabasesSQL\Partitions\F_part_05.ndf',
SIZE = 150 GB,
FILEGROWTH = 10 MB)
TO FILEGROUP DATA_PART_05;
5 storage ont été créé et dans chacun de ces espaces de stockage on a créé un fichier de 150 Go avec uns stratégie de croissance par pas de 10 Mo. Notez que chaque fichier est créé sur un disque différent, qui doit être physique (une partition de disque, comme un LUN de SAN « taillé dans la masse » n’aura aucun effet bénéfique).
3.2 - création d’une fonction de partitionnement
CREATE PARTITION FUNCTION F_P_FAC_DATE (DATE)
AS
RANGE RIGHT
FOR VALUES ('2008-01-01', '2009-01-01', '2010-01-01');
Notez que l’on a créé 3 valeurs pivot ce qui impose au moins 4 partitions :
- partition 1, de – l’infini à 2007-12-31
- partition 2, de 2008-01-01 Ã 2008-12-31
- partition 3, de 2009-01-01 Ã 2009-12-31
- partition 4, de 2010-01-01 Ã + l’infini
RANGE RIGHT signifie que la valeur borne est incluse dans la partition à droite.
Le paramètre passé en argument est un type de données SQL équivalent au type de données de l’infopartition.
3.3 - création du schéma de partitionnement
CREATE PARTITION SCHEME F_S_FAC_DATE
AS PARTITION F_P_FAC_DATE
TO (DATA_PART_01, DATA_PART_02, DATA_PART_03, DATA_PART_04, DATA_PART_05);
Notez que le schéma de partitionnement fait référence à la fonction de partitionnement.
Il inclus les 5 espaces de stockage définis précédemment, c’est à dire les 4 nécessaire au schéma de partitionnement, et un storage supplémentaire de réserve pour des travaux ultérieurs.
3.4 - création de l’objet partitionné
CREATE TABLE T
(COLONNE1 ....
...
COLONNE_DATE DATE NOT NULL,
...
COLONNEn ...)
TO F_S_FAC_DATE (COLONNE_DATE);
La table crée possède une colonne qui servira de partitionnement ayant un type DATE et non NULLable.
C’est cette colonne qui est passée en argument de la fonction de partition de la clause TO pour préciser que la table est stockée sur un système partitionné.
4 – gestion du partitionnement
Un système de partitionnement n’a pas d’intérêt sans les outils pour l’administrer. Pour ce faire, il doit être possible de :
- rajouter une partition (ALTER PARTITION FUNCTION … SPLIT RANGE …)
- fusionner deux partitions contiguës (ALTER PARTITION FUNCTION … MERGE RANGE …)
- voire, échanger le contenu de deux partitions (ALTER TABLE … SWITCH PARTITION …)
Sous MS SQL Server, ces opérations étant faites à bas niveau et SQL Server rend la main immédiatement après le lancement de ces ordres SQL. Les utilisateurs peuvent continuer à travailler comme si de rien n’était.
Enfin, il est souhaitable d’obtenir certaines informations de méta données sur le partitionnement. Comme, quelles sont les partitions, quels objets sont partitionnés (sys.partitions, sys.partition_functions, sys.partition_parameters, sys.partition_range_values), dans quelle partition se situe telle ou telle ligne de la table ($partition.FonctionDePartitionnement(LaColonnePartitionnée)).
5 – Comparaison MS SQL Server avec les autres SGBDR…
A la question d’un internaute :
A-t-on la même flexibilité avec SQL Server et son méchanisme de schéma de partition qu’avec Oracle (pour citer l’exemple qui supporte la plupart de méthodes) ?
Autrement dit, puis-je utiliser les méthodes BY LIST, BY RANGE et BY HASH sur SQL Server?
la réponse est bien évidemment oui :
BY LIST et BY RANGE correspondent à ce que fait le schéma de partitionnement associé aux commandes :
ALTER PARTITION FUNCTION ... MERGE RANGE ...
Quand au partitionnement par hachage il n’est pas implémenté directement sur SQL Server (pas d’index en hash, donc pas de partitionnement direct sur hachage).
Vous pouvez néanmoins implémenter un hachage manuel à l’aide d’une fonction comme CHECKSUM, BINARY_CHECKSUM ou HashBytes et une colonne calculée dans la table.
Il est donc possible de faire une partition sur une clef de hachage.
Démonstration :
-- création de la base
CREATE DATABASE DB_TEST;
GO
-- ajout d'un espace de stockage supplémentaire
ALTER DATABASE DB_TEST
ADD FILEGROUP FG_H1;
GO
-- ajout d'un fichier à l'espace de stockage supplémentaire
ALTER DATABASE DB_TEST
ADD FILE (NAME = 'F_H1',
FILENAME = 'C:\DATABASES\FH1.mdf',
SIZE = 2 Mb)
TO FILEGROUP FG_H1;
GO
-- ajout d'un 2e espace de stockage supplémentaire
ALTER DATABASE DB_TEST
ADD FILEGROUP FG_H2;
GO
-- ajout d'un 2e fichier au 2e espace de stockage supplémentaire
ALTER DATABASE DB_TEST
ADD FILE (NAME = 'F_H2',
FILENAME = 'C:\DATABASES\FH2.mdf',
SIZE = 2 Mb)
TO FILEGROUP FG_H2;
GO
-- création de la fonction de partitionnement
CREATE PARTITION FUNCTION PF_H (INT)
AS RANGE LEFT
FOR VALUES (0);
GO
-- création du schéma de partitionnement
CREATE PARTITION SCHEME PS_H
AS PARTITION PF_H
TO (FG_H1, FG_H2);
GO
-- création de la table partitionnée
CREATE TABLE T_PRT
(PRT_DATA VARCHAR(64) NOT NULL,
PRT_H AS CHECKSUM(PRT_DATA) PERSISTED)
ON PS_H(PRT_H)
En effet, il suffit de rajouter une colonne calculée persistante en implémentant un hachage manuel à l’aide d’une fonction comme CHECKSUM, BINARY_CHECKSUM ou HashBytes.
Il est donc parfaitement possible de faire une partition sur une clef de hachage.
Pour compléter votre lecture : « Partitioning in SQL Server 2008 »
http://www.sqlservercentral.com/articles/partition/64740/
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
