Rob,
At 08:55 07/08/2016, you wrote:
We think that using WAL mode works for us, indeed inspection seems to
indicate it does, but the size of the -wal file appears to be far
larger than would be expected. Is there a problem here? It doesn't
appear to be a problem but would welcome any comments.
After reading your post I'd like to clear up a few points about WAL mode.
We can also see that the main sqlite database is NOT updated (or at
least the timestamp isn't) whilst we are running the updates in WAL
mode. This appears to be correct as the updates would be in the -wal file.
The WAL mode is persistant and consistant. That means that once
successfully put in his mode the DB itself will remain in WAL mode for
every (new) connection. Thus your updates and the long-running query
are both running under WAL mode. That is, provided the WAL mode was set
prior to the start of the long-running query, but that detail doesn't
matter for reads in this case.
It doesn't matter whether your query is a single query statement (hence
in auto-commit mode) or a huge transaction extracting and massaging
data in multiple temp tables and myriads of read/write statements, all
inside an explicit transaction), ACID properties guarantee that once
your query is started, it will see the DB in the state prior to any
updates that could occur during its run. Else you would obtain
potentially dangerously inconsistant data of course.
We have not set the journal_size_limit and we have a -wal file which
is 224MB in size, somewhat larger than 4MB. We are running
3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d
which does not appear to have the code in 3.11.0 so that the WAL file
is proportional to the size of the transaction. From the same page of
the manual:
```
Very large write transactions.
A checkpoint can only complete when no other transactions are running,
which
means the WAL file cannot be reset in the middle of a write
transaction. So a large change to a large database
might result in a large WAL file. The WAL file will be checkpointed
once the write transaction completes
(assuming there are no other readers blocking it) but in the meantime,
the file can grow very big.
As of SQLite version 3.11.0, the WAL file for a single transaction
should be proportional in size to
the transaction itself. Pages that are changed by the transaction
should only be written into the WAL
file once. However, with older versions of SQLite, the same page might
be written into the WAL file multiple
times if the transaction grows larger than the page cache.
```
Reread the quoted part again: only with SQLite versions 3.11.0 and
above will a given page be written only once in the WAL file when
initiated within a given transaction.
Since you're running a prior version, it's pretty logical to expect
that your updates will cause writes of multiple distinct copies of the
same pages in the WAL file. You should update your SQLite version to
see a possible improvement there. That, or refer only to the old 3.8.2
documentation, but this is an inferior option because there have been
significant improvements meanwhile.
Also the ratio in the help file (1000 pages or about 4Mb) applies to
the default page size (4Kb).
Finally, while the long-running query is running, no checkpoint can run
to completion. Doc states under "Checkpoint starvation.":
However, if a database has many concurrent overlapping readers and
there is always at least one active reader, then no checkpoints will
be able to complete and hence the WAL file will grow without bound.
Since you clearly can't introduce a read-gap inside your read query,
the .wal file will grow as large as it needs until completion of the
query. You mentionned that you tested with much more frequent updates
than the real-world case (120x actually), so .wal file size shouldn't
be an issue in your actual use case.
HTH
--
Jean-Christophe
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
