Cockroachdb one-node performance

I tested performance of one-node cockroachdb using sysbench.
The test case of sysbench is oltp-insert.lua.

The result is:

  • TPS in sysbench: 4000
  • %CPU of cockroach: 300%

My test machine has 32 CPUs and 62G memory.
Cockroachdb version is 2.0.0.

In the same machine, I also tested mysql-5.7 using the same sysbench
test case, and the result is:

  • TPS in sysbench: 45000
  • %CPU of mysqld: 2000%

So, my questions are

  • how to improve one-node cockroachdb performance, to eat up all the CPUS?
  • is there any article about performance comparision of cockroachdb and mysql?

To increase throughput, use multiple simultaneous connections to CockroachDB.

Also, the best recommended way to increase throughput is to use multiple nodes on different servers. CockroachDB performance scales horizontally (more nodes with same number of clients per node) better than vertically (more connections on a single node).

I do not know the answer to your question about comparison articles. Note however that you should be wary of comparisons between apples and oranges – MySQL does not offer the availability guarantees of a replicated CockroachDB cluster.

You may find the following article interesting: https://www.cockroachlabs.com/blog/performance-part-two/

I use 128 client connections.

The sysbench command line is

sysbench  \
  --db-driver=pgsql \
  --pgsql-host=127.0.0.1 \
  --pgsql-port=30601 \
  --pgsql-user=root \
  --pgsql-password= \
  --time=0 \
  --events=10000000 \
  --report-interval=1 \
  --threads=128 \
  oltp_insert.lua run

The cockroach command line

cockroach start --insecure --store=node1 --port=30601 --http-port=30701 --cache=25% --max-sql-memory=25% --background

When I change threads to 1280, results is almost the same.

We use mysql heavily, and now try to do some investigation about
distributed database.

Although cockroach is better for scaling horizontally, but we
still want cockroach not behind mysql too much in one-node.

Hi @haomiao,

Thanks for your interest in CockroachDB! We have yet to invest significant time into tuning our sysbench performance, but you can read through this thread to see our most recent investigation. This is something we’re planning on investing some effort into for our next release.

For now, it would be nice to understand why there is such a large disparity between CockroachDB and MySQL with this oltp_insert.lua workload. The first thing I notice is that the workload is performing single row INSERT statements. It’s important to point out that we have found inserting multiple rows into Cockroach in each INSERT statement to be orders of magnitude faster. This is especially true with distributed clusters. In fact, it can have such an impact on throughput that it is part of our performance best practices doc. Of course, we can’t change the benchmark, so let’s try to figure out what else we can do to close the gap between CRDB and MySQL.

One thing that sticks out to me is that the benchmark accepts an option called auto_inc, which determines whether rows will be inserted with incrementally increasing IDs or with random IDs. I think it’s worth trying out the experiment (again with 128 client connections) with this auto_inc option turned off. The reason for this is because auto-incrementing primary keys can lead to contention and write hotspots, and it’s almost always better to evenly distribute load across a table when possible.

If that doesn’t work, we can explore other tuning options.

No much improvement after adding auto_inc=0.

The result:

[ 212s ] thds: 128 tps: 3664.78 qps: 3664.78 (r/w/o: 0.00/3664.78/0.00) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 213s ] thds: 128 tps: 3651.20 qps: 3651.20 (r/w/o: 0.00/3651.20/0.00) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 214s ] thds: 128 tps: 3455.00 qps: 3455.00 (r/w/o: 0.00/3455.00/0.00) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00

Are there some performance tuning parameters of cockroach?

@haomiao, I did some profiling while running sysbench and came up with one easy win.

One thing that’s sticking out to me is the number of err/s the load generator is seeing. I suspect that these are serializable errors and that Cockroach is expecting the client to be retrying the txn. Of course, because sysbench is not Cockroach specific, it doesn’t know that this retry loop is expected. This could help explain some of the throughput difference you’re seeing. Is MySQL also showing such a large number of errors per second.

EDIT: @Nino pointed out to me that the err/s figure is listed on the right of the label, not the left. So it doesn’t look like there were as many errors as I had thought. Still, this is something to consider if we do see errors in the future.

Are there some performance tuning parameters of cockroach?

We don’t have specific performance tuning parameters, but there are a number of cluster-wide knobs that you can play with. These can all be set using the SET CLUSTER SETTING statement.