Cockroach Xiaoqiang DB installation and TPCC test

Overview

Xiaoqiang DB is a distributed database, similar to TiDB, based on the postgresql protocol, and the bottom layer is LevelDB.
This article documents its cluster installation, use and testing process.

Install

Installation is simple, one package: https://www.cockroachlabs.com/docs/v21.1/install-cockroachdb-linux#download-the-binary

curl https://binaries.cockroachdb.com/cockroach-v21.1.11.linux-amd64.tgz | tar -xz && sudo cp -i cockroach-v21.1.11.linux-amd64/cockroach /usr/local/bin/
  • 1

run the cluster

Basically just refer to the documentation: https://www.cockroachlabs.com/docs/v21.1/secure-a-cluster

Send packages to several machines

scp cockroach [email protected]:/usr/local/bin/
scp cockroach [email protected]:/usr/local/bin/
scp cockroach [email protected]:/usr/local/bin/
  • 1
  • 2
  • 3

Create a directory on each machine:

mkdir -p /extra/server/cockroach
  • 1

Create an authentication file on any machine and distribute it to all nodes:

mkdir certs my-safe-directory
cockroach cert create-ca --certs-dir=certs --ca-key=my-safe-directory/ca.key
zip -r certs.zip certs/ my-safe-directory/

scp certs.zip [email protected]:/extra/server/cockroach/
scp certs.zip [email protected]:/extra/server/cockroach/
scp certs.zip [email protected]:/extra/server/cockroach/
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

Do the same on each machine: (you can use the pssh tool)

where hostname needs to be changed:

1st

cd /extra/server/cockroach
cockroach cert create-node node01 --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach cert create-client root --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach start --certs-dir=certs --store=cocknode --listen-addr=node01:26257 --http-addr=node01:8080 --join=node01:26257,node02:26257,node03:26257 --background
  • 1
  • 2
  • 3
  • 4

2nd unit

cd /extra/server/cockroach
cockroach cert create-node node02 --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach cert create-client root --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach start --certs-dir=certs --store=cocknode --listen-addr=node02:26257 --http-addr=node02:8080 --join=node01:26257,node02:26257,node03:26257 --background
  • 1
  • 2
  • 3
  • 4

3rd

cd /extra/server/cockroach
cockroach cert create-node node03 --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach cert create-client root --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach start --certs-dir=certs --store=cocknode --listen-addr=node03:26257 --http-addr=node03:8080 --join=node01:26257,node02:26257,node03:26257 --background
  • 1
  • 2
  • 3
  • 4

Last very important step: initialize the cluster, run on node01:

# cockroach init --certs-dir=certs --host=node01:26257
Cluster successfully initialized
  • 1
  • 2

View node status

# cockroach node status --certs-dir certs --host node01:26257
  id |   address    | sql_address  |  build   |         started_at         |         updated_at         | locality | is_available | is_live
-----+--------------+--------------+------------+--- -------------------------+-------------------------------------- ----+------------+---------------+----------
   1 | node01:26257 | node01:26257 | v21.1.11 | 2021-11-20 03:20:45.74466  | 2021-11-20 04:18:21.751861 |          | true         | true
   2 | node02:26257 | node02:26257 | v21.1.11 | 2021-11-20 04:16:19.100838 | 2021-11-20 04:18:25.108128 |          | true         | true
   3 | node03:26257 | node03:26257 | v21.1.11 | 2021-11-20 04:16:43.813861 | 2021-11-20 04:18:22.822455 |          | true         | true
(3 rows)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

use

command line SQL

[[email protected] cockroach]# cockroach sql --certs-dir=certs --host=node01:26257
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v21.1.11 (x86_64-unknown-linux-gnu, built 2021/10/18 14:39:35, go1.15.14) (same version as client)
# Cluster ID: acf38b1c-e958-4cbf-a0ef-fedeb736ca14
#
# Enter \? for a brief introduction.
#
[email protected]:26257/defaultdb> create database bank;
CREATE DATABASE

Time: 18ms total (execution 18ms / network 0ms)

[email protected]:26257/defaultdb> CREATE TABLE bank.accounts (id INT PRIMARY KEY, balance DECIMAL);
CREATE TABLE

Time: 23ms total (execution 23ms / network 0ms)

[email protected]:26257/defaultdb> INSERT INTO bank.accounts VALUES (1, 1000.50);
INSERT 1

Time: 23ms total (execution 23ms / network 0ms)

[email protected]:26257/defaultdb> SELECT * FROM bank.accounts;
  id | balance
-----+----------
   1 | 1000.50
(1 row)

Time: 2ms total (execution 2ms / network 0ms)

[email protected]:26257/defaultdb> \q



[[email protected] cockroach]# cockroach sql --certs-dir=certs --host=node02:26257
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v21.1.11 (x86_64-unknown-linux-gnu, built 2021/10/18 14:39:35, go1.15.14) (same version as client)
# Cluster ID: acf38b1c-e958-4cbf-a0ef-fedeb736ca14
#
# Enter \? for a brief introduction.
#
[email protected]:26257/defaultdb> select * from bank.accounts;
  id | balance
-----+----------
   1 | 1000.50
(1 row)

Time: 18ms total (execution 18ms / network 0ms)
  • 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
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56

interface operation

You can access the interface to operate: http://node01:8080/#/login?redirectTo=%2F

We need to create a user:

# cockroach sql --certs-dir certs --host=node01:26257
root@node01:26257/defaultdb> create user jimo with password 'xxxxxxxx';
CREATE ROLE

# Grant admin privileges to user 
root @node01 : 26257 / defaultdb >  grant admin to jimo ; 
GRANT

