Sybase ASE - Impacts sur les performances des coupures de pages (Page splits), paramètre fillfactor

Introduction

Lors d’une migration "cross plateforme" d’une instance Sybase Adaptive Server Enterprise, de Sun Solaris Sparc vers Sun Solaris X86, réalisée sans soucis particuliers par ailleurs (cf article : Sybase 12.5.3 - Dump/Load cross plateforme) , deux des plus grosses tables du modèle ont été extraites par période (trimestre) puis réinsérées avec le binaire bcp, tant pour accélérer la méthode de migration (dump, load, sp_post_xpoad) que pour réorganiser ces 2 tables qui en avaient bien besoin.

À l’issue de la migration, catastrophe ! Les performances sont dramatiques. La première alimentation de ces tables présente une durée d’exécution extrêmement dégradée (4h au lieu de 20 minutes). sp_sysmon reporte alors dans ses statistiques un nombre de coupures de pages (Page Splits) par secondes et par transactions énorme ainsi qu’un taux de maintenance très élevé des indexes non "clustered".

Index Management
----------------

Nonclustered Maintenance        per sec      per xact       count  % of total
-------------------------  ------------  ------------  ----------  ----------
  Ins/Upd Requiring Maint          41.9          23.9       89769       n/a
    # of NC Ndx Maint             125.7          71.8      269307       n/a
    Avg NC Ndx Maint / Op           n/a           n/a     3.00000       n/a

  Deletes Requiring Maint           0.0           0.0           0       n/a
    # of NC Ndx Maint               0.0           0.0           0       n/a

  RID Upd from Clust Split         28.7          16.4       61524       n/a
    # of NC Ndx Maint             830.1         474.0     1778016       n/a
    Avg NC Ndx Maint / Op           n/a           n/a    28.89955       n/a

  Upd/Del DOL Req Maint             3.0           1.7        6320       n/a
    # of DOL Ndx Maint              3.6           2.1        7808       n/a
    Avg DOL Ndx Maint / Op          n/a           n/a     1.23544       n/a

Page Splits                        17.1           9.8       36686       n/a
  Retries                           0.0           0.0           0       0.0 %
  Deadlocks                         0.0           0.0           0       0.0 %
  Add Index Level                   0.0           0.0           0       0.0 %

Trop tardivement hélas, on se rappelle alors le chapitre dédié au phénomène de coupures de pages, phénomène appelé "Page Split" du cours "Performance and Tuning", et plus particulièrement du paramètre fillfactor, paramètre définissant le taux de remplissage des pages d’une table en verrouillage table (APL - All Pages Locking).

Dès le lendemain, naturellement, les traitements retrouvent leurs durées d’exécution normales (20 minutes environ).

Cet article va montrer de façon chiffrée quelques éléments expliquant le phénomène de coupures de pages "Page Split", ainsi que plusieurs méthodes pour l’éviter ou en atténuer les conséquences

Contexte du test

Les tests sont réalisés sur une table de 4,5 millions de lignes occupant 600 Mo environ et sans typage exotique (colonnes text, etc.). Lors de chaque test, la table est recréée puis alimentée avec le binaire bcp.

La clé primaire est composite et posée après l’alimentation des données.

CREATE unique clustered index PK on TEST_TABLE (INSTRUMENT_ID,
RETRIEVAL_DTE, SOURCE_ID) 

L’objectif est de mesurer l’insertion dans cette table de 10000 lignes (2 Mo) suivant diverses conditions d’exécution, via la séquence INSERT / SELECT.

Les diverses mesures sont réalisées à l’aide des outils suivants :

• sp_sysmon (Analyse des performances d’un serveur Sybase ASE avec sp_sysmon ).
• optdiag.
• dbcc log (Sybase - Lire les journaux de transactions avec dbcc log ).

Elément important lors du test, les données sont insérées par date (RETRIEVAL_DTE), colonne qui n’est pas la première colonne de l’index clustered composite. Aussi, chaque ajout n’est pas réalisé nécessairement en fin de table.

