Why is SQLite so slow (~2 q/s) on a specific machine?

Question

On one of my servers (i7 Ivy Core, 32 GB RAM, Debian 6 @ 64bit, PHP 5.4.10) I experience extremely slow inserts with SQLite. The following test program reports just 2.2 inserts/second (14 seconds for inserting 30 rows).

unlink("test.db");

$db = new PDO('sqlite:test.db');

$db->exec("CREATE TABLE test (dummy INT)");

$count = 30;

$t = microtime(true);
for ($i=0; $i<$count; $i++) {
  $db->exec("INSERT INTO test VALUES ($i)")
   or die("SQLite error: ".$db->errorInfo()[2]."\n");
}
$elapsed = microtime(true)-$t;
echo sprintf("%d inserts in %.3f secs (%.1f q/s)\n", 
  $count, $elapsed, $count/$elapsed);

Output:

$ time php test.php
30 inserts in 13.911 secs (2.2 q/s)

real    0m14.634s
user    0m0.004s
sys     0m0.016s

I know that I can accelerate this by wrapping BEGIN/END around the insert statements (which gives me 200000 q/s) but even without a transaction this should be much faster. On other (older) machines (same PHP version) I reach 300+ queries/sec without explicit transactions.

What could be the cause for this? Do I have to tune Sqlite or the O/S?

Answer 1

I have done a similar test on a Linux 64bit machine using strace -C -tt to have an idea of where SQLite3 is taking time.

% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 99.03    0.004000          32       124           fsync
  0.64    0.000026           0       222           mprotect
  0.32    0.000013           0       216           munmap

The obvious delay is in the fsync function, which is:

• configurable
• depends on general disk I/O (check out iotop, iostat)
• heavily depends on IOSS (therefore, the file system and disk allocation - you might get one value on ext3, a different one on xfs, and a third one on btrfs)
• depends of course, indirectly, on underlying hardware and its quirks or tunings.

By turning syncing off, my SQLite3 performance increases by a factor of around three thousand:

$db = new PDO('sqlite:test.db');

$db->exec('pragma synchronous = off;');

I too have two different values on two very similar machines (one has ext4, the other XFS, but I'm not positive this is the main reason - their load profiles are also different).

By the way, using prepared statements just about doubles the execution speed at the fastest level (from 45k to 110k INSERTs, in batches of 3000 since at that speed 30 INSERTs are bound to give spurious timings), and raises the lowest speed from about 6 to about 150.

So this (using prepared statements) might be a good solution to improve repeated operations without touching file synchronization, i.e., while still being demonstrably sure that data risk level remains the same. After that I'd try transactions or fsync (maybe even memory journaling) depending on the risk and worth of a data outage.

When designing a system from the ground up, some tests on different FS's are surely advisable.

Tests on different file systems (same machine)

ext4 (acl,user_xattr,data=order)         5.5 queries/s
using transactions                       170 queries/s
disabling fsync                        16000 queries/s
using transactions and disabling fsync 47200 queries/s

On a temporary file system, fsync is cheap, so turning it off yields little benefit. Most of the time is spent guarding, so transactions are key.

tmpfs                                  13700 queries/s
disabling fsync                        15350 queries/s
enabling transactions                  47900 queries/s
using transactions and disabling fsync 48200 queries/s

Of course, proper data organization and indexing has to be taken into account and, for large data sets, might well turn out to be more important.

---

UPDATE: to squeeze some more performance, one can also put the SQLite journal into memory with pragma journal_mode=MEMORY;

Also, you can tell ext3/4 not to bother updating atime on SQLite databases (this hugely depends on the implementation, though). You can try adding noatime to the file system where the database resides, and if it works, you can put it into /etc/fstab (you can also use relatime instead of the more extreme noatime:

sudo mount /var -o remount,noatime