SQLite Forum

WAL journal file-size keeps on growing...
Login

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:

https://www.sqlite.org/cgi/src/timeline?r=wal2