2017-05-19

PostgreSQL 9.6.2 vs ScyllaDB 1.7RC2

Since CockroachDB 1.0 not yet performant (ain't got time to wait), today we're gonna test PostgreSQL 9.6.2 vs ScyllaDB 1.7RC2 on Ubuntu XFS filesystem.

test1: postgresql
INSERT: 34.667685316s (3.47 ms/op)
UPDATE: 35.117617526s (3.51 ms/op)
SELECT: 47.529755777s (0.73 ms/op)
CPU: 35.14s     Real: 117.64s   RAM: 58 544 KB

test2: postgresql jsonb
INSERT: 33.861673279s (3.39 ms/op)
UPDATE: 34.038996914s (3.40 ms/op)
SELECT: 45.340834079s (0.70 ms/op)
CPU: 33.62s     Real: 113.58s   RAM: 58 140 KB

test4: scylladb
INSERT: 2.133985799s (0.21 ms/op)
UPDATE: 2.167973712s (0.22 ms/op)
SELECT: 2m24.804415353s (2.22 ms/op)
CPU: 41.29s     Real: 152.57s   RAM: 79 708 KB

Hmm.. This is weird, because the difference between this and previous benchmark are:

LabelThisPrevious
Operating System Ubuntu 17.04 Manjaro
Kernel 4.10.0-19-generic
tuned by scylla_setup
aufs_friendly 4.10.13-1
PostgreSQL config:
effective_cache
shared_buffers
work_mem
ubuntu default:
4G/128M/4M
modified:
2G/2G/16M
PostgreSQL total time 113.58s 116.34s
ScyllaDB  official deb 1.7RC2 official docker 1.6.4
ScyllaDB config 8GB / 8 / XFS 4GB / 4 / EXT4
ScyllaDB total time 152.57s 141.34s

Probably the kernel factor? Oh yeah, you can get the source on github (you can create PR if there's bug). Now let's test it in parallel with one third of data (only show first 3 and last 3 result):

test1: postgresql
I-18: (6.17 ms/op: 101)
I-12: (6.16 ms/op: 101)
I-14: (6.19 ms/op: 101)
I-32: (157.80 ms/op: 101)
I-05: (167.31 ms/op: 101)
I-31: (168.23 ms/op: 101)
INSERT: 16.99355453s (5.10 ms/op)
U-13: (5.92 ms/op: 101)
U-16: (5.97 ms/op: 101)
U-04: (6.00 ms/op: 101)
U-21: (1312.46 ms/op: 101)
U-28: (1333.33 ms/op: 101)
U-20: (1333.60 ms/op: 101)
UPDATE: 2m14.695128106s (40.41 ms/op)
S-24: (14.56 ms/op: 139)
S-69: (17.86 ms/op: 115)
S-78: (24.18 ms/op: 88)
S-06: (18.14 ms/op: 556)
S-46: (18.54 ms/op: 556)
S-37: (1427.27 ms/op: 91)
SELECT: 2m9.888985893s (5.98 ms/op: 21716)
CPU: 13.07s     Real: 281.90s   RAM: 59 072 KB

test2: postgresql jsonb
I-03: (6.23 ms/op: 101)
I-10: (6.28 ms/op: 101)
I-04: (6.30 ms/op: 101)
I-08: (157.54 ms/op: 101)
I-29: (225.82 ms/op: 101)
I-22: (314.39 ms/op: 101)
INSERT: 31.754559744s (9.53 ms/op)
U-23: (6.45 ms/op: 101)
U-13: (6.51 ms/op: 101)
U-04: (6.54 ms/op: 101)
U-05: (1287.63 ms/op: 101)
U-01: (1287.77 ms/op: 101)
U-26: (1953.67 ms/op: 101)
UPDATE: 3m17.321405467s (59.20 ms/op)
S-27: (27.54 ms/op: 124)
S-21: (21.89 ms/op: 159)
S-29: (30.55 ms/op: 115)
S-48: (323.69 ms/op: 417)
S-05: (202.41 ms/op: 667)
S-03: (121.61 ms/op: 1111)
SELECT: 2m15.109162326s (6.22 ms/op: 21716)
CPU: 13.78s     Real: 364.50s   RAM: 56 688 KB

test4: scylladb
I-17: (1.59 ms/op: 101)
I-12: (1.59 ms/op: 101)
I-30: (1.60 ms/op: 101)
I-13: (1.65 ms/op: 101)
I-22: (1.65 ms/op: 101)
I-06: (1.65 ms/op: 101)
INSERT: 166.992399ms (0.05 ms/op)
U-06: (1.60 ms/op: 101)
U-12: (1.61 ms/op: 101)
U-29: (1.62 ms/op: 101)
U-31: (1.68 ms/op: 101)
U-30: (1.68 ms/op: 101)
U-11: (1.68 ms/op: 101)
UPDATE: 170.240627ms (0.05 ms/op)
S-79: (52.05 ms/op: 86)
S-74: (50.06 ms/op: 98)
S-75: (53.78 ms/op: 96)
S-03: (12.18 ms/op: 1111)
S-02: (8.43 ms/op: 1666)
S-42: (8.53 ms/op: 1666)
SELECT: 14.678651323s (0.68 ms/op: 21716)
CPU: 16.92s     Real: 18.08s    RAM: 76 824 KB

WTF! These numbers are blazing fast!
Too bad that the gocql or probably the scylla-server sometimes refused to connect:

2017/05/19 14:16:28 gocql: unable to create session: unable to discover protocol version: dial tcp 127.0.0.1:9042: i/o timeout
2017/05/19 14:16:33 gocql: unable to create session: unable to discover protocol version: dial tcp 127.0.0.1:9042: i/o timeout
2017/05/19 14:20:30 gocql: unable to create session: unable to discover protocol version: dial tcp 127.0.0.1:9042: i/o timeout

2017/05/19 14:18:28 gocql: unable to create session: unable to discover protocol version: dial tcp 127.0.0.1:9042: getsockopt: connection refused
2017/05/19 14:18:44 gocql: unable to dial control conn 127.0.0.1: dial tcp 127.0.0.1:9042: i/o timeout
2017/05/19 14:18:44 gocql: unable to create session: control: unable to connect to initial hosts: dial tcp 127.0.0.1:9042: i/o timeout

2017/05/19 14:20:34 gocql: unable to create session: unable to discover protocol version: dial tcp 127.0.0.1:9042: i/o timeout
2017/05/19 14:20:37 gocql: unable to create session: unable to discover protocol version: dial tcp 127.0.0.1:9042: i/o timeout 

2017/05/19 14:27:46 gocql: unable to create session: unable to discover protocol version: dial tcp 127.0.0.1:9042: i/o timeout

Or when using cqlsh 127.0.0.1:

Connection error: ('Unable to connect to any servers', {'127.0.0.1': error(None, "Tried connecting to [('127.0.0.1', 9042)]. Last error: timed out")})
Connection error: ('Unable to complete the operation against any hosts', {})
Connection error: ('Unable to connect to any servers', {'127.0.0.1': error(4, "Tried connecting to [('127.0.0.1', 9042)]. Last error: Interrupted system call")})

Other those issue, I think ScyllaDB is freakingly awesome!

EDIT: this issue only happened after doing these intensive benchmark, after restarting this no longer happened