cockroach小强DB安装与TPCC测试

概述

小强DB是一款分布式数据库，类似TiDB，基于postgresql协议，底层是LevelDB。
本文记录其集群安装、使用和测试过程。

安装

安装很简单，一个包：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/

运行集群

基本上参考文档就行：https://www.cockroachlabs.com/docs/v21.1/secure-a-cluster

将包发送到几台机器

scp cockroach root@node01:/usr/local/bin/
scp cockroach root@node02:/usr/local/bin/
scp cockroach root@node03:/usr/local/bin/

在每台机器都创建目录：

mkdir -p /extra/server/cockroach

随便找台机器创建认证文件，并分发到所有节点：

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 root@node01:/extra/server/cockroach/
scp certs.zip root@node02:/extra/server/cockroach/
scp certs.zip root@node03:/extra/server/cockroach/

每台机器上执行相同的操作：(可以使用pssh工具)

其中hostname需要改变：

第1台

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

第2台

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

第3台

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

最后非常重要的一步：初始化集群，在node01上运行：

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

查看节点状态

# 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)

使用

命令行SQL

[root@centos71 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.
#
root@node01:26257/defaultdb> create database bank;
CREATE DATABASE

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

root@node01:26257/defaultdb> CREATE TABLE bank.accounts (id INT PRIMARY KEY, balance DECIMAL);
CREATE TABLE

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

root@node01:26257/defaultdb> INSERT INTO bank.accounts VALUES (1, 1000.50);
INSERT 1

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

root@node01:26257/defaultdb> SELECT * FROM bank.accounts;
  id | balance
-----+----------
   1 | 1000.50
(1 row)

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

root@node01:26257/defaultdb> \q



[root@centos71 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.
#
root@node02:26257/defaultdb> select * from bank.accounts;
  id | balance
-----+----------
   1 | 1000.50
(1 row)

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

界面操作

可以访问界面去操作：http://node01:8080/#/login?redirectTo=%2F

我们需要创建一个用户：

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

# 给用户授予管理员权限
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)

停止节点

正常停止节点的方式为

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

但是假如只有3个节点，那么这种方式无法停止，因为停掉一个就构不成集群了。5个节点可以最多停止2台。以此类推，n个节点最多停止 n/2向下取整个节点。
如果停不掉，可以手动kill节点。

停止后再起节点

不再需要创建节点和客户端操作，只需要启动节点：

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

运行TPCC测试

cockroach db自带TPCC测试，见文档：https://www.cockroachlabs.com/docs/v21.1/performance-benchmarking-with-tpcc-small

导入数据

[root@centos71 cockroach]# cockroach workload fixtures import tpcc --warehouses=2  'postgres://jimo:xxxxxxxx@node01: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

运行压测

efc这个参数一般要达到95%以上才算有效，需要增加数据量，增加测试时长（5分钟以上）。

cockroach workload run tpcc --warehouses=2 --ramp=20s --duration=1m --conns=1 \
postgres://jimo:xxxxxxxx@node01:26257 \
postgres://jimo:xxxxxxxx@node02:26257 \
postgres://jimo:xxxxxxxx@node03: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