YCSB performance benchmark on CockroachDB
Intro to go-ycsb
go-ycsb is a Go port of YCSB. It fully supports all YCSB generators and the Core workload so we can do the basic CRUD benchmarks with Go.
Install golang package
$ wget https://dl.google.com/go/go1.19.2.linux-amd64.tar.gz
$ tar -C /usr/local -xvzf go1.19.2.linux-amd64.tar.gz
$ cat /usr/local/go/VERSION
go1.19.2
$ vim /root/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/go/bin
export PATH
$ source /root/.bash_profile
$ go version
go version go1.19.2 linux/amd64
Install go-ycsb
$ git clone https://github.com/pingcap/go-ycsb.git
$ cd go-ycsb
$ make
go build -tags " libsqlite3" -o bin/go-ycsb cmd/go-ycsb/*
# github.com/mattn/go-sqlite3
/root/go/pkg/mod/github.com/mattn/go-sqlite3@v2.0.1+incompatible/backup.go:12:21: fatal error: sqlite3.h: No such file or directory
#include <sqlite3.h>
^
compilation terminated.
make: *** [build] Error 2
$ cat /etc/centos-release
CentOS Linux release 7.9.2009 (Core)
$ yum install sqlite-devel.x86_64
$ rpm -qa | grep sqlite
sqlite-3.7.17-8.el7_7.1.x86_64
sqlite-devel-3.7.17-8.el7_7.1.x86_64
$ make
go build -tags " libsqlite3" -o bin/go-ycsb cmd/go-ycsb/*
$ ./bin/go-ycsb --help
Go YCSB
Usage:
go-ycsb [command]
Available Commands:
help Help about any command
load YCSB load benchmark
run YCSB run benchmark
shell YCSB Command Line Client
Flags:
-h, --help help for go-ycsb
Use "go-ycsb [command] --help" for more information about a command.
YCSB workloads
YCSB includes a set of core workloads that define a basic benchmark for cloud systems. Of course, you can define your own workloads, as described in Implementing New Workloads. However, the core workloads are a useful first step, and obtaining these benchmark numbers for a variety of different systems would allow you to understand the performance tradeoffs of different systems.
The core workloads consist of six different workloads:
[root@cockroach-db-host go-ycsb]# ls db
aerospike basic cassandra etcd minio mysql pg rocksdb sqlite
badger boltdb elasticsearch foundationdb mongodb pegasus redis spanner tikv
[root@cockroach-db-host go-ycsb]# ls workloads/
minio workloada workloadb workloadc workloadd workloadd.orig workloade workloadf workload_template
Workload A: Update heavy workload
$ cat go-ycsb/workloads/workloada
# Workload A: Update heavy workload
# Application example: Session store recording recent actions
#
# Read/update ratio: 50/50
# Default data size: 1 KB records (10 fields, 100 bytes each, plus key)
# Request distribution: zipfian
recordcount=1000
operationcount=1000
workload=core
readallfields=true
readproportion=0.5
updateproportion=0.5
scanproportion=0
insertproportion=0
requestdistribution=uniform
Workload B: Read mostly workload
$ cat go-ycsb/workloads/workloadb
# Yahoo! Cloud System Benchmark
# Workload B: Read mostly workload
# Application example: photo tagging; add a tag is an update, but most operations are to read tags
#
# Read/update ratio: 95/5
# Default data size: 1 KB records (10 fields, 100 bytes each, plus key)
# Request distribution: zipfian
recordcount=1000
operationcount=1000
workload=core
readallfields=true
readproportion=0.95
updateproportion=0.05
scanproportion=0
insertproportion=0
requestdistribution=uniform
Workload C: Read only
$ cat go-ycsb/workloads/workloadc
# Yahoo! Cloud System Benchmark
# Workload C: Read only
# Application example: user profile cache, where profiles are constructed elsewhere (e.g., Hadoop)
#
# Read/update ratio: 100/0
# Default data size: 1 KB records (10 fields, 100 bytes each, plus key)
# Request distribution: zipfian
recordcount=1000
operationcount=1000
workload=core
readallfields=true
readproportion=1
updateproportion=0
scanproportion=0
insertproportion=0
requestdistribution=uniform
Workload D: Read latest workload
$ cat go-ycsb/workloads/workloadd
# Yahoo! Cloud System Benchmark
# Workload D: Read latest workload
# Application example: user status updates; people want to read the latest
#
# Read/update/insert ratio: 95/0/5
# Default data size: 1 KB records (10 fields, 100 bytes each, plus key)
# Request distribution: latest
# The insert order for this is hashed, not ordered. The "latest" items may be
# scattered around the keyspace if they are keyed by userid.timestamp. A workload
# which orders items purely by time, and demands the latest, is very different than
# workload here (which we believe is more typical of how people build systems.)
recordcount=1000
operationcount=1000
workload=core
readallfields=true
readproportion=0.95
updateproportion=0
scanproportion=0
insertproportion=0.05
requestdistribution=latest
Workload E: Short ranges
$ cat go-ycsb/workloads/workloade
# Yahoo! Cloud System Benchmark
# Workload E: Short ranges
# Application example: threaded conversations, where each scan is for the posts in a given thread (assumed to be clustered by thread id)
#
# Scan/insert ratio: 95/5
# Default data size: 1 KB records (10 fields, 100 bytes each, plus key)
# Request distribution: zipfian
# The insert order is hashed, not ordered. Although the scans are ordered, it does not necessarily
# follow that the data is inserted in order. For example, posts for thread 342 may not be inserted contiguously, but
# instead interspersed with posts from lots of other threads. The way the YCSB client works is that it will pick a start
# key, and then request a number of records; this works fine even for hashed insertion.
recordcount=1000
operationcount=1000
workload=core
readallfields=true
readproportion=0
updateproportion=0
scanproportion=0.95
insertproportion=0.05
requestdistribution=uniform
maxscanlength=1
scanlengthdistribution=uniform
Workload F: Read-modify-write
$ cat go-ycsb/workloads/workloadf
# Yahoo! Cloud System Benchmark
# Workload F: Read-modify-write workload
# Application example: user database, where user records are read and modified by the user or to record user activity.
#
# Read/read-modify-write ratio: 50/50
# Default data size: 1 KB records (10 fields, 100 bytes each, plus key)
# Request distribution: zipfian
recordcount=1000
operationcount=1000
workload=core
readallfields=true
readproportion=0.5
updateproportion=0
scanproportion=0
insertproportion=0
readmodifywriteproportion=0.5
requestdistribution=uniform
Load the data to database
The database needs to be created before data load.
root@cockroach-db-host-ip:26257/defaultdb> create database test;
CREATE DATABASE
Time: 16ms total (execution 16ms / network 0ms)
root@cockroach-db-host-ip:26257/defaultdb> show databases;
database_name | owner | primary_region | regions | survival_goal
----------------+-------+----------------+---------+----------------
defaultdb | root | NULL | {} | NULL
postgres | root | NULL | {} | NULL
system | node | NULL | {} | NULL
test | root | NULL | {} | NULL
(4 rows)
Time: 6ms total (execution 5ms / network 1ms)
Load the database as below:
[root@cockroach-db-host go-ycsb]# ./bin/go-ycsb load --help
YCSB load benchmark
Usage:
go-ycsb load db [flags]
Flags:
-h, --help help for load
--interval int Interval of outputting measurements in seconds (default 10)
-p, --prop stringArray Specify a property value with name=value
-P, --property_file strings Spefify a property file
--table string Use the table name instead of the default "usertable"
--target int Attempt to do n operations per second (default: unlimited) - can also be specified as the "target" property
--threads int Execute using n threads - can also be specified as the "threadcount" property (default 1)
$ ./bin/go-ycsb load cockroach -P workloads/workloadd -p pg.host=cockroach-db-host-ip -p pg.port=26257 -p pg.user=root -p pg.db=test -p pg.sslmode=disable -p dropdata=true
***************** properties *****************
"insertproportion"="0.05"
"dropdata"="true"
"scanproportion"="0"
"workload"="core"
"recordcount"="1000"
"pg.port"="26257"
"dotransactions"="false"
"updateproportion"="0"
"pg.user"="root"
"requestdistribution"="latest"
"readallfields"="true"
"pg.sslmode"="disable"
"command"="load"
"pg.host"="cockroach-db-host-ip"
"operationcount"="1000"
"pg.db"="test"
"readproportion"="0.95"
**********************************************
Run finished, takes 2.794042708s
INSERT - Takes(s): 2.8, Count: 1000, OPS: 361.7, Avg(us): 2734, Min(us): 1794, Max(us): 29903, 99th(us): 3557, 99.9th(us): 10999, 99.99th(us): 29903
The “–threads” option can be specified to increase the number of threads for data load.
$ ./bin/go-ycsb load cockroach -P workloads/workloadd --threads 96 -p pg.host=cockroach-db-host-ip -p pg.port=26257 -p pg.user=root -p pg.db=test -p pg.sslmode=disable -p dropdata=true
Verify the loaded data
root@cockroach-db-host-ip:26257/defaultdb> use test;
SET
Time: 1ms total (execution 1ms / network 0ms)
root@cockroach-db-host-ip:26257/test> show tables;
schema_name | table_name | type | owner | estimated_row_count | locality
--------------+------------+-------+-------+---------------------+-----------
public | usertable | table | root | 1000 | NULL
(1 row)
Time: 39ms total (execution 38ms / network 0ms)
root@cockroach-db-host-ip:26257/test> show columns from usertable;
column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden
--------------+--------------+-------------+----------------+-----------------------+------------------+------------
ycsb_key | VARCHAR(64) | false | NULL | | {usertable_pkey} | false
field0 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
field1 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
field2 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
field3 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
field4 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
field5 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
field6 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
field7 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
field8 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
field9 | VARCHAR(100) | true | NULL | | {usertable_pkey} | false
(11 rows)
Time: 56ms total (execution 55ms / network 1ms)
root@cockroach-db-host-ip:26257/test> show ranges from table usertable;
start_key | end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities
------------+---------+----------+-----------------------+--------------+-----------------------+----------+---------------------
NULL | NULL | 45 | 1.0670000000000000000 | 1 | | {1,2,3} | {,,}
(1 row)
Time: 26ms total (execution 25ms / network 1ms)
root@cockroach-db-host-ip:26257/test> select count(*) from usertable;
count
---------
1000
(1 row)
Time: 3ms total (execution 2ms / network 0ms)
root@cockroach-db-host-ip:26257/test> select * from usertable limit 2;
ycsb_key | field0 | field1 | field2 | field3 | field4 | field5 | field6 | field7 | field8 |
field9
user6282602628620641459 | CRvLzkeCGxHtROFZjmDBFtRGwQigtZAPNCOSMdNXgDNsmpONJsVKRyJpleAmmigBbLoCUvtqDwPNYNoipLCXFqrEvBLnIoDrHSVq | dmhlgvUykChQAJzTfgSwBwEuWwBhLgcYUkBFAObtredxAUHWkCVaWfgMQCKIYUUrnHbguWZGrZXmYVRKAoZHVOnKdBsMYYaUqCWC | UtVaaGeRrAoupCumOJNesfunCVXnJhFoWBlKqebBYFZyooDIaYHPeQVgvCqEfCdmGpVtcLZJYhjvMSyFBZixzyMiykEoTZXMbFic | VmmyYkAgPawZuSqZmSiIEoEnssZHuLfbBhcpFixthpYsZUHgbxfpiffrgOFSlIYgejLLQuXMaGDxBbKHegWaHXpDBdxUUHZgJSLx | cCRKTqdUukzOdPwxqTBKyBEQPkIyIgAkxdaqXAiYbNwhuJVCoEqpBPSUApjLyvSXdyAqovEwSnsiIVvuviUCTiqcelbJuwvnCGyx | JuggBRLaLLqWhcCNoxEpbJZsXhcxSGyaPNRUrLoqrnKvpJfLvABueeZQOsmCERRMXasJIIJazBQoqLKVHfELiOwzolwgcXfOtLco | DJrJlWIgICwWgOuEQjnzUiZoCnGnKYXcJtrrgEYznFnKsOEmapuWzuwFVekgElVlYuwOoruMKqHoIOjHqLYZCUzoiSsIbPHZscaq | MOZiEsuFeHnouWlGmBcvNNZvXuOWqHlHKBDEFfYXcsaHNUGJPLUWpEGeHOKOilztleethxYoHLdAFBZdClYRRApEDoezGpuEvpwa | YvdqeMSnQPLoXYfExfduQrNSFRiTVKFPZdGATDpQMvtCHAdqTqMYjMpElQgcRjUuJUBMqwXJDzcVWlGiQSAirgVVDBGoAHqyzgdA | mkKBHtYOEsdXlpndKPOYHTQiqkFbTAsDbESpWeYXVSiBximrDHhjBARBZHkjWQjzZGITkqjecazQeCHvmkePqefSNieJtFTdbvRI
user6282603728132269670 | DSNUAvAFJxstogmWRPJmbNikPspeilFAZQJKzLRdxJpyOQJeQbltpxqnYJQtWwihrPbvZoOzbaMrMlLKBiAWjttcMskyfguSFidf | pSYiwZdqValtHyYGoYwkVYGawNErokKdMTiNCJUrcKTQWSQWKGixRVDxJTcaCfUXHMLxSSvDdFTVjTuxqWGWkWlxKocypFUGupqH | YBoLQdTXAQpjUgZReUGUfAOzsrBMKZyoZCdwxWJrOBkRlbbpXUmZLQxHHnDgnLNCNEnxozGPFCGEOVilyqLCixqDIsPNhccTdhbq | UQNzeZJXzbvwwupufEHeacjxxrTGCYfppLsbOCxLyfvuhZYPmoSyhKupkGRkHsYSnQVCFHCokWmHFWnLbYctVGdAunsPnkDjQAPk | HkqgViwhjGaBGUXJikhpwPMFCxqtOxoIunZQjmeKcytoItvGrfvcztncPzUukauoovIoQRmBCcQhJpoZxDLmXcpQJgFAbDYsbBGc | VDazXEFSiKZSKoSPMpAnFNAzqbrGcZwwrSMjFIJoEJPfvDzdwWwiVmHRkRGuFzwrXTOAKOiazelzsQFMAARAZdreFMWzKIwXieCP | lwbhKgtyTCZYJZmeRbVmWzkmPsWvdURifyZgGuSwVRrcCDCmrwvEdnFTWgCsYmcqzFgziAjgakhSuJjyixzoNThbtIgTkPuzAMVX | cKRURxsRlJvcKgrRQwMFeRLKCWfiavzTKqTJbYbWKZgOkZuutCIltPbwufcIMPDRWdgAZVaPqGCussiIxcFjMHMioVNLLQklptbG | OzGsnjbnnUtzsxjSVLpukwOcdegoTYrBLhOISwiNdNMGzKXBbDIRpBQqvNFEUEVfMPPJIWGSVzvSGTyAazIOvSMnWeEdKnEuggge | aHOwSIrvztvWYhrsdYaYGjtdjLTiVgfZluTgohVwNABYlZyiYPdrZgmMCFTQLOXqcbBbtahLNzPxdtoeqxrCBAussTPjmJrIzxns
(2 rows)
Time: 2ms total (execution 1ms / network 1ms)
Run the YCSB benchmark
[root@cockroach-db-host go-ycsb]# ./bin/go-ycsb run --help
YCSB run benchmark
Usage:
go-ycsb run db [flags]
Flags:
-h, --help help for run
--interval int Interval of outputting measurements in seconds (default 10)
-p, --prop stringArray Specify a property value with name=value
-P, --property_file strings Spefify a property file
--table string Use the table name instead of the default "usertable"
--target int Attempt to do n operations per second (default: unlimited) - can also be specified as the "target" property
--threads int Execute using n threads - can also be specified as the "threadcount" property (default 1)
[root@cockroach-db-host go-ycsb]# ./bin/go-ycsb run cockroach -P workloads/workloadd --threads 96 -p pg.host=cockroach-db-host-ip -p pg.port=26257 -p pg.user=root -p pg.db=test -p pg.sslmode=disable
Run finished, takes 41m57.357806424s
INSERT - Takes(s): 2517.3, Count: 4992285, OPS: 1983.2, Avg(us): 5053, Min(us): 1214, Max(us): 32863, 99th(us): 26719, 99.9th(us): 31471, 99.99th(us): 32671
READ - Takes(s): 2517.3, Count: 94554325, OPS: 37561.2, Avg(us): 2131, Min(us): 389, Max(us): 20687, 99th(us): 11543, 99.9th(us): 19695, 99.99th(us): 20591
Reference
- https://github.com/pingcap/go-ycsb
- https://github.com/brianfrankcooper/YCSB/wiki/Core-Workloads
- https://github.com/brianfrankcooper/YCSB/wiki/Running-a-Workload
- https://www.scylladb.com/product/benchmarks/scylla-vs-cockroachdb/
- https://medium.com/@siddontang/use-go-ycsb-to-benchmark-different-databases-8850f6edb3a7
- https://github.com/cockroachdb/docs/blob/master/v2.1/cockroach-workload.md
- Yahoo announcement
- Paper - Benchmarking Cloud Serving Systems with YCSB
- Yahoo lab experiments
- A Quick Introduction to YCSB
- The Ultimate YCSB Benchmark Guide 2021
- http://highscalability.com/blog/2021/2/17/benchmark-ycsb-numbers-for-redis-mongodb-couchbase2-yugabyte.html
- https://en.wikipedia.org/wiki/YCSB