SQLite performance tuning
My favorite database is slow - but it’s easy to tune it
Lately I’ve begun to realize that the data in the systems I’m creating isn’t that important. Well, it’s a little bit of exaggeration but almost true: data is very cheap and all my efforts that were trying to make sure it’s safe are close to perfect waste.
Let’s suppose I’m always planning to some catastrophic hardware failure so all my database operations are using redo logs, transactions and all the bells and whistles. Fine, what have I achieved exactly? If the machine or hard drive dies, I’ll have much bigger problems than worrying about the last transaction that was being served by system when it failed.
For instance, I’ll have to buy a new machine (or hard drive). That will take a while then comes the install, configuration, and boot... Only after all these steps (which might span across days) would I have to worry about my daily backups (if I had any) and whether they are restorable or not. Most probably I will never even think about the last failed transaction.
But in this case, why bother? Why not just disable journaling (hence transactions) and synchronized write operations and let my applications fly - because in this case the performance of the database will be (in some cases) a magnitude larger!
(I wouldn’t be too worried about transactions either – read “Your Coffee Shop Doesn't Use Two-Phase Commit”)
So, in my last couple of projects, I just decided to let it go: the performance is amazing and if something goes wrong I have the daily backup (in some cases hourly). I haven’t really lost anything, have I?
Recently I’ve been using SQLite quite extensively: basically to save anything to the disk and not worry about the file system. The database is a single file so if I want to introduce load balancing, I just set up an rsync service to replicate the changes across the (read only) nodes and that’s it (if you have an easier solution, please let me know!)
Of course it’s free and open source, but the best part is: if I turn off synched write operations it’s faster than any other database I’ve worked with!
To turn off the synchronized writing to the disk, simple use a connection string like:
"Data Source=file.db3;Journal Mode=Off;Synchronous=Off;”
Please note, that turning off the journaling disables the atomic transaction capability of SQLite!
On the other hand, if you want to be absolutely sure that all write operations are completed before returning the operation to you, you can set the following:
"Data Source=file.db3;Journal Mode=WAL;Synchronous=FULL;”
The latter one can be very slow (but if your app it read intensive, it’s not always an issue). The new WAL (Write Ahead Log) is a SQLite 3.7 feature and somewhat faster than the previous traditional journal.
Do not try to rely always on the so-called ‘best practices’! Almost all of the greatest systems are using some very non-enterprise approach to serve their clients – use the right tool for the right job (and of course if you are worried about your transactional data, please use transactions – although there is a good chance that you might not need that at all…)