Re: [sqlite] WAL checkpoint starved?

[Daniel Polski]

Den 2017-06-07 kl. 17:09, skrev Simon Slavin:


On 7 Jun 2017, at 1:49pm, Daniel Polski  wrote:


Ok, have I understood this correctly:

If doing a manual checkpoint with SQLITE_CHECKPOINT_TRUNCATE, that call will 
block for maximum the time set by the busy_timeout while trying to proceed.
If the busy timeout is 0, the call will return immediately if something 
currently is blocking checkpoint progress.
If the busy timeout is set to something > 0, the checkpoint call will retry to 
proceed during the timeout.

Richard answered the part about checkpoints.  In WAL mode, instead of being 
blocked, a reading thread sees the data as it was when the last transaction 
finished.  This is in contrast to the older non-WAL mode, where reading could 
block writing.  However in both modes, one writing thread will block another.

I write about timeouts.  If you do not set a timeout, SQLite immediately treats 
access contention as a fatal error, and returns with SQLITE_BUSY or 
SQLITE_LOCKED.  If you do set a timeout, SQLite will keep attempting access for 
up to that time.  If it eventually gets access it returns with a success result 
code as if there had never been any problem.


Aha, thank you both for the explanations.
I guess one possible reason for the checkpoint to return an error (when 
using a timeout) could be if there is a transaction opened before the 
checkpoint call, which is still using the WAL file when the timeout 
expires..?
Got any more examples which could make it return an error I should be 
aware of?


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

Re: [sqlite] WAL checkpoint starved?

[Simon Slavin]

On 7 Jun 2017, at 1:49pm, Daniel Polski  wrote:

> Ok, have I understood this correctly:
> 
> If doing a manual checkpoint with SQLITE_CHECKPOINT_TRUNCATE, that call will 
> block for maximum the time set by the busy_timeout while trying to proceed.
> If the busy timeout is 0, the call will return immediately if something 
> currently is blocking checkpoint progress.
> If the busy timeout is set to something > 0, the checkpoint call will retry 
> to proceed during the timeout.

Richard answered the part about checkpoints.  In WAL mode, instead of being 
blocked, a reading thread sees the data as it was when the last transaction 
finished.  This is in contrast to the older non-WAL mode, where reading could 
block writing.  However in both modes, one writing thread will block another.

I write about timeouts.  If you do not set a timeout, SQLite immediately treats 
access contention as a fatal error, and returns with SQLITE_BUSY or 
SQLITE_LOCKED.  If you do set a timeout, SQLite will keep attempting access for 
up to that time.  If it eventually gets access it returns with a success result 
code as if there had never been any problem.

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

Re: [sqlite] WAL checkpoint starved?

[Richard Hipp]

On 6/7/17, Daniel Polski  wrote:
>
>
> Den 2017-06-07 kl. 15:02, skrev Richard Hipp:
>> On 6/7/17, Daniel Polski  wrote:
>>> Does the [TRUNCATE] checkpoint call lock out new requests which might
>>> prohibit
>>> checkpoint progress while waiting for the timeout?
>> It prohibits new writers.  New readers are allowed to proceed.
>>
>>> What will happen with other connections new read/write requests during
>>> the timeout while the checkpointing is running..?
>> Readers proceed normally.  Writers fail with an SQLITE_BUSY errors.
>>
>
> Aha, can those new readers in turn block the checkpointing from proceeding?

No.  Because the new readers will be referencing the most recent
commit, they will not block the checkpoint.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL checkpoint starved?

[Daniel Polski]

Den 2017-06-07 kl. 15:02, skrev Richard Hipp:

On 6/7/17, Daniel Polski  wrote:

Does the [TRUNCATE] checkpoint call lock out new requests which might prohibit
checkpoint progress while waiting for the timeout?

It prohibits new writers.  New readers are allowed to proceed.


What will happen with other connections new read/write requests during
the timeout while the checkpointing is running..?

Readers proceed normally.  Writers fail with an SQLITE_BUSY errors.



Aha, can those new readers in turn block the checkpointing from proceeding?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL checkpoint starved?

[Richard Hipp]

On 6/7/17, Daniel Polski  wrote:
> Does the [TRUNCATE] checkpoint call lock out new requests which might prohibit
> checkpoint progress while waiting for the timeout?

