How should sqlite3 be used in a "steady state"?

user_5133167 0
Viewed 127

I have tasks running every few seconds that need to read and write to my sqlite3 database.

To avoid starting python interpreters and opening the database for each task, the processes are always running and the sqlite3 connection is never closed.

To improve performance and allow more concurrent reads, I'm using WAL mode.

The problem is that "checkpoints" are never made because the connections are not closed so my wal file is growing and I have bad performance. The first value returned by PRAGMA wal_checkpoint(TRUNCATE); is 1, indicating it was blocked from completing.

What is the standard way to have multiple processes always running on the same database?

  1. is not closing connections ok if we don't use WAL mode?
  2. should I regularly close and reopen connections? (eg with a timer)
  3. is there a way to make wal_checkpoint execute? (I don't know why they are blocked)

Any other solution is welcome!

sqlite python
1 Answers

It's perfectly fine to keep the connections open, but if you are worried about it you can try measuring the performance of both approaches, so you have some data to back up the decision.

SQLIte docs have a section regarding runaway WAL files:

In normal cases, new content is appended to the WAL file until the WAL file accumulates about 1000 pages (and is thus about 4MB in size) at which point a checkpoint is automatically run and the WAL file is recycled. The checkpoint does not normally truncate the WAL file.

A checkpoint is only able to run to completion, and reset the WAL file, if there are no other database connections using the WAL file. If another connection has a read transaction open, then the checkpoint cannot reset the WAL file because doing so might delete content out from under the reader.

Can you give a bit more details about the system, how it operates and what you are trying to achieve? Maybe there's another way.

user_7583840
Related