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 »

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 »

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 »