WAL journal file-size keeps on growing...
In my application I am using configuration of WAL-journal with synchronous set to NORMAL.
While running my unit-test application that put stress on sqlite, writing data from various threads, I found that the journal file-size keeps on growing forever.
Only when my unit-test terminates and all connections are closed - only then the journal file is removed.
At this point its size is ~2GB.
I read that using pragma journal_size_limit I can limit the size of the journal file.
I tried to use it but it had no effect. I am calling it right after the connection was opened: PRAGMA journal_size_limit=1000 Later when running the command PRAGMA journal_size_limit (with no parameters) I can see that the value was set, as 1000 is returned, however, the file keeps on growing until my program exits.
When in this mode WAL|NORMAL - Can I set the journal to be truncated every now and then - for example, at checkpoint time?
Thanks for any tips, PazO
(2) By Gunter Hick (gunter_hick) on 2020-11-10 15:55:05 in reply to 1 [link] [source]
Journal size limit is measured in bytes and only applies to an empty journal file. The WAL file will grow without bounds until a checkpoint takes place that reaches the very end of the WAL file. Usually, a checkpoint is performed when a commit causes the WAL file to be longer than 1000 pages (PAGES, not bytes). There are conditions when running a checkpoint to completion is not possible, like disabled checkpointing, checkpoint starvation because of open read transactions and large write transactions.
For testing purposes you might want to have your code force a checkpoint at some point using
and see whether this shrinks the WAL file as expected. If it doesn't, something is preventing checkpointing. Probably a _step() which is never terminated with _finalize() or _reset(). If it does then something weirder is going on.
As explained elsewhere, the WAL file is a different file to the journal file. There's no way to limit the WAL file apart from checkpointing frequently.
After doing some more tests:
I tried both PRAGMA journal_size_limit and the SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT build-flag.
Both worked the same. Using the build-flag I set max file-size to 10 MB (10485760 bytes).
The behavior is like so:
When I run my high-stress test, the journal file-size climb up with no boundary, reaching ~1.5GB before test terminated.
When test terminated the file disappears.
I will mention that the underlying database size AFTER ALL UPDATES is ~6MB.
If I introduce occasional sleep in my test threads then file-size goes up and down between ~50MB and 10MB (my max-size limit).
This shows that the occasional checkpoints do work when they can.
When stress terminated and before connections are closed I inserted ~1 minute sleep.
During this time file-size remained on 10MB so I assume that during my real program lifetime it will stay on this size...
This is cloud solve your problem: https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md
But note the caveat in the last paragraph of the first section:
A database in wal2 mode may only be accessed by versions of SQLite compiled from this branch. Attempting to use any other version of SQLite results in an SQLITE_NOTADB error.
Where "this branch" means: