Sybase Adaptive Server Enterprise 15 et le partitionnement sémantique

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'.

SQL> exec 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 jaune fluo 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) 
 )
go

insert test2 values ( -40 ) 
go
insert test2 values ( 30 )
go
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.

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.

SQL> exec 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().

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.

SQL> 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 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.

truncate table test partition p4
go
alter table test drop partition p4
go
alter table test add partition  ( p4 values <= ('20080101') , p5 values <=(MAX) )
go

Les données sont réimportées avec le binaire bcp :

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 :

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 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 

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'
go
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.

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   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.

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 p07

dbcc :

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 p03

reorg :

reorg rebuild test partition p01