KingbaseES KBBENCH 压测

kbbench 是一种在KingbaseES 上运行基准测试的简单程序。它可能在并发的数据库会话中一遍一遍地运行相同序列的SQL 命令，并且计算平均事务率（每秒的事务数）。默认情况下，kbbench 会测试一种基于TPC-B 但是要更宽松的场景，其中在每个事务中涉及五个SELECT、UPDATE 以及INSERT 命令。

1、创建测试库

create database kbbenchdb;

2、初始化测试库装载数据 -s：比例因子，在kbbench_accounts中插入20000*s行数据

kbbench -h 192.168.50.58 -p 54321  kbbenchdb -d test -U system -s 200 -i

结果：

dropping old tables...
creating tables...
generating data...
100000 of 20000000 tuples (0%) done (elapsed 0.33 s, remaining 65.73 s)
200000 of 20000000 tuples (1%) done (elapsed 1.11 s, remaining 109.75 s)
300000 of 20000000 tuples (1%) done (elapsed 3.32 s, remaining 218.32 s)
400000 of 20000000 tuples (2%) done (elapsed 5.23 s, remaining 256.47 s)
500000 of 20000000 tuples (2%) done (elapsed 7.20 s, remaining 280.89 s)
600000 of 20000000 tuples (3%) done (elapsed 8.97 s, remaining 289.92 s)
19500000 of 20000000 tuples (97%) done (elapsed 453.60 s, remaining 11.63 s)
19600000 of 20000000 tuples (98%) done (elapsed 455.53 s, remaining 9.30 s)
19700000 of 20000000 tuples (98%) done (elapsed 457.71 s, remaining 6.97 s)
19800000 of 20000000 tuples (99%) done (elapsed 459.85 s, remaining 4.64 s)
19900000 of 20000000 tuples (99%) done (elapsed 461.67 s, remaining 2.32 s)
20000000 of 20000000 tuples (100%) done (elapsed 464.92 s, remaining 0.00 s)
vacuuming...
creating primary keys..

3、3个并发3 线程为例，进行测试

kbbench -h 192.168.50.58 -M extended -c 3 -j 3 -T 300 -d kbbenchdb -U system -r

结果：

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 200
query mode: extended
number of clients: 3
number of threads: 3
duration: 300 s
number of transactions actually processed: 3879
latency average = 232.149 ms
tps = 12.922714 (including connections establishing)
tps = 12.936579 (excluding connections establishing)
statement latencies in milliseconds:
         0.016  \set aid random(1, 100000 * :scale)
         0.083  \set bid random(1, 1 * :scale)
         0.009  \set tid random(1, 10 * :scale)
         0.009  \set delta random(-5000, 5000)
        28.446  BEGIN;
        22.607  UPDATE kbbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        22.362  SELECT abalance FROM kbbench_accounts WHERE aid = :aid;
        23.340  UPDATE kbbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        47.907  UPDATE kbbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        22.126  INSERT INTO kbbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        81.533  END;

前六行报告一些最重要的参数设置，最后两行报告每秒的事务数，分别TPS代表包括和不包括开始数据库会话所花时间的情况。

4、清理数据

kbbench -h 192.168.50.58 -i --init-steps=d  kbbenchdb -p 54321  -U system

结果：

dropping old tables...
done.

参数说明

参数 说明 M 提交到服务器查询协议： -simple：简单协议（默认） -extend：扩展协议 -prepared：带预备语句扩展查询语句 n 在运行测试前不进行清理，如果测试前不包括标准表kbbench_accounts、kbbench_branches、kbbench_history、kbbench_tellers，则该参数为必选 r 报告平均每个命令的每个语句等待时间 f 从文件中读取脚本 P（大写）间隔时间报告运行进度 c 客户端数量 j 线程数，尽量与cpu核数保持一致 T 测试时长 p（小写） 数据库端口 U 数据库用户 test 数据库名称

自定义压测脚本：

kbbench -M prepared -n -r -f test.sql -P 10 -c 4 -j 4 -T 60 -p 54321 -U system kbbenchdb

TPC-B 事务 test.sql

\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE kbbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM kbbench_accounts WHERE aid = :aid;
UPDATE kbbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE kbbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO kbbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid,
:aid, :delta, CURRENT_TIMESTAMP);
END;

业务场景复现

对bufferconten的LWlock锁业务场景进行复现，buffer_content等待事件主要是由热块竞争引起的等待，优化主要考虑buffer调整、减少不必要的索引等。 1）数据准备

--测试数据
create table t1(id int,no int,info text,crt_time timestamp);
create index bt_idx_1 on t1 using btree(id);
create index bt_idx_2 on t1 using btree(no);
create index hash_idx_1 on t1 using hash(info);
insert into t1 values (generate_series(1,1000),generate_series(1,1000),md5(random()::text),now());
​
--压测脚本
insert.sql
​
begin;
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
insert into t1 values (floor(999 + random()*(2000 - 999 + 1)),floor(999 + random()*(2000 - 999 + 1)),md5(random()::text),now());
end;

2、压测