journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

Kniep Stefan (CM/ESN3)
I'm working on a database application that is shut down quite often. At each shutdown, only a small amount of data is written to the DB, which is in WAL mode. Due to HW restrictions, I had to reduce the total amount of data written to disk, which is why I have activated SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE.

This works fine as long as TRUNCATE checkpoints are issued from within my code. However, since the amount of data that is written into the DB at each shutdown varies, it is suboptimal to just trigger the checkpoint at each n-th shutdown. To get a better trade-off between the amount of written data and WAL size, it would be nice to trigger the checkpoints based on the size of the WAL. It seems that PRAGMA journal_size_limit doesn't help here: the WAL file grows infinitely when no checkpoints are issued from within my code. Is this the expected behavior (I guess so) or a configuration problem?

Note that PRAGMA wal_autocheckpoint also doesn't help, but this is pretty obvious because it only triggers a PASSIVE checkpoint. I would rather not access the WAL file directly (bypassing SQLite) for checking it's size. Does anybody have a proper solution for this problem?

P.S.: As a test, I have modified sqlite3WalClose() (and the place where it is called) to delete/truncate the WAL when journal_size_limit is reached. I had to change only two lines, but of course I am not sure that my change doesn't break anything.
--
Stefan Kniep

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

Simon Slavin-3


On 20 Nov 2017, at 3:41pm, Kniep Stefan (CM/ESN3) <[hidden email]> wrote:

> P.S.: As a test, I have modified sqlite3WalClose() (and the place where it is called) to delete/truncate the WAL when journal_size_limit is reached. I had to change only two lines, but of course I am not sure that my change doesn't break anything.

Do not delete the WAL file unless all connections to the database have been closed properly — without crashes, and without an error code being returned to SQLite.  Having an empty WAL file does not do exactly the same thing as having no WAL file.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users