4 ms·
WAL mode has some issues where depending on the write pattern, the WAL size can grow to infinity, slowing down performance a lot. I think this usually happens w
by zackees 3y ago
WAL mode has some issues where depending on the write pattern, the WAL size can grow to infinity, slowing down performance a lot. I think this usually happens when you have lots of writes that lock the table so sqlite never gets to doing wal_autocheckpoint.
I believe that WAL2 fixes this:
Wal2 mode does not have this problem. In wal2 mode, wal files do not grow indefinitely even if the checkpointer never has a chance to finish uninterrupted
https://sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://sqlite.org/cgi/src/doc/wal2/doc/wal2.md
- datadeft 3y agoI am not sure if this works on my end: sqlite> PRAGMA journal_mode = delete; delete sqlite> PRAGMA journal_mode = wal2; delete Does this mean wal2 is not available?
- polyrand 3y agowal2 mode is not part of the main development branch of SQLite, you need to compile SQLite yourself from that branch. Same with BEGIN CONCURRENT [0] [0]: https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...
- datadeft 3y agoThank you!
- freddw 3y agod’oh, I kind of speculated that this pattern might be possible to apply to WAL above without having read far enough down to see it was implemented in WAL2. Though I mentioned RCU the hot/cold partitions were something I was also thinking of. I wonder if this could be further extended to better support concurrent writes. Depending on the implementation, with wal2 readers may be reading from both hot and cold files without blocking. So this may potentially be extendable to read from two hot files, or two hot files and two cold files.