[mapserver-users] mapcache - sqlite cache large wal files and blocked checkpoints when seeding

[Travis Kirstine]

I've set up a sqlite3 cache using addition pragma statements to set journal
mode to WAL and journal mode to normal.

   <cache name="sqlite" type="sqlite3">
      <pragma name="synchronous">1</pragma>
      <pragma name="journal_mode">wal</pragma>
      <dbfile>./{tileset}.sqlite3</dbfile>
   </cache>

These setting "should" increase performance however I've noticed that when
seeding the cache the wal file is growing excessively over time (+50GB)
which would result in slower reads.  I believe this is being caused by
seeding the cache using multiple threads (-n 4) causing checkpoint
starvation as defined here (https://www.sqlite.org/wal.html) as:

"
Checkpoint starvation. A checkpoint is only able to run to completion, and
reset the WAL file, if there are no other database connections using the
WAL file. If another connection has a read transaction open, then the
checkpoint cannot reset the WAL file because doing so might delete content
out from under the reader. The checkpoint will do as much work as it can
without upsetting the reader, but it cannot run to completion. The
checkpoint will start up again where it left off after the next write
transaction. This repeats until some checkpoint is able to complete.

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
"
This is verified by manually attempting to force the checkpoint in the
command line while the seeder is running :
sqlite> PRAGMA wal_checkpoint(TRUNCATE);
1|-1|-1
The first column is usually 0 but will be 1 if a RESTART or FULL or
TRUNCATE checkpoint was blocked from completing, for example because
another thread or process was actively using the database.

.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20180911/d0ef20c3/attachment.html>