WAL checkpoint not running?
(1) By anonymous on 2022-10-06 20:34:00 [link] [source]
$ ll
-rw-r--r-- 1 root root 5.0M Oct 6 20:05 database.db
-rw-r--r-- 1 root root 32K Oct 6 20:22 database.db-shm
-rw-r--r-- 1 root root 8.5M Oct 6 20:28 database.db-wal
This database is owned by a single process, which is alive and healthy.
Auto checkpoint is set to 1000 and page size is 4096, so I expected a checkpoint to run when the WAL's size reached ~4MB.
What could be preventing or disabling the auto checkpoint feature?
(2) By Keith Medcalf (kmedcalf) on 2022-10-06 22:23:00 in reply to 1 [link] [source]
What could be preventing or disabling the auto checkpoint feature?
If you did not fiddle with the auto_checkpoint mechanism, then what is the basis for you thinking it is being "prevented or disabled"?
(3) By anonymous on 2022-10-06 22:28:56 in reply to 2 [link] [source]
The WAL file has exceeded the size which triggers auto checkpointing and has remained at this size for weeks - this suggests to me that checkpointing isn't running? Is there a way to confirm this?
(4) By Keith Medcalf (kmedcalf) on 2022-10-06 22:43:58 in reply to 3 [link] [source]
The WAL file only shrinks if you have specifically instructed that it do so.
Did you do issue an instruction to do so?
Otherwise, everything seems to be working as designed.
(5) By Keith Medcalf (kmedcalf) on 2022-10-06 22:52:07 in reply to 3 [source]
You can always connect to the database and manually checkpoint.
What can interfere with the checkpoint process? The same as interferes with anything and everything: Transactions that you BEGIN but never COMMIT/ROLLBACK; and, statements that you forgot to reset/finalize.
(6) By anonymous on 2022-10-06 23:08:49 in reply to 1 [link] [source]
From the Pragma journal_size_limit documentation
"Similarly, in WAL mode, the write-ahead log file is not truncated following a checkpoint. Instead, SQLite reuses the existing file for subsequent WAL entries since overwriting is faster than appending."
If you want to limit the WAL size then:
"The journal_size_limit pragma may be used to limit the size of rollback-journal and WAL files left in the file-system after transactions or checkpoints. Each time a transaction is committed or a WAL file resets, SQLite compares the size of the rollback journal file or WAL file left in the file-system to the size limit set by this pragma and if the journal or WAL file is larger it is truncated to the limit."