WAL journal file-size keeps on growing...
(1) By PazO (JohnSmith) on 2020-11-10 14:30:07
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.<br> Only when my unit-test terminates and all connections are closed - only then the journal file is removed.<br> At this point its size is ~2GB. I read that using pragma **journal_size_limit** I can limit the size of the journal file.<br> 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:<br> 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]
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]
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 [link]
After doing some more tests: I tried both **PRAGMA journal_size_limit** and the **SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT** build-flag.<br> Both worked the same. Using the build-flag I set max file-size to 10 MB (10485760 bytes). The behavior is like so:<br> When I run my high-stress test, the journal file-size climb up with no boundary, reaching ~1.5GB before test terminated. <br> When test terminated the file disappears.<br> 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).<br> This shows that the occasional checkpoints do work when they can. When stress terminated **and before connections are closed** I inserted ~1 minute sleep.<br> 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]
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]
> 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: <https://www.sqlite.org/cgi/src/timeline?r=wal2>