Dalun Blog

TiDB / CockroachDB / YugabyteDB Sysbench Performance Benchmark

dalun -

Environment

Infra

- Bench Node: 1 vCPU / 1GB / 25GB SSD / 192.168.0.1/24
- DB    Node: 2 vCPU / 4GB / 80GB SSD / 192.168.0.2/24

1Gbps Link in 192.168.0.0/24, created on Vultr

Software Version

  • TiDB: v4.0
  • CockroachDB: v20.2.3
  • YugabyteDB: v2.5.0.0
  • sysbench: Git #bbee5d5

Benchmark

  • 100 tables
  • 100000 rows in each table

Setup

TiDB

Setup instruction follow official document

  • install
[root@dbnode ~]# curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
[root@dbnode ~]# source .bash_profile
  • create cluster
[root@dbnode ~]# tiup playground v4.0.0 --host 192.168.0.2 --db 1 --pd 3 --kv 3 --monitor

CockroachDB

Setup instruction follow official document

  • install
[root@dbnode ~]# wget -qO- https://binaries.cockroachdb.com/cockroach-v20.2.3.linux-amd64.tgz | tar xvz
[root@dbnode ~]# cp -i cockroach-v20.2.3.linux-amd64/cockroach /usr/local/bin
  • create cluster
[root@dbnode ~]# cockroach start --insecure --store=sb-local1 --listen-addr=0.0.0.0:26257 --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259 --background
[root@dbnode ~]# cockroach start --insecure --store=sb-local2 --listen-addr=0.0.0.0:26258 --http-addr=localhost:8081 --join=localhost:26257,localhost:26258,localhost:26259 --background
[root@dbnode ~]# cockroach start --insecure --store=sb-local3 --listen-addr=0.0.0.0:26259 --http-addr=localhost:8082 --join=localhost:26257,localhost:26258,localhost:26259 --background
[root@dbnode ~]# cockroach init --insecure --host=localhost:26257

YugabyteDB

Setup instruction follow official document

  • install
[root@dbnode ~]# wget https://downloads.yugabyte.com/yugabyte-2.5.0.0-linux.tar.gz
[root@dbnode ~]# tar xvfz yugabyte-2.5.0.0-linux.tar.gz && cd yugabyte-2.5.0.0/
[root@dbnode yugabyte-2.5.0.0]# ./bin/post_install.sh
  • create 1node cluster
[root@dbnode bin]# ./yugabyted start --data_dir /tmp/sb1 --base_dir /tmp/sbb1 --listen 0.0.0.0
  • create 3node cluster
[root@dbnode bin]# ./yugabyted start --data_dir /tmp/sb1 --base_dir /tmp/sbb1 --listen 127.0.0.1
[root@dbnode bin]# ./yugabyted start --data_dir /tmp/sb2 --base_dir /tmp/sbb2 --listen 127.0.0.2 --join 127.0.0.1
[root@dbnode bin]# ./yugabyted start --data_dir /tmp/sb3 --base_dir /tmp/sbb3 --listen 127.0.0.3 --join 127.0.0.1

sysbench

  • install
[root@benchnode ~]# apt install sysbench
  • run pgsql bench
[root@benchnode ~]# sysbench /usr/share/sysbench/{oltp_test_item}.lua --time 60 --db-driver=pgsql --pgsql-host=192.168.0.2 --pgsql-port={pg_port}  --pgsql-user={pg_user} --pgsql-db=postgres --pgsql-password={pg_pass} --tables=10 --table_size=100000 {prepare -> run -> cleanup}
  • run mysql bench
[root@benchnode ~]# sysbench /usr/share/sysbench/{oltp_test_item}.lua --db-driver=mysql --mysql-host=192.168.0.2 --mysql-port=4000 --mysql-user=root --mysql-db=mysql --tables=10 --table-size=100000 {prepare -> run -> cleanup}

Result

TiDB

