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 of the
. . .

Read more »

Octoprint as a systemd service - running it with high priority

Image
Octoprint provides remote access to your 3D printer, and as it's written in Python, you can run it on practically any operating system. If you are using a standard Raspberry PI image (for instance, because you run it on an Orange PI ), rather than the customised Octoprint image, you can manually install it and create a systemd service to run it: Add this to /etc/systemd/system/octoprint.service  [Unit] Description=Octoprint After=network.target [Service] ExecStart=/home/pi/octoprint/bin/octoprint serve WorkingDirectory=/home/pi/octoprint StandardOutput=inherit StandardError=inherit Restart=always User=pi CPUSchedulingPolicy=rr CPUSchedulingPriority=80 [Install] WantedBy=multi-user.target Then run sudo systemctl daemon-reload sudo systemctl enable octoprint This will start Octoprint on every reboot with a high priority (so unlikely to stutter during printing).
. . .

Read more »