Les axes d’amélioration sont multiples et puisqu’il s’agit d’éviter un nombre trop important de coupure de pages (page splits) et d’écritures dans le journal, les paramètres ci-dessous vont être analysés :

• Taux de remplissage : paramètre fillfactor.
• Changement du mode de coupures de pages avec dbcc tune ascinserts.
• Taille des I/Os dans le journal : log I/O size.
• Taille du cache "user log cache size".
• Mode de verrouillage de la table :
  • Verrouillage table : APL - All Pages Locking (Allpages).
  • Verrouillage page de données : DAP - Data Pages Locking (DataPages).
  • Verrouillage ligne : DOL - Data Only Locking (Datarows).
• Partitionnement aléatoire (round robin).

Le phénomène de coupures de pages (Page Splits) sur les tables APL

Pour simplifier la description du phénomène de coupures de pages dans le contexte de cet article, seules les 2 colonnes INSTRUMENT_ID et RETRIEVAL_DTE de l’index clustered sont représentées dans les schémas. Ces 2 colonnes sont respectivement nommées INSTID et DATE dans les schémas pour la lisibilité.

Afin de respecter l’ordre de tri de la table sur l’index clustered unique INSTRUMENT_ID, RETRIEVAL_DTE et SOURCE_ID, 2 mécanismes de coupures de page sont possibles.

Mécanisme de coupures de pages par défaut du moteur ASE

À l’issue de la réinjection des données avec bcp, la table TEST_TABLE qui est en verrouillage table (APL All Pages Locking) est parfaitement organisée ainsi :

Dans le mécanisme par défaut de la coupure de pages, lors de l’insertion du couple "INSTRUMENT_ID=1,RETRIEVAL_DTE=05/01/2011" : une nouvelle page est allouée avec déplacement de la moitié des lignes pour insérer cette nouvelle ligne comme le montre le schéma ci-dessous.

Le déplacement des lignes sur cette nouvelle page est très coûteux et engendre tout naturellement de la maintenance sur les indexes non clustered posés sur la table (cf extrait sp_sysmon en introduction de cet article).

L’insertion du couple "INSTRUMENT_ID=1,RETRIEVAL_DTE=06/01/2011" n’implique pas une coupure de page :

En revanche une nouvelle coupure se produit lors de l’insertion du couple "INSTRUMENT_ID=1,RETRIEVAL_DTE=07/01/2011" avec déplacement de la moitié des lignes sur une nouvelle page.

Et le phénomène de coupure de page se poursuit ainsi de suite.

Mécanisme de coupures de pages en mode "ascending inserts"

À l’issue de la réinjection des données avec bcp, la table TEST_TABLE qui est en verrouillage table (APL All Pages Locking) est parfaitement organisée ainsi :

Dans la méthode de coupure en mode "ascending inserts", la coupure n’est pas réalisée au milieu de la page comme dans la méthode par défaut mais au point d’insertion.

Lors de l’insertion du couple "INSTRUMENT_ID=1,RETRIEVAL_DTE=05/01/2011" : la coupure est réalisée au point d’insertion et les lignes suivantes sont déplacées sur une nouvelle page.

L’insertion du couple "INSTRUMENT_ID=1,RETRIEVAL_DTE=06/01/11" engendre à nouveau une coupure de page avec allocation d’une nouvelle page.

L’insertion de la donnée "INSTRUMENT_ID=1,RETRIEVAL_DTE=07/01/11" est stockée dans la nouvelle page allouée précédemment.

Cette méthode est la plus adaptée lorsqu’il s’agit de traitements d’insertions dans lesquels les données sources sont déjà triées dans l’ordre de l’index clustered, ce qui n’anéantit pas pour autant le coût de maintenance des indexes non clustered.

Mesure initiale et reproductibilité du phénomène de coupures de pages

TEST_TABLE est en verrouillage APL (All Pages Locking) et l’option "trunc log on chkpt" n’est pas posée dans la base de données afin de pouvoir consulter le journal avec dbcc log.

