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]

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

> 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>