Evaluating SQLite Performance by Testing All Parameters

First, some information about the parameters.

Open mode – No-mutex or Full-mutex

If the SQLITE_OPEN_NOMUTEX flag is set, then the database connection opens in the multi-thread threading mode as long as the single-thread mode has not been set at compile-time or start-time. If the SQLITE_OPEN_FULLMUTEX flag is set then the database connection opens in the serialized threading mode unless single-thread was previously selected at compile-time or start-time. (Open mode)

Locking mode – Exclusive or Normal

In NORMAL locking-mode (the default unless overridden at compile-time using SQLITE_DEFAULT_LOCKING_MODE), a database connection unlocks the database file at the conclusion of each read or write transaction. When the locking-mode is set to EXCLUSIVE, the database connection never releases file-locks. The first time the database is read in EXCLUSIVE mode, a shared lock is obtained and held. The first time the database is written, an exclusive lock is obtained and held. (Locking mode)

Transaction mode – Deferred, Immediate, or Exclusive

After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. … After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete. (Transaction mode)

Synchronous mode – Full, Normal, or Off

When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. FULL is the most commonly used synchronous setting when not in WAL mode.

and

WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode. (Synchronous mode)

Journal Mode – WAL, Truncate, or Memory

The TRUNCATE journaling mode commits transactions by truncating the rollback journal to zero-length instead of deleting it. On many systems, truncating a file is much faster than deleting the file since the containing directory does not need to be changed. … The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt. … The WAL journaling mode uses a write-ahead log instead of a rollback journal to implement transactions. The WAL journaling mode is persistent; after being set it stays in effect across multiple database connections and after closing and reopening the database. A database in WAL journaling mode can only be accessed by SQLite version 3.7.0 (2010-07-21) or later. (Journal mode)

To help me decide which parameters are optimal for my use case I’ve set up a test running permutations of available parameters when setting up a SQLite connection. I perform a series of operations then sort the timings below on both an SSD and an HDD. Here is the generator of permuted parameters to test:

Here is the test table schema and composite index:

Note: All the inserts are in batch mode with auto-commit false and deliberate committing of the inserts at the end of the batch.

Insert 50,000 rows (SSD)

Preliminarily, it looks like NOMUTEX open mode with synchronization OFF, and either MEMORY or TRUNCATE with a large page size allow for the fastest inserts. A large page size (8192 KB) seems to be in common with faster inserts.

Insert 50,000 rows twice (SSD)

I need a non-memory journal, so I try this again with WAL and TRUNCATE journalling modes. I insert all the records twice to stress the index and journal. Again the TRUNCATE journal mode outperformed WAL on an SSD when purely inserting. Also, having a large page size improves the speed with synchronization OFF.

Insert 500,000 rows twice (SSD)

NOMUTEX is consistently faster than FULLMUTEX, so I use that only from now on. Next, I insert 500,000 rows twice. The takeaway is that TRUNCATE journal mode beats WAL on an SSD when purely inserting. Again, a large page size improves speed. Synchronization mode has no noticeable impact on speed here.

Insert 50,000 rows twice, fewer degrees of freedom (SSD)

I again set NOMUTEX, exclude OFF synchronization (because power failure is a possibility), and use a page size of 8192 KB with 1000 pages of cache since they performed best above. I test the rest of the parameters below. Of the 16 remaining permutations, the differences between them were negligible. I run each permutation several times to magnify the differences in the next section.

Insert 50,000 rows 10x, fewer degrees of freedom (SSD)

Repeating the above test but with 10 rounds of inserts each (to magnify parameter differences), the variations between the permutations of parameters remain negligible except for the very last permutation. WAL actually performs slightly better than the traditional journal mode.

Insert, copy 50,000 rows 10x, fewer degrees of freedom (SSD)

Below I insert 50,000 records, then I copy the database using the ATTACH method ten times. Specifically,

Journal mode WAL edged out TRUNCATE for this scenario. All parameters performed reasonably well. Consistently the slowest performance results from the settings NORMAL, IMMEDIATE, FULL, and WALL together.

Insert, copy 50,000 rows 10x, fewer degrees of freedom (HDD)

I repeat the above scenario on an HDD. Aside from running four times slower, the results parallel the results on an SSD.

Insert, copy 500,000 rows 10x: Full vs. Normal Sync (SSD)

To really compare the two synchronous modes (except OFF), I perform two stressful tests with the parameters below. NORMAL synchronous mode is much faster.

Results

My use case is such that by using individual SQLite databases for each security I’m recording time-series data for – around 55,000 – I have a single thread per SQLite database. While I may have eight or more threads with network activity running simultaneously, I can avoid locking issues with this design, but I do need read-access for ETL and graphing in worker threads.

Open mode

Because I control the synchronization of the reads and writes in my own code, and my model is one thread per database, I can use the NOMUTEX open mode confidently which lends itself to a noticeable speedup.

Locking mode

Locking mode had no noticeable impact on performance due to the single-thread-per-database model, so I opt for NORMAL.

Transaction mode

I will use IMMEDIATE transaction mode so I can perform some read-only ETL as well as graphing while a given database is being written to. There is no performance difference between this and an exclusive transaction here.

Journal mode

WAL journal mode is theoretically faster than the classic journal mode, and the above tests leaned toward that. A page size of 8KB worked well. The cache size didn’t affect performance so I capped it at 1000 pages.

Synchronous mode

The man pages for SQLite state the NORMAL synchronous mode works well with WAL. I found that FULL+WAL was much slower as confirmed by the docs. Just to highlight this point, I ran the final test in the previous section and NORMAL was clearly faster (27.5s vs. 35.7s).

Settings

These are the settings that are most performant for my use case which is similar to the above tests.