Introduction
Ce document va évoquer essentiellement par l’exemple la nouvelle fonctionnalité mise en œuvre dans ASE version 15 – sous licence - le partitionnement.
Le partitionnement est une réponse à la croissance des volumes en base en environnement transactionnel. Il consiste à diviser un objet en fragments distincts réduisant ainsi le volume de données à manipuler, tant pour les opérations courantes de lecture ou d’écriture, mais également pour les tâches d’administration.
Il existe 4 manières de partitionner les tables : par round-robin (aléatoire), modèle existant dans les versions pré 15, par hachage, par liste ou par 'range' (intervalles). C’est cette dernière méthode qui sera étudiée ici.
Il est à noter que désormais toutes les tables sont considérées comme partitionnées avec Adaptive Server Enterprise 15, le défaut étant le mode 'round robin' sur un unique segment (soit pas de partitionnement).
Manipulation des partitions
Création
Cette fonction étant sous licence, il est nécessaire de télécharger une clé valide, à intégrer dans SYSAM, et d’activer au sein du serveur le paramètre 'enable semantic partitioning'.
exec sp_configure "enable semantic partitioning", 1La fonction de partitionnement s’intègre dans le dictionnaire de
commande usuel, du coup, il ne s’agit que d’options supplémentaires
à préciser dans les commandes existantes ; ainsi leur création, modification ou
suppression, s’effectue à l’aide des commandes classiques create
table, alter table, create index, …
Dans le cadre de cette présentation, une table test va être créée, contenant une date et une valeur numérique – une ligne par mois.
create table test
(
ts datetime,
val int
)
partition by range(ts)
(
p1 values <= ('20050101') on 'default' ,
p2 values <= ('20060101') on 'default',
p3 values <= ('20070101') on 'default',
p4 values <= (MAX) on 'default'
)
declare @cur datetime
select @cur = "20040101"
while @cur < "20090101"
begin
insert test values ( @cur, datepart(mm,@cur) )
select @cur = dateadd(mm, 1, @cur)
endDans l’extension de syntaxe on y indique le
type de partitionnement (range), la ou les colonnes de répartition (ts), puis
la liste des partitions (p1, p2, p3, p4), leurs bornes ( '20050101', .. ),
ainsi que leurs localisations sur les segments ('default').
Le classement des données s’effectue par bornes inclusives. Ainsi, dans l’exemple, la partition p1 va contenir toutes les données dont la valeur du timestamp (ts) est inférieure ou égale à 2005. La partition p2 va elle stocker toutes les lignes strictement supérieures à la borne précédente, soit l’année 2005 + 1 jour…
On observe qu’il est possible de placer les partitions sur des segments différents, donc potentiellement des axes différents et profiter ainsi au maximum des ressources physiques.
La valeur MAX est importante. Tout d’abord, elle est optionnelle et
lorsqu’elle n’est pas spécifiée, cela signifie que les plages de données
acceptées sont bornées. Toute insertion hors plage est rejetée avec le message
9573.
create table test2 ( val int ) partition by range(val) ( p1 values <= (10), p2 values <=(20) ) go insert test2 values ( -40 ) go insert test2 values ( 30 ) goMsg 9573, Level 16, State 1 Server 'DBA_T5_ASE', Line 1 The server failed to create or update a row in table 'test2' because the values of the row's partition-key columns do not fit into any of the table's partitions. Command has been aborted. (0 rows affected)
Si la valeur MAX est définie, alors la table peut accepter toutes les
données. Dans ce cas, il peut être nécessaire de mettre en œuvre des
procédures de maintenance permettant de re-balancer la dernière partition.
C’est d’ailleurs l’objet d’un cas pratique un peu plus
loin.
sp_help et sp_helpartition
La commande sp_help est enrichie de nouvelles informations concernant les
partitions. Celles-ci proviennent d’une nouvelle commande,
sp_helpartition.
exec sp_helpartition test3name type partition_type partitions partition_keys ----- ---------- -------------- ----------- -------------- test3 base table range 4 ts (1 row affected) partition_name partition_id pages row_count segment create_date -------------- ------------ ----------- ----------- ------- ------------------- p1 1984007068 1 13 default Mar 9 2007 10:23AM p2 2000007125 1 12 default Mar 9 2007 10:23AM p3 2016007182 1 12 default Mar 9 2007 10:23AM p4 2032007239 1 23 default Mar 9 2007 10:23AM Partition_Conditions ---------------------- VALUES <=('20050101') VALUES <=('20060101') VALUES <=('20070101') VALUES <=(MAX) Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg) ----------- ----------- ----------- -------------------- -------------------- 1 1 1 1.000000 1.000000 (return status=0)
On retrouve toutes les informations utiles sur la configuration, comme le
nombre de partitions, leur définition (Partition_Conditions), mais également le
nombre de pages et de lignes pour chacune des partitions (pages, row_count).
Ces informations permettent de contrôler la bonne répartition des informations
en fonction de la stratégie adoptée (classement d’historiques,
parallélisme, batches).
Les informations sur le nombre de lignes dans les partitions sont également
disponibles grâce à de nouvelles commandes : row_count(), partition_id().
select row_count(db_id(), object_id('test'), partition_id('test', 'p1')) 'P1', row_count(db_id(), object_id('test'), partition_id('test', 'p2')) 'P2', row_count(db_id(), object_id('test'), partition_id('test', 'p3')) 'P3', row_count(db_id(), object_id('test'), partition_id('test', 'p4')) 'P4'P1 P2 P3 P4 --------------------- --------------------- --------------------- --------------------- 13 12 12 12 (1 row affected)
Conversion d’une table existante en table partitionnée
La procédure de transformation d’une table standard en table partitionnée implique la suppression des indexes posés sur la table. Le côté négatif est que ce type de fonctionnalité s’appliquant aux tables volumineuses, une reconstruction n’est pas forcément simple à mettre en œuvre. Le bon côté est que les données seront défragmentées et classées après l’opération, ce qui ne fait jamais de mal.
La syntaxe est identique à celle vue lors du create table, à l’ordre
près : alter.
alter table test3
partition by range(ts)
(
p1 values <= ('20050101'),
p2 values <=('20060101'),
p3 values <=('20070101'),
p4 values <=(MAX)
) Si des indexes existent, le message suivant est délivré
Msg 13956, Level 16, State 1
Server 'DBA_T5_ASE', Line 1
ALTER TABLE 'test3' failed. Repartition with rebuilding index is not supported when
partition type changes. Drop the indexes before repartition.Ajout / Suppression d’une partition
La commande alter table prend en charge une nouvelle syntaxe permettant
d’ajouter (add) ou de supprimer (drop) les partitions d’une
table.
alter table test add | drop partition ...;Par nature, la gestion de ces opérations va verrouiller uniquement le bloc concerné, en outre, celui-ci étant un sous-ensemble de la table, l’opération est plus rapide qu’auparavant.
Dans l’exemple suivant, une partition supplémentaire est créée en divisant la dernière partition en 2, afin de répartir les données de manière plus balancée.
exec sp_helpartition test… partition_name partition_id pages row_count segment create_date -------------- ------------ ----------- ----------- ------- ------------------- p1 1424005073 1 13 default Mar 8 2007 8:35AM p2 1440005130 1 12 default Mar 8 2007 8:35AM p3 1456005187 1 12 default Mar 8 2007 8:35AM p4 2112007524 1 23 default Mar 9 2007 10:45AM Partition_Conditions ---------------------- VALUES <= ('20050101') VALUES <= ('20060101') VALUES <= ('20070101') VALUES <= (MAX)
L’objectif est de scinder la partition p4 en 2, une première couvrant
l’année 2007, la deuxième appliquée sur le MAX.
Première étape, une sauvegarde des données de la partition p4 est nécessaire. On va pour cela profiter de l’enrichissement par Sybase de l’utilitaire bcp, capable désormais de prendre en compte l’export d’une seule partition.
% bcp part..test partition p4 out test.bcpc -c –U user -P mdpLa suite est classique, on supprime les données de la partition p4 (truncate
partition, nouvelle commande), puis l’on modifie la structure de la
table.
truncate table test partition p4
go
alter table test drop partition p4
go
alter table test add partition ( p4 values <= ('20080101') , p5 values <=(MAX) )
goLes données sont réimportées avec le binaire bcp :
bcp part..test in test.bcpc -c -U user -P mdpFinalement, la commande sp_helpartition permet de contrôler l’opération et
vérifier que la répartition est désormais parfaite :
exec sp_helpartition test… partition_name partition_id pages row_count segment create_date -------------- ------------ ----------- ----------- ------- ------------------- p1 1424005073 1 13 default Mar 8 2007 8:35AM p2 1440005130 1 12 default Mar 8 2007 8:35AM p3 1456005187 1 12 default Mar 8 2007 8:35AM p4 12524047 2 12 default Mar 12 2007 9:12AM p5 28524104 2 11 default Mar 12 2007 9:12AM Partition_Conditions ---------------------- VALUES <= ('20050101') VALUES <= ('20060101') VALUES <= ('20070101') VALUES <= ('20080101') VALUES <=(MAX)
Indexation des tables partitionnées
Sybase implémente deux manières d’implémenter l’indexation sur cette nouvelle structure de stockage. Des indexes dits globaux, qui référencent l’intégralité des données de la table, ou des indexes locaux, qui ne prennent en charge que le contenu d’une partition.
À noter que les indexes 'clustered' sont toujours locaux (pour le
partitionnement aléatoire par "round robin", il n’est global que pour assurer la
compatibilité avec les versions précédentes).
Par voie de conséquence, les indexes 'clustered' dans le cadre d’une
répartition par intervalles (range) doivent obligatoirement contenir la colonne
de répartition par segment.
Index global
C’est l’index historique, il référence toute la table. L’intérêt de cet index est sa capacité à adresser toutes les données, quelle que soit la partition.
Index local
Cette nouvelle forme d’indexation est intéressante à plusieurs titres. Tout d’abord, elle fournit des structures d’indexation différentes par partition, ce qui accroit fortement la disponibilité des données en termes de verrouillage. Par ailleurs, la hauteur de l’arbre est naturellement limitée ce qui participe à l’amélioration des performances. Enfin, le moteur ASE peut profiter si la configuration le permet d’options de parallélisation des accès, en répartissant les worker process par partition.
Rappel : tout index 'clustered' est local.
Commentaire
En terme d’espace utilisé, il n’existe pas de différence de taille entre les deux types d’indexes. Le nombre de blocs dans les scénarios pris en compte, montre une allocation identique ; concernant la hauteur de l’arbre, plus la taille de la table est importante, plus la différence est significative. La taille de l’arbre devient plus petite en index local qu’en global.
Le caractère local ou global apparaît clairement en utilisant la commande
sp_helpindex.
exec sp_helpindex test3Object has the following indexes index_name index_keys index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created index_local ---------- ---------- ----------------- ----------------------- ---------------- -------------------- ------------------- ------------ idx3 val nonclustered 0 0 0 Mar 12 2007 3:55PM Global Index idx1 val nonclustered 0 0 0 Mar 9 2007 10:23AM Local Index pk_test3 ts clustered, unique 0 0 0 Mar 9 2007 10:23AM Local Index
Impact sur les performances
Le plan d’exécution
L’optimiseur va pouvoir bénéficier du partitionnement de deux manières
complémentaires. Pour filtrer les données tout d’abord, en fonction de la
clause where, certaines partitions vont pouvoir être éliminées directement.
set showplan on go select count(1) from test3 where ts between '20050305' and '20050501' goQUERY PLAN FOR STATEMENT 1 (at line 1). 2 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SCALAR AGGREGATE Operator | Evaluate Ungrouped COUNT AGGREGATE. | | |SCAN Operator | | FROM TABLE | | test3 | | [ Eliminated Partitions : 1 3 4 ] | | Using Clustered Index. | | Index : pk_test3 | | Forward Scan. | | Positioning by key. | | Keys are: | | ts ASC | | Using I/O Size 2 Kbytes for data pages. | | With LRU Buffer Replacement Strategy for data pages. ----------- 58
On observe que l’optimiseur a choisi d’entrée d’éliminer 3 partitions sur les 4 existantes, réduisant de fait la charge du moteur. On voit qu’il a pu utiliser un index existant.
Autre moyen, la parallélisation des traitements. Si la parallélisation est activée, ASE aura tendance à positionner un « worker process » par partition de manière à utiliser les ressources machine en complément des ressources cpu.
select count(1) from test where ts between '20050305' and '20090501'QUERY PLAN FOR STATEMENT 1 (at line 1). Executed in parallel by coordinating process and 4 worker processes. 5 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SCALAR AGGREGATE Operator | Evaluate Ungrouped SUM OR AVERAGE AGGREGATE. | | |EXCHANGE Operator (Merged) | |Executed in parallel by 4 Producer and 1 Consumer processes. | | | | |EXCHANGE:EMIT Operator | | | | | | |SCALAR AGGREGATE Operator | | | | Evaluate Ungrouped COUNT AGGREGATE. | | | | | | | | |SCAN Operator | | | | | FROM TABLE | | | | | test | | | | | [ Eliminated Partitions : 1 ] | | | | | Index : l_ts | | | | | Forward Scan. | | | | | Positioning by key. | | | | | Index contains all needed columns. Base table will not be read. | | | | | Keys are: | | | | | ts ASC | | | | | Executed in parallel with a 4-way partition scan. | | | | | Using I/O Size 16 Kbytes for index leaf pages. | | | | | With LRU Buffer Replacement Strategy for index leaf pages. ----------- 2013120
Comparatif
Petit bug avec ASE 15.0.1, la commande set statistics io ne fonctionne pas
quand le paraléllisme est activé. Il est conseillé d’utiliser la commande
set statistics plancost.
Soient deux tables (APL) au contenu strictement identique : 4,5 millions de lignes pour 200000 pages, dont la structure est la suivante :
Column name Type I Null Dflt Rule Table Num
-------------------- ------------ - ---- ---- ---- -------------------- ---
PORTFOLIO_ID int 0 No INVESTMENT_POSITION 1
INSTRUMENT_ID int 0 No INVESTMENT_POSITION 2
DATE datetime 0 No INVESTMENT_POSITION 3
TECH_QUANTITY float 0 No INVESTMENT_POSITION 4
PHYSICAL_QUANTITY float 0 No INVESTMENT_POSITION 5
STATUS char(2 ) 0 No INVESTMENT_POSITION 6
LAST_RECORD_DATE datetime 0 Yes INVESTMENT_POSITION 7
VALUATION float 0 Yes INVESTMENT_POSITION 8
LAST_VALUATION_DATE datetime 0 Yes INVESTMENT_POSITION 9
CREATED_BY varchar(20 ) 0 No INVESTMENT_POSITION 10
CREATED_ON datetime 0 No INVESTMENT_POSITION 11
UPDATED_BY varchar(20 ) 0 Yes INVESTMENT_POSITION 12
UPDATED_ON datetime 0 Yes INVESTMENT_POSITION 13
TIMESTAMP timestamp 0 Yes INVESTMENT_POSITION 14Une clé primaire unique composite est définie sur les colonnes DATE,
PORTFOLIO_ID et INSTRUMENT_ID.
Nommons les tables TBL_std pour la première, définie sans partition
(standard) et TBL_part pour la seconde, dont 4 partitions ont été définies sur
la colonne DATE. Les partitions sont correctement balancées.
Les tables sont 'parfaites' d’un point de vue organisation (large I/O
efficiency à 99%, page cluster ratio à 99%… )
Le parallélisme n’est pas actif.
| Test | TBL_std |
TBL_part |
|---|---|---|
| # pages | 196615 | 203606 |
| Index height | 3 | 3 |
| # OAM | 1105 | 979 |
|
|
|
Le volume est peut être trop faible pour conclure, toutefois les plans d’exécution étant différents, il n’y a pas de différence très majeure en temps de réponse même si le partitionnement offre des améliorations.
Maintenance
Pour finir, quelques commandes nouvelles et/ou améliorées permettant de bénéficier du partitionnement afin de réduire les tâches de maintenance.
truncate :
truncate table test partition p07dbcc :
dbcc checktable('test',null,p5)Checking partition 'p5' (partition ID 364525301) of table 'test'. The logical page size of this table is 2048 bytes. The total number of data pages in partition 'p5' (partition ID 364525301) is 4183. Partition 'p5' (partition ID 364525301) has 527039 data rows. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
update statistics :
update statistics test partition p03reorg :
reorg rebuild test partition p01