Checkpointing (PASSIVE) at every close() - Was: Get wal page count out of context of wal_hook?
There is some other way to look at my original question. Or to cast some light on it.
SQLite does a full checkpoint on the last attachment close to a database file, actually checkpointing whatever left in the wal file and finally removing the wal/shm files.
I have implemented a PASSIVE checkpoint right before every attachment close in my application, because the way it is designed, it is a nice opportunity to pay a small delay for that checkpointing: the bulk of the work for the consumer process has been done, and while that passive checkpoint and close occurs, nothing/nobody is waiting on it, other workloads might be at work or starting, but no user pay a (small) hit with this checkpointing (passive, so probably partially) at close. Because after the close the work-unit is done. If the user continues to interact, that will be through the help of other work-units.
This of course is in addition to the full final checkpoint that SQLite will do on last close because chances are that anyway, I have multiple concurrent attachments to a single database for a long time. The last close won't typically occur before hours.
The above passive checkpoint on every attachment close (which are generally not the last close) is better, for this application architecture, than a checkpoint occurring on commit, after some delay (number of pages in the wal files for instance). The checkpoint on commit will imply a delay for the caller of the commit, delay which might impact the overall delay of computing something. That the caller work-unit commits does not imply it is done with what it has do, yet it will pay a (small) unexpected price on commit() and might delay its answer to its consumer task.
The scheme works well. But could be slightly even better if right before doing a passive checkpoint the application could decide wether to do it or postpone it, based on the number of pages in the wal file. The idea being that there is no real need to do it unless there are clearly a potential for some amount of work to be done. This is exactly what the default automatic checkpointing of SQLite does (by default on 1000 pages or more), but at commit time. I'm interested to do this same thing at close() time (those closes which are not the last one).
Using wal_hook() each attachment can be informed of that number of wal pages at commit time and decide to checkpoint or not. It can also take note of the number of pages. Which could allow it to decide later, before its own close(), to checkpoint or not. But that behaviour implies that the current attachment has done commit()s recently. Else the information they have on the count of wal pages is at best very inexact and at worse (an attachment which made no commit) inexistant. Implementing it this way is inherently wrong and it currently is better to blindly call checkpoint passive right before each close().
This is why I was looking for a proper API way to query for the count of wal pages; to use it before each attachment close() and decide wether or not I take this opportunity to first run a passive checkpoint.
Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Le 25 nov. 2017 à 09:21, Olivier Mascia <[hidden email]> a écrit :
> Dear all,
> I'm looking for a mean to query the current WAL page count (at other times than at commit through the wal_hook() callback).
> Did I overlooked something in the C API which would allow me to query this?