On Monday, August 02, 2010 4:25 AM, Nikolaus Rath wrote:
Hi,
Could someone clarify to me how the VACUUM command works if WAL is
enabled?
I would like to compact my database, but I am note sure if I should:
1) Run PRAGMA wal_checkpoint to get all outstanding commits into the
database file and then VACUUM to compact the database file,
or should I
2) Run VACUUM to create a compact version of the database file (but
writing into the WAL file) and then run PRAGMA wal_checkpoint to get
the changes done by VACUUM into the database file itself?
The documentation says that "The VACUUM command cleans the main database
by copying its contents to a temporary database file and reloading the
original database file from the copy". But it seems to me that this
might be a remnant from the days of the old journal, since now the WAL
file could be used instead of the temporary database.
Hi,
This is not a definitive answer, I'm afraid -- I also was waiting for some
insight on this -- but my understanding is that VACUUM still works
effectively the same way - i.e. it still copies the contents of the main
database into a temporary database file and reloads the original database
file from the copy. Also it only runs in exclusive mode (i.e. requires no
open transactions in order to complete).
This has a number of implications:
1. It doesn't store the "results" of the VACUUM in the wal journal file (the
temporary database file that it uses cannot be wal-enabled). Whether you do
a checkpoint before or not, I think shouldn't matter, since the algorithm
that copies the data will work equally well for all intents and purposes in
either case.
2. I'm not sure what a checkpoint after will actually acheive - isn't the
journal file invalidated by the replacement of the original database file
with the newly created one, and therefore is it not just ignored? (this is
a question really to the developers).
3. Because the database file is recreated, the wal journal mode setting is
actually dropped (I believe this is a bug) but you won't notice until you
close all the connections to the database file and then reconnect.
Given that this is the case, and now I'm making a suggestion to the sqlite
developers, I believe the VACUUM command should, when used on a wal-enabled
database, change the journal setting back to "delete", thereby forcing a
checkpoint and clean-up of the wal journal file, then do the vacuum, then
re-enable the wal journal setting on the new database file.
I've attached a patch to this email as a follow-up to this suggestion. The
patch very simply inserts OP_JournalMode opcodes before and after the normal
OP_Vacuum opcode where a wal-enabled database is detected. This
necessitated a small enhancement to OP_JournalMode to optionally return a
boolean on success rather than a string containing the journal mode. I've
added/modified some tests inside walbak.test. Test walbak-1.10 demonstrates
what I believe is the bug inside 3.7.0; the others had to be modified in
accordance with how the wal journal file is now handled by this patch.
Anyway, I hope this is helpful.
Andy
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
