Il existe deux méthodes pour créer et alimenter un objet, soit en utilisant la commande de définition explicite CREATE TABLE, soit en dupliquant une structure existante, définition implicite, par SELECT INTO.
La documentation Sybase nous apprend qu'il est préférable d'utiliser les méthodes SELECT INTO TABLE plutôt que CREATE TABLE INSERT SELECT parce que cette opération est dite 'minimally logged', c'est à dire que seules les pages d'allocation sont journalisées, et non les données.
L'usage de tables temporaires rend la compréhension de ces deux mécaniques importantes à connaitre. Cette étude mesure les différences de comportement.
Le comportement est étudié avec un serveur Sybase Adaptive Server Enterprise 15.0.2 en lançant séquentiellement les deux tests dans une base dédiée, les différentes mesures étant réalisées via sp_sysmon, set statistics et/ou dbcc log.
disk init name='data_bench', size='200M', physname='/tmp/data_bench.dat' disk init name='log_bench', size='200M', physname='/tmp/log_bench.dat' go create database bench on data_bench=200 log on log_bench=200 go sp_dboption bench, 'select into', true go use bench go checkpoint go
Une table simple sans typage exotique de 2 millions de lignes est utilisée dans ce test.
CREATE TABLE bench..test ( INSTRUMENT_ID int NOT NULL, AS_ID int NOT NULL, DI_ID int NOT NULL, RETRIEVAL_DATE datetime NOT NULL, VALUE float NOT NULL, RECORD_STATUS char(1) NOT NULL ) go
Une fois alimentée, elle a les caractéristiques suivantes :
optdiag statistics bench..test -S ... -U ... -P ...
Statistics for table: "test"
Data page count: 35334
Empty data page count: 0
Data row count: 2155367.0000000000000000
Forwarded row count: 0.0000000000000000
Deleted row count: 0.0000000000000000
Data page CR count: 4434.0000000000000000
OAM + allocation page count: 144
First extent data pages: 0
Data row size: 31.0000000000000000
Parallel join degree: 0.0000000000000000
Unused page count: 7
OAM page count: 1
La commande dbcc log, détaillée par ailleurs sur sqlpac, permet de décortiquer le contenu du journal de transactions d'une base.
cat dbcclog.sql
dbcc traceon(3604) go dbcc log(6,0,0,0,0,-1,1) go
Pour la base bench (dbid 6) , toutes (-1) les entêtes (1) d'évènements danns le journal sont extraites.
isql -S ... -U ... -P ... -i dbcclog.sql -o dbcclog.log
Le contenu extrait ressemble à ceci :
head -20 dbcclog.log
LOG SCAN DEFINITION:
Database id : 6
Forward scan: starting at beginning of log
LOG RECORDS:
MODIFY (431135,0) sessionid=431124,3
attcnt=1 rno=0 op=9 padlen=0 sessionid=431124,3 len=120
odc_stat=0x0000 (0x0000)
loh_status: 0x0 (0x00000000)
objid=2032007239 ptnid=2032007239 pageno=937 offset=0 status=0x800 (0x0800 (XSTAT_EXPAGE))
cid=0 indid=0
old ts=0x0000 0x02cf2ef7 new ts=0x0000 0x02d04414
xvallen=32
DOL_UPDATE (431135,1) sessionid=431124,3
attcnt=1 rno=1 op=65 padlen=4 sessionid=431124,3 len=72
odc_stat=0x0000 (0x0000)
loh_status: 0x0 (0x00000000)
objectid=28 syspartitions ptnid=28 pageno=35 rowno=23 cid=0 erl=0
status (xstat)=0x00 (0x0000)
En appliquant un filtre awk sur le fichier généré, l'activité transactionnelle est résumée en quelques lignes tout en conservant le détail des opérations :
nawk '
NF == 3 && $3 ~ "sessionid=" {
tab[$3 " " $1]++
}
END {
for ( i in tab ) {
print i, tab[i] }
}' dbcclog.log | sort
Ce qui donne :
sessionid=197486,30 ENDXACT 1 sessionid=197487,1 CHECKPOINT 1 sessionid=197487,2 BEGINXACT 1 ...
| Mesure | select into | insert select |
|---|---|---|
| Commande | select * into bench..test from db..matable go |
insert into bench..test select * from b..matable go |
| Durée
set statistics time |
6 secondes | 13 secondes |
| E/S
set statistics io |
Table: test scan count 1, logical reads: (regular=2157596 ... Table: matable scan count 1, logical reads: (regular=35349 ... Total writes for this command: 5312 |
Table: test scan count 1, logical reads: (regular=2226310 ... Table: matable scan count 1, logical reads: (regular=35349 ... Total writes for this command: 45541 Environ 70 000 lectures et 40 000 écritures de plus par la méthode insert/select dans la table cible par rapport à la méthode select into. |
| Compteurs systèmes
sp_sysmon begin_sample ..end_sample |
Transaction Profile APL Heap Table 2 156 556 Data Only Lock Table 54 Transaction Management by Full ULC 9 ULC Log Records 1 613 Transaction Log Writes 43 Transaction Log Alloc 98 Lock Management Total Lock Requests 35 944 Data Cache Management Large I/Os Performed 4 347 2K LRU Buffer Grab 13 4K LRU Buffer Grab 40 16K LRU Buffer Grab 4 298 Disk I/O Management data dev Total I/Os 5 270 log dev Total I/Os 43 |
Transaction Profile APL Heap Table 2 156 558 Data Only Lock Table 43 Transaction Management by Full ULC 50 584 ULC Log Records 2 226 881 Transaction Log Writes 51 395 Transaction Log Alloc 101 319 Lock Management Total Lock Requests 36 195 Data Cache Management Large I/Os Performed 51 144 2K LRU Buffer Grab 728 4K LRU Buffer Grab 50 451 16K LRU Buffer Grab 2 Disk I/O Management data dev Total I/Os 27 045 log dev Total I/Os 51 967 La gestion du journal est radicalement différente d'une méthode à l'autre : autant d'entrées dans le journal que de lignes à écrire dans le second cas (insert/select), nombre très faible dans le premier cas (select into). Tous les autres paramètres évoluent en fonction de ce comportement : plus de journalisation implique un usage plus important du pool de 4K dans le cache mais aussi du device de log |
| Taille consommée dans le journal log
sp_helpsegment 'logsegment' |
3M | 200M
La taille de la table est de 70 Mo : l'ecriture des données coûte 200 Mo dans le journal. 270 Mo ont été écrits au total pour cette opération (70 de données et 200 de journal). |
| Contenu du journal
dbcc log |
... sessionid=431040,4 BEGINXACT 1 sessionid=431040,4 BT_DELETE 13 sessionid=431040,4 DOL_DELETE 10 sessionid=431040,4 ENDXACT 1 sessionid=431041,9 BEGINXACT 1 sessionid=431041,9 ALLOC 1 sessionid=431041,9 BT_INSERT 14 sessionid=431041,9 DBNEXTID 1 sessionid=431041,9 DOL_INSERT 11 sessionid=431041,9 DOL_UPDATE 3 sessionid=431041,9 OAMATPUT 1 sessionid=431041,9 OAMCREATE 1 sessionid=431041,9 OAMENTRYMOVE 1 sessionid=431041,9 OAMINSERT 1 sessionid=431041,9 ENDXACT 1 sessionid=431043,6 BEGINXACT 1 sessionid=431043,6 AEXTENT 140 sessionid=431043,6 ALLOC 973 sessionid=431043,6 DOL_UPDATE 2 sessionid=431043,6 OAMINSERT 139 sessionid=431043,6 MODIFY 2 sessionid=431043,6 SOPGDEALLOC 24 sessionid=431043,6 ENDXACT 1 sessionid=431123,19 BEGINXACT 1 sessionid=431123,19 DOL_UPDATE 1 sessionid=431123,19 ENDXACT 1 Les étapes de création de la table sont retrouvées (évènement ALLOC, OAM% etc...), les opérations de type BT correspondent à la manipulation des tables systèmes sysobjects, sysindexes, systabstats, sysstatistics syspartitions, syscolumns |
... sessionid=197487,2 BEGINXACT 1 sessionid=197487,2 LOGDEALLOC 372 sessionid=197487,2 MODIFY 7 sessionid=197487,2 ENDXACT 1 sessionid=197496,24 CHECKPOINT 1 sessionid=197496,25 BEGINXACT 1 sessionid=197496,25 INSERT 2155367 sessionid=197496,25 ALLOC 35333 sessionid=197496,25 DOL_UPDATE 35333 sessionid=197496,25 OAMINSERT 138 sessionid=197496,25 ENDXACT 1 sessionid=299184,5 CHECKPOINT 1 |
La journalisation induite par la syntaxe INSERT SELECT est incomparablement plus coûteuse en terme de cache, d'E/S, de taille de journal et donc de durée que la commande equivalente SELECT INTO.
La méthode SELECT INTO est non seulement plus performante mais permet d'obtenir un bien meilleurs niveau de concurrence d'accès
En mode INSERT/SELECT, en fin de traitement, 200 Mo de journal ont été utilisés. Les données de la table occupent 70 Mo, d'où proviennent alors les 130 autres Mo ?
Si la séquence est détaillée, il apparaît une succession de 61 insertions (INSERT), une allocation (ALLOC) puis une mise à jour de syspartitions (DOL_UPDATE). Concernant les 61 insertions, optdiag indique une taille moyenne de ligne (Data row size) de 31o, soit une consommation de 2K (31*61 =~ 2K)
Les deux opérations ALLOC et DOL_UPDATE ont un impact important sur l'opération : 70 000 écritures à elles-deux dans le journal en pages de 2K et qui correspondent à une consommation de 130 Mo, soit plus que les données mêmes.
Pour un usage temporaire, hors contexte de réplication standby, il est donc fortement recommandé d'éviter la journalisation et donc de privilegier la méthode SELECT INTO plutôt que la méthode INSERT SELECT.
| Version | Date | Commentaires |
|---|---|---|
| 1.0 | 03/2010 | Version initiale |
SQLPAC : Sybase - Lire les journaux transactions avec dbcc log