Introduction
Microsoft SQL Server implémente depuis la version 2008 une fonctionalité permettant de compresser les pages de données.
L’objectif annoncé est double, d’abord de permettre de réduire de manière significative la taille d’une base, ensuite de réaliser des gains de performance en réduisant le nombre de d’E/S nécessaires à la manipulation de données.
La promesse de cette fonction est séduisante à priori car elle remplace des I/Os physiques - très lents - par des cycles CPU - extrêmement rapides.
La présente note va montrer que le premier objectif est largement atteint, mais que le second est moins systématique qu’il n’y paraît.
Présentation
La stratégie de compression repose sur une extension à tous les types (ou presque) d’une nouveauté apparue en SQL 2005, le type vardecimal, stockant les valeurs numériques de manière optimisée. Conséquence fâcheuse pour cette nouveauté, elle devient désormais obsolète (deprecated) dans la prochaine version de SQL Server (Denali).
Les typages exotiques (text, image, sql_variant, uniqueidentifier, table, xml, user_defined) ne sont pas pris en charge par la compression.
La compression, comme l’encryption, est absolument transparente pour les applications clientes.
La compression est réalisée au sein de chaque page et s’applique aux tables, indexes et partitions de manière indépendante. De fait, toutes les combinaisons sont possibles. On peut compresser une partition unitairement et pas les autres. Pour une même table, des types de compression différents par index peuvent être définis.
Deux stratégies différentes de compression influant sur le degré de transformation de la page sont disponibles : la compression de lignes et la compression de pages.
Compression de lignes (ROW)
Description de la compression de lignes (ROW)
Dans la compression de lignes, les types fixes sont remplacés par des types équivalents variables. Le surcoût par colonne compressée est de 4 bits, indiquant entre autre la taille de la donnée.
Concrètement, une colonne de type CHAR(10) NOT NULL
contenant la valeur
'ABCDEF
' occupera 10 octets classiquement, et uniquement 6 (+ 4 bits) en mode
compressé.
Mieux, ce transtypage s’applique également aux valeurs numériques. Avec la
compression de lignes, les valeurs numériques sont optimisées en réduisant le
nombre d’octets nécessaires au stockage, sans perte de précision. Ainsi, une
colonne de type INT
(valeurs de -2^31 à +2^31 -1) occupant donc 4 octets
habituellement quelle que soit la valeur saisie n’en consommera qu’un seul
(plus les 4 bits) pour les valeurs comprises entre 0 et 255, comme pour le type
tinyint.
Enfin, les valeurs NULL
et 0
sont également traitées de manière optimisée,
seuls les 4 bits additionnels sont nécessaires à leur stockage.
Le phénomène de gain d’espace dans la compression de lignes est illustré ci-dessous :
Le schéma montre bien l’effet des conversions : malgré un surcoût structurel de la solution (4 bits supplémentaires par colonne compressée), de l’espace supplémentaire est rendu disponible, espace qui sera utilisé pour stocker d’autres lignes.
Notons la compression en 1 seul octet au lieu de 4 pour les valeurs numériques 123 et 45 codées désormais comme un type tinyint (rouge-orangé dans le schéma).
Compression de lignes - Exemple
Soit une table contenant trois colonnes de taille fixe dans laquelle sont insérées des données ne remplissant pas la totalité du champ.
create table test ( a char(10) not null, b INTEGER NOT NULL , c char(5) not null) go insert test ( a, b , c ) select top 10000 'ABCDEF' , 123 , 'xyz' from syscolumns a join syscolumns b on 1=1 insert test ( a, b , c ) select top 10000 'ijk' , 456 , 'ijk' from syscolumns a join syscolumns b on 1=1 go
(10000 row(s) affected) (10000 row(s) affected)
La deuxième insertion (ijk
) est nécessaire pour démontrer la compression de
type PAGE
présentée ultérieurement dans cet article.
execute sp_spaceused test
name rows reserved data index_size unused ------------------------------------------------------------- test 20000 584 KB 560 KB 8 KB 16 KB
La taille de la table est de 560 Kb, soit 70 pages (560/8), chaque colonne utilisant l’espace alloué de taille fixe correspondant au typage défini.
La table est maintenant compressée en mode ROW
:
alter table test rebuild with ( data_compression = ROW ) go
Command(s) completed successfully.
execute sp_spaceused test
name rows reserved data index_size unused ------------------------------------------------------------- test 20000 336 KB 312 KB 86 KB 16 KB
La taille de la table est désormais de 312 Kb, soit 39 pages (312/8) pour un gain de plus de 55% environ.
Compression de pages (PAGE)
La stratégie de compression de pages est une version améliorée de la
compression de lignes (ROW
) par l’ajout dans la page d’une structure
référençant les valeurs répétitives. Les valeurs répétitives sont stockées en
entête de page et seule la référence à cette structure est matérialisée dans
les lignes.
Cette structure de référence peut contenir :
- des valeurs exactes : compression de pages avec dictionnaire (Dictionary compression).
- des préfixes récurrents : compression de pages avec préfixes (Prefix compression).
La stratégie appliquée n’est pas paramétrable, le moteur décide de la
méthode en fonction de seuils internes. Si aucune compression par page n’est
possible, elle n’est pas réalisée, la compression de lignes (ROW
) est appliquée
néanmoins.
La stratégie de compression de pages (préfixes ou dictionnaire) est également réévaluée lors d’opérations d’écritures comme une coupure de pages (split de pages) par exemple.
Les compressions de pages par préfixes et par dictionnaire sont complémentaires et peuvent donc être implémentées au sein d’une même page.
Compression de pages par préfixes (Prefix compression)
Dans les faits, cette structure est une ligne supplémentaire en début de
page référençant la valeur de la colonne contenant le tronc commun le plus
grand dans la page. Cette ligne est nommée *Anchor Record
*. Dès lors, chaque
colonne contient une donnée relative à cette ligne sous le format
<T><DATA>
, où <T>
correspond à la taille de l’ancre (anchor
)
à prendre en compte et <DATA>
à la valeur à ajouter.
Cette ligne supplémentaire n’influe pas sur le décompte des enregistrements, ni ne peut être sélectionnée.
En orangé sur l’illustration ci-dessus, figure la nouvelle ligne d’ancres (anchor record). La valeur 123 par rapport à sa compression optimisée en mode ROW est désormais un préfixe.
Sur la ligne 2, première colonne, la valeur 6SSE est interprétée en prenant les 6 premiers caractères de l’ancre COMPRE auxquels on ajoute SSE, ce qui donne la valeur souhaitée.
La valeur null (représentée ici par <>) signifie 'valeur complète de l’ancre'. Elle est différenciée de la valeur NULL par la structure de 4 bits évoquée précédemment.
Information paradoxale à noter : une colonne (PRESSE dans l’exemple) dont le contenu n’a aucun point commun avec l’ancre consomme plus d’espace après compression.
Dernier élément, la deuxième colonne a deux valeurs d’ancre possibles : PRESSE et TOTAUX. Une seule valeur étant permise pour l’ancre, c’est la valeur qui permet le plus fort taux de compression dans la page qui est choisie.
Compression de pages avec dictionnaire (Dictionary compression)
Un dictionnaire est créé si et seulement si une valeur strictement identique est présente dans AU MOINS 2 colonnes différentes.
Ce choix d’implémentation considère que si une valeur est répétée au sein d’une colonne, elle sera prise en compte par la stratégie *prefix*. Sauf erreur, il n’y a qu’une ancre possible par colonne, aussi, gageons que cette limite évoluera dans les prochaines versions car cette implémentation ne permet pas de compresser efficacement des colonnes contenant de faibles valeurs distinctes dans une colonne.
Le dictionnaire est un tableau à une dimension, intégré à la ligne d’ancres
(anchor record
). La première entrée a pour référence 0, la deuxième 1, etc.
Dans le mode dictionnaire, chaque colonne possède toujours sa propre valeur de référence dans la ligne d’ancres, mais on observe une nouvelle entrée qui est le tableau des valeurs répétées. Dans cette stratégie, la colonne contient la position de la valeur dans ce tableau de valeurs répétées et non sa valeur (en rouge dans l’illustration).
Ainsi, la chaîne PRESSE
, répétée et présente dans 2 colonnes différentes,
est intégrée dans le dictionnaire et représentée par l’identifiant [0]
(première entrée : [0], deuxième entrée : [1], etc.).
Compression de pages - Exemple
Poursuivons l’exemple défini précédemment dans lequel la compression en mode
ROW
avait divisé la taille de l’objet par plus de deux.
La compression en mode PAGE
est à présent appliquée.
alter table test rebuild with ( data_compression = PAGE ) go
Command(s) completed successfully.
execute sp_spaceused test
name rows reserved data index_size unused ------------------------------------------------------------- test 20000 272 KB 232 KB 8 KB 32 KB
La taille des données de la table est désormais de 232 KB, soit 34 pages
(232/8) et un gain de 25 % supplémentaires environ par rapport au mode ROW
.
Rappel : deux insertions différentes ont été réalisées en préparation de cette table test. La première insertion génère du contenu différent dans chaque colonne, la seconde insertion génère des valeurs répétées dans 2 colonnes différentes induisant la création d’un dictionnaire.
Performances - Remarques générales (typage, surcoût…)
Première remarque : la stratégie employée ne compresse efficacement que les modèles suboptimaux. Cela signifie que meilleur est le typage du modèle, plus faible est le taux de compression.
Dans l’exemple illustré ci dessous, 50 000 lignes contenant la chaîne 'xxx
'
on été insérées dans des tables dont seul le typage de la donnée varie. Le gain
obtenu avec la compression est relativement faible lorsque les types sont soit
déjà variables (varchar
), soit correctement définis (char(5)
).
Deuxième remarque : le surcoût d’un accès à une table compressée est
comparable au coût d’une jointure avec typage hétérogène (cast
), +10% en
plus.
Troisième remarque : bien que potentiellement plus efficace en terme
d’espace gagné, l’accès à un objet compressé par PAGE
est plus coûteux qu’une
simple compression par ROW
. Le choix d’une stratégie - par objet - doit donc
être réfléchi.
Mauvaise nouvelle : les pages compressées montées en cache restent compressées. C’est une mauvaise nouvelle car même si cela permet de stocker plus d’informations en mémoire, cela implique cependant un conversion systématique de ces données manipulées. Surconsommation CPU donc et dégradations de performance pour les données 'chaudes', même en lecture. Ces conversions systématiques des données compressées en cache nuisent à la montée en charge de l’instance. Conséquence : il est nécessaire de modifier la structure de la table en la partitionnant, de façon à ne compresser que les données - et les indexes - les moins sollicités.
Bancs d’essais - benchmarks
L’impact de la compression est tellement dépendant du modèle de données, de son typage, du volume de la table, de la puissance des machines, du compromis espace/cpu voulu, qu’il est hasardeux de fournir des mesures de performance.
L’exercice est quand même réalisé ici, exercice permettant à la fois de parcourir quelques syntaxes mais aussi de montrer des effets possibles.
Contexte des benchmarks
Un jeu d’enregistrements totalement artificiel de 200 Mo environ est créé. Ce jeu mélange plusieurs types différents : types fixes et variables, chaînes, valeurs numériques.
Plusieurs scénarios sont testés : compression par ligne, par page,
partitionnement, requête avec scan complet, sur un ensemble moyen et sur petits
volumes. Les résultats présentés sont les moyennes de 3 tests réalisés avec
données en cache, les résultats sont mesurés avec les commandes de base
statistics io, time
.
Les différents cas testés sont référencés comme suit dans les tableaux et/ou graphiques qui suivent :
Nom | Description |
---|---|
data |
Table classique, aucune compression ni partitionnement. |
comp_row |
La table est entièrement compressée en mode ROW . |
comp_page |
La table est entièrement compressée en mode PAGE . |
part |
La table possède 10 partitions, 1 par année. |
part_comp_row |
La table possède 10 partitions, 1 par année, les 8 premières sont
compressées en mode ROW . |
part_comp_page |
La table possède 10 partitions, 1 par année, les 8 premières sont
compressées en mode PAGE . |
-- -------------------------------------------------------------
-- table de réference
select top 50 name into #names from sysobjects
SELECT top 500 ID=IDENTITY (int, 1, 1) into #ids from sysobjects a, sysobjects b,sysobjects c
select top 10 status=ID into #status from #ids
-- structure initiale
select 'name' = convert(varchar(50), name) ,
'dt' = dateadd(day,- id,'20110101'),
'status' = status,
'value' = ABS(CAST(NEWID() AS binary(6)) % 1000) ,
'ownr' = convert(char(20), replicate('x',status)),
'comment'= convert(varchar(100),name + ' ' + CONVERT(char(50),id) + convert(varchar(2),status))
into data_ref
from #names
join #ids on 1=1
join #status on 1=1
where dateadd(day,- id,'20110101') between '20100101' and '20101231'
-- Pour la compression page : au moins 2 colonnes avec la même valeur
update data_ref set comment = name where ownr in ( 'xx','xxx' )
-- Ajout de l'historique
insert data_ref select name, DATEADD(year,-1,dt),status,value,ownr,comment
from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-2,dt),status,value,ownr,comment
from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-3,dt),status,value,ownr,comment
from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-4,dt),status,value,ownr,comment
from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-5,dt),status,value,ownr,comment
from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-6,dt),status,value,ownr,comment
from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-7,dt),status,value,ownr,comment
from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-8,dt),status,value,ownr,comment
from data_ref where dt between '20100101' and '20101231'
insert data_ref select name, DATEADD(year,-9,dt),status,value,ownr,comment
from data_ref where dt between '20100101' and '20101231'
go
create clustered index clu_dt on data_ref(dt)
go
-- -------------------------------------------------------------
-- Partitionnement
CREATE PARTITION FUNCTION [part_fct_dt](datetime)
AS RANGE LEFT FOR VALUES ('20020101','20030101','20040101','20050101','20060101',
'20070101','20080101','20090101','20100101','20110101')
CREATE PARTITION SCHEME [part_sch_dt] AS PARTITION [part_fct_dt]
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
-- -------------------------------------------------------------
-- table entière standard:
select * into data from data_ref
create clustered index clu_dt on data(dt) with sorted_data
-- -------------------------------------------------------------
-- table entière compressée : rows
select * into data_comp_row from data_ref
create clustered index clu_dt on data_comp_row(dt) with sorted_data
ALTER TABLE data_comp_row REBUILD WITH (DATA_COMPRESSION = ROW)
-- -------------------------------------------------------------
-- table entière compressée : page
select * into data_comp_page from data_ref
create clustered index clu_dt on data_comp_page(dt) with sorted_data
ALTER TABLE data_comp_page REBUILD WITH (DATA_COMPRESSION = PAGE);
-- -------------------------------------------------------------
-- Table partitionnée sans compression
select * into data_part from data_ref
create clustered index clu_dt on data_part(dt) ON part_sch_dt(dt)
go
-- -------------------------------------------------------------
-- Table partitionnée : partiellement compressée ROW
select * into data_part_comp_row from data_ref
create clustered index clu_dt on data_part_comp_row(dt) ON part_sch_dt(dt)
ALTER TABLE data_part_comp_row
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1 TO 8) ) ;
go
-- -------------------------------------------------------------
-- Table partitionnée : partiellement compressée PAGE
select * into data_part_comp_page from data_ref
create clustered index clu_dt on data_part_comp_page(dt) ON part_sch_dt(dt)
ALTER TABLE data_part_comp_page
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1 TO 8) )
Taille des objets
L’efficacité de la compression est patente, près de 20% de réduction pour le
mode ROW
et 40% pour le mode PAGE
.
À noter un impact important du partitionnement : les gains sont très
nettement moins élevés, respectivement 16% pour le mode ROW
et 30% pour le mode
PAGE
. Cela montre l’importance de l’ordre de tri des données lors de la prise
en compte de la compression.
Test #1 : Sélection grande plage - valeurs anciennes
Ce test consiste à extraire 6 mois de données - anciennes (dans la plage de compression), soit 107000 lignes.
select *
into #tmp1
from data
where dt between '20050601' and '20051231'
Pas de surprise, le nombre d’I/O relevé par requête est fonction du nombre de pages de la table. Autre élément prévisible, la compression par page est toujours plus lente que par ligne.
Sur les tables non partitionnées, la durée d’exécution est sensiblement identique. Pas de dégradation notable, bilan positif donc, bien que décevant au regard du nombre d’I/Os sauvés.
Le cas des tables partitionnées est plus inquiétant, la dégradation est très significative avec près de 50% de durée supplémentaire pour le mode PAGE alors même que le nombre d’I/Os est 40% inférieur.
Premier test mitigé donc.
Test #2 : Sélection plage moyenne - valeurs anciennes
Ce test consiste à extraire 1 mois de données - anciennes (dans la plage de compression), soit 15500 lignes.
select *
into #tmp1
from data
where dt between '20050601' and '20050701'
On retrouve notre nombre d’I/O fonction du nombre de pages de la table.
Confirmation du mauvais comportement de la compression par PAGE
pour une
table partitionnée : près de 50% de temps supplémentaire.
De manière générale la compression influe négativement sur les performances et de manière plus marquée que lors du test précédent. Rappel, les tests sont menés avec les données en cache, c’est donc le surcoût de la décompression de chaque ligne qui est observé ici.
Test #3 : Sélection grande plage - valeurs récentes
Ce test consiste à extraire 6 mois de données - récentes (dans la plage de données non compressées pour les partitions).
select *
into #tmp1
from data
where dt between '20100601' and '20101231'
Concernant les trois premiers tests pour la table non partitionnée, le résultat ne change pas par rapport au test #1.
Phénomène rassurant, les performances d’accès à des partitions non compressées sont cohérentes. Bien que les différentes mesures montrent une durée très légèrement - mais systématique - supérieure.
Test #4 : Sélection plage moyenne - valeurs récentes
select *
into #tmp1
from data
where dt between '20100601' and '20100701'
Les résultats, qui confirment ceux du test #2, sont une nouvelle fois
mitigés. Le coût de la décompression en cache a une influence très forte et
négative sur les performances. Dans le cadre d’une table non partitionnée, +60%
de dégradations pour une compression en mode ROW
et +100% pour une compression
en mode PAGE
, +35% de dégradations dans les autres cas.
Test #5 : insertions
Dans ce test, l’équivalent de 6 mois de données est inséré en mode insert/select
select * into #tmp1 from data_ref where dt between '20050601' and '20050701'
go
insert data select * from #tmp1
go
La différence en terme d’I/Os entre ces tests n’est pas significative ou
plutôt les quelques centaines d’I/O gagnés selon le contexte n’ont pas d’impact
positif sur les performances. En revanche, la conséquence est réelle pour les
performances : près de 30% de dégradation pour le mode PAGE
, 10% pour le mode
ROW
.
On retrouve peu ou prou le comportement illustré par la sélection du test #1.
Test #6 : Mises à jour
L’équivalent de 1 mois de données pour deux colonnes de type statique est modifié dans ce test.
update dataset comment='xxx', value=4 where dt between '20050601' and '20050701'
Aucune suprise, le comportement est cohérent avec les mesures réalisées jusqu’ici : moins d’I/Os mais plus longue durée d’exécution.
L’impact de la compression PAGE
est très négatif lors des mises à jour :
avec ce type de compression, le temps de mise à jour est 2 fois supérieur dans
le cas d’une table non partitionnée, et 4 fois supérieur dans le cas d’une
table partitionnée !
Test #7 : Suppressions
L’équivalent de 1 mois de données est supprimé ici.
delete datawhere dt between '20050601' and '20050701'
À noter que notre exemple contient un index clustered : la suppression provoque un tri et une réécriture de pages de données amplifiant les effets observés lors de la mise à jour.
Conclusion
La promesse de gain d’espace est tenue par cette option de compression de données, même si son efficacité n’est spectaculaire que sur des modèles incorrectement définis.
Cependant, moins d’I/Os induits ne signifie pas systématiquement des meilleurs temps de réponse, la transformation systématique des lignes compressées ayant encore un coût très important
Bien que décevants au regard de la promesse initiale, ces relatifs mauvais résultats ne doivent pas occulter les avantages certains de cette solution. Maîtriser la croissance d’une base, les coûts induits en terme de maintenance et de stockage rendent cette option totalement pertinente.
De même, les impacts négatifs peuvent s’avérer transparents ou du moins acceptables, selon le contexte de mise en place.
Aussi, l’implémentation de la compression ne peut être réalisée sans analyse ni tests préalables.
Quelques conseils
Avant toute mise en œuvre de la solution de compression
- évaluer la pertinence du modèle et son typage.
- partitionner les tables importantes en séparant les données utiles des autres.
- dans le cadre d’applications OLTP à forte concurrence d’accès, étudier très soigneusement la mise en place de la solution. La compression doit être appliquée à bon escient.
- privilégier la compression
ROW
.
Requêtes utiles
La compression est-elle utilisée dans la base en cours ?
if exists ( select 1 from sys.dm_db_persisted_sku_features where feature_name='Compression' )
print 'Compression utilisee'
else
print 'Pas de table compressee'
go
Quelles tables sont compressées ?
select 'name' = OBJECT_NAME(object_id), index_id, partition_number, rows, 'type' = data_compression_desc from sys.partitions where data_compression > 0 go
name index_id partition_number rows type -------------------------------------------------- test 0 1 20000 PAGE
Statistiques d’utilisation des objets
select * from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL)
go
...
retourne pas mal d’informations intéressantes comme le nombre de lectures, d’opérations d’écriture.
Supprimer une compression
alter table ... with ( data_compression = NONE )
go
Estimer le gain de compression
execute sp_estimate_data_compression_savings 'dbo', 'data', NULL, NULL, 'ROW' go
Object schema inx_id part# curSize(Kb) EstimateSize(Kb) ... ------- ------- ------- ------ ------------ ---------------- ... data dbo 1 1 245896 196512 ...
L’estimation est réalisée par échantillonage et le résultat ici très pertinent.
execute sp_spaceused data_comp_row go
Object rows reserved data ... -------------- ------- ------------ ---------- ... data_comp_row 1809500 197648 KB 196888 KB ...
Annexe
Démonstration de la compression ROW par calcul
La formule ci-dessous est extraite de MSDN (Estimating the size of a Heap ) et montre par le calcul l’effet du transtypage.
Table classique | Table compressée |
---|---|
-- NB = 2 + ((NC + 7) / 8) = 2 + ((1 + 7) / 8) = 3 -- RS = FDS + VDS + NB + 4 = 10 + 0 + 3 + 4 = 17 -- RPP = 8096 / (RS + 2) = 8096 / (17 + 2) = 426 -- NP = NR / RPP = 10000 / 426 = 24 |
-- NB = 2 + ((NC + 7) / 8) = 2 + ((1 + 7) / 8) = 3 -- RS = FDS + VDS + NB + 4 = 0 + 4 + 3 + 4 = 11 -- RPP = 8096 / (RS + 2) = 8096 / (11 + 2) = 622 -- NP = NR / RPP = 10000 / 622 = 16 |
On retrouve les valeurs équivalentes à celles, réelles, retournées par
sp_spaceused
.