AryaWu/sqlite
0
1# 2007 August 212#3# The author disclaims copyright to this source code. In place of4# 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 out14# more database pages than it has to, stuff like that).15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set ::testprefix io20 21db close22sqlite3_simulate_device23sqlite3 db test.db -vfs devsym24 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 nRec38# 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 path45# is committed.46# 47 48set ::nWrite 049proc nWrite {db} {50 set bt [btree_from_db $db]51 db_enter $db52 array set stats [btree_pager_stats $bt]53 db_leave $db54 set res [expr $stats(write) - $::nWrite]55 set ::nWrite $stats(write)56 set res57}58 59set ::nSync 060proc nSync {} {61 set res [expr {$::sqlite_sync_count - $::nSync}]62 set ::nSync $::sqlite_sync_count63 set res64}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 db73} {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. Each77# 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 pages92# to be added to the root page of abc. 4 pages in total93# are written to the db file - the two leaf pages, the root94# of abc and the change-counter page.95do_test io-1.3 {96 execsql { INSERT INTO abc VALUES(5,randstr(230,230)); }97 nWrite db98} {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 leaf114# to the b-tree used to store table abc. It should only be necessary to115# write to 3 pages to do this: the change-counter, the root-page and116# the new leaf page.117do_test io-1.5 {118 execsql { INSERT INTO abc VALUES(9,randstr(230,230)); }119 nWrite db120} {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 ; nSync133 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 should148# only write 1 page and require a single fsync().149# 150# The single fsync() is the database file. Only one page is reported as151# written because page 1 - the change-counter page - is written using152# an out-of-band method that bypasses the write counter.153#154# UPDATE: As of [05f98d4eec] (adding SQLITE_DBSTATUS_CACHE_WRITE), the155# second write is also counted. So this now reports two writes and a156# single fsync.157#158sqlite3_simulate_device -char atomic159do_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 is165# 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 devsym173 execsql { SELECT * FROM abc } db2174} {1 2 3 4}175do_test io-2.4.2 {176 file exists test.db-journal177} {0}178do_test io-2.4.3 {179 execsql { COMMIT }180 execsql { SELECT * FROM abc } db2181} {1 2 3 4 5 6}182db2 close183 184# Test that the journal file is created and sync()d if the transaction185# modifies more than one database page, even if the IOCAP_ATOMIC flag186# is set.187#188do_test io-2.5.1 {189 execsql { CREATE TABLE def(d, e) }190 nWrite db ; nSync191 execsql {192 BEGIN;193 INSERT INTO abc VALUES(7, 8);194 }195 file exists test.db-journal196} {0}197do_test io-2.5.2 {198 execsql { INSERT INTO def VALUES('a', 'b'); }199 file exists test.db-journal200} {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 transaction207# modifies a single database page and also appends a page to the file.208# Internally, this case is handled differently to the one above. The209# journal file is not actually created until the 'COMMIT' statement210# 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 1214# is immediately modified and the journal file immediately comes into215# existence. To fix this test, the BEGIN is changed into a a216# 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-journal224} {0}225do_test io-2.6.2 {226 # Create a file at "test.db-journal". This will prevent SQLite from227 # opening the journal for exclusive access. As a result, the COMMIT228 # should fail with SQLITE_CANTOPEN and the transaction rolled back.229 #230 file mkdir test.db-journal231 catchsql {232 INSERT INTO abc VALUES(9, randstr(1000,1000));233 COMMIT234 }235} {1 {unable to open database file}}236do_test io-2.6.3 {237 forcedelete test.db-journal238 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 file246# is created during execution of the COMMIT statement, so we have to247# use the same technique to check that it is created as in the above 248# block.249forcedelete test2.db test2.db-journal250ifcapable 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-journal260 } {0}261 do_test io-2.7.2 {262 execsql { INSERT INTO abc2 SELECT * FROM abc }263 file exists test2.db-journal264 } {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-journal270 catchsql { COMMIT }271 } {1 {unable to open database file}}272 do_test io-2.7.5 {273 forcedelete test2.db-journal274 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-journal289} {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 sector301# size is larger than the page-size.302#303do_test io-2.9.1 {304 db close305 sqlite3 db test.db306 sqlite3_simulate_device -char atomic -sectorsize 2048307 execsql {308 BEGIN;309 INSERT INTO abc VALUES(9, 10);310 }311 file exists test.db-journal312} {1}313do_test io-2.9.2 {314 execsql { ROLLBACK; }315 db close316 forcedelete test.db test.db-journal317 sqlite3 db test.db -vfs devsym318 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-journal328} {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 atomic1k338 execsql {339 BEGIN;340 INSERT INTO abc VALUES(11, 12);341 }342 file exists test.db-journal343} {1}344do_test io-2.10.2 {345 execsql { ROLLBACK }346 sqlite3_simulate_device -char atomic2k347 execsql {348 BEGIN;349 INSERT INTO abc VALUES(11, 12);350 }351 file exists test.db-journal352} {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-journal368} {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-journal376} {0}377 378} ;# /* ifcapable atomicwrite */379 380#----------------------------------------------------------------------381# Test cases io-3.* test the IOCAP_SEQUENTIAL optimization.382#383sqlite3_simulate_device -char sequential -sectorsize 0384ifcapable pager_pragmas {385 do_test io-3.1 {386 db close387 forcedelete test.db test.db-journal388 sqlite3 db test.db -vfs devsym389 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 nSync399 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 actual419 # size may be a little less than that. So this test case just tests420 # 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_append434 435# With the SAFE_APPEND flag set, simple transactions require 3, rather436# 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 2446if {$::tcl_platform(os) ne "Windows NT"} {447 ifcapable dirsync {448 incr expected_sync_count449 }450}451 452do_test io-4.1 {453 execsql { DELETE FROM abc }454 nSync455 execsql { INSERT INTO abc VALUES('a', 'b') }456 nSync457} $expected_sync_count458 459# With SAFE_APPEND set, the nRec field of the journal file header should460# be set to 0xFFFFFFFF before the first journal sync. The nRec field461# 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-journal467} {1}468if {$::tcl_platform(platform) eq "unix"} {469 do_test io-4.2.2 {470 hexio_read test.db-journal 8 4471 } {FFFFFFFF}472}473do_test io-4.2.3 {474 execsql { COMMIT }475 nSync476} $expected_sync_count477sqlite3_simulate_device -char safe_append478 479# With SAFE_APPEND set, there should only ever be one journal-header480# 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-journal513} {1}514if {$tcl_platform(platform) != "symbian"} {515 # This test is not run on symbian because the file-buffer makes it516 # 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 required522 # at least 4 cache-spills. If there were no journal headers written523 # to the journal file after the cache-spill, then the size of the524 # 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 formula529 # will not predict the size of the journal file.530 #531 file size test.db-journal532 } [expr 512 + (1024+8)*41]533}534 535#----------------------------------------------------------------------536# Test cases io-5.* test that the default page size is selected and537# used correctly.538#539set tn 0540foreach {char sectorsize pgsize} {541 {} 512 1024542 {} 1024 1024543 {} 2048 2048544 {} 8192 8192545 {} 16384 8192546 {atomic} 512 8192547 {atomic512} 512 1024548 {atomic2K} 512 2048549 {atomic2K} 4096 4096550 {atomic2K atomic} 512 8192551 {atomic64K} 512 1024552} {553 incr tn554 if {$pgsize>$::SQLITE_MAX_PAGE_SIZE} continue555 db close556 forcedelete test.db test.db-journal557 sqlite3_simulate_device -char $char -sectorsize $sectorsize558 sqlite3 db test.db -vfs devsym559 db eval {560 PRAGMA auto_vacuum=OFF;561 }562 ifcapable !atomicwrite {563 if {[regexp {^atomic} $char]} continue564 }565 do_test io-5.$tn {566 execsql {567 CREATE TABLE abc(a, b, c);568 }569 expr {[file size test.db]/2}570 } $pgsize571}572 573#----------------------------------------------------------------------574#575do_test io-6.1 {576 db close577 sqlite3_simulate_device -char atomic578 forcedelete test.db579 sqlite3 db test.db -vfs devsym580 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_close603} {}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 page618 # cache size to become too small to hold the entire db in memory.619 if {[permutation] == "memsubsys1"} continue620 621 db_restore622 sqlite3 db test.db -vfs devsym623 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 $sql631 632 # Corrupt the database file on disk. This should not matter for the633 # purposes of the following "PRAGMA integrity_check", as the entire634 # database should be cached in the pager-cache. If corruption is635 # reported, it indicates that executing $sql caused the pager cache636 # 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 close640}641 642sqlite3_simulate_device -char {} -sectorsize 0643unregister_devsim644 645finish_test646