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
