SQLite performance and Django

Tuning SQLite in your Django project

SQLite is very fast on read operations and slow on write operations comparing to databases like MySQL. However SQLite can be tuned by changing few settings. You can find some article about it on the net, here I'll show some results of using two settings changes.

Test script

# -*- coding: utf-8 -*-
from os import environ
environ['DJANGO_SETTINGS_MODULE'] = 'settings'
from settings import *

data = '''Slackware Linux 12.0 has been released: "Well folks, it's that time to announce a new stable Slackware release again. So, without further ado, announcing Slackware version 12.0!
Since we've moved to supporting the 2.6 kernel series exclusively (and fine-tuned the system to get the most out of it), we feel that Slackware 12.0 has many improvements over our last release and is a
 must-have upgrade for any Slackware user. Here are some of the advanced features of Slackware 12.0: runs the 2.6.21.5 version of the Linux kernel; system binaries are linked with the GNU C Library,
  version 2.5; X11 7.2.0; Apache 2.2.4 web server with Dynamic Shared Object support, SSL, and PHP 5.2.3; the udev dynamic device management system; updated versions of the Slackware package
   management tools...." Read the rest of the release announcement for full details. The CD and DVD images should be available from Slackware's FTP/HTTP mirrors in a few days; in the meantime get the new
    Slack via BitTorrent: slackware-12.0-install-dvd.iso (3,714MB).'''

from pages.models import *
Page.objects.all().delete()
for i in range(0,10):
	p = Page(title='test title' + str(i), slug='test-slug' + str(i), description='a sample description of an article bla foo bar', text=data)
	p.save()
	print str(i) + ' entry saved'

I put this script in a Django project folder which has "Pages" application with "Page" model. This code will add 10 entries to the table. Execution time was measured with "time" command:

time python test.py

To compare vanilla results with some changes I've added (before "data" string) this code:

from django.db import connection
cursor = connection.cursor()
#cursor.execute('PRAGMA temp_store = MEMORY;')
#cursor.execute('PRAGMA synchronous=OFF')

Which allows to test influence of two settings changes. PRAGMA temp_store = MEMORY - store temporary files in RAM, and PRAGMA synchronous=OFF - do not wait for the I/O operation to finish. Tests were done on a XFS partition on Gentoo Linux with 2.6.21 kernel and pysqlite 2.3.3.

NOTE: Changing PRAGMA settings may result in database corruption.

No changes - 1.915 sec
PRAGMA synchronous=OFF - 0,783 sec
PRAGMA temp_store = MEMORY - 1,842 sec
Both changes - 0,764 sec

Asynchronous mode is much faster.

Next, for 100 entries I tested some selects:

from pages.models import *
for i in range(0,10):
	s1 = Page.objects.all()
	print len(s1)
	s1 = Page.objects.filter(id__gt=40)
	print len(s1)

Setting changes did not have effect on the script execution time (~0,76 sec)

Next I tested updates, for 100 entries:

p = Page.objects.all()
for i in p:
	i.description = 'a test sentence to check out sqlite performance'
	i.save()

No changes - 12.567 sec
Both changes - 2,032 sec

Difference is quite big. To add more variables I've run that code with SQLite database placed in RAM (tmpfs):

mount -t tmpfs tmpfs folder

Database file was placed in tmpfs mount folder. As a comparison I used also a second EXT3 partition:

XFS - 12.567 sec
EXT3 - 1,215 sec
EXT3, both changes - 0,620 sec
RAM - 0,611 sec

As you can see, speed of SQLite write operation depends on the speed of the file system. XFS isn't fast compared to EXT3, which in asynchronous mode is nearly as fast as RAM.

PRAGMA or Transactions

One of reddit users, "multani" pointed out that using transactions for several write operations will bring much speed increase without the need of using PRAGMA changes. More on reddit page.

Changing SQLite settings in Django projects

We can use a middleware to execute those two queries (better way probably exists):

class sqliteSettings(object):
	def process_request(self, request):
		from django.db import connection
		cursor = connection.cursor()
		cursor.execute('PRAGMA temp_store = MEMORY;')
		cursor.execute('PRAGMA synchronous=OFF')

RkBlog

14 July 2008; Piotr Maliński