So my new understanding: This happens at the end of the write to the WAL file, but before the actual checkpoint. And then any checkpoint just works normally. So basically, even with journal_size_limit = 0, the WAL will always be at least as large as the last write, even if checkpointed successfully and completely.
-So I insert 10 GB of data, the WAL grows to 10GB. -At the end of writing to the WAL it says "yup, you don't have extra, so I'm leaving you alone." -The auto checkpoint runs, (let's says it completes everything), and rewinds the WAL (if possible) without truncating it, because auto checkpoints are passive. -Now the main DB file is all synched, but I have a 10GB WAL file, which stays that way until the next write. -I insert 1 byte of data -If the WAL was rewound successfully it writes the new data at the start of the WAL. -At the end of writing that to the WAL it says "wow you're bloated, I'm truncating you." and cuts it back to 1 page in size. -Checkpoint runs on the 1 byte insert. WAL stays at 1 page. Starting to make sense now, thanks. It was that "hey, I'm all checkpointed and synched up but the file's still huge" bit there throwing me off. Of course I have to ask again here, am I understanding it correctly now? -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy Sent: Friday, October 28, 2016 5:21 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] WAL pragma question 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
