Home CockroachDB performance benchmarking
Post
Cancel

CockroachDB performance benchmarking

CockroachDB key concepts

  • Range - CockroachDB stores all user data (tables, indexes, etc.) and almost all system data in a giant sorted map of key-value pairs. This keyspace is divided into “ranges”, contiguous chunks of the keyspace, so that every key can always be found in a single range.

    From a SQL perspective, a table and its secondary indexes initially map to a single range, where each key-value pair in the range represents a single row in the table (also called the primary index because the table is sorted by the primary key) or a single row in a secondary index. As soon as that range reaches 512 MiB in size, it splits into two ranges. This process continues for these new ranges as the table and its indexes continue growing.

    1
    2
    
    [root@host1 ~]# tail -f /var/log/cockroachdb_logs/cockroach.log
    I220908 17:54:02.220498 5523606 kv/kvserver/pkg/kv/kvserver/replica_command.go:420 ⋮ [n1,split,s1,r499/1:‹/Table/113/1/4{394/6…-693/6…}›] 1700  initiating a split of this range at key ‹/Table/113/1/4402/59627› [r501] (‹512 MiB above threshold size 512 MiB›)‹›
    
  • Replica - CockroachDB replicates each range (3 times by default) and stores each replica on a different node.

Refer to here for more.

Image

Production checklist

Check here for the important recommendations for production deployments of CockroachDB. The following only lists some of the recommended settings.

Memory

  • Disable Linux memory swapping. Over-allocating memory on production machines can lead to unexpected performance issues when pages have to be read back into memory.
  • For production deployments, set –cache to 25% or higher. Avoid setting –cache and –max-sql-memory to a combined value of more than 75% of a machine’s total RAM. Doing so increases the risk of memory-related failures.

Storage

  • The maximum recommended storage capacity per node is 2.5 TiB, regardless of the number of vCPUs.
  • Use dedicated volumes for the CockroachDB store. Do not share the store volume with any other I/O activity.
  • Store CockroachDB log files in a separate volume from the main data store so that logging is not impacted by I/O throttling.
  • The recommended Linux filesystems are ext4 and XFS.

Disk I/O

  • Use sysbench to benchmark IOPS on your cluster. If IOPS decrease, add more nodes to your cluster to increase IOPS.
  • Do not use LVM in the I/O path. Dynamically resizing CockroachDB store volumes can result in significant performance degradation. Using LVM snapshots in lieu of CockroachDB backup and restore is also not supported.
  • The optimal configuration for striping more than one device is RAID 10. RAID 0 and 1 are also acceptable from a performance perspective.

Network

When starting a node, two main flags are used to control its network connections:

  • –listen-addr determines which address(es) to listen on for connections from other nodes and clients.
  • –advertise-addr determines which address to tell other nodes to use.

Image

Load balancing

Each CockroachDB node is an equally suitable SQL gateway to a cluster, but to ensure client performance and reliability, it’s important to use load balancing:

  • Performance: Load balancers spread client traffic across nodes. This prevents any one node from being overwhelmed by requests and improves overall cluster performance (queries per second).
  • Reliability: Load balancers decouple client health from the health of a single CockroachDB node. To ensure that traffic is not directed to failed nodes or nodes that are not ready to receive requests, load balancers should use CockroachDB’s readiness health check.

Cache and SQL memory size

CockroachDB manages its own memory caches, independently of the operating system. These are configured via the –cache and –max-sql-memory flags.

Each node has a default cache size of 128MiB that is passively consumed. The default was chosen to facilitate development and testing, where users are likely to run multiple CockroachDB nodes on a single machine. Increasing the cache size will generally improve the node’s read performance.

Each node has a default SQL memory size of 25%. This memory is used as-needed by active operations to store temporary data for SQL queries.

  • Increasing a node’s cache size will improve the node’s read performance.
  • Increasing a node’s SQL memory size will increase the number of simultaneous client connections it allows, as well as the node’s capacity for in-memory processing of rows when using ORDER BY, GROUP BY, DISTINCT, joins, and window functions.

You can check cache size and SQL memory pool size in the log. In the following example output, it matches with the specified 25% cache and SQL memory size setting.

