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:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: db close
21: sqlite3_simulate_device
22: sqlite3 db test.db -vfs devsym
23:
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: #
42:
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: }
53:
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: }
60:
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}
69:
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}
85:
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}
94:
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}
107:
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}
116:
117: ifcapable atomicwrite {
118:
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: } {}
125:
126: # Clear the write and sync counts.
127: nWrite db ; nSync
128:
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}
140:
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}
154:
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
174:
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}
196:
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: forcedelete 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}
234:
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: forcedelete 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: forcedelete 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: }
271:
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}
290:
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: forcedelete 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: } {}
323:
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: } {}
347:
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}
360:
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}
368:
369: } ;# /* ifcapable atomicwrite */
370:
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: forcedelete 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: }
420:
421: #----------------------------------------------------------------------
422: # Test cases io-4.* test the IOCAP_SAFE_APPEND optimization.
423: #
424: sqlite3_simulate_device -char safe_append
425:
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: }
442:
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
449:
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
469:
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: }
525:
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: forcedelete 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: }
563:
564: sqlite3_simulate_device -char {} -sectorsize 0
565: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to io.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test