Some SQLite 3.7 Benchmarks
Since I wrote the benchmarks for insertions in my last post, SQLite 3.7 has been released. I figured it’d be interesting to see if 3.7 changed the situation at all.
Prepared Statements
The specific versions compared here are 3.6.23.1 and 3.7.3. I ran the prepared statements benchmark as is without changing any source code. Both are using a rollback journal in this case.
Prepared Statements Runtime in Seconds
Prepared Statements Inserts Per Second
As you can see, the new version of SQLite definitely provides better performance. There is a speedup of about 3 seconds.
Journal Mode Comparison
One of the main features SQLite 3.7 added was Write Ahead Logging (WAL). The main advantage of write-ahead logging is it allows more concurrent access to the database than a rollback journal. These benchmarks don’t show the true potential of write-ahead logging. The benchmarks are single threaded, and they insert a large amount of data in one transaction. It’s listed as a disadvantage that large transactions can be slow with write-ahead logging, even having the potential to return an error. I wanted to evaluate write-ahead logging as a drop in replacement.
I ran the prepared statements benchmark with the default, memory, and wal settings. I also ran each setting with and without synchronous on. The synchronous setting controls how often sqlite waits for data to be physically written to the hard disk. The default setting is full, which is the safest because it waits for data to be written to the hard disk most frequently. This is compared to synchronous being off, which lets the operating system decide when information should be written to the hard disk. In this case if there is a software crash, it’s more likely the database could become corrupt.
Journal Mode Runtime Comparison
Journal Mode Inserts Per Second
Up until about 100,000 insertions all the journal modes and synchronous settings are about even. After 100,000, the insertion benchmarks with synchronous off are faster than their default synchronous counterparts. Journal mode set to memory and synchronous off offered the best performance for this benchmark.
on April 11, 2011 at 4:50 pm
Permalink
Based on these findings, what settings would you recommend for a high-write based application (like a datastore web api) with up to 20 simultaneous clients every few hundred ms?
on April 12, 2011 at 1:46 am
Permalink
If you’re going to use SQLite, I would take a look at my post about Fast Bulk Insertions found here: http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/ . It covers different techniques on how to get information into a database quickly.
That said SQLite doesn’t handle concurrent connections very well, so another database engine may provide better results.
on August 28, 2011 at 11:27 am
Permalink
[…] Performance with different journal modes are compared (Sqlite3.7) http://blog.quibb.org/2010/10/some-sqlite-3-7-benchmarks/ […]
on August 12, 2015 at 2:30 pm
Permalink
Ant plan to re-run the test again with 3.8.11?