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
