SQLite performance and Django
Test script
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:
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
Next, for 100 entries I tested some selects: Setting changes did not have effect on the script execution time (~0,76 sec)
Next I tested updates, for 100 entries:
- 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):
RkBlog