root@node01:26257/defaultdb> show users;
  username | options | member_of
-----------+---------+------------
  admin    |         | {}
  root     |         | {admin}
  jimo      |          | {admin}
 ( 3  rows )
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

stop node

The way to stop a node normally is

cockroach quit --certs-dir=certs --host=node01:26257
  • 1

But if there are only 3 nodes, then this method cannot be stopped, because stopping one will not constitute a cluster. 5 nodes can stop up to 2. And so on, up to n nodes stopn/2Take the entire node down.
If you can't stop it, you can manually kill the node.

restart node after stop

Node creation and client operations are no longer required, just start the node:

cd /extra/server/cockroach && \
cockroach start --certs-dir=certs --store=cocknode --listen-addr=node01:26257 --http-addr=node01:8080 --join=node01:26257,node02:26257,node03:26257 --background
  • 1
  • 2

Run the TPCC test

Cockroach db comes with TPCC test, see documentation: https://www.cockroachlabs.com/docs/v21.1/performance-benchmarking-with-tpcc-small

Import Data

[[email protected] cockroach]# cockroach workload fixtures import tpcc --warehouses=2  'postgres://jimo:[email protected]:26257'

I211114 07:44:15.546954 1 ccl/workloadccl/fixture.go:342  [-] 1  starting import of 9 tables
I211114 07:44:15.675401 71 ccl/workloadccl/fixture.go:472  [-] 2  imported 105 B in warehouse table (2 rows, 0 index entries, took 117.000094ms, 0.00 MiB/s)
I211114 07:44:15.794153 72 ccl/workloadccl/fixture.go:472  [-] 3  imported 2.0 KiB in district table (20 rows, 0 index entries, took 235.750266ms, 0.01 MiB/s)
I211114 07:44:16.940730 76 ccl/workloadccl/fixture.go:472  [-] 4  imported 228 KiB in new_order table (18000 rows, 0 index entries, took 1.382229645s, 0.16 MiB/s)
I211114 07:44:18.514542 79 ccl/workloadccl/fixture.go:472  [-] 5  imported 33 MiB in order_line table (600531 rows, 0 index entries, took 2.956025071s, 11.16 MiB/s)
I211114 07:44:19.401554 78 ccl/workloadccl/fixture.go:472  [-] 6  imported 61 MiB in stock table (200000 rows, 0 index entries, took 3.842945189s, 15.92 MiB/s)
I211114 07:44:20.239329 73 ccl/workloadccl/fixture.go:472  [-] 7  imported 35 MiB in customer table (60000 rows, 60000 index entries, took 4.680871223s, 7.50 MiB/s)
I211114 07:44:21.033754 77 ccl/workloadccl/fixture.go:472  [-] 8  imported 7.8 MiB in item table (100000 rows, 0 index entries, took 5.475260713s, 1.42 MiB/s)
I211114 07:44:22.140146 75 ccl/workloadccl/fixture.go:472  [-] 9  imported 3.0 MiB in order table (60000 rows, 60000 index entries, took 6.581649596s, 0.46 MiB/s)
I211114 07:44:23.048653 74 ccl/workloadccl/fixture.go:472  [-] 10  imported 4.3 MiB in history table (60000 rows, 0 index entries, took 7.490192208s, 0.58 MiB/s)
I211114 07:44:23.084137 1 ccl/workloadccl/fixture.go:351  [-] 11  imported 145 MiB bytes in 9 tables (took 7.537020751s, 19.19 MiB/s)
I211114 07:44:24.306160 1 ccl/workloadccl/cliccl/fixtures.go:355  [-] 12  fixture is restored; now running consistency checks (ctrl-c to abort)
I211114 07:44:24.323545 1 workload/tpcc/tpcc.go:485  [-] 13  check 3.3.2.1 took 17.332934ms
I211114 07:44:24.454235 1 workload/tpcc/tpcc.go:485  [-] 14  check 3.3.2.2 took 130.643508ms
I211114 07:44:24.463841 1 workload/tpcc/tpcc.go:485  [-] 15  check 3.3.2.3 took 9.505905ms
I211114 07:44:24.818812 1 workload/tpcc/tpcc.go:485  [-] 16  check 3.3.2.4 took 354.934165ms
I211114 07:44:24.900468 1 workload/tpcc/tpcc.go:485  [-] 17  check 3.3.2.5 took 81.607492ms
I211114 07:44:25.265701 1 workload/tpcc/tpcc.go:485  [-] 18  check 3.3.2.7 took 365.184399ms
I211114 07:44:25.335478 1 workload/tpcc/tpcc.go:485  [-] 19  check 3.3.2.8 took 69.729165ms
I211114 07:44:25.394851 1 workload/tpcc/tpcc.go:485  [-] 20  check 3.3.2.9 took 59.328942ms
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

Run stress test

This parameter of efc is generally considered to be effective if it reaches more than 95%. It is necessary to increase the amount of data and increase the test time (more than 5 minutes).

cockroach workload run tpcc --warehouses=2 --ramp=20s --duration=1m --conns=1 \
postgres://jimo:xxx[email protected]:26257 \
postgres://jimo:[email protected]:26257 \
postgres://jimo:[email protected]:26257

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.3: SKIP: not enough orders to be statistically significant
Audit check 9.2.2.5.4: SKIP: not enough payments 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

_elapsed_______tpmC____efc__avg(ms)__p50(ms)__p90(ms)__p95(ms)__p99(ms)_pMax(ms)
   60.0s       21.0  81.6%     20.1     19.9     22.0     24.1     26.2     26.2
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

Related: Cockroach Xiaoqiang DB installation and TPCC test