On 10/28/2016 09:53 PM, David Raymond wrote:
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.
Right. The truncation is the last step in a database write, not part of
a checkpoint operation.
-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?
Yep, that's it.
Dan.
-----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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
