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).
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'.
SQL> sp_configure "enable semantic partitioning", 1
La 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.
SQL> 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'
)
SQL>
declare @cur datetime
select @cur = "20040101"
while @cur < "20090101"
begin
insert test values ( @cur, datepart(mm,@cur) )
select @cur = dateadd(mm, 1, @cur)
end
En gras figure l’extension de syntaxe. Extension de syntaxe où l’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) ) SQL> insert test2 values ( -40 ) (1 row affected) SQL> insert test2 values ( 30 ) Msg 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.
La commande sp_help est enrichie de nouvelles informations concernant les partitions. Celles-ci proviennent d’une nouvelle commande, sp_helpartition
SQL> sp_helpartition test3
name 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().
SQL> 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)
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.
SQL> 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.
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.
SQL> 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.
SHELL> bcp part..test partition p4 out test.bcpc -c –U user -P mdp
La 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.
SQL> truncate table test partition p4 SQL> alter table test drop partition p4 SQL> alter table test add partition ( p4 values <= ('20080101') , p5 values <=(MAX) )
Les données sont réimportées avec le binaire bcp :
SHELL> bcp part..test in test.bcpc -c –U user -P mdp
Finalement, la commande sp_helpartition permet de contrôler l'opération et vérifier que la répartition est désormais parfaite :
SQL> 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)
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 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.
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.
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.
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
SQL> sp_helpindex test3 Object 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
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.
SQL> set showplan on;
SQL> select count(1) from test3 where ts between '20050305' and '20050501'
QUERY 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.
SQL> 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
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 14
Une 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 |
Select count(1) from TBL_std ( index idx1 ) t where DATE between '20070125' and '20070214' |
rows: 655782 est: 633744 lio: 27604 est: 26633 pio: 2441 est: 3348 Table: TBL_std scan count 1, logical reads: (regular=27604 apf=0 total=27604), physical reads: (regular=8 apf=2433 total=2441), apf IOs used=2433 Total writes for this command: 0 Execution Time 61. SQL Server cpu time: 6100 ms. SQL Server elapsed time: 6363 ms. |
rows: 655782 est: 630772 lio: 28648 est: 27451 pio: 2940 est: 3432 Table: TBL_part scan count 2, logical reads: (regular=28648 apf=0 total=28648), physical reads: (regular=8 apf=2932 total=2940), apf IOs used=2932 Total writes for this command: 0 Execution Time 32. SQL Server cpu time: 3200 ms. SQL Server elapsed time: 4216 ms. |
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.
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 :
SQL> truncate table test partition p07
dbcc :
SQL> 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 :
SQL> update statistics test partition p03
reorg :
SQL> reorg rebuild test partition p01
| Version | Date | Commentaires |
|---|---|---|
| 1.0 | 04/2009 | Version initiale |
ISUG TechCast Series, Data
partitioning in ASE 15
Adaptive Server Enterprise
15.0, Transact SQL User's guide, Partitioning tables and indexes