SQLite is paranoid by default

UrBackup uses SQLite to save information about your backups. The data ranges from small stuff such as settings and when a file/image backup was done and where it is saved, to a entry for every copy or in some cases linked file in a file backup. If you have a lot of files there will be a lot of entries in the last case, resulting in a very large SQLite file.
So maybe you have heard of SQLite. It is used practically everywhere right now. Firefox saves its bookmarks with it, Android phones and the IPhone their settings.
Here is a list of well-known companies which use SQLite: http://sqlite.org/famous.html

Lately SQLite got a new journalling mode which allows simultaneous reads and writes. This journalling mode is also used in stand-alone databases like PostgreSQL, thus making SQLite competitive in the concurrency area as well. This works by appending the changes to the database to a separate file. After some accumulation of changes they are applied to the database (checkpoint). Read access is blocked only during this application of changes.

Now I thought that appending the changes to that files does not require actual data to be written to disk and that because of that the write performance of SQLite is greatly improved (The appended data can be kept in system buffers until the checkpoint occurs).
There was some speed improvement but not that much. The reason for that is that SQLite is paranoid by default and enforces that every change to the database is actually written to disk after some data was saved. This is reasonable if this data is important, but in this case a backup interrupted by a power outage is worthless anyway (incomplete).
Of course you can change this default behaviour by executing:

PRAGMA synchronous=NORMAL;

On each connection. This will be done in the new version of both UrBackup server and client, greatly speeding up anything write related. Especially the statistics calculations are far faster now.

 

One thought on “SQLite is paranoid by default

  1. So glad to see this post, I’ve noticed the SQLite database slowing things down a lot on my UrBackup server lately. I’ve actually hit the point where my poor server was waiting for io nearly all the time, between the hardware upgrade I have coming in a few days and when the new version comes out hopefully it will be all better.

    Love your work on this project, its been the exact backup product I’ve been wanting for a few years.

Leave a Reply

Your email address will not be published. Required fields are marked *