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