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

[sqlite] WAL checkpoint starved?

[Daniel Polski]

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.


Some more background information:
The sqlite version in the client is 3.17.0.

1. Operation
  - We normally run ~18 threads with "own" connections open to the 
sqlite database. They read concurrently, but all writes are protected by 
a mutex and all insert/update/deletes starts with "begin immediate 
transaction".  We do -not- ensure there are "reader gaps", since I 
believe that shouldn't be necessary if using truncate mode.


2. Backup
  - About every hour we run a separate process, which uses the backup 
api to create a backup (this process is not obeying the mutex described 
above and runs in parallell with the normal operation).


3. GUI/API
  - Uses one consistent database connection in PHP
  - Serveral instances access the one database connection
  - Mostly read operations. The write operations are not manually 
wrapped inside begin & commit since they're all single commands.


First I suspected the main thread to hang, so the call to 
sqlite3_wal_checkpoint_v2 stopped executing every minute. But then the 
the auto checkpointing should take over since that's not turned off. So 
now I'm thinking that something is blocking the checkpoint to be able to 
complete, but can't figure out what that could be or how to find what it 
is, if it is so.


Any ideas why I can end up with that large WAL file, except the 
information in the chapter "Avoiding Excessively Large WAL Files" 
describes? (Or if I have misunderstood the information there..?)

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

Re: [sqlite] WAL Checkpoint Blocking Behavior

[Clemens Ladisch]

Matt Fichman wrote:
> I plan to disable WAL autocheckpoints and manually run permissive WAL
> checkpoints on a second thread (as hinted at by the docs).
>
> If I do this, can a WAL checkpoint on the checkpoint thread still block or
> significantly delay queries/updates from the main thread?

The documentation says :
| Checkpoint as many frames as possible without waiting for any database
| readers or writers to finish, then sync the database file if all frames
| in the log were checkpointed.  [...] passive mode might leave the
| checkpoint unfinished if there are concurrent readers or writers.

A passive checkpoint does not interfere with any other connection that
is currently reading or writing. But if the checkpoint gets the databse
lock first, the other connection must wait.


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

[sqlite] WAL Checkpoint Blocking Behavior

[Matt Fichman]

Hi all,

I'm trying to use SQLite in an asynchronous application that requires
low/predictable latency access to the database. To achieve this is, I plan
to disable WAL autocheckpoints and manually run permissive WAL checkpoints
on a second thread (as hinted at by the docs).

If I do this, can a WAL checkpoint on the checkpoint thread still block or
significantly delay queries/updates from the main thread?

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

[sqlite] WAL checkpoint

[Sairam Gaddam]

Before checkpointing the data from WAL, if the DB is queried, will the
result include updated data from WAL or not?
Will this situation arise? because writing to WAL and checkpoint occur very
fast but if a query comes in between, will the result be updated or not
before checkpoint.

[sqlite] WAL checkpoint

[Igor Tandetnik]

On 2/24/2016 12:56 AM, Sairam Gaddam wrote:
> Before checkpointing the data from WAL, if the DB is queried, will the
> result include updated data from WAL or not?

It will. A transaction reads both from WAL and the original database 
file - whichever contains the fresher data.
-- 
Igor Tandetnik