SQLite performance and Django
Check out the new site at https://rkblog.dev.
14 July 2008
Comments
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'
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')
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
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)
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
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
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
Check out the new site at https://rkblog.dev.
Comment article