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 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set ::testprefix io 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# io-6.* - Test that the pager-cache is not being flushed unnecessarily 44# after a transaction that uses the special atomic-write path 45# is committed. 46# 47 48set ::nWrite 0 49proc nWrite {db} { 50 set bt [btree_from_db $db] 51 db_enter $db 52 array set stats [btree_pager_stats $bt] 53 db_leave $db 54 set res [expr $stats(write) - $::nWrite] 55 set ::nWrite $stats(write) 56 set res 57} 58 59set ::nSync 0 60proc nSync {} { 61 set res [expr {$::sqlite_sync_count - $::nSync}] 62 set ::nSync $::sqlite_sync_count 63 set res 64} 65 66do_test io-1.1 { 67 execsql { 68 PRAGMA auto_vacuum = OFF; 69 PRAGMA page_size = 1024; 70 CREATE TABLE abc(a,b); 71 } 72 nWrite db 73} {2} 74 75# Insert into the table 4 records of aproximately 240 bytes each. 76# This should completely fill the root-page of the table. Each 77# INSERT causes 2 db pages to be written - the root-page of "abc" 78# and page 1 (db change-counter page). 79do_test io-1.2 { 80 set ret [list] 81 execsql { INSERT INTO abc VALUES(1,randstr(230,230)); } 82 lappend ret [nWrite db] 83 execsql { INSERT INTO abc VALUES(2,randstr(230,230)); } 84 lappend ret [nWrite db] 85 execsql { INSERT INTO abc VALUES(3,randstr(230,230)); } 86 lappend ret [nWrite db] 87 execsql { INSERT INTO abc VALUES(4,randstr(230,230)); } 88 lappend ret [nWrite db] 89} {2 2 2 2} 90 91# Insert another 240 byte record. This causes two leaf pages 92# to be added to the root page of abc. 4 pages in total 93# are written to the db file - the two leaf pages, the root 94# of abc and the change-counter page. 95do_test io-1.3 { 96 execsql { INSERT INTO abc VALUES(5,randstr(230,230)); } 97 nWrite db 98} {4} 99 100# Insert another 3 240 byte records. After this, the tree consists of 101# the root-node, which is close to empty, and two leaf pages, both of 102# which are full. 103do_test io-1.4 { 104 set ret [list] 105 execsql { INSERT INTO abc VALUES(6,randstr(230,230)); } 106 lappend ret [nWrite db] 107 execsql { INSERT INTO abc VALUES(7,randstr(230,230)); } 108 lappend ret [nWrite db] 109 execsql { INSERT INTO abc VALUES(8,randstr(230,230)); } 110 lappend ret [nWrite db] 111} {2 2 2} 112 113# This insert should use the quick-balance trick to add a third leaf 114# to the b-tree used to store table abc. It should only be necessary to 115# write to 3 pages to do this: the change-counter, the root-page and 116# the new leaf page. 117do_test io-1.5 { 118 execsql { INSERT INTO abc VALUES(9,randstr(230,230)); } 119 nWrite db 120} {3} 121 122ifcapable atomicwrite { 123 124#---------------------------------------------------------------------- 125# Test cases io-2.* test the atomic-write optimization. 126# 127do_test io-2.1 { 128 execsql { DELETE FROM abc; VACUUM; } 129} {} 130 131# Clear the write and sync counts. 132nWrite db ; nSync 133 134# The following INSERT updates 2 pages and requires 4 calls to fsync(): 135# 136# 1) The directory in which the journal file is created, 137# 2) The journal file (to sync the page data), 138# 3) The journal file (to sync the journal file header), 139# 4) The database file. 140# 141do_test io-2.2 { 142 execsql { INSERT INTO abc VALUES(1, 2) } 143 list [nWrite db] [nSync] 144} {2 4} 145 146# Set the device-characteristic mask to include the SQLITE_IOCAP_ATOMIC, 147# then do another INSERT similar to the one in io-2.2. This should 148# only write 1 page and require a single fsync(). 149# 150# The single fsync() is the database file. Only one page is reported as 151# written because page 1 - the change-counter page - is written using 152# an out-of-band method that bypasses the write counter. 153# 154# UPDATE: As of [05f98d4eec] (adding SQLITE_DBSTATUS_CACHE_WRITE), the 155# second write is also counted. So this now reports two writes and a 156# single fsync. 157# 158sqlite3_simulate_device -char atomic 159do_test io-2.3 { 160 execsql { INSERT INTO abc VALUES(3, 4) } 161 list [nWrite db] [nSync] 162} {2 1} 163 164# Test that the journal file is not created and the change-counter is 165# updated when the atomic-write optimization is used. 166# 167do_test io-2.4.1 { 168 execsql { 169 BEGIN; 170 INSERT INTO abc VALUES(5, 6); 171 } 172 sqlite3 db2 test.db -vfs devsym 173 execsql { SELECT * FROM abc } db2 174} {1 2 3 4} 175do_test io-2.4.2 { 176 file exists test.db-journal 177} {0} 178do_test io-2.4.3 { 179 execsql { COMMIT } 180 execsql { SELECT * FROM abc } db2 181} {1 2 3 4 5 6} 182db2 close 183 184# Test that the journal file is created and sync()d if the transaction 185# modifies more than one database page, even if the IOCAP_ATOMIC flag 186# is set. 187# 188do_test io-2.5.1 { 189 execsql { CREATE TABLE def(d, e) } 190 nWrite db ; nSync 191 execsql { 192 BEGIN; 193 INSERT INTO abc VALUES(7, 8); 194 } 195 file exists test.db-journal 196} {0} 197do_test io-2.5.2 { 198 execsql { INSERT INTO def VALUES('a', 'b'); } 199 file exists test.db-journal 200} {1} 201do_test io-2.5.3 { 202 execsql { COMMIT } 203 list [nWrite db] [nSync] 204} {3 4} 205 206# Test that the journal file is created and sync()d if the transaction 207# modifies a single database page and also appends a page to the file. 208# Internally, this case is handled differently to the one above. The 209# journal file is not actually created until the 'COMMIT' statement 210# is executed. 211# 212# Changed 2010-03-27: The size of the database is now stored in 213# bytes 28..31 and so when a page is added to the database, page 1 214# is immediately modified and the journal file immediately comes into 215# existence. To fix this test, the BEGIN is changed into a a 216# BEGIN IMMEDIATE and the INSERT is omitted. 217# 218do_test io-2.6.1 { 219 execsql { 220 BEGIN IMMEDIATE; 221 -- INSERT INTO abc VALUES(9, randstr(1000,1000)); 222 } 223 file exists test.db-journal 224} {0} 225do_test io-2.6.2 { 226 # Create a file at "test.db-journal". This will prevent SQLite from 227 # opening the journal for exclusive access. As a result, the COMMIT 228 # should fail with SQLITE_CANTOPEN and the transaction rolled back. 229 # 230 file mkdir test.db-journal 231 catchsql { 232 INSERT INTO abc VALUES(9, randstr(1000,1000)); 233 COMMIT 234 } 235} {1 {unable to open database file}} 236do_test io-2.6.3 { 237 forcedelete test.db-journal 238 catchsql { COMMIT } 239} {0 {}} 240do_test io-2.6.4 { 241 execsql { SELECT * FROM abc } 242} {1 2 3 4 5 6 7 8} 243 244# Test that if the database modification is part of multi-file commit, 245# the journal file is always created. In this case, the journal file 246# is created during execution of the COMMIT statement, so we have to 247# use the same technique to check that it is created as in the above 248# block. 249forcedelete test2.db test2.db-journal 250ifcapable attach { 251 do_test io-2.7.1 { 252 execsql { 253 ATTACH 'test2.db' AS aux; 254 PRAGMA aux.page_size = 1024; 255 CREATE TABLE aux.abc2(a, b); 256 BEGIN; 257 INSERT INTO abc VALUES(9, 10); 258 } 259 file exists test.db-journal 260 } {0} 261 do_test io-2.7.2 { 262 execsql { INSERT INTO abc2 SELECT * FROM abc } 263 file exists test2.db-journal 264 } {0} 265 do_test io-2.7.3 { 266 execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 } 267 } {1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10} 268 do_test io-2.7.4 { 269 file mkdir test2.db-journal 270 catchsql { COMMIT } 271 } {1 {unable to open database file}} 272 do_test io-2.7.5 { 273 forcedelete test2.db-journal 274 catchsql { COMMIT } 275 } {1 {cannot commit - no transaction is active}} 276 do_test io-2.7.6 { 277 execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 } 278 } {1 2 3 4 5 6 7 8} 279} 280 281# Try an explicit ROLLBACK before the journal file is created. 282# 283do_test io-2.8.1 { 284 execsql { 285 BEGIN; 286 DELETE FROM abc; 287 } 288 file exists test.db-journal 289} {0} 290do_test io-2.8.2 { 291 execsql { SELECT * FROM abc } 292} {} 293do_test io-2.8.3 { 294 execsql { 295 ROLLBACK; 296 SELECT * FROM abc; 297 } 298} {1 2 3 4 5 6 7 8} 299 300# Test that the atomic write optimisation is not enabled if the sector 301# size is larger than the page-size. 302# 303do_test io-2.9.1 { 304 db close 305 sqlite3 db test.db 306 sqlite3_simulate_device -char atomic -sectorsize 2048 307 execsql { 308 BEGIN; 309 INSERT INTO abc VALUES(9, 10); 310 } 311 file exists test.db-journal 312} {1} 313do_test io-2.9.2 { 314 execsql { ROLLBACK; } 315 db close 316 forcedelete test.db test.db-journal 317 sqlite3 db test.db -vfs devsym 318 execsql { 319 PRAGMA auto_vacuum = OFF; 320 PRAGMA page_size = 2048; 321 CREATE TABLE abc(a, b); 322 } 323 execsql { 324 BEGIN; 325 INSERT INTO abc VALUES(9, 10); 326 } 327 file exists test.db-journal 328} {0} 329do_test io-2.9.3 { 330 execsql { COMMIT } 331} {} 332 333# Test a couple of the more specific IOCAP_ATOMIC flags 334# (i.e IOCAP_ATOMIC2K etc.). 335# 336do_test io-2.10.1 { 337 sqlite3_simulate_device -char atomic1k 338 execsql { 339 BEGIN; 340 INSERT INTO abc VALUES(11, 12); 341 } 342 file exists test.db-journal 343} {1} 344do_test io-2.10.2 { 345 execsql { ROLLBACK } 346 sqlite3_simulate_device -char atomic2k 347 execsql { 348 BEGIN; 349 INSERT INTO abc VALUES(11, 12); 350 } 351 file exists test.db-journal 352} {0} 353do_test io-2.10.3 { 354 execsql { ROLLBACK } 355} {} 356 357do_test io-2.11.0 { 358 execsql { 359 PRAGMA locking_mode = exclusive; 360 PRAGMA locking_mode; 361 } 362} {exclusive exclusive} 363do_test io-2.11.1 { 364 execsql { 365 INSERT INTO abc VALUES(11, 12); 366 } 367 file exists test.db-journal 368} {0} 369 370do_test io-2.11.2 { 371 execsql { 372 PRAGMA locking_mode = normal; 373 INSERT INTO abc VALUES(13, 14); 374 } 375 file exists test.db-journal 376} {0} 377 378} ;# /* ifcapable atomicwrite */ 379 380#---------------------------------------------------------------------- 381# Test cases io-3.* test the IOCAP_SEQUENTIAL optimization. 382# 383sqlite3_simulate_device -char sequential -sectorsize 0 384ifcapable pager_pragmas { 385 do_test io-3.1 { 386 db close 387 forcedelete test.db test.db-journal 388 sqlite3 db test.db -vfs devsym 389 db eval { 390 PRAGMA auto_vacuum=OFF; 391 } 392 # File size might be 1 due to the hack to work around ticket #3260. 393 # Search for #3260 in os_unix.c for additional information. 394 expr {[file size test.db]>1} 395 } {0} 396 do_test io-3.2 { 397 execsql { CREATE TABLE abc(a, b) } 398 nSync 399 execsql { 400 PRAGMA temp_store = memory; 401 PRAGMA cache_size = 10; 402 BEGIN; 403 INSERT INTO abc VALUES('hello', 'world'); 404 INSERT INTO abc SELECT * FROM abc; 405 INSERT INTO abc SELECT * FROM abc; 406 INSERT INTO abc SELECT * FROM abc; 407 INSERT INTO abc SELECT * FROM abc; 408 INSERT INTO abc SELECT * FROM abc; 409 INSERT INTO abc SELECT * FROM abc; 410 INSERT INTO abc SELECT * FROM abc; 411 INSERT INTO abc SELECT * FROM abc; 412 INSERT INTO abc SELECT * FROM abc; 413 INSERT INTO abc SELECT * FROM abc; 414 INSERT INTO abc SELECT * FROM abc; 415 } 416 # File has grown - showing there was a cache-spill - but there 417 # have been no calls to fsync(). The file is probably about 30KB. 418 # But some VFS implementations (symbian) buffer writes so the actual 419 # size may be a little less than that. So this test case just tests 420 # that the file is now greater than 20000 bytes in size. 421 list [expr [file size test.db]>20000] [nSync] 422 } {1 0} 423 do_test io-3.3 { 424 # The COMMIT requires a single fsync() - to the database file. 425 execsql { COMMIT } 426 list [file size test.db] [nSync] 427 } "[expr {[nonzero_reserved_bytes]?40960:39936}] 1" 428} 429 430#---------------------------------------------------------------------- 431# Test cases io-4.* test the IOCAP_SAFE_APPEND optimization. 432# 433sqlite3_simulate_device -char safe_append 434 435# With the SAFE_APPEND flag set, simple transactions require 3, rather 436# than 4, calls to fsync(). The fsync() calls are on: 437# 438# 1) The directory in which the journal file is created, (unix only) 439# 2) The journal file (to sync the page data), 440# 3) The database file. 441# 442# Normally, when the SAFE_APPEND flag is not set, there is another fsync() 443# on the journal file between steps (2) and (3) above. 444# 445set expected_sync_count 2 446if {$::tcl_platform(platform)=="unix"} { 447 ifcapable dirsync { 448 incr expected_sync_count 449 } 450} 451 452do_test io-4.1 { 453 execsql { DELETE FROM abc } 454 nSync 455 execsql { INSERT INTO abc VALUES('a', 'b') } 456 nSync 457} $expected_sync_count 458 459# With SAFE_APPEND set, the nRec field of the journal file header should 460# be set to 0xFFFFFFFF before the first journal sync. The nRec field 461# occupies bytes 8-11 of the journal file. 462# 463do_test io-4.2.1 { 464 execsql { BEGIN } 465 execsql { INSERT INTO abc VALUES('c', 'd') } 466 file exists test.db-journal 467} {1} 468if {$::tcl_platform(platform)=="unix"} { 469 do_test io-4.2.2 { 470 hexio_read test.db-journal 8 4 471 } {FFFFFFFF} 472} 473do_test io-4.2.3 { 474 execsql { COMMIT } 475 nSync 476} $expected_sync_count 477sqlite3_simulate_device -char safe_append 478 479# With SAFE_APPEND set, there should only ever be one journal-header 480# written to the database, even though the sync-mode is "full". 481# 482do_test io-4.3.1 { 483 execsql { 484 INSERT INTO abc SELECT * FROM abc; 485 INSERT INTO abc SELECT * FROM abc; 486 INSERT INTO abc SELECT * FROM abc; 487 INSERT INTO abc SELECT * FROM abc; 488 INSERT INTO abc SELECT * FROM abc; 489 INSERT INTO abc SELECT * FROM abc; 490 INSERT INTO abc SELECT * FROM abc; 491 INSERT INTO abc SELECT * FROM abc; 492 INSERT INTO abc SELECT * FROM abc; 493 INSERT INTO abc SELECT * FROM abc; 494 INSERT INTO abc SELECT * FROM abc; 495 } 496 expr {[file size test.db]/1024} 497} {43} 498ifcapable pager_pragmas { 499 do_test io-4.3.2 { 500 execsql { 501 PRAGMA synchronous = full; 502 PRAGMA cache_size = 10; 503 PRAGMA synchronous; 504 } 505 } {2} 506} 507do_test io-4.3.3 { 508 execsql { 509 BEGIN; 510 UPDATE abc SET a = 'x'; 511 } 512 file exists test.db-journal 513} {1} 514if {$tcl_platform(platform) != "symbian"} { 515 # This test is not run on symbian because the file-buffer makes it 516 # difficult to predict the exact size of the file as reported by 517 # [file size]. 518 do_test io-4.3.4 { 519 # The UPDATE statement in the statement above modifies 41 pages 520 # (all pages in the database except page 1 and the root page of 521 # abc). Because the cache_size is set to 10, this must have required 522 # at least 4 cache-spills. If there were no journal headers written 523 # to the journal file after the cache-spill, then the size of the 524 # journal file is give by: 525 # 526 # <jrnl file size> = <jrnl header size> + nPage * (<page-size> + 8) 527 # 528 # If the journal file contains additional headers, this formula 529 # will not predict the size of the journal file. 530 # 531 file size test.db-journal 532 } [expr 512 + (1024+8)*41] 533} 534 535#---------------------------------------------------------------------- 536# Test cases io-5.* test that the default page size is selected and 537# used correctly. 538# 539set tn 0 540foreach {char sectorsize pgsize} { 541 {} 512 1024 542 {} 1024 1024 543 {} 2048 2048 544 {} 8192 8192 545 {} 16384 8192 546 {atomic} 512 8192 547 {atomic512} 512 1024 548 {atomic2K} 512 2048 549 {atomic2K} 4096 4096 550 {atomic2K atomic} 512 8192 551 {atomic64K} 512 1024 552} { 553 incr tn 554 if {$pgsize>$::SQLITE_MAX_PAGE_SIZE} continue 555 db close 556 forcedelete test.db test.db-journal 557 sqlite3_simulate_device -char $char -sectorsize $sectorsize 558 sqlite3 db test.db -vfs devsym 559 db eval { 560 PRAGMA auto_vacuum=OFF; 561 } 562 ifcapable !atomicwrite { 563 if {[regexp {^atomic} $char]} continue 564 } 565 do_test io-5.$tn { 566 execsql { 567 CREATE TABLE abc(a, b, c); 568 } 569 expr {[file size test.db]/2} 570 } $pgsize 571} 572 573#---------------------------------------------------------------------- 574# 575do_test io-6.1 { 576 db close 577 sqlite3_simulate_device -char atomic 578 forcedelete test.db 579 sqlite3 db test.db -vfs devsym 580 execsql { 581 PRAGMA mmap_size = 0; 582 PRAGMA page_size = 1024; 583 PRAGMA cache_size = 2000; 584 CREATE TABLE t1(x); 585 CREATE TABLE t2(x); 586 CREATE TABLE t3(x); 587 CREATE INDEX i3 ON t3(x); 588 INSERT INTO t3 VALUES(randomblob(100)); 589 INSERT INTO t3 SELECT randomblob(100) FROM t3; 590 INSERT INTO t3 SELECT randomblob(100) FROM t3; 591 INSERT INTO t3 SELECT randomblob(100) FROM t3; 592 INSERT INTO t3 SELECT randomblob(100) FROM t3; 593 INSERT INTO t3 SELECT randomblob(100) FROM t3; 594 INSERT INTO t3 SELECT randomblob(100) FROM t3; 595 INSERT INTO t3 SELECT randomblob(100) FROM t3; 596 INSERT INTO t3 SELECT randomblob(100) FROM t3; 597 INSERT INTO t3 SELECT randomblob(100) FROM t3; 598 INSERT INTO t3 SELECT randomblob(100) FROM t3; 599 INSERT INTO t3 SELECT randomblob(100) FROM t3; 600 } 601 602 db_save_and_close 603} {} 604 605foreach {tn sql} { 606 1 { BEGIN; 607 INSERT INTO t1 VALUES('123'); 608 INSERT INTO t2 VALUES('456'); 609 COMMIT; 610 } 611 2 { BEGIN; 612 INSERT INTO t1 VALUES('123'); 613 COMMIT; 614 } 615} { 616 617 # These tests don't work with memsubsys1, as it causes the effective page 618 # cache size to become too small to hold the entire db in memory. 619 if {[permutation] == "memsubsys1"} continue 620 621 db_restore 622 sqlite3 db test.db -vfs devsym 623 execsql { 624 PRAGMA cache_size = 2000; 625 PRAGMA mmap_size = 0; 626 SELECT x FROM t3 ORDER BY rowid; 627 SELECT x FROM t3 ORDER BY x; 628 } 629 do_execsql_test 6.2.$tn.1 { PRAGMA integrity_check } {ok} 630 do_execsql_test 6.2.$tn.2 $sql 631 632 # Corrupt the database file on disk. This should not matter for the 633 # purposes of the following "PRAGMA integrity_check", as the entire 634 # database should be cached in the pager-cache. If corruption is 635 # reported, it indicates that executing $sql caused the pager cache 636 # to be flushed. Which is a bug. 637 hexio_write test.db [expr 1024 * 5] [string repeat 00 2048] 638 do_execsql_test 6.2.$tn.3 { PRAGMA integrity_check } {ok} 639 db close 640} 641 642sqlite3_simulate_device -char {} -sectorsize 0 643finish_test 644