SQLITE database WAL file size keeps growing

Question

I am writing continuously into a db file which has PRAGMA journal_mode=WAL, PRAGMA journal_size_limit=0. My C++ program has two threads, one reader(queries at 15 sec intervals) and one writer(inserts at 5 sec intervals).

Every 3 min I am pausing insertion to run a sqlite3_wal_checkpoint_v2() from the writer thread with the mode parameter as SQLITE_CHECKPOINT_RESTART. To ensure that no active read operations are going on at this point, I set a flag that checkpointing is about to take place and wait for reader to complete (the connection is still open) before running checkpoint. After checkpoint completion I again indicate to readers it is okay to resume querying.

sqlite3_wal_checkpoint_v2() returns SQLITE_OK, and pnLog and Ckpt as equal(around 4000), indicating complete wal file has been synced with main db file. So next write should start from beginning according to documentation. However, this does not seem to be happening as the subsequent writes cause the WAL file to grow indefinitely, eventually up to some GBs.

I did some searching and found that that readers can cause checkpoint failure due to open transactions. However, the only reader I'm using is ending its transaction before the checkpoint starts. What else could be preventing the WAL file from not growing?

Omen

Answer 1

This is far too late as an answer, but may be useful to other people.

According to the SQLite documentation, your expectations should be correct, but if you read this SO post, problems arise also in case of non-finalized statements. Therefore, if you just sqlite3_reset() your statement, there are chances anyway that the db may look busy or locked for a checkpoint. Note that this may happen also with higher levels of SQLITE_CHECKPOINT_values.

Also, the SQLITE_CHECKPOINT_TRUNCATE value, if checkout is successfully operated, will truncate the -wal file to zero length. That may help you check that all pages have been inserted in the db.

Another discussion in which -wal files grow larger and larger due to unfinalized statements is this.

Gibeon

Other discussions on this and on WAL file growing size: on SO, a bug for mozilla; a second one. – Gibeon

Opening my database file with sqlite3 database.db, then sqlite> PRAGMA wal_checkpoint(SQLITE_CHECKPOINT_TRUNCATE); fixed the problem for me – Mclain