SQLite performance and Django
Tuning SQLite in your Django projectSQLite 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 scriptI 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.pyTo 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 folderDatabase 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 TransactionsOne 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 projectsWe can use a middleware to execute those two queries (better way probably exists):