I've found something weird in a log from a client.
Normally our WAL files are < 100kB, but in this log I noticed the file
was >40MB. This was totally unexpected since we run this call every minute:
int val = sqlite3_wal_checkpoint_v2(myDB->getDbPointer(), NULL,
SQLITE_CHECKPOINT_TRUNCATE, &wal_size, &wal_checkpointed);
and I thought that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint
to completion. We haven't turned off automatic checkpoints, just added
the above to make them happen more often than default, and be called
from a specific thread.
Some more background information:
The sqlite version in the client is 3.17.0.
1. Operation
- We normally run ~18 threads with "own" connections open to the
sqlite database. They read concurrently, but all writes are protected by
a mutex and all insert/update/deletes starts with "begin immediate
transaction". We do -not- ensure there are "reader gaps", since I
believe that shouldn't be necessary if using truncate mode.
2. Backup
- About every hour we run a separate process, which uses the backup
api to create a backup (this process is not obeying the mutex described
above and runs in parallell with the normal operation).
3. GUI/API
- Uses one consistent database connection in PHP
- Serveral instances access the one database connection
- Mostly read operations. The write operations are not manually
wrapped inside begin & commit since they're all single commands.
First I suspected the main thread to hang, so the call to
sqlite3_wal_checkpoint_v2 stopped executing every minute. But then the
the auto checkpointing should take over since that's not turned off. So
now I'm thinking that something is blocking the checkpoint to be able to
complete, but can't figure out what that could be or how to find what it
is, if it is so.
Any ideas why I can end up with that large WAL file, except the
information in the chapter "Avoiding Excessively Large WAL Files"
describes? (Or if I have misunderstood the information there..?)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
