SQLite Write-Ahead Logging
I’ve been working with SQLite lately. It has become my go-to database for all projects!
One problem I encountered was that, by default, it uses rollback journal, where any write to the database will also block all reads. Because of this, my fedidevs.com site became unresponsive for about an hour every night while the nightly job inserted fresh data.
The solution was to enable Write-Ahead Logging. WAL allows multiple readers to access the database simultaneously, even if the table is being written to simultaneously. The link above has a few disadvantages listed, but for most web-server use cases, WAL is the better option.
Enable WAL by setting the
sqlite3 db.sqlite3 'PRAGMA journal_mode=WAL;'
PRAGMA command only has to be run once per database. The setting is persistent.
When WAL is enabled, SQLite will create
.shm files. The
.wal file records transactions committed but not yet applied to the main database. The
.shm file is used for shared memory and caching.
Do remember to keep an eye on your .wal file sizes. Certain operations (like
VACUUM) can make them grow as large or even larger than the database itself. If that happens to you as it did to me, you can regain the disk space by running the
sqlite3 db.sqlite3 'PRAGMA wal_checkpoint(TRUNCATE);'
The article SQLite: Vacuuming the WALs is worth a read if you need to run VACUUM often.
With WAL enabled and the VACUUM command removed, fedidevs.com (and other sites) are running smoothly - bound only by the fact that my Raspberry Pi runs on a very slow SD card 😅
I’ll write a post about my Raspberry Pi setup in the future.