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

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 Runtime Comparison

Journal Mode Inserts Per Second

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.

Posted on October 14, 2010 at 9:00 am by Joe · Permalink
In: C++ · Tagged with: ,

4 Responses

Subscribe to comments via RSS

  1. Written by Xeoncross
    on April 11, 2011 at 4:50 pm
    Reply · 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?

    • Written by Joe
      on April 12, 2011 at 1:46 am
      Reply · 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.

  2. Written by SQLITE Benchmark References « Zhihui's Blog
    on August 28, 2011 at 11:27 am
    Reply · Permalink

    […] Performance with different journal modes are compared (Sqlite3.7) http://blog.quibb.org/2010/10/some-sqlite-3-7-benchmarks/ […]

  3. Written by Testo
    on August 12, 2015 at 2:30 pm
    Reply · Permalink

    Ant plan to re-run the test again with 3.8.11?

Subscribe to comments via RSS

Leave a Reply