It prohibits new writers.  New readers are allowed to proceed.

> What will happen with other connections new read/write requests during
> the timeout while the checkpointing is running..?

Readers proceed normally.  Writers fail with an SQLITE_BUSY errors.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL checkpoint starved?

[Daniel Polski]

Den 2017-06-05 kl. 17:48, skrev Simon Slavin:

On 5 Jun 2017, at 1:45pm, Daniel Polski  wrote:


How do I make the checkpointing work like the above documentation describes?

Set a timeout.  Perhaps a very long one (one minute, which is what I use in 
some places).  You can do this two ways:





Please note that the timeout applies only to the connection (and therefore the 
program and the computer) that you set it under.  If you want all connections 
accessing the database to respect the timeout, you have to set it individually 
for all of them.


Ok, have I understood this correctly:

If doing a manual checkpoint with SQLITE_CHECKPOINT_TRUNCATE, that call 
will block for maximum the time set by the busy_timeout while trying to 
proceed.
If the busy timeout is 0, the call will return immediately if something 
currently is blocking checkpoint progress.
If the busy timeout is set to something > 0, the checkpoint call will 
retry to proceed during the timeout.


If the above is correct I got some more questions:
Does the checkpoint call lock out new requests which might prohibit 
checkpoint progress while waiting for the timeout?
What will happen with other connections new read/write requests during 
the timeout while the checkpointing is running..?


Thank you,


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

Re: [sqlite] WAL checkpoint starved?

[Simon Slavin]

On 5 Jun 2017, at 1:45pm, Daniel Polski  wrote:

> How do I make the checkpointing work like the above documentation describes?

Set a timeout.  Perhaps a very long one (one minute, which is what I use in 
some places).  You can do this two ways:





Please note that the timeout applies only to the connection (and therefore the 
program and the computer) that you set it under.  If you want all connections 
accessing the database to respect the timeout, you have to set it individually 
for all of them.

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

Re: [sqlite] WAL checkpoint starved?

[Daniel Polski]

Den 2017-06-05 kl. 12:34, skrev Richard Hipp:

On 6/5/17, Daniel Polski  wrote:


Den 2017-06-02 kl. 16:07, skrev Richard Hipp:

and I thought that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint
to completion.