1
2
3
4
5
6
$ vim cockroach.log
I220905 17:49:33.671304 1 server/config.go:487 ⋮ [n?] 6  system total memory: 1008 GiB
I220905 17:49:33.671318 1 server/config.go:489 ⋮ [n?] 7  server configuration:
I220905 17:49:33.671318 1 server/config.go:489 ⋮ [n?] 7 +‹max offset             500000000›
I220905 17:49:33.671318 1 server/config.go:489 ⋮ [n?] 7 +‹cache size             252 GiB›
I220905 17:49:33.671318 1 server/config.go:489 ⋮ [n?] 7 +‹SQL memory pool size   252 GiB›

CockroachDB workloads

Image

bank workload

1
2
3
$ cockroach workload init bank 'postgresql://root@host1:26257?sslmode=disable'
I220831 23:52:14.593170 1 workload/workloadsql/dataload.go:146  [-] 1  imported bank (0s, 1000 rows)
I220831 23:52:14.609421 1 workload/workloadsql/workloadsql.go:136  [-] 2  starting 9 splits
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
$ cockroach workload run bank --duration=1m 'postgresql://root@host1:26257?sslmode=disable'
I220831 23:52:55.378492 1 workload/cli/run.go:414  [-] 1  creating load generator...
I220831 23:52:55.380594 1 workload/cli/run.go:445  [-] 2  creating load generator... done (took 2.103665ms)
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
    1.0s        0         2929.8         2936.4      3.0     15.2     50.3    234.9 transfer
    2.0s        0         3387.3         3161.8      3.4     13.6     23.1     54.5 transfer
    3.0s        0         2755.2         3026.3      3.9     13.6     23.1    302.0 transfer
    4.0s        0         3295.5         3093.6      3.3     13.6     33.6    469.8 transfer
    5.0s        0         3536.5         3182.2      3.5     12.1     18.9     35.7 transfer
    6.0s        0         3558.1         3244.8      3.5     11.0     21.0     39.8 transfer
    7.0s        0         3566.9         3290.8      3.7     11.0     17.8     39.8 transfer
    8.0s        0         3317.6         3294.2      3.7     12.6     24.1     62.9 transfer
    9.0s        0         2992.9         3260.7      4.1     14.7     22.0     39.8 transfer
   10.0s        0         3628.2         3297.4      3.5     11.5     19.9     39.8 transfer
   11.0s        0         3604.5         3325.3      3.5     11.5     17.8     37.7 transfer
   12.0s        0         3668.4         3353.9      3.5     11.0     17.8     31.5 transfer
   13.0s        0         3485.9         3364.1      3.7     11.5     18.9     31.5 transfer
   14.0s        0         3377.9         3365.1      3.7     12.6     21.0     56.6 transfer
   15.0s        0         3084.4         3346.3      4.1     13.6     21.0     92.3 transfer
   16.0s        0         3650.1         3365.3      3.4     11.5     17.8     44.0 transfer
   17.0s        0         3662.7         3382.8      3.5     11.5     17.8     37.7 transfer
   18.0s        0         3461.8         3387.2      3.7     11.0     19.9    159.4 transfer
   19.0s        0         3426.3         3389.3      3.7     12.1     19.9     41.9 transfer
   20.0s        0         3196.0         3379.6      3.9     13.1     19.9     35.7 transfer
<omitted..>

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
   60.0s        0         206551         3442.5      4.6      3.7     12.1     19.9    469.8  transfer

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result
   60.0s        0         206551         3442.5      4.6      3.7     12.1     19.9    469.8

TPC-C workload

–warehouses

The number of warehouses for loading initial data, at approximately 200 MB per warehouse. Applicable commands: init or run Default: 1

–workers

The number of concurrent workers. Applicable commands: init or run Default: –warehouses * 10

The number os wareshouses can be specified by “–warehouses” option. By default, only one warehouse is created. Refer to this post for more about TPC-C benchmarking.

1
2
3
4
5
6
7
8
9
10
11
12
13
$ cockroach workload init tpcc 'postgresql://root@host1:26257?sslmode=disable'
Error: failed insert into warehouse: pq: duplicate key value violates unique constraint "warehouse_pkey"

