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