SQLite WAL performance improvement

Question

I have an application running on embedded linux. I have a pre-built DB with some tables where each has a lot of rows (thousands) and 52 columns. I built the DB ahead, because i am concerned that if I'll do 'INSERT' at the run-time I will make disk fragmentation, so instead i build a DB first with a lot of garbage 'INSERT's and in the run-time i use 'UPDATE's .

I am writing a lot of data to the DB every 3 seconds, And for the write procedure to be fast, I use the WAL mode in SQLite. Though, I have a problem of performance. It seems that whenever a checkpoint occurs, It takes too long and the processor can't do it in less than 3 seconds. In order to improve this, I created a thread that after like 10 writing calls, it receives a message-queue from the main thread and than checkpointing.

So now, it seems like the situation is better but the WAL file is getting bigger and bigger and bigger... How can I work around here?

Answer 1

In WAL mode, SQLite writes any changed pages into the -wal file. Only during a checkpoint are these pages written back into the database file.

The -wal file can be truncated only if there aren't any concurrent readers.

You can try to clean out the WAL file explicitly by calling sqlite3_wal_checkpoint_v2 with SQLITE_CHECKPOINT_RESTART or executing PRAGMA wal_checkpoint(RESTART), but this will fail if there are any concurrent readers.

Answer 2

To avoid fragmentation and remove a need to pre-insert data, you should use sqlite3_file_control() with SQLITE_FCNTL_CHUNK_SIZE to set chunk size. Allocating database file space in large chunks (say 1MB at a time), should reduce file-system fragmentation and improve performance. Mozilla project is currently using this setting in Firefox/Thunderbird with great success.

Regarding WAL. If you are writing a lot of data so often, you should consider wrapping your writes into bigger transactions. Normally, every INSERT is auto-committed and SQLite has to wait until data is really flushed to disk or flash - which is obviously very slow. If you wrap multiple writes to one transaction, SQLite need not to worry about every single row, and can flush many rows at once, most likely into single flash write - which is much faster. So, if you can, try to wrap at least few hundred writes into one transaction.

In my experience, WAL on flash is not really working very well, and I find it more beneficial to stick to old journalling mode. For example, Android 4 does not use WAL mode for its SQLite databases, and probably for a reason. As you have noticed, WAL has a tendency to grow without bound in some situations (however, it will also happen if transactions are rarely committed - so be sure to do that once in a while).