Re: [sqlite] WAL grows without bounds, short concurrent writes & reads
Awww sadness. Regardless, thanks for the update. > On Mar 29, 2019, at 11:07 AM, Richard Hipp wrote: > > On 3/29/19, Joshua Wise wrote: >> Dan, are there any plans to merge the wal2 branch into the trunk? > > No, not at this time. > > > -- > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL grows without bounds, short concurrent writes & reads
On 3/29/19, Joshua Wise wrote: > Dan, are there any plans to merge the wal2 branch into the trunk? No, not at this time. -- 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 grows without bounds, short concurrent writes & reads
Dan, are there any plans to merge the wal2 branch into the trunk?
> On Mar 29, 2019, at 7:10 AM, Dan Kennedy wrote:
>
>
> There's code here, if you want to experiment with it:
>
> https://sqlite.org/src/timeline?r=wal2
>
> Docs:
>
> https://sqlite.org/src/artifact/a807405a05e19a49
>
> Dan.
>
>
>
> On 29/3/62 01:33, Florian Uekermann wrote:
>> Hi,
>>
>> A very simple reproducer bash script using the sqlite3 CLI is appended at
>> the end.
>>
>> I am using WAL mode in a setting with sequential writes and many concurrent
>> reads. Due to WAL mode the readers don't get blocked, which is great and
>> since writes are sequential, they never get blocked either.
>> However, I am seeing the WAL grow on every write without ever restarting at
>> the beginning of the file if there is a constant influx of new reads (with
>> limited lifetime).
>> This causes the WAL file to grow to many GB within minutes, even if the
>> database state fits into a few MB or even kB after closing all connections.
>>
>> The output of "PRAGMA wal_checkpoint(PASSIVE);" usually looks like this:
>> "0|123|123", which I interpret as the checkpointer being caught up on the
>> current state. I believe the reason that new writes are appended at the end,
>> instead of restarting the WAL, is that while reads are short-lived, there is
>> always at least one going on, so the log of the last write has to be kept,
>> which in turn prevents a reset of the WAL.
>>
>> An example read (r) write (w) pattern could look like this (b: begin, e:
>> end):
>> r1_b; w1; r2b; r1e; w2; r2b; w3; r3b; r2e ...
>>
>> A solution could be to start a second WAL when the first one exceeds some
>> size threshold, which would allow resetting the first one after all readers
>> finish that started before the wal_checkpoint finished, even if there are
>> new writes in the second WAL. Then the roles/order of the two WALs flipped,
>> allowing the second WAL to be reset regardless of read/write frequency.
>> I believe that would limit the total WAL size to about 2 times of the size
>> of writes happening within the timespan of a single read.
>>
>> This solution has been suggested previously on this list by Mark Hamburg,
>> but the threads lack a simple reproducer and detailed problem description.
>>
>> Best regards,
>> Florian
>>
>> Test script:
>>
>> rm -f test.sqlite
>> ./sqlite3 test.sqlite <<< "
>> PRAGMA journal_mode=WAL;
>> CREATE TABLE t (value INTEGER);
>> REPLACE into t (rowid, value) VALUES (1,0);
>> "
>>
>> for i in {1..1000}
>> do
>>
>> ./sqlite3 test.sqlite <<< "
>> BEGIN;
>> SELECT value FROM t WHERE rowid=1;
>> .system sleep 0.2
>> SELECT value FROM t WHERE rowid=1;
>> COMMIT;
>> " &
>>
>> sleep 0.1
>> ./sqlite3 test.sqlite <<< "
>> BEGIN;
>> REPLACE into t (rowid, value) VALUES (1,$i);
>> .print inc
>> COMMIT;
>> "
>> wc -c test.sqlite-wal
>> done
>>
>> ___
>> 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 grows without bounds, short concurrent writes & reads
There's code here, if you want to experiment with it:
https://sqlite.org/src/timeline?r=wal2
Docs:
https://sqlite.org/src/artifact/a807405a05e19a49
Dan.
On 29/3/62 01:33, Florian Uekermann wrote:
Hi,
A very simple reproducer bash script using the sqlite3 CLI is appended
at the end.
I am using WAL mode in a setting with sequential writes and many
concurrent reads. Due to WAL mode the readers don't get blocked, which
is great and since writes are sequential, they never get blocked either.
However, I am seeing the WAL grow on every write without ever
restarting at the beginning of the file if there is a constant influx
of new reads (with limited lifetime).
This causes the WAL file to grow to many GB within minutes, even if
the database state fits into a few MB or even kB after closing all
connections.
The output of "PRAGMA wal_checkpoint(PASSIVE);" usually looks like
this: "0|123|123", which I interpret as the checkpointer being caught
up on the current state. I believe the reason that new writes are
appended at the end, instead of restarting the WAL, is that while
reads are short-lived, there is always at least one going on, so the
log of the last write has to be kept, which in turn prevents a reset
of the WAL.
An example read (r) write (w) pattern could look like this (b: begin,
e: end):
r1_b; w1; r2b; r1e; w2; r2b; w3; r3b; r2e ...
A solution could be to start a second WAL when the first one exceeds
some size threshold, which would allow resetting the first one after
all readers finish that started before the wal_checkpoint finished,
even if there are new writes in the second WAL. Then the roles/order
of the two WALs flipped, allowing the second WAL to be reset
regardless of read/write frequency.
I believe that would limit the total WAL size to about 2 times of the
size of writes happening within the timespan of a single read.
This solution has been suggested previously on this list by Mark
Hamburg, but the threads lack a simple reproducer and detailed problem
description.
Best regards,
Florian
Test script:
rm -f test.sqlite
./sqlite3 test.sqlite <<< "
PRAGMA journal_mode=WAL;
CREATE TABLE t (value INTEGER);
REPLACE into t (rowid, value) VALUES (1,0);
"
for i in {1..1000}
do
./sqlite3 test.sqlite <<< "
BEGIN;
SELECT value FROM t WHERE rowid=1;
.system sleep 0.2
SELECT value FROM t WHERE rowid=1;
COMMIT;
" &
sleep 0.1
./sqlite3 test.sqlite <<< "
BEGIN;
REPLACE into t (rowid, value) VALUES (1,$i);
.print inc
COMMIT;
"
wc -c test.sqlite-wal
done
___
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] WAL grows without bounds, short concurrent writes & reads
Hi,
A very simple reproducer bash script using the sqlite3 CLI is appended
at the end.
I am using WAL mode in a setting with sequential writes and many
concurrent reads. Due to WAL mode the readers don't get blocked, which
is great and since writes are sequential, they never get blocked either.
However, I am seeing the WAL grow on every write without ever restarting
at the beginning of the file if there is a constant influx of new reads
(with limited lifetime).
This causes the WAL file to grow to many GB within minutes, even if the
database state fits into a few MB or even kB after closing all connections.
The output of "PRAGMA wal_checkpoint(PASSIVE);" usually looks like this:
"0|123|123", which I interpret as the checkpointer being caught up on
the current state. I believe the reason that new writes are appended at
the end, instead of restarting the WAL, is that while reads are
short-lived, there is always at least one going on, so the log of the
last write has to be kept, which in turn prevents a reset of the WAL.
An example read (r) write (w) pattern could look like this (b: begin, e:
end):
r1_b; w1; r2b; r1e; w2; r2b; w3; r3b; r2e ...
A solution could be to start a second WAL when the first one exceeds
some size threshold, which would allow resetting the first one after all
readers finish that started before the wal_checkpoint finished, even if
there are new writes in the second WAL. Then the roles/order of the two
WALs flipped, allowing the second WAL to be reset regardless of
read/write frequency.
I believe that would limit the total WAL size to about 2 times of the
size of writes happening within the timespan of a single read.
This solution has been suggested previously on this list by Mark
Hamburg, but the threads lack a simple reproducer and detailed problem
description.
Best regards,
Florian
Test script:
rm -f test.sqlite
./sqlite3 test.sqlite <<< "
PRAGMA journal_mode=WAL;
CREATE TABLE t (value INTEGER);
REPLACE into t (rowid, value) VALUES (1,0);
"
for i in {1..1000}
do
./sqlite3 test.sqlite <<< "
BEGIN;
SELECT value FROM t WHERE rowid=1;
.system sleep 0.2
SELECT value FROM t WHERE rowid=1;
COMMIT;
" &
sleep 0.1
./sqlite3 test.sqlite <<< "
BEGIN;
REPLACE into t (rowid, value) VALUES (1,$i);
.print inc
COMMIT;
"
wc -c test.sqlite-wal
done
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