L’opération d’insertion est la suivante :

dump tran test with truncate_only
go
checkpoint
go

exec sp_sysmon begin_sample
insert TEST_TABLE select  * from TEST_TABLE_1JOUR
exec sp_sysmon end_sample
go

select op, dbo.sp_logrectype(op), count(1) 
from syslogs
group by op
order by 3 desc
go

dbcc traceon(3604)
go
dbcc log(14,0,0,0,0,-1,1)
go

sp_logrectype est une procédure stockée développée par Rob Verschoor qui retourne le libellé des opérations dans le journal des transactions syslogs : Sypron.nl - Rob Verschoor (sql_udf_collection.sql) .

Sans index unique clustered, l’opération est effectuée au rythme de 11 213 insertions par secondes sur la table. Il s’agit de notre valeur de référence.

Avec l’index unique clustered posé sur la table avant l’alimentation,

create unique clustered index PK on TEST_TABLE(INSTRUMENT_ID,RETRIEVAL_DTE,SOURCE_ID)
go

l’import atteint difficilement le rythme de 71 lignes par seconde, 158 fois plus long.

Le contexte de la catastrophe est reproduit.

Dans la mesure de référence (sans index clustered), aucun phénomène de coupure de pages "Page splits" n’est mesuré - placement des données en fin de table et très peu d’écritures dans le journal (430), écritures qui correspondent simplement aux insertions (INSERT), à l’allocation des pages OAM (OAMINSERT) et aux mises à jour de syspartitions (DOL_UPDATE).

Dans la table parfaitement organisée avec un index clustered unique avant alimentation, le test d’insertion est lui générateur du phénomène de Page splits, phénomène que l’on retrouve sous forme de très nombreuses écritures dans le journal avec le libellé SPLIT :

SPLIT                    (513985,10)    sessionid=513981,4
  attcnt=1 rno=10 op=16 padlen=0 sessionid=513981,4 len=56
  odc_stat=0x0000 (0x0000)
  loh_status: 0x0 (0x00000000)
  objid=1712006099  ptnid=1712006099 pageno=405122 offset=1046 status=0x800 (0x0800 (XSTAT_EXPAGE))
  cid=0 indid=0
  old ts=0x0000 0x0071c55a  new ts=0x0000 0x0071c55d
  xnewpg=404993

Tests de modification de paramètres pour amoindrir le phénomène "Page Splits" (fillfactor…)

Fillfactor : définition du pourcentage de remplissage des pages

Pour éviter les coupures de pages, on fait en sorte, à la création de la table et/ou de l’index de conserver dans chaque page un peu d’espace de façon à limiter ce phénomène

Dans le cas présent, le paramètre fillfactor est appliqué à la création de l’index unique clustered sur la table TEST_TABLE déjà alimentée :

create unique clustered index PK on TEST_TABLE(INSTRUMENT_ID,RETRIEVAL_DTE,SOURCE_ID)
with fillfactor=80

Une variation légère du taux remplissage des pages avec le paramètre fillfactor permet de corriger instantanément le problème. À 90%, l’insertion est près de 3 fois plus rapide avec 2 fois moins d’écritures dans le journal, à 80% le contexte est parfait, aucune coupure de pages ne se produit.

La perte d’espace engendrée par cette stratégie est de 24% avec un taux de remplissage fillfactor à 80.

dbcc tune (ascinserts) : modification du mode de coupure des pages

En fonction de l’ordre de tri de la table et de l’ordre d’insertion des données, la méthode de coupures de pages par défaut dans le moteur ASE peut être pénalisante. L’instruction dbcc tune va remplacer la stratégie de coupure de page 'au milieu' (mode par défaut) par une stratégie 'page vierge' (mode ascending inserts), le but étant de minimiser la nouvelle allocation de pages.

Pour modifier le mode de coupures de pages en mode "ascending inserts" pour la table TEST_TABLE :

dbcc tune(ascinserts, 1, "TEST_TABLE")
go

Les 3 premières variations de ce test correspondent à 3 tris différents des données de la source : sur la clé 1 (INSTRUMENT_ID), ensuite la clé 2 (RETRIEVAL_DTE), puis la clé 3 (SOURCE_ID) de l’index clustered.

Le dernier jeu de test (123) est réalisé en triant les données de la source dans l’ordre du cluster (INSTRUMENT_ID, RETRIEVAL_DTE, SOURCE_ID). On est dans ce cas 5 à 6 fois plus rapide qu’à la première tentative.

Pas d’amélioration notable par rapport aux gains obtenus avec un paramètre fillfactor fixé à 80%. Le nombre de coupures de pages reste constant. Ce paramètre magique, dans notre cas de figure, ne l’est pas du tout, sauf dans le cadre d’une maîtrise de l’ordre de tri de la source.

Taille des I/Os dans le journal : Log io size

Puisque la coupure de pages "Page Splits" engendre un nombre important d’écritures dans le journal, en optimisant cette opération on devrait pouvoir observer une amélioration notable de performances.

La modification de la taille des pages lues et écrites dans le journal est modifiable avec la procédure système sp_logiosize. Par exemple pour définir une taille de pages de 4K pour les I/Os dans le journal :

exec sp_logiosize '4K'
go

Dans les faits, une diminution sensible du nombre d’écritures dans le journal (TLog Writes) est observée avec l’augmentation de la taille des I/Os, mais la diminution est sans réelle influence sur la durée d’exécution des traitements. L’influence de la couche système (FS, cache OS, stripping) est également plus importante que la configuration logicielle.

Taille du cache "user log cache size"

Dans le même esprit que précédemment, afin de réduire les évènements 'ULC Flushes to Xact Log', le cache "user log cache size" est redimensionné à des tailles différentes :

exec sp_configure 'user log cache size', 8192
go

Dans les faits, une diminution sensible du nombre d’écritures est constatée pour une taille de 8K, mais sans réelle influence sur la durée d’exécution des traitements (#Rows per sec et Page Splits).

Schéma de verrouillage de la table

La nature du stockage influe sur la gestion du contenu des pages et l’écriture du contenu. Pour une table en verrouillage ligne (DOL - Data Only Locking) en particulier, le mécanisme de redirection de lignes (row forwarding) pourrait s’avérer intéressant en termes de performances.

alter table TEST_TABLE lock allpages | datapages | datarows
go

Le temps de traitements (rows/sec) est meilleur lorsque la table est en verrouillage page de données ou lignes, mais encore 10 fois inférieur au résultat obtenu en verrouillage table avec un taux de remplissage défini à 80%. En revanche le nombre de coupures de pages (Page Splits) est 3 ou 4 fois plus important dans les modes de verrouillage page de données ou lignes.

Comment expliquer ce comportement ? Pour les tables en verrouillage table (APL), les coupures de pages sont réalisées en mode synchrone, alors que dans les modes de verrouillage pages de données ou lignes, les coupures de pages sont réalisées en mode asynchrone par le processus HouseKeeper depuis la version 15.0.2, version utilisée ici.

N.B. : Il faut se souvenir également que les pages de données d’une table en verrouillage ligne (DOL) ne sont pas le dernier niveau d’un index clustered.

Partitionnement de la table en mode aléatoire (round robin)

La possibilité de répartir les écritures sur des structures parallèles (partitions) pourrait avoir une influence sur la rapidité d’exécution.

alter table TEST_TABLE partition  2
go

Pas de surprise dans ce jeu de test : comme il s’agit d’une insertion unitaire sur la table avec un index clustered, le partitionnement n’apporte pas de gain significatif en performances brutes.

Conclusion

On ne le répètera jamais assez : le positionnement d’un index clustered unique composite sur une table en verrouillage APL doit être étudié de manière approfondie lors de la modélisation de l’application.

Dans tous les cas de figure, seule la définition du paramètre fillfactor à 80% a anéanti de façon significative le phénomène de coupure des pages dans le contexte rencontré ici au détriment d’une perte d’espace de 25%.