Do you have a busy callback handler registered
(https://sqlite.org/c3ref/busy_handler.html)

No busy callback is registered, but we do set the:
sqlite3_busy_timeout(db, 0)


SQLITE_CHECKPOINT_TRUNCATE waits until either it is able to run the
checkpoint to completion and truncate the WAL file, or until the
timeout expires.  Since you have the timeout turned off, the
SQLITE_CHECKPOINT_TRUNCATE does not wait at all, and simply fails if a
reader is preventing the timeout from running to completion.




Aha.

"In applications with many concurrent readers, one might also consider 
running manual checkpoints with the SQLITE_CHECKPOINT_RESTART or 
SQLITE_CHECKPOINT_TRUNCATE option which will ensure that the checkpoint 
runs to completion before returning."


How do I make the checkpointing work like the above documentation 
describes? (I thought my call would block until it finished since it was 
described as "will ensure that the checkpoint runs to completion").


So in my case, what's happening is:
- trying to checkpoint
- checkpoint finds there are active readers
- block access for new readers, wait 0 time for current active readers 
to finish

- times up, readers still active, return checkpoint failed?
..Correct?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL checkpoint starved?

[Richard Hipp]

On 6/5/17, Daniel Polski  wrote:
>
>
> Den 2017-06-02 kl. 16:07, skrev Richard Hipp:
>>>
>>> and I thought that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint
>>> to completion.
>> Do you have a busy callback handler registered
>> (https://sqlite.org/c3ref/busy_handler.html)
> No busy callback is registered, but we do set the:
> sqlite3_busy_timeout(db, 0)
>

SQLITE_CHECKPOINT_TRUNCATE waits until either it is able to run the
checkpoint to completion and truncate the WAL file, or until the
timeout expires.  Since you have the timeout turned off, the
SQLITE_CHECKPOINT_TRUNCATE does not wait at all, and simply fails if a
reader is preventing the timeout from running to completion.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL checkpoint starved?

[J Decker]

On Sun, Jun 4, 2017 at 10:54 PM, Daniel Polski 
wrote:

>
> Den 2017-06-02 kl. 16:07, skrev Clemens Ladisch:
>
>> Daniel Polski wrote:
>>
>>> Any ideas why I can end up with that large WAL file
>>>
>> Sounds like checkpoint starvation.
>> Does the checkpoint call actually succeed?
>>
>
> Unfortunately I don't know (adding a log message for that now).
>
> Any suggestions about how to recover if the call to
> sqlite3_wal_checkpoint_v2 fails? (Would prefer the checkpointing to be the
> highest priority, locking out everything else until it can succeed).
>
> I'd like to thank you for working through this.  Maybe wal checkpoint will
work someday... and actually empty the wal file.
The only way for it to work is to close the file is what I was told... so
you can't keep any of the other connections open either.

>
> ___
> 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 checkpoint starved?

[Daniel Polski]

Den 2017-06-02 kl. 16:07, skrev Clemens Ladisch:

Daniel Polski wrote:

Any ideas why I can end up with that large WAL file

Sounds like checkpoint starvation.
Does the checkpoint call actually succeed?


Unfortunately I don't know (adding a log message for that now).

Any suggestions about how to recover if the call to 
sqlite3_wal_checkpoint_v2 fails? (Would prefer the checkpointing to be 
the highest priority, locking out everything else until it can succeed).


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

Re: [sqlite] WAL checkpoint starved?

[Daniel Polski]

Den 2017-06-02 kl. 16:07, skrev Richard Hipp:

On 6/2/17, Daniel Polski  wrote:

I've found something weird in a log from a client.
Normally our WAL files are < 100kB, but in this log I noticed the file
was >40MB. This was totally unexpected since we run this call every minute:

int val = sqlite3_wal_checkpoint_v2(myDB->getDbPointer(), NULL,
SQLITE_CHECKPOINT_TRUNCATE, _size, _checkpointed);

and I thought that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint
to completion. We haven't turned off automatic checkpoints, just added
the above to make them happen more often than default, and be called
from a specific thread.

Do you have a busy callback handler registered
(https://sqlite.org/c3ref/busy_handler.html)

No busy callback is registered, but we do set the:
sqlite3_busy_timeout(db, 0)

We also register:
sqlite3_config(SQLITE_CONFIG_LOG, sqliteErrorLogCallback, NULL);

And we register 5 functions like this:
sqlite3_create_function(db, "function_x", 0, SQLITE_UTF8, NULL, 
_x, NULL, NULL);
(the called functions called are extremely fast and simple, only 
updating a bool and an int).


Other than the above I believe we use sqlite "out of the box", with no 
special compiler flags or other changes.



and are you checking the
return code from sqlite3_wal_callback_v2()?
Unfortunately not in that version of the application (the return 
variable was only used during development debugging).
What could the possible reasons be for it to fail, if using 
SQLITE_CHECKPOINT_TRUNCATE?


Some more background information:
The growing WAL file has so far been reported from 1 out of more than a 
hundred installations (others could have been affected without me 
getting to know it though). But it has been seen twice in the logs from 
that installation. The application where it's detected is under heavier 
usage load than most other installations, but is not the only 
application under high load.

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

Re: [sqlite] WAL checkpoint starved?

[Clemens Ladisch]

Daniel Polski wrote:
> Any ideas why I can end up with that large WAL file

Sounds like checkpoint starvation.
Does the checkpoint call actually succeed?


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

Re: [sqlite] WAL checkpoint starved?

[Richard Hipp]

On 6/2/17, Daniel Polski  wrote:
> I've found something weird in a log from a client.
> Normally our WAL files are < 100kB, but in this log I noticed the file
> was >40MB. This was totally unexpected since we run this call every minute:
>
> int val = sqlite3_wal_checkpoint_v2(myDB->getDbPointer(), NULL,
> SQLITE_CHECKPOINT_TRUNCATE, _size, _checkpointed);
>
> and I thought that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint
> to completion. We haven't turned off automatic checkpoints, just added
> the above to make them happen more often than default, and be called
> from a specific thread.

Do you have a busy callback handler registered
(https://sqlite.org/c3ref/busy_handler.html) and are you checking the
return code from sqlite3_wal_callback_v2()?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users