On 10/28/2016 03:16 AM, David Raymond wrote:
I'm playing around with WAL mode here for the first time, along with some of
the pragmas, and I'm getting some weird results. I was hoping someone could let
me know if I'm missing something, or if yes, it is indeed weird.
For starters, I'm looking at the journal_size_limit pragma.
http://www.sqlite.org/pragma.html#pragma_journal_size_limit
In its description it does say that it works for WAL mode. "To always truncate rollback journals and WAL
files to their minimum size, set the journal_size_limit to zero." So I create a new database, turn on
WAL mode, set that pragma, create a table, and insert some stuff into it. Then I check the file sizes, and
the -wal file hasn't shrunk at all. I made sure it was large enough to go over the wal_autocheckpoint
threshold and it didn't shrink. I ran a manual "pragma wal_checkpoint;" and it didn't shrink. I
tried with specifying passive, full, and restart and it didn't shrink. It seems that I can only get it to
shrink by doing an explicit "pragma wal_checkpoint(truncate);" But if that's the only way to shrink
the file down, then what's the point of the pragma here?
Or, as is more likely, what obvious thing is my brain missing at the end of the
day?
In wal mode, the wal file is truncated according to "PRAGMA
journal_size_limit" after the first transaction is written following a
checkpoint. Or, technically, after a writer writes a transaction into
the beginning of the physical file. So:
sqlite> PRAGMA journal_mode = wal;
wal
sqlite> PRAGMA journal_size_limit = 0;
0
sqlite> CREATE TABLE t1(a, b);
sqlite> INSERT INTO t1 VALUES(randomblob(100000), randomblob(100000));
/* WAL file is now roughly 200KiB */
sqlite> PRAGMA wal_checkpoint;
0|52|52
/* Still roughly 200KiB */
sqlite> INSERT INTO t1 VALUES(1, 1);
/* Now truncated to 4KiB */
This is because the locking scheme only allows writers to modify the wal
file, not checkpointers.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