Item Tx/s Latency min (ms) Latency avg (ms) Latency max (ms) Latency 95p (ms)
oltp_delete 288.23 1.29 3.46 14.24 4.57
oltp_insert 332.18 2.33 3.00 14.49 3.43
oltp_point_select 617.75 1.33 1.62 10.32 1.82
oltp_read_only 604.51 25.57 28.09 58.20 31.94
oltp_read_write 561.81 31.45 37.34 58.22 42.61
oltp_update_index 250.57 3.14 3.99 22.76 4.82
oltp_update_non_index 258.56 3.02 3.86 36.10 4.65
oltp_write_only 487.85 11.75 14.33 44.83 18.95
select_random_points 68.99 7.05 14.48 55.92 21.11
select_random_ranges 269.18 2.19 3.71 14.47 5.00

CockroachDB

Item Tx/s Latency min (ms) Latency avg (ms) Latency max (ms) Latency 95p (ms)
oltp_delete 617.68 1.05 1.62 25.54 1.96
oltp_insert 198.56 2.83 5.03 106.36 7.17
oltp_point_select 728.46 0.78 1.37 33.35 1.73
oltp_read_only 606.40 23.18 28.02 135.94 36.24
oltp_read_write 449.85 34.45 46.67 311.53 64.47
oltp_update_index 471.66 1.47 2.12 48.43 2.86
oltp_update_non_index 416.49 1.73 2.40 43.75 2.91
oltp_write_only 541.03 9.71 12.93 83.90 17.95
select_random_points 53.11 8.47 18.82 131.74 30.26
select_random_ranges 216.49 2.99 4.61 43.71 6.09

YugabyteDB (1-Node Cluster)

Item Tx/s Latency min (ms) Latency avg (ms) Latency max (ms) Latency 95p (ms)
oltp_delete 411.01 1.17 2.43 18.29 3.89
oltp_insert 355.39 2.08 2.81 24.47 3.43
oltp_point_select 591.91 1.44 1.69 207.49 1.86
oltp_read_only 9.61 1664.73 1769.76 2030.33 1938.16
oltp_read_write 11.26 1744.68 1865.75 2054.83 2009.23
oltp_update_index 275.13 2.75 3.63 24.27 4.10
oltp_update_non_index 346.58 2.28 2.88 26.90 3.36
oltp_write_only 603.20 9.78 11.60 32.16 14.73
select_random_points 40.44 10.28 24.72 63.21 31.94
select_random_ranges 1.95 481.83 513.71 577.10 539.71

YugabyteDB (3-Node Cluster)

Item Tx/s Latency min (ms) Latency avg (ms) Latency max (ms) Latency 95p (ms)
oltp_delete 151.83 1.02 6.58 78.53 10.09
oltp_insert 132.92 5.21 7.52 99.84 9.56
oltp_point_select 516.29 1.47 1.93 30.76 2.61
oltp_read_only 8.79 1787.12 1934.69 2660.52 2320.55
oltp_read_write 7.19 1927.86 2920.22 9512.29 6713.97
oltp_update_index 93.91 6.25 10.64 416.51 17.01
oltp_update_non_index 172.86 3.85 5.78 59.12 7.17
oltp_write_only 287.86 17.07 24.31 108.06 31.94
select_random_points 41.47 8.02 24.11 79.50 31.37
select_random_ranges 1.97 474.78 508.71 554.65 530.08

Conclusion

  • Not sure what  happend on YbDB OLTP
  • CrDB at max latency will be unstable
  • Memory Usage:
    • TiDB > YbDB3 > YbDB1 > CrDB
  • Table
    • C: CrDB = TiDB > YbDB1 > YbDB3
    • D: CrDB = TiDB > YbDB1 > YbDB3
  • Row
    • C: YbDB1 > TiDB > CrDB > YbDB3
    • R: CrDB > TiDB > YbDB1 > YbDB3
    • U: CrDB > YbDB1 > TiDB > YbDB3
    • D: CrDB > YbDB1 > TiDB > YbDB3

Overall

CrDB > TiDB > YbDB