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
