Sybase Adaptive Server Enterprise autorise le partitionnement sémantique des tables depuis la v15.0.
Le partitionnement consiste en un découpage logique des tables sans aucun impact sur la syntaxe des requêtes SELECT, DELETE, INSERT et UPDATE. L’opération est transparente pour l’écriture des requêtes.
Chaque partition est un sous-objet de la table et contient certaines de ses lignes de données.
Les partitions ne sont pas consultables : aucune requête ne peut les attaquer. Bien qu’une partition contienne une partie des lignes d’une table, il n’est pas possible de savoir lesquelles.
Lorsqu’une table n’est pas pas partitionnée, ASE considère qu’elle contient une seule partition et lui attribue un nom qui commence par le nom de la table.
La procédure stockée qui nous informe des partitions d’une table est sp_helpartition suivi ou non du nom d’une table.
Il y a 4 types de partitionnement (les 3 derniers sont les partitionnements sémantiques)
- By roundrobin : c’est le partitionnement par défaut dans ASE. Lors des INSERT, il distribue les lignes équitablement et circulairement entre les partitions. L’intérêt c’est juste de partager les lignes de manière égale sur toutes les partitions.
- By hash : on peut choisir la colonne sur laquelle ASE va se baser pour confectionner un hachage. Il n’est pas possible de connaître cette règle de hachage : ASE la choisit au mieux pour répartir les lignes équitablement sur toutes les partitions. Il est important de choisir une colonne très significative pour profiter au mieux de ce hachage.
- By list : ségrégation des lignes de la table en fonction de valeurs précises dans une colonne décisive.
- By range : ségrégation des lignes de la table en fonction de plages de valeurs dans une colonne décisive.
Le partitionnement “by-range” se montre très performant sur les tables contenant énormément de lignes, surtout si certaines lignes sont rarement consultées.
Au moment du partitionnement, il est judicieux de découper sa table en 3 ou 4 morceaux. Prenons l’exemple d’une application spécialisée dans la météorologie : elle stocke toutes les données météo quotidiennes de toutes les villes du monde. La table en question va progressivement contenir beaucoup de lignes. Or, la plupart des requêtes de lecture qui vont l’atteindre se cantonneront aux dates les plus récentes : météo d’aujourd’hui, d’hier, d’avant-hier. La table est donc encombrée par son historique.
Parmi toutes les données contenues sur la table, pour favoriser la lecture des dates récentes, il est possible d’associer le partitionnement “by-range” et la segmentation.
En effet, il peut être judicieux de créer 3 segments dans la base : chacun relié à un device différent. Les devices peuvent être eux-même alloués sur des supports physiques de performance et coût différents :
- Un device pour les “anciennes” données, par exemple, un disque dur peu coûteux et offrant des performances basiques mais un espace de stockage immense.
- Un device pour les données “récentes”, par exemple, un disque dur plus performant, nettement plus cher.
- Un device pour les données “actuelles”, par exemple, un support SSD réputé pour être particulièrement rapide mais trop coûteux pour disposer d’énormes capacités.
Dès lors, avec un découpage par date, les données de plus d’un mois se retrouveront sur le premier device, les données de moins d’un mois mais plus d’une semaine se retrouveront sur le deuxième disque et enfin, les données de moins d’une semaine seront accessibles à une rapidité inégalée grâce au SSD du 3ème device. Grâce au “partition pruning” (terme d’horticulture qui signifie la “taille”), lors de son tablescan, l’optimiseur va ignorer certaines partitions s’il est sûr à 100% que la donnée qu’il cherche ne s’y trouve pas.
The type of query is SELECT.
ROOT:EMIT Operator
|SCAN Operator
| FROM TABLE
| temperatures
| [ Eliminated Partitions : 1 2 ]
| Table Scan.
| Forward Scan.
| Positioning at start of table.
| Using I/O Size 2 Kbytes for data pages.
| With LRU Buffer Replacement Strategy for data pages.
Durant les périodes creuses, par exemple, la nuit ou les week-end, il faudra systématiquement reconstruire la table afin de rafraîchir ce partitionnement. Les partitions qui n’auront pas changé (typiquement, celle qui contient les données les plus anciennes) ne seront pas reconstruites.
La limite de ce système c’est la façon dont on recherche les données ensuite : seules les recherches basées sur la date au sens chronologique bénéficieront de cette mise en place. Pour toute recherche du genre “météo de tous les 1er Janvier de 1970 à nos jours” ou encore “toutes les données météo de la ville de New York de 1970 à nos jours”, le partitionnement sera sans effet et sera peut-être même pire qu’avant car les 3 disques seront sollicités y compris le plus lent. Pour surmonter ces cas-là, on pourra faire intervenir la “dénormalisation”.
L’avantage du système c’est sa complète transparence pour l’applicatif : rien n’est à re-développer côté applicatif.
Passons à la pratique. Il faut commencer par autoriser le partitionnement sémantique (by-hash, by-list ou by-range) en passant la commande sp_configure 'enable semantic partitioning', 1.
Ensuite il faut créer les n devices correspondant aux n supports de stockage.
disk init name='device_lent', physname='D:/sybase/meteo_data', size='1000M'
disk init name='device_medium', physname='E:/sybase/meteo_data', size='100M'
disk init name='device_rapide', physname='F:/sybase/meteo_data', size='50M'
Ensuite, il faut attacher la base de données aux n devices :
create database meteo on
device_lent=1000,
device_medium=100,
device_rapide=50
Puis il faut créer les n segments correspondants :
sp_addsegment segment_lent, meteo, device_lent
sp_addsegment segment_medium, meteo, device_medium
sp_addsegment segment_rapide, meteo, device_rapide
Enfin, vient le moment où on découpe la table en la répartissant sur les n segments :
alter table temperatures partition by range (date)
(
partition_ancienne values <= ('2013-01-01') on segment_lent,
partition_recente values <= ('2013-02-15') on segment_medium,
partition_actuelle values <= (MAX) on segment_rapide
)
On peut aussi trouver ce genre de montage avec le partitionnement par liste (by-list) pour d’autres applications. Par exemple, on peut imaginer une table qui contient des données consultables et des données “supprimées” au sens applicatif. En quelques sortes, des données visibles et des données cachées mais qui restent dans la table pour des raisons de conservation historique.
Comme précédemment, on va partitionner pour conserver les données cachées sur un disque physique peu coûteux et donc peu performant et les données visibles sur un disque performant.
Mais il y a un gros inconvénient chez le partitionnement par liste : lorsque la colonne responsable du partitionnement contient une valeur non prévue, l’INSERT tombe en erreur. L’application est donc tenue de gérer correctement le contenu de la colonne décisive.
Exemple de mise en pratique :
alter table contrats partition by list (visibilite)
(
partition_cachee values (0) on segment_cachee,
partition_visible values (1) on segment_visible
)
Continuez votre lecture sur le blog :
- Direct i/o, dsync on/off, raw device (David Baffaleuf) [SybaseVintage]
- Jointure entre syssegments et sysusages (Capdata team) [Sybase]
- PostgreSQL : évolution du partitionnement de 9.6 à 12 (1/2) (Capdata team) [PostgreSQL]
- PostgreSQL : évolution du partitionnement de 9.6 à 12 (2/2) (Capdata team) [PostgreSQL]
- “Pruning” de partitions sous PostgreSQL ou comment bien élaguer ! (Capdata team) [PostgreSQL]