Re: [sqlite] [WAL] Querying/Finding out the WAL size (in pages) without trying to checkpoint

[Simon Slavin]

On 20 Sep 2016, at 2:39pm, Jose Arroyo  wrote:

> However, this writer process has no control over the reader processes, so
> checkpoints may not necessary complete successfully ("If another connection
> has a read transaction open, then the checkpoint cannot reset the WAL file
> because doing so might delete content out from under the reader"). A large
> WAL means slow queries, which means fewer gaps where a checkpoint could
> finish successfully, so this issue is a bit of a vicious circle.

Okay, I understand your explanation now.  I can't think of any immediate 
obvious solution but I hope others can.

It does seem, however, that your problem is in your reader threads, not your 
writer thread.  I would do my best to ensure that my reader threads exerted 
locks for as little time as possible (maybe by using transactions) and that 
they did not hog the database file (perhaps introducing a mutual sleep for one 
second every ten seconds or every 100 seconds).  This should ensure that your 
writing thread could checkpoint at least that frequently.

But you know your code and the limitations of the API you're working through 
and may know that these aren't possible.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [WAL] Querying/Finding out the WAL size (in pages) without trying to checkpoint

[Jose Arroyo]

On 20 September 2016 at 14:29, Simon Slavin  wrote:

>
> On 20 Sep 2016, at 8:59am, Jose Arroyo  wrote:
>
> > 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.
>
> Can I ask why you're trying to have as few checkpoints as possible ?  The
> fewer you have, the more time they take, the more annoying it is to your
> users, the more processes reach their timeout times.
>
> I can understand that there are times when you want as few checkpoints as
> possible, but if you explain why then we may be able to suggest a strategy
> which doesn't involve hacking SQLite and becoming dependent on a particular
> version behaving a specific way.
>
> For instance, you could use "PRAGMA wal_autocheckpoint=N" and use a
> smaller value for N than 1000.  That sort-of does what you asked since it
> will automatically fire when the WAL journal reaches a certain size (of
> unwritten data).
>
> Another common strategy is that every process which does writing has a
> checkpoint every 1000 transactions.  Or 100.  or 10.  Whatever suits.
> Again, it is a system which takes care of itself and will not stop working
> if a new version of SQLite handles WAL files differently.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Sorry, I used the wrong word, I meant "reader gaps" not "checkpoint gaps"

I don't exactly want to minimize the number of checkpoints, what I want is
figuring out a way to force a successful checkpoint if the WAL starts
growing unbounded, by leaving a "reader gap" without the writer process
itself being the one responsible for handling this "reader gap".

My problem is that with the dynamics of how the writer and the reader
processes interact, there is a certain DB size threshold after which long
periods of checkpoint starvation occur. The writer process (there is only
on in my scenario) actually implements it's own wal checkpointing
mechanism, where it tries to checkpoint in a separate thread when wal_pages
> 1000, and tries to checkpoint synchronously while blocking all queued
updates if ever wal_pages > 1.

However, this writer process has no control over the reader processes, so
checkpoints may not necessary complete successfully ("If another connection
has a read transaction open, then the checkpoint cannot reset the WAL file
because doing so might delete content out from under the reader"). A large
WAL means slow queries, which means fewer gaps where a checkpoint could
finish successfully, so this issue is a bit of a vicious circle.

I can't modify the writer process too much, and without a busy_handler that
notifies all readers to stop for a while any wal_checkpoints that block
readers (FULL, RESTART, TRUNCATE)  just end up "timeout'ing" during these
"checkpoint starvation" periods (this is linked to the SPARQL layer on top
of SQLite that I'm working with, I think).

So that's why I'm looking for a way of detecting unusual WAL growth
moments, notify all readers so that they leave just enough time for a
checkpoint to complete but avoid doing this unless absolutely necessary. If
the writer process does TRUNCATE checkpoints after a certain WAL size,
monitoring the WAL file size directly, as Paul mentioned, might be a good
enough approach.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [WAL] Querying/Finding out the WAL size (in pages) without trying to checkpoint

[Simon Slavin]

On 20 Sep 2016, at 8:59am, Jose Arroyo  wrote:

> 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.

Can I ask why you're trying to have as few checkpoints as possible ?  The fewer 
you have, the more time they take, the more annoying it is to your users, the 
more processes reach their timeout times.

I can understand that there are times when you want as few checkpoints as 
possible, but if you explain why then we may be able to suggest a strategy 
which doesn't involve hacking SQLite and becoming dependent on a particular 
version behaving a specific way.

For instance, you could use "PRAGMA wal_autocheckpoint=N" and use a smaller 
value for N than 1000.  That sort-of does what you asked since it will 
automatically fire when the WAL journal reaches a certain size (of unwritten 
data).

Another common strategy is that every process which does writing has a 
checkpoint every 1000 transactions.  Or 100.  or 10.  Whatever suits.  Again, 
it is a system which takes care of itself and will not stop working if a new 
version of SQLite handles WAL files differently.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [WAL] Querying/Finding out the WAL size (in pages) without trying to checkpoint

[Paul Sanderson]

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  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 
> 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 
>> 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

Re: [sqlite] [WAL] Querying/Finding out the WAL size (in pages) without trying to checkpoint

[Jose Arroyo]

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 
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 
> 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

Re: [sqlite] [WAL] Querying/Finding out the WAL size (in pages) without trying to checkpoint

[Paul Sanderson]

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  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