$ cockroach workload init tpcc 'postgresql://root@host1:26257?sslmode=disable' --drop
I220901 00:32:56.365593 1 workload/workloadsql/dataload.go:146  [-] 1  imported warehouse (0s, 1 rows)
I220901 00:32:56.372727 1 workload/workloadsql/dataload.go:146  [-] 2  imported district (0s, 10 rows)
I220901 00:32:57.034349 1 workload/workloadsql/dataload.go:146  [-] 3  imported customer (1s, 30000 rows)
I220901 00:32:57.248382 1 workload/workloadsql/dataload.go:146  [-] 4  imported history (0s, 30000 rows)
I220901 00:32:57.462320 1 workload/workloadsql/dataload.go:146  [-] 5  imported order (0s, 30000 rows)
I220901 00:32:57.490476 1 workload/workloadsql/dataload.go:146  [-] 6  imported new_order (0s, 9000 rows)
I220901 00:32:57.921658 1 workload/workloadsql/dataload.go:146  [-] 7  imported item (0s, 100000 rows)
I220901 00:32:59.267268 1 workload/workloadsql/dataload.go:146  [-] 8  imported stock (1s, 100000 rows)
I220901 00:33:00.916392 1 workload/workloadsql/dataload.go:146  [-] 9  imported order_line (2s, 300343 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
$ cockroach workload run tpcc --duration=10m 'postgresql://root@host1:26257?sslmode=disable'
I220901 00:34:17.147411 1 workload/cli/run.go:414  [-] 1  creating load generator...
Initializing 2 connections...
Initializing 0 idle connections...
Initializing 10 workers and preparing statements...
I220901 00:34:17.151817 1 workload/cli/run.go:445  [-] 2  creating load generator... done (took 4.411152ms)
<omitted..>

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
  600.0s        0             12            0.0     32.6     31.5     44.0     44.0     44.0  delivery

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
  600.0s        0            120            0.2     17.9     17.8     26.2     41.9     54.5  newOrder

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
  600.0s        0             13            0.0      6.7      5.8     14.2     15.2     15.2  orderStatus

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
  600.0s        0            131            0.2     10.8     11.0     14.7     24.1     29.4  payment

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
  600.0s        0             13            0.0     10.8     10.5     15.7     15.7     15.7  stockLevel

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result
  600.0s        0            289            0.5     14.4     13.1     28.3     41.9     54.5
Audit check 9.2.1.7: SKIP: not enough delivery transactions to be statistically significant
Audit check 9.2.2.5.1: SKIP: not enough orders to be statistically significant
Audit check 9.2.2.5.2: SKIP: not enough orders to be statistically significant
Audit check 9.2.2.5.5: SKIP: not enough payments to be statistically significant
Audit check 9.2.2.5.6: SKIP: not enough order status transactions to be statistically significant
Audit check 9.2.2.5.3: PASS
Audit check 9.2.2.5.4: PASS

_elapsed_______tpmC____efc__avg(ms)__p50(ms)__p90(ms)__p95(ms)__p99(ms)_pMax(ms)
  600.0s       12.0  93.3%     17.9     17.8     23.1     26.2     41.9     54.5

YCSB workload

1
2
$ cockroach workload init ycsb 'postgresql://root@host1:26257?sslmode=disable'
I220831 23:57:53.909658 1 workload/workloadsql/dataload.go:146  [-] 1  imported usertable (2s, 10000 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
$ cockroach workload run ycsb --duration=10m 'postgresql://root@host1:26257?sslmode=disable'
I220831 23:58:24.410319 1 workload/cli/run.go:414  [-] 1  creating load generator...
I220831 23:58:24.427528 1 workload/cli/run.go:445  [-] 2  creating load generator... done (took 17.212701ms)
_elapsed___errors__ops/sec(inst)___ops/sec(cum)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)
    1.0s        0        11011.5        11243.7      1.1      2.4      3.8     11.0 read
    1.0s        0          581.7          593.9      3.0      5.5      7.3     12.6 update
    2.0s        0        12105.1        11674.4      1.1      1.8      3.4     10.0 read
    2.0s        0          636.9          615.4      2.8      3.9      6.6     11.0 update
    3.0s        0        11590.6        11646.5      1.1      2.0      4.5     14.2 read
    3.0s        0          607.1          612.7      2.8      6.0      9.4     12.1 update
    4.0s        0        11813.5        11688.2      1.1      1.9      3.5     13.1 read
    4.0s        0          622.0          615.0      2.8      4.5      7.6     13.6 update
    5.0s        0        11959.5        11742.5      1.1      2.0      3.3      8.1 read
    5.0s        0          607.0          613.4      2.6      4.7      7.1     12.1 update
    6.0s        0        12186.2        11816.5      1.0      1.8      3.5     12.6 read
    6.0s        0          638.0          617.5      2.6      4.1     10.0     16.8 update
    7.0s        0        11815.8        11816.3      1.1      2.0      3.7      8.4 read
    7.0s        0          646.0          621.6      2.8      4.7      6.8      9.4 update
    8.0s        0        11850.8        11820.6      1.1      2.0      3.3      7.6 read
    8.0s        0          617.9          621.1      2.8      4.5      6.6      9.4 update
    9.0s        0        11713.5        11808.7      1.1      2.0      3.9     11.0 read
    9.0s        0          609.1          619.8      2.8      4.5      7.6     11.5 update
   10.0s        0        11949.6        11822.8      1.1      2.0      3.3     13.1 read
   10.0s        0          622.0          620.0      2.8      4.2      6.0      8.4 update

<omitted..>

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
  600.0s        0        8576709        14294.5      1.0      1.0      1.6      2.9     62.9  read

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__total
  600.0s        0         451026          751.7      2.5      2.5      3.8      6.6     27.3  update

_elapsed___errors_____ops(total)___ops/sec(cum)__avg(ms)__p50(ms)__p95(ms)__p99(ms)_pMax(ms)__result
  600.0s        0        9027735        15046.2      1.1      1.0      2.2      3.5     62.9

Restart CockroachDB cluster

  1. Stop the process on each node
1
2
3
4
5
  $ ps -ef | grep cock | grep -v grep
  root     12217     1 99 Sep06 ?        2-20:28:09 cockroach start --log-dir=/var/log/cockroachdb_logs --store=/mnt/cockroachdb_mnt1 --insecure --advertise-addr=host1 --join=host1,host2,host3 --cache=.25 --max-sql-memory=.25

  $ kill -9 12217
  $ ps -ef | grep cock | grep -v grep
  1. Start the process on each node
1
  $ cockroach start --store=/mnt/cockroanchdb_mnt1 --insecure --advertise-addr=host1 --join=host1,host2,host3 --cache=.25 -max-sql-memory=.25 --background

CockroachDB commands

List node IDs:

1
2
3
4
5
6
7
$ cockroach node ls --insecure
  id
------
   1
   2
   3
(3 rows)

Show node status:

1
2
3
4
5
6
7
$ cockroach node status --insecure
  id |       address       |     sql_address     |  build  |         started_at         |         updated_at         | locality | is_available | is_live
-----+---------------------+---------------------+---------+----------------------------+----------------------------+----------+--------------+----------
   1 | host1:26257 | host1:26257 | v22.1.6 | 2022-09-03 16:24:12.001861 | 2022-09-03 22:23:09.022236 |          | true         | true
   2 | host2:26257 | host2:26257 | v22.1.6 | 2022-09-03 16:24:12.04249  | 2022-09-03 22:23:09.048582 |          | true         | true
   3 | host3:26257 | host3:26257 | v22.1.6 | 2022-09-03 16:24:12.390521 | 2022-09-03 22:23:09.402081 |          | true         | true
(3 rows)

Show status and range/replica details:

1
2
3
4
5
6
7
$ cockroach node status --ranges --insecure
  id |       address       |     sql_address     |  build  |         started_at         |         updated_at         | locality | is_available | is_live | replicas_leaders | replicas_leaseholders | ranges | ranges_unavailable | ranges_underreplicated
-----+---------------------+---------------------+---------+----------------------------+----------------------------+----------+--------------+---------
   1 | host1:26257 | host1:26257 | v22.1.6 | 2022-09-03 16:24:12.001861 | 2022-09-03 22:23:36.022463 |          | true         | true    |         306 |                   306 |    941 |                  0 |                      0
   2 | host2:26257 | host2:26257 | v22.1.6 | 2022-09-03 16:24:12.04249  | 2022-09-03 22:23:36.048508 |          | true         | true    |              310 |                   310 |    941 |                  0 |                      0
   3 | host3:26257 | host3:26257 | v22.1.6 | 2022-09-03 16:24:12.390521 | 2022-09-03 22:23:36.40421  |          | true         | true    |              325 |                   325 |    941 |                  0 |                      0
(3 rows)

Show status and disk usage details:

1
2
3
4
5
6
7
$ cockroach node status --stats --insecure
  id |       address       |     sql_address     |  build  |         started_at         |         updated_at         | locality | is_available | is_live |  live_bytes  |  key_bytes  | value_bytes  | intent_bytes | system_bytes
-----+---------------------+---------------------+---------+----------------------------+----------------------------+----------+--------------+---------+--------------+-------------+--------------+--------------+---------------
   1 | host1:26257 | host1:26257 | v22.1.6 | 2022-09-03 16:24:12.001861 | 2022-09-03 22:23:45.022008 |          | true         | true    | 246665681135 | 47930398573 | 220956161393 |        40331 |       812555
   2 | host2:26257 | host2:26257 | v22.1.6 | 2022-09-03 16:24:12.04249  | 2022-09-03 22:23:45.048777 |          | true         | true    | 246665683880 | 47930401894 | 220956192532 |        28025 |       817028
   3 | host3:26257 | host3:26257 | v22.1.6 | 2022-09-03 16:24:12.390521 | 2022-09-03 22:23:45.40156  |          | true         | true    | 246666008478 | 47930560065 | 220957658233 |        74382 |       741028
(3 rows)

Show status and decommissioning details for active and inactive nodes:

1
2
3
4
5
6
7
$ cockroach node status --decommission --insecure
  id |       address       |     sql_address     |  build  |         started_at         |         updated_at         | locality | is_available | is_live | gossiped_replicas | is_decommissioning | membership | is_draining
-----+---------------------+---------------------+---------+----------------------------+----------------------------+----------+--------------+---------
   1 | host1:26257 | host1:26257 | v22.1.6 | 2022-09-03 16:24:12.001861 | 2022-09-03 22:24:03.043432 |          | true         | true    |               941 | false              | active     | false
   2 | host2:26257 | host2:26257 | v22.1.6 | 2022-09-03 16:24:12.04249  | 2022-09-03 22:24:03.048948 |          | true         | true    |               941 | false              | active     | false
   3 | host3:26257 | host3:26257 | v22.1.6 | 2022-09-03 16:24:12.390521 | 2022-09-03 22:24:03.401953 |          | true         | true    |               941 | false              | active     | false
(3 rows)

Show complete status details for active and inactive nodes:

1
2
3
4
5
6
7
$ cockroach node status --all --insecure
  id |       address       |     sql_address     |  build  |         started_at         |         updated_at         | locality | is_available | is_live | replicas_leaders | replicas_leaseholders | ranges | ranges_unavailable | ranges_underreplicated |  live_bytes  |  key_bytes  | value_bytes  | intent_bytes | system_bytes | gossiped_replicas | is_decommissioning | membership | is_draining
-----+---------------------+---------------------+---------+----------------------------+----------------------------+----------+--------------+---------
   1 | host1:26257 | host1:26257 | v22.1.6 | 2022-09-03 16:24:12.001861 | 2022-09-03 22:24:07.539077 |          |         true |    true |              306 |                   306 |    942 |                  0 |                      0 | 246677946612 | 47939212836 | 221027696131 |        81398 |       722359 |        941        |              false |   active   |    false
   2 | host2:26257 | host2:26257 | v22.1.6 | 2022-09-03 16:24:12.04249  | 2022-09-03 22:24:07.548886 |          |         true |    true |              311 |                   311 |    942 |                  0 |                      0 | 246677952989 | 47939228198 | 221027812276 |        67816 |       731258 |        941        |              false |   active   |    false
   3 | host3:26257 | host3:26257 | v22.1.6 | 2022-09-03 16:24:12.390521 | 2022-09-03 22:24:07.901765 |          |         true |    true |              325 |                   325 |    942 |                  0 |                      0 | 246678252011 | 47939394591 | 221029217819 |        55249 |       869542 |        941        |              false |   active   |    false
(3 rows)

Show status details for a specific node:

1
2
3
4
5
$ cockroach node status 1 --insecure
  id |       address       |     sql_address     |  build  |         started_at         |         updated_at         | locality | is_available | is_live
-----+---------------------+---------------------+---------+----------------------------+----------------------------+----------+--------------+----------
   1 | host1:26257 | host1:26257 | v22.1.6 | 2022-09-03 16:24:12.001861 | 2022-09-03 22:24:30.044382 |          | true         | true
(1 row)

Reference

This post is licensed under CC BY 4.0 by the author.

Deploy CockroachDB in kubernetes cluster

CockroachDB TPC-C performance benchmarking

Comments powered by Disqus.