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