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