1# 2007 August 21 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# 12# The focus of this file is testing some specific characteristics of the 13# IO traffic generated by SQLite (making sure SQLite is not writing out 14# more database pages than it has to, stuff like that). 15# 16# $Id: io.test,v 1.12 2007/10/09 08:29:32 danielk1977 Exp $ 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# Test summary: 22# 23# io-1.* - Test that quick-balance does not journal pages unnecessarily. 24# 25# io-2.* - Test the "atomic-write optimization". 26# 27# io-3.* - Test the IO traffic enhancements triggered when the 28# IOCAP_SEQUENTIAL device capability flag is set (no 29# fsync() calls on the journal file). 30# 31# io-4.* - Test the IO traffic enhancements triggered when the 32# IOCAP_SAFE_APPEND device capability flag is set (fewer 33# fsync() calls on the journal file, no need to set nRec 34# field in the single journal header). 35# 36# io-5.* - Test that the default page size is selected and used 37# correctly. 38# 39 40set ::nWrite 0 41proc nWrite {db} { 42 set bt [btree_from_db $db] 43 db_enter $db 44 array set stats [btree_pager_stats $bt] 45 db_leave $db 46 set res [expr $stats(write) - $::nWrite] 47 set ::nWrite $stats(write) 48 set res 49} 50 51set ::nSync 0 52proc nSync {} { 53 set res [expr {$::sqlite_sync_count - $::nSync}] 54 set ::nSync $::sqlite_sync_count 55 set res 56} 57 58do_test io-1.1 { 59 execsql { 60 PRAGMA auto_vacuum = OFF; 61 PRAGMA page_size = 1024; 62 CREATE TABLE abc(a,b); 63 } 64 nWrite db 65} {2} 66 67# Insert into the table 4 records of aproximately 240 bytes each. 68# This should completely fill the root-page of the table. Each 69# INSERT causes 2 db pages to be written - the root-page of "abc" 70# and page 1 (db change-counter page). 71do_test io-1.2 { 72 set ret [list] 73 execsql { INSERT INTO abc VALUES(1,randstr(230,230)); } 74 lappend ret [nWrite db] 75 execsql { INSERT INTO abc VALUES(2,randstr(230,230)); } 76 lappend ret [nWrite db] 77 execsql { INSERT INTO abc VALUES(3,randstr(230,230)); } 78 lappend ret [nWrite db] 79 execsql { INSERT INTO abc VALUES(4,randstr(230,230)); } 80 lappend ret [nWrite db] 81} {2 2 2 2} 82 83# Insert another 240 byte record. This causes two leaf pages 84# to be added to the root page of abc. 4 pages in total 85# are written to the db file - the two leaf pages, the root 86# of abc and the change-counter page. 87do_test io-1.3 { 88 execsql { INSERT INTO abc VALUES(5,randstr(230,230)); } 89 nWrite db 90} {4} 91 92# Insert another 3 240 byte records. After this, the tree consists of 93# the root-node, which is close to empty, and two leaf pages, both of 94# which are full. 95do_test io-1.4 { 96 set ret [list] 97 execsql { INSERT INTO abc VALUES(6,randstr(230,230)); } 98 lappend ret [nWrite db] 99 execsql { INSERT INTO abc VALUES(7,randstr(230,230)); } 100 lappend ret [nWrite db] 101 execsql { INSERT INTO abc VALUES(8,randstr(230,230)); } 102 lappend ret [nWrite db] 103} {2 2 2} 104 105# This insert should use the quick-balance trick to add a third leaf 106# to the b-tree used to store table abc. It should only be necessary to 107# write to 3 pages to do this: the change-counter, the root-page and 108# the new leaf page. 109do_test io-1.5 { 110 execsql { INSERT INTO abc VALUES(9,randstr(230,230)); } 111 nWrite db 112} {3} 113 114ifcapable atomicwrite { 115 116#---------------------------------------------------------------------- 117# Test cases io-2.* test the atomic-write optimization. 118# 119do_test io-2.1 { 120 execsql { DELETE FROM abc; VACUUM; } 121} {} 122 123# Clear the write and sync counts. 124nWrite db ; nSync 125 126# The following INSERT updates 2 pages and requires 4 calls to fsync(): 127# 128# 1) The directory in which the journal file is created, 129# 2) The journal file (to sync the page data), 130# 3) The journal file (to sync the journal file header), 131# 4) The database file. 132# 133do_test io-2.2 { 134 execsql { INSERT INTO abc VALUES(1, 2) } 135 list [nWrite db] [nSync] 136} {2 4} 137 138# Set the device-characteristic mask to include the SQLITE_IOCAP_ATOMIC, 139# then do another INSERT similar to the one in io-2.2. This should 140# only write 1 page and require a single fsync(). 141# 142# The single fsync() is the database file. Only one page is reported as 143# written because page 1 - the change-counter page - is written using 144# an out-of-band method that bypasses the write counter. 145# 146sqlite3_simulate_device -char atomic 147do_test io-2.3 { 148 execsql { INSERT INTO abc VALUES(3, 4) } 149 list [nWrite db] [nSync] 150} {1 1} 151 152# Test that the journal file is not created and the change-counter is 153# updated when the atomic-write optimization is used. 154# 155do_test io-2.4.1 { 156 execsql { 157 BEGIN; 158 INSERT INTO abc VALUES(5, 6); 159 } 160 sqlite3 db2 test.db 161 execsql { SELECT * FROM abc } db2 162} {1 2 3 4} 163do_test io-2.4.2 { 164 file exists test.db-journal 165} {0} 166do_test io-2.4.3 { 167 execsql { COMMIT } 168 execsql { SELECT * FROM abc } db2 169} {1 2 3 4 5 6} 170db2 close 171 172# Test that the journal file is created and sync()d if the transaction 173# modifies more than one database page, even if the IOCAP_ATOMIC flag 174# is set. 175# 176do_test io-2.5.1 { 177 execsql { CREATE TABLE def(d, e) } 178 nWrite db ; nSync 179 execsql { 180 BEGIN; 181 INSERT INTO abc VALUES(7, 8); 182 } 183 file exists test.db-journal 184} {0} 185do_test io-2.5.2 { 186 execsql { INSERT INTO def VALUES('a', 'b'); } 187 file exists test.db-journal 188} {1} 189do_test io-2.5.3 { 190 execsql { COMMIT } 191 list [nWrite db] [nSync] 192} {3 4} 193 194# Test that the journal file is created and sync()d if the transaction 195# modifies a single database page and also appends a page to the file. 196# Internally, this case is handled differently to the one above. The 197# journal file is not actually created until the 'COMMIT' statement 198# is executed. 199# 200do_test io-2.6.1 { 201 execsql { 202 BEGIN; 203 INSERT INTO abc VALUES(9, randstr(1000,1000)); 204 } 205 file exists test.db-journal 206} {0} 207do_test io-2.6.2 { 208 # Create a file at "test.db-journal". This will prevent SQLite from 209 # opening the journal for exclusive access. As a result, the COMMIT 210 # should fail with SQLITE_CANTOPEN and the transaction rolled back. 211 # 212 set fd [open test.db-journal w] 213 puts $fd "This is not a journal file" 214 close $fd 215 catchsql { COMMIT } 216} {1 {unable to open database file}} 217do_test io-2.6.3 { 218 file delete -force test.db-journal 219 catchsql { COMMIT } 220} {1 {cannot commit - no transaction is active}} 221do_test io-2.6.4 { 222 execsql { SELECT * FROM abc } 223} {1 2 3 4 5 6 7 8} 224 225 226# Test that if the database modification is part of multi-file commit, 227# the journal file is always created. In this case, the journal file 228# is created during execution of the COMMIT statement, so we have to 229# use the same technique to check that it is created as in the above 230# block. 231file delete -force test2.db test2.db-journal 232ifcapable attach { 233 do_test io-2.7.1 { 234 execsql { 235 ATTACH 'test2.db' AS aux; 236 PRAGMA aux.page_size = 1024; 237 CREATE TABLE aux.abc2(a, b); 238 BEGIN; 239 INSERT INTO abc VALUES(9, 10); 240 } 241 file exists test.db-journal 242 } {0} 243 do_test io-2.7.2 { 244 execsql { INSERT INTO abc2 SELECT * FROM abc } 245 file exists test2.db-journal 246 } {0} 247 do_test io-2.7.3 { 248 execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 } 249 } {1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10} 250 do_test io-2.7.4 { 251 set fd [open test2.db-journal w] 252 puts $fd "This is not a journal file" 253 close $fd 254 catchsql { COMMIT } 255 } {1 {unable to open database file}} 256 do_test io-2.7.5 { 257 file delete -force test2.db-journal 258 catchsql { COMMIT } 259 } {1 {cannot commit - no transaction is active}} 260 do_test io-2.7.6 { 261 execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 } 262 } {1 2 3 4 5 6 7 8} 263} 264 265# Try an explicit ROLLBACK before the journal file is created. 266# 267do_test io-2.8.1 { 268 execsql { 269 BEGIN; 270 DELETE FROM abc; 271 } 272 file exists test.db-journal 273} {0} 274do_test io-2.8.2 { 275 execsql { SELECT * FROM abc } 276} {} 277do_test io-2.8.3 { 278 execsql { 279 ROLLBACK; 280 SELECT * FROM abc; 281 } 282} {1 2 3 4 5 6 7 8} 283 284# Test that the atomic write optimisation is not enabled if the sector 285# size is larger than the page-size. 286# 287do_test io-2.9.1 { 288 sqlite3_simulate_device -char atomic -sectorsize 2048 289 execsql { 290 BEGIN; 291 INSERT INTO abc VALUES(9, 10); 292 } 293 file exists test.db-journal 294} {1} 295do_test io-2.9.2 { 296 execsql { ROLLBACK; } 297 db close 298 file delete -force test.db test.db-journal 299 sqlite3 db test.db 300 execsql { 301 PRAGMA auto_vacuum = OFF; 302 PRAGMA page_size = 2048; 303 CREATE TABLE abc(a, b); 304 } 305 execsql { 306 BEGIN; 307 INSERT INTO abc VALUES(9, 10); 308 } 309 file exists test.db-journal 310} {0} 311do_test io-2.9.3 { 312 execsql { COMMIT } 313} {} 314 315# Test a couple of the more specific IOCAP_ATOMIC flags 316# (i.e IOCAP_ATOMIC2K etc.). 317# 318do_test io-2.10.1 { 319 sqlite3_simulate_device -char atomic1k 320 execsql { 321 BEGIN; 322 INSERT INTO abc VALUES(11, 12); 323 } 324 file exists test.db-journal 325} {1} 326do_test io-2.10.2 { 327 execsql { ROLLBACK } 328 sqlite3_simulate_device -char atomic2k 329 execsql { 330 BEGIN; 331 INSERT INTO abc VALUES(11, 12); 332 } 333 file exists test.db-journal 334} {0} 335do_test io-2.10.3 { 336 execsql { ROLLBACK } 337} {} 338 339do_test io-2.11.0 { 340 execsql { 341 PRAGMA locking_mode = exclusive; 342 PRAGMA locking_mode; 343 } 344} {exclusive exclusive} 345breakpoint 346do_test io-2.11.1 { 347 execsql { 348 INSERT INTO abc VALUES(11, 12); 349 } 350 file exists test.db-journal 351} {0} 352breakpoint 353 354do_test io-2.11.2 { 355 execsql { 356 PRAGMA locking_mode = normal; 357 INSERT INTO abc VALUES(13, 14); 358 } 359 file exists test.db-journal 360} {0} 361 362} ;# /* ifcapable atomicwrite */ 363 364#---------------------------------------------------------------------- 365# Test cases io-3.* test the IOCAP_SEQUENTIAL optimization. 366# 367sqlite3_simulate_device -char sequential -sectorsize 0 368ifcapable pager_pragmas { 369 do_test io-3.1 { 370 db close 371 file delete -force test.db test.db-journal 372 sqlite3 db test.db 373 db eval { 374 PRAGMA auto_vacuum=OFF; 375 } 376 file size test.db 377 } {0} 378 do_test io-3.2 { 379 execsql { CREATE TABLE abc(a, b) } 380 nSync 381 execsql { 382 PRAGMA cache_size = 10; 383 BEGIN; 384 INSERT INTO abc VALUES('hello', 'world'); 385 INSERT INTO abc SELECT * FROM abc; 386 INSERT INTO abc SELECT * FROM abc; 387 INSERT INTO abc SELECT * FROM abc; 388 INSERT INTO abc SELECT * FROM abc; 389 INSERT INTO abc SELECT * FROM abc; 390 INSERT INTO abc SELECT * FROM abc; 391 INSERT INTO abc SELECT * FROM abc; 392 INSERT INTO abc SELECT * FROM abc; 393 INSERT INTO abc SELECT * FROM abc; 394 INSERT INTO abc SELECT * FROM abc; 395 INSERT INTO abc SELECT * FROM abc; 396 } 397 # File has grown - showing there was a cache-spill - but there 398 # have been no calls to fsync(): 399 list [file size test.db] [nSync] 400 } {31744 0} 401 do_test io-3.3 { 402 # The COMMIT requires a single fsync() - to the database file. 403 execsql { COMMIT } 404 list [file size test.db] [nSync] 405 } {39936 1} 406} 407 408#---------------------------------------------------------------------- 409# Test cases io-4.* test the IOCAP_SAFE_APPEND optimization. 410# 411sqlite3_simulate_device -char safe_append 412 413# With the SAFE_APPEND flag set, simple transactions require 3, rather 414# than 4, calls to fsync(). The fsync() calls are on: 415# 416# 1) The directory in which the journal file is created, (unix only) 417# 2) The journal file (to sync the page data), 418# 3) The database file. 419# 420# Normally, when the SAFE_APPEND flag is not set, there is another fsync() 421# on the journal file between steps (2) and (3) above. 422# 423if {$::tcl_platform(platform)=="unix"} { 424 set expected_sync_count 3 425} else { 426 set expected_sync_count 2 427} 428do_test io-4.1 { 429 execsql { DELETE FROM abc } 430 nSync 431 execsql { INSERT INTO abc VALUES('a', 'b') } 432 nSync 433} $expected_sync_count 434 435# With SAFE_APPEND set, the nRec field of the journal file header should 436# be set to 0xFFFFFFFF before the first journal sync. The nRec field 437# occupies bytes 8-11 of the journal file. 438# 439do_test io-4.2.1 { 440 execsql { BEGIN } 441 execsql { INSERT INTO abc VALUES('c', 'd') } 442 file exists test.db-journal 443} {1} 444if {$::tcl_platform(platform)=="unix"} { 445 do_test io-4.2.2 { 446 set fd [open test.db-journal] 447 fconfigure $fd -translation binary -encoding binary 448 seek $fd 8 449 set blob [read $fd 4] 450 close $fd 451 binary scan $blob i res 452 format 0x%X $res 453 } {0xFFFFFFFF} 454} 455do_test io-4.2.3 { 456 execsql { COMMIT } 457 nSync 458} $expected_sync_count 459sqlite3_simulate_device -char safe_append 460 461# With SAFE_APPEND set, there should only ever be one journal-header 462# written to the database, even though the sync-mode is "full". 463# 464do_test io-4.3.1 { 465 execsql { 466 INSERT INTO abc SELECT * FROM abc; 467 INSERT INTO abc SELECT * FROM abc; 468 INSERT INTO abc SELECT * FROM abc; 469 INSERT INTO abc SELECT * FROM abc; 470 INSERT INTO abc SELECT * FROM abc; 471 INSERT INTO abc SELECT * FROM abc; 472 INSERT INTO abc SELECT * FROM abc; 473 INSERT INTO abc SELECT * FROM abc; 474 INSERT INTO abc SELECT * FROM abc; 475 INSERT INTO abc SELECT * FROM abc; 476 INSERT INTO abc SELECT * FROM abc; 477 } 478 expr {[file size test.db]/1024} 479} {43} 480ifcapable pager_pragmas { 481 do_test io-4.3.2 { 482 execsql { 483 PRAGMA synchronous = full; 484 PRAGMA cache_size = 10; 485 PRAGMA synchronous; 486 } 487 } {2} 488} 489do_test io-4.3.3 { 490 execsql { 491 BEGIN; 492 UPDATE abc SET a = 'x'; 493 } 494 file exists test.db-journal 495} {1} 496do_test io-4.3.4 { 497 # The UPDATE statement in the statement above modifies 41 pages 498 # (all pages in the database except page 1 and the root page of 499 # abc). Because the cache_size is set to 10, this must have required 500 # at least 4 cache-spills. If there were no journal headers written 501 # to the journal file after the cache-spill, then the size of the 502 # journal file is give by: 503 # 504 # <jrnl file size> = <jrnl header size> + nPage * (<page-size> + 8) 505 # 506 # If the journal file contains additional headers, this formula 507 # will not predict the size of the journal file. 508 # 509 file size test.db-journal 510} [expr 1024 + (1024+8)*41] 511 512#---------------------------------------------------------------------- 513# Test cases io-5.* test that the default page size is selected and 514# used correctly. 515# 516set tn 0 517foreach {char sectorsize pgsize} { 518 {} 512 1024 519 {} 1024 1024 520 {} 2048 2048 521 {} 8192 8192 522 {} 16384 8192 523 {atomic} 512 8192 524 {atomic512} 512 1024 525 {atomic2K} 512 2048 526 {atomic2K} 4096 4096 527 {atomic2K atomic} 512 8192 528 {atomic64K} 512 1024 529} { 530 incr tn 531 if {$pgsize>$::SQLITE_MAX_PAGE_SIZE} continue 532 db close 533 file delete -force test.db test.db-journal 534 sqlite3_simulate_device -char $char -sectorsize $sectorsize 535 sqlite3 db test.db 536 db eval { 537 PRAGMA auto_vacuum=OFF; 538 } 539 ifcapable !atomicwrite { 540 if {[regexp {^atomic} $char]} continue 541 } 542 do_test io-5.$tn { 543 execsql { 544 CREATE TABLE abc(a, b, c); 545 } 546 expr {[file size test.db]/2} 547 } $pgsize 548} 549 550sqlite3_simulate_device -char {} -sectorsize 0 551finish_test 552