Database Benchmarking

I don’t believe much in benchmarking databases, I don’t think benchmarking results are very meaningful or indicative of what real applications will behave like. There are simply too many factors that benchmarks fail to account for.

Still, not having the slightest clue on db performance is also bad.

I will look at two databases in this post, two that do not compare well. But it’s not my goal to compare them and decide that one is better than the other (especially since they do different things), rather, I want to learn something about what they do.

The first is the ubiquitous sqlite. The second is a KV store of my own writing, nonblonde.

https://sourceforge.net/projects/nonblonde/

https://nonblonde.sourceforge.net/

Now clearly nonblonde doesn’t do what sqlite does. Sqlite does complex querying and knows quite a few tricks that nonblonde doesn’t. Hence it might be a bit wrong to compare two such mismatched databases, but again, the goal is to learn, not to draw conclusions.

For the tests in this post I will have long keys. No database likes long keys much and I expect all perform better with shorter keys. That’s only too bad. My keys will be 142 bytes long on average. Nothing unrealistic about them, they are URLs and file system paths.

Every key associates 40 bytes of data.

I’m trying to get the best performance out the two databases, but I will not go to great lengths.

I set sqlite on WAL mode. I mean to give both databases plenty of cache.

I know what they both do when they sync files on each edit. Sqlite docs say 60 transactions per second on a 7200RPM disk. I have a SSD, but I need go no further. I will not test the disk syncying performance, I want to know what the database does with no syncing at all. This way, I can decide later what syncing policy suits my use.

I’ll do two insertions runs, first inserting 1M keys in an empty db in 64K batches, second inserting 64K, one per transaction.

Sqlite commands to setup the database:

PRAGMA cache_size=1048576;
PRAGMA journal_mode=WAL;
PRAGMA synchronous=OFF;
CREATE TABLE t1(a VARCHAR(640) primary key, c VARCHAR(40));

	Pop. at start	Batch size	No. batches	Total keys	Sqlite time	nonblonde time
First run	0	65536	16	1048576	32s	5s
Second run	1048576	1	65536	65536	6.5s	2.6s

We need to understand what the two databases are doing.

Sqlite writes data in a lump table. It needs either an index on the table or a primary key on the table to be able to subsequently query the table efficiently. In here, I chose the latter. Sqlite does not strip common prefixes from keys. The primary key clause must be creating an index by itself. The end result is that each key is written twice: once in the “table”, once in the “index”.

In contrast, nonblonde is only a KV store. A b-tree. Properly, nonblonde allows tables, and each table is written in its own b-tree. nonblonde knows transactions and stuff. It also knows to remove common prefixes from keys. And since the table is the index and the index the table, they keys are stored once.

I don’t think what sqlite does is wrong, it might just not be what everybody wants. Conversely, if querying on multiple criteria is envisaged, one would add several tables (indexes) to nonblonde and might get exactly where sqlite is.

The two databases have different goals for data insertion. Sqlite tries hard to keep data in order for the purpose of optimizing subsequent range queries. Curiously (or bizarrely) it does not believe it can rise to the task, and thus its docs recommend optimizing the database daily. nonblonde recognizes it cannot chase this organization goal and chooses to localize writing. With the result that the file space is not managed very tightly. Just like for sqlite, rewriting the database tables in sequential order would see better range query times.

The resulting database files are larger than need to be, especially so for nonblonde. They can be reorganized for a tighter fit.

	Sqlite file size	nonblonde file size
after first run, before reorganizing	517MB	419MB
before second run, after reorganizing	486MB	87MB

Due to its prefix stripping, nonblonde managed to actually compress data. Sqlite on the other hand wrote each key twice.

The times given for sqlite are the best obtained, but perversely, under different conditions. I meant to give plenty of cache to both databases, but that did no go as I expected for sqlite. With plenty of cache, sqlite completed the first run in 32s. Without a cache indication, it took 72s. The 6s time for the second run was obtained without indicating a cache size. When I tried giving it a cache large enough to hold the entire database I got 34s instead.

The insertion times being as they are, let’s see how fast we can query data.

I use at first a scripted querying that is not exactly efficient, driving large generation and parsing times. These times reflect in the numbers given below, and they affect sqlite disproportionately.

I query present keys, randomly selected.

Key count	Sqlite	nonblonde
1024	.119s	.100s
4096	.296s	.109s
16384	.942s	.244s
65536	3.372s	.504s

The database is opened for each of the query runs. nonblonde has a large database opening time, reflected in the obtained timings.

Both sqlite and nonblonde are allowed plenty of cache, yet sqlite does not seem to benefit from it.

I need to factor out the input generation and parsing time, so I compare the times I get for querying 64K keys one time against querying the same key 64K times.

Key count X Times	Sqlite	nonblonde
64K X 1	3.159s	.493s
1 X 64K	1.786s	.17s
diff	1.373s	.323s

Sqlite is searching the index and then goes to retrieve the record from the table, doing reads for both of the two. nonblonde has to read only one structure.

Sqlite sees faster times if all the queries are put in one “transaction”. By about 10-15%.

Finally, I look at the cold vs hot query times. The numbers above are for a database that’s likely in the OS buffers. I query 64K keys after a system restart, so that the database is read from disk, and I query the same keys again, to see the results coming from the OS buffers.

Db state	Sqlite	nonblonde
Cold (first run)	30.5s	6.3s
Hot (second run)	3.49s	.5s