2019-04-19

Huge List of Database Benchmark

Today we will benchmark a single node version of distributed database (and some non-distributed database for comparison), the client all written with Go (with any available driver). The judgement will be about performance (that mostly write, and some infrequent read), not about the distribution performance (I will take a look in some other time). I searched a lot of database from DbEngines for database that could suit my needs for my next project. For session kv-store I'll be using obviously first choice is Aerospike, but since they cannot be run inside server that I rent (that uses OpenVZ), so I'll go for second choice that is Redis. Here's the list of today's contender:
  • CrateDB, a highly optimized for huge amount of data (they said), probably would be the best for updatable time series, also with built-in search engine, so this one is quite fit my use case probably to replace [Riot (small scale) or Manticore (large scale)] and [InfluxDB or TimescaleDB], does not support auto increment
  • CockroachDB, self-healing database with PostgreSQL-compatible connector
  • MemSQL, which also can replace kv-store, there's a limit of 128GB for free version
    The client/connector is MySQL-compatible
  • MariaDB (MySQL), one of the most popular open source RDBMS, for the sake of comparison
  • PostgreSQL, my favorite RDBMS, for the sake of comparison 
What's the extra motivation of this post?
I almost never use distributed database, since all of my project have no more than 200 concurrent users/sec. I've encountered bottleneck before, and the culprit is multiple slow complex queries, that could be solved by queuing to another message queue, and process them one by one instead of bombing database's process at the same time and hogging out the memory.

The benchmark scenario would be like this:
1. 50k inserts of single column string value, 200k inserts of 2 column unique value, 900k insert of unique
INSERT INTO users(id, uniq_str) -- x50k
INSERT INTO items(fk_id, typ, amount) -- x50k x4
INSERT INTO rels(fk_low, fk_high, bond) -- x900k

2. while inserting at 5%+, there would be at least 100k random search queries of unique value/, and 300k random search queries, every search queries, there would be 3 random update of amount
SELECT * FROM users WHERE uniq_str = ? -- x100k
SELECT * FROM items WHERE fk_id = ? AND typ IN (?) -- x100k x3
UPDATE items SET amount = amount + xxx WHERE id = ? -- x100k x3

3. while inserting at 5%+, there would be also at least 100k random search queries
SELECT * FROM items WHERE fk_id = ?

4. while inserting at 5%+, there also at least 200k query of relations and 50% chance to update the bond
SELECT * FROM rels WHERE fk_low = ? or fk_high = ? -- x200k
UPDATE rels SET bond = bond + xxx WHERE id = ? -- x200k / 2


This benchmark represent simplified real use case of the game I'm currently develop. Let's start with PostgreSQL 10.7 (current one on Ubuntu 18.04.1 LTS), the configuration generated by pgtune website:

max_connections = 400
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

Create the user and database first:

sudo su - postgres
createuser b1
createdb b1
psql 
GRANT ALL PRIVILEGES ON DATABASE b1 TO b1
\q

Add to pg_hba.conf if required, then restart:

local   all b1 trust
host all b1 127.0.0.1/32 trust
host all b1 ::1/128 trust

For slow databases, all values reduced by 20 except query-only.

$ go run pg.go lib.go
[Pg] RandomSearchItems (100000, 100%) took 24.62s (246.21 µs/op)
[Pg] SearchRelsAddBonds (10000, 100%) took 63.73s (6372.56 µs/op)
[Pg] UpdateItemsAmounts (5000, 100%) took 105.10s (21019.88 µs/op)
[Pg] InsertUsersItems (2500, 100%) took 129.41s (51764.04 µs/op)
USERS CR    :    2500 /    4999 
ITEMS CRU   :   17500 /   14997 +  698341 / 14997
RELS  CRU   :    2375 /   16107 / 8053
SLOW FACTOR : 20
CRU µs/rec  : 5783.69 / 35.26 / 7460.65

Next we'll try with MySQL 5.7, create user and database first, then multiply all memory config by 10 (since there are automatic config generator for mysql?):

innodb_buffer_pool_size=4G

sudo mysql
CREATE USER 'b1'@'localhost' IDENTIFIED BY 'b1';
CREATE DATABASE b1;
GRANT ALL PRIVILEGES ON b1.* TO 'b1'@'localhost';
FLUSH PRIVILEGES;
sudo mysqltuner # not sure if this useful

And here's the result:

$ go run maria.go lib.go
[My] RandomSearchItems (100000, 100%) took 16.62s (166.20 µs/op)
[My] SearchRelsAddBonds (10000, 100%) took 86.32s (8631.74 µs/op)
[My] UpdateItemsAmounts (5000, 100%) took 172.35s (34470.72 µs/op)
[My] InsertUsersItems (2500, 100%) took 228.52s (91408.86 µs/op)
USERS CR    :    2500 /    4994 
ITEMS CRU   :   17500 /   14982 +  696542 / 13485 
RELS  CRU   :    2375 /   12871 / 6435 
SLOW FACTOR : 20 
CRU µs/rec  : 10213.28 / 23.86 / 13097.44

Next we'll try with MemSQL 6.7.16-55671ba478, while the insert and update performance is amazing, the query/read performance is 3-4x slower than traditional RDBMS:

$ go run memsql.go lib.go # 4 sec before start RU
[Mem] InsertUsersItems (2500, 100%) took 4.80s (1921.97 µs/op)
[Mem] UpdateItemsAmounts (5000, 100%) took 13.48s (2695.83 µs/op)
[Mem] SearchRelsAddBonds (10000, 100%) took 14.40s (1440.29 µs/op)
[Mem] RandomSearchItems (100000, 100%) took 64.87s (648.73 µs/op)
USERS CR    :    2500 /    4997 
ITEMS CRU   :   17500 /   14991 +  699783 / 13504 
RELS  CRU   :    2375 /   19030 / 9515 
SLOW FACTOR : 20 
CRU µs/rec  : 214.75 / 92.70 / 1255.93

$ go run memsql.go lib.go # 2 sec before start RU
[Mem] InsertUsersItems (2500, 100%) took 5.90s (2360.01 µs/op)
[Mem] UpdateItemsAmounts (5000, 100%) took 13.76s (2751.67 µs/op)
[Mem] SearchRelsAddBonds (10000, 100%) took 14.56s (1455.95 µs/op)
[Mem] RandomSearchItems (100000, 100%) took 65.30s (653.05 µs/op)
USERS CR    :    2500 /    4998 
ITEMS CRU   :   17500 /   14994 +  699776 / 13517 
RELS  CRU   :    2375 /   18824 / 9412 
SLOW FACTOR : 20 
CRU µs/rec  : 263.69 / 93.32 / 1282.38

$ go run memsql.go lib.go # SLOW FACTOR 5
[Mem] InsertUsersItems (10000, 100%) took 31.22s (3121.90 µs/op)
[Mem] UpdateItemsAmounts (20000, 100%) took 66.55s (3327.43 µs/op)
[Mem] RandomSearchItems (100000, 100%) took 85.13s (851.33 µs/op)
[Mem] SearchRelsAddBonds (40000, 100%) took 133.05s (3326.29 µs/op)
USERS CR    :   10000 /   19998
ITEMS CRU   :   70000 /   59994 +  699944 / 53946
RELS  CRU   :   37896 /  300783 / 150391
SLOW FACTOR : 5
CRU µs/rec  : 264.80 / 121.63 / 1059.16


Next we'll try CrateDB 3.2.7, with similar setup like PostgreSQL, the result:

go run crate.go lib.go
[Crate] SearchRelsAddBonds (10000, 100%) took 49.11s (4911.38 µs/op)
[Crate] RandomSearchItems (100000, 100%) took 101.40s (1013.95 µs/op)
[Crate] UpdateItemsAmounts (5000, 100%) took 246.42s (49283.84 µs/op)
[Crate] InsertUsersItems (2500, 100%) took 306.12s (122449.00 µs/op)
USERS CR    :    2500 /    4965 
ITEMS CRU   :   17500 /   14894 +  690161 / 14895 
RELS  CRU   :    2375 /    4336 / 2168 
SLOW FACTOR : 20 
CRU µs/rec  : 13681.45 / 146.92 / 19598.85

Next is CockroachDB 2.1.3, the result:

go run cockroach.go lib.go
[Cockroach] SearchRelsAddBonds (10000, 100%) took 61.87s (6187.45 µs/op)
[Cockroach] RandomSearchItems (100000, 100%) took 93.12s (931.22 µs/op)
[Cockroach] UpdateItemsAmounts (5000, 100%) took 278.10s (55620.39 µs/op)
[Cockroach] InsertUsersItems (2500, 100%) took 371.76s (148704.47 µs/op)
USERS CR    :    2500 /    4993 
ITEMS CRU   :   17500 /   14979 +  699454 / 14979 
RELS  CRU   :    2375 /    5433 / 2716 
SLOW FACTOR : 20 
CRU µs/rec  : 16615.02 / 133.14 / 20673.81


These benchmark performed using i7-4720HQ 32GB RAM with SSD disk. At first there's a lot that I want to add to this benchmark to make this huge '__'), such as:
  • YugaByteDB, similar to CockroachDB/ScyllaDB but written in C++
  • TiDB, a work in progress approach of CockroachDB but with MySQL-compatible connector
  • DGraph, a graph database written in Go, the backup is local same as MemSQL (you cannot do something like this ssh foo@bar "pg_dump | xz - -c" | pv -r -b > /tmp/backup_`date +%Y%m%d_%H%M%S`.sql.xz")
  • Cayley, a graph layer written in Go, can support many backend storage
  • ScyllaDB, a C++ version of Cassandra
  • Clickhouse, claimed to be fastest OLAP database, but doesn't support UPDATE.
  • RQLite, a distributed SQLite
  • Redis, obviously for the sake of comparison
  • OrientDB, multi-model graph database
  • RethinkDB, document-oriented database
  • ArangoDB, multi-model database, with built-in Foxx Framework for creating REST APIs
  • Tarantool, a redis competitor with ArrangoDB-like features but with Lua instead of JS, I want to see if this simpler to use but with near equal performance than Aerospike
  • MongoDB, one of the most popular open source document database, for the sake of comparison, I'm not prefer this one because of the memory usage.
  • Aerospike, fastest distributed kv-store I ever found, just for the sake of comparison, the free version limited to 2 namespace with 4 billions object. Too bad this one cannot be installed on OpenVZ-based VM.
As you probably know, I'm a huge fan of Go for backend, not any other language, so I exclude any JVM-based database (in exception of OrientDB because their screenshot looks interesting). For now, I have found what I need, so probably i'll add the rest later. The code for this benchmark can be found here: https://github.com/kokizzu/hugedbbench (send pull request then i'll run and update this post) and the spreadsheet here: http://tiny.cc/hugedb 
The chart (lower is better) shown below: