[bug/sqlite] sqlite.db-wal file grows indefinitely without flushing to sqlite.db

[tsmethurst]

Someone's reported this in the Matrix already too, but didn't open an issue for it yet. Opening it now since I can see the problem on my server as well:

-rw-r--r--    1 gotosocial gotosocial 7.1G Apr  3 00:01 sqlite.db
-rw-r--r--    1 gotosocial gotosocial 3.4M Apr  4 16:05 sqlite.db-shm
-rw-r--r--    1 gotosocial gotosocial 1.7G Apr  4 16:05 sqlite.db-wal

^^ Here there's a db size of 7.1G, which is fine for an old and widely federating instance, but a wal file of 1.7G, which is much, much larger than it ought to be. This won't cause any immediate problems on this particular server, but it is likely to cause slowdown over time since reading from the wal file is less optimized than reading from the db itself.

The SQLite docs contain some details on why this situation might occur: https://www.sqlite.org/wal.html#avoiding_excessively_large_wal_files

The one that jumps out at me is checkpoint starvation, particularly:

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.

We should see if we can figure out why this is happening; are we leaving readers open maybe? Or is there some change in the latest version of SQLite that we're using which disables the automatic checkpoint mechanism, or leaves too many readers open, or something?

We could also try enabling manual periodic checkpointing (once per 30 minutes or so), and see if this helps, though I feel like that might just mask some other underlying problem.

[tsmethurst]

This looks very much like this: https://gitlab.com/cznic/sqlite/-/issues/179

[tsmethurst]

Closed by #2811 (for now).