Please let me know if this works for you - I've never tried it so at the moment it's just an idea :) 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 13:05, Jose Arroyo <jose.m.arroyo...@gmail.com> wrote: > 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... > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
