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. 


Why?

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?

SQLite

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!

How

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.
Summary
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…)

Comments

Post a Comment

Popular posts from this blog

MurMurHash3, an ultra fast hash algorithm for C# / .NET

Image
Finding good hash functions for larger data sets is always challenging. The well know hashes, such as MD5, SHA1, SHA256 are fairly slow with large data processing and their added extra functions (such as being cryptographic hashes) isn’t always required either. Performance and low collision rate on the other hand is very important, so many new hash functions were inverted in the past few years. One of the most notable ones is MurMurHash3, which is an improved version of its predecessor (v2). The new version can create both 32 bit and 128 bit hash values, making it suitable for a wide range of applications. 64 bit architecture The 32 bit hash isn’t too important for large amount of data matching, so I only worked with and tested the 128 bit version. The latter one is optimized for x86-64 architecture, giving amazing speeds for data processing. As a 128 bit value consists of two 64 integers, the processor is fairly good with working with such large hash values – they are not byte...
. . .

Read more »

Quick select algorithm - find the Kth element in a list in linear time

Image
Quick select algorithm (Hoare's selection algorithm) – select the Kth element or  the first K element  from a list in linear time Working with large datasets is always painful, especially when it needs to be displayed in a ‘human readable’ format. It is a very frequent task to display only the largest, newest, most expensive etc. items. While sorting the whole dataset definitely gives a correct result, it is much slower than it needs to be – it needs at least O(n*log(n)) time and an it often uses recursion for the sorting, so in practice it can be quite slow. The quick select algorithm can get the top K element from a list of N items in linear time, O(n), with a very reasonable multiplication factor. The quick select does not use recursion so the performance is great for even large datasets. Algorithm The idea of the quick select is quite simple: just like with quicksort, select a random element from the list, and place every item that is smaller to the first half o...
. . .

Read more »

ESP32 - send a push notification from the Arduino ESP32 device to your phone

Image
Sending a push notification to your phone from an event from an ESP32 is simple. What you'll need (if you are using a Mac for development): Arduino IDE Arduino for ESP32 Serial port simulator for ESP32 to upload your code to the board Wifi network IFTTT account IFTTT app on your phone Wifi network that your ESP32 board can access Architecture Create an IFTTT applet This should read something like this: If Maker Event "notification", then Send a notification from the IFTTT app Change the notification template Go to the settings of your new IFTTT applet and change the template to include "Value1", like this: Get your Webhook endpoint in IFTTT On your applet, click the triangle looking thing (webhook), then Settings, or simply go to this URL  https://ifttt.com/maker_webhooks/settings   Visit the private URL in your browser, replace the trigger name with "notification", so it reads like ...trigger/notification/ with/... and in the Value1 field type in a ...
. . .

Read more »