Sybase ASE, comparaison SELECT INTO / INSERT SELECT

Introduction

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.

Contexte

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

Lire le contenu du journal de transaction

La commande dbcc log, détaillée par ailleurs sur sqlpac, permet de décortiquer le contenu du journal de transactions d’une base.

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

Résultats

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’écriture 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`

Conclusion

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.