WAL journal file-size keeps on growing...
(1) By PazO (JohnSmith) on 2020-11-10 14:30:07 [link] [source]
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.
My question:
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.
(3) By Simon Slavin (slavin) on 2020-11-11 23:48:53 in reply to 1 [link] [source]
For testing purposes you might want to have your code force a checkpoint at some point using
https://sqlite.org/c3ref/wal_checkpoint.html
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.
(4) By PazO (JohnSmith) on 2020-11-12 08:38:11 in reply to 3 [source]
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...
(5) By phpstatic on 2020-11-18 15:51:27 in reply to 4 [link] [source]
This is cloud solve your problem: https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md
(6) By Stephan Beal (stephan) on 2020-11-18 15:56:37 in reply to 5 [link] [source]
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: