SQLite: Fsyncing with journal_mode = wal and synchronous = normal

Vote count:

0

I have an interesting issue using SQLite 3.7.13 on Debian 7.

I'm using SQLite with "PRAGMA journal_mode = wal" and "PRAGMA synchronous = normal" to try and avoid fsyncs in the main event loop of a Python program. As suggested by the documentation I have disabled automatic checkpoints and I am periodically running "PRAGMA wal_checkpoint" in a different thread to sync the most recent data to disk.

This is working however I have found that the first operation after a checkpoint in the main program thread is causing a one-off fsync call for the WAL file itself. Any further operations do not cause an fsync. I have verified this using strace.

Looking at the SQLite docs it says:

WAL file header is synchronized when a WAL file begins to be reused after a checkpoint"

I'm looking for a way to prevent any fsyncs occurring in the main thread, but that still allows me to perform periodic checkpoints from another thread. Is there something more that can be done in the check point thread to avoid the fsync in the main thread?

I have looked at "synchronous = off", however that also blocks the fsyncs for the checkpoints.

Note: I have a separate connection to the database for each of the two threads (in case that is relevant)

Thanks

asked 1 min ago

Phil

58

SQLite: Fsyncing with journal_mode = wal and synchronous = normal