The writer process doesn't ever do "truncating" checkpoints, even if it does handle checkpoints manually. I'm starting to realize that truncating checkpoints are a good idea, especially after going through "checkpoint starvation" moments...
Advertising
In that case, I think I could make your suggestion work. Thank you ! On 20 September 2016 at 11:57, Paul Sanderson <sandersonforens...@gmail.com> wrote: > From within SQLite - I don't know if its possible. > > But if you can query the WAL file size you should be able to determine > the number of pages easily enough. > > pages = (walfilesize-32)/(DBpagesize+24) > > the only caveats I can think of are: > > The WAL file is not truncated after a checkpoint so you would need to > make sure your checkpoint was done in truncate mode: > > PRAGMA wal_checkpoint(truncate) > > WAL auto checkpoints are always passive so you would need to disable > autocheckpointing and do all checkpoints yourself manually as above to > ensure WAL is truncated. > > PRAGAM wal_autocheckpoint = 0 > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQLite- > Forensic-Toolkit > -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > > > On 20 September 2016 at 08:59, Jose Arroyo <jose.m.arroyo...@gmail.com> > wrote: > > From looking at the source code, it seems to me that the values returned > > on wal_hooks come from some sqlite's internal variables so it doesn't > seem > > to be possible to query the DB for it. I suspect that the WAL size can be > > calculated from the WAL index file somehow, but I don't really grok how > it > > is actually used from the docs online ( > > https://www.sqlite.org/fileformat.html#wal_index_format). > > > > My current issue is that I'm encountering WAL checkpoint starvation and > I'm > > trying to figure out a way to create checkpoint gaps only when needed. > The > > scenario is pretty standard, one writer process and several readers > > processes. The writer notifies the readers in some specific DB updates > > scenarios, the readers query, do some stuff, update some fields by > > messaging the writer process who will queue up those updates. After a > > certain DB size, checkpoints start failing more and more often to the > point > > that the WAL size starts growing unbounded. > > > > The caveat is that all this happens under a SPARQL layer and that I don't > > have much room to modify how the writer process works. So I was wondering > > if I could access the current number of pages in the WAL from outside the > > writer process in a cheap way. > > > > Any advice or guidance is greatly appreciated > > > > Cheers, > > José M. Arroyo > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
