【数据库】性能测试--压测工具kbbench

---

前言

介绍使用KES数据库自带压测工具kbbench，对数据进行并发压测。kbbench工具是重复运行相同SQL命令，可以自定义DML语句来对数据库进行简单的压测。

---

一、基础压测

1.准备数据

--i:初始化模式；-s：比例因子，在kbbench_accounts中插入20000*s行数据
./Server/bin/kbbench -U system -p54321 -i test -s 2
[test@localhost KES_install]$ ./Server/bin/kbbench -U system -p54321 -i test -s 2
dropping old tables...
注意:  表 "kbbench_accounts" 不存在
注意:  表 "kbbench_branches" 不存在
注意:  表 "kbbench_history" 不存在
注意:  表 "kbbench_tellers" 不存在
creating tables...
generating data...
100000 of 200000 tuples (50%) done (elapsed 0.14 s, remaining 0.14 s)
200000 of 200000 tuples (100%) done (elapsed 0.51 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

2.检查数据

test=# \d
                 List of relations
 Schema |          Name           | Type  | Owner
--------+-------------------------+-------+--------
 public | kbbench_accounts        | table | system
 public | kbbench_branches        | table | system
 public | kbbench_history         | table | system
 public | kbbench_tellers         | table | system
 public | sys_stat_statements     | view  | system
 public | sys_stat_statements_all | view  | system
(6 rows)

test=# select count(*) from kbbench_accounts ;
 count
--------
 200000
(1 row)
test=#

3.压测

[test@localhost KES_install]$ ./Server/bin/kbbench -U system -p54321 -r -j 2 -c 4 -T 30 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 2
query mode: simple
number of clients: 4
number of threads: 2
duration: 30 s
number of transactions actually processed: 57482
latency average = 2.088 ms
tps = 1915.903319 (including connections establishing)
tps = 1916.431845 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random(1, 100000 * :scale)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         0.066  BEGIN;
         0.263  UPDATE kbbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.194  SELECT abalance FROM kbbench_accounts WHERE aid = :aid;
         0.299  UPDATE kbbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.601  UPDATE kbbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.170  INSERT INTO kbbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.486  END;

参数说明：
-r(report latencies)：测试结束后，报告平均每个命令的每个语句等待时间
-j(jobs=threads)：线程数量
-c(clients)：客户端数量
-T(time=seconds)：运行时长
latency average：时延
tps = 1915.903319 (including connections establishing)：tps数

二、自定义压测

2.1 准备数据

create table tab1(id int,info text,crt_time timestamp);
create table tab2(id int,info text,crt_time timestamp);
create table tab3(id int,info text,crt_time timestamp);
create table tab4(id int,info text,crt_time timestamp);
create table tab5(id int,info text,crt_time timestamp);
create index tab1_idx on tab1 (id);
create index tab2_idx on tab1 (id);
create index tab3_idx on tab1 (id);
create index tab4_idx on tab1 (id);
create index tab5_idx on tab1 (id);
insert into tab1 values (generate_series(1,1000),md5(random()::text),now());
insert into tab2 values (generate_series(1,1000),md5(random()::text),now());
insert into tab3 values (generate_series(1,1000),md5(random()::text),now());
insert into tab4 values (generate_series(1,1000),md5(random()::text),now());
insert into tab5 values (generate_series(1,1000),md5(random()::text),now());

2.2 准备压测sql

\set id random(1,1000)
delete from tab1 where id=:id;
delete from tab2 where id=:id;
delete from tab3 where id=:id;
delete from tab4 where id=:id;
delete from tab5 where id=:id;
insert into tab1 values ((random()*(10^3))::int,md5(random()::text),now());
insert into tab2 values ((random()*(10^3))::int,md5(random()::text),now());
insert into tab3 values ((random()*(10^3))::int,md5(random()::text),now());
insert into tab4 values ((random()*(10^3))::int,md5(random()::text),now());
insert into tab5 values ((random()*(10^3))::int,md5(random()::text),now());
update tab1 set crt_time=now() where id =:id;
update tab2 set crt_time=now() where id =:id;
update tab3 set crt_time=now() where id =:id;
update tab4 set crt_time=now() where id =:id;
update tab5 set crt_time=now() where id =:id;
select * from tab1 where id=:id;
select * from tab2 where id=:id;
select * from tab3 where id=:id;
select * from tab4 where id=:id;
select * from tab5 where id=:id;

2.3 执行压测

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

说明：
在执行性能压测时可以用nmon监控系统资源，如果只是为了单独观察某方面资源表现。在debug时可以执行系统监控命令，将监控文件重定向到文本，以便调整业务模型，如：
nohup iostat -txdk sda 3 120 >> io_result.txt &

参数说明

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

2.4 查看结果

tps为326，平均时延为12.254 ms

[test@localhost KES_install]$ ./Server/bin/kbbench -M prepared -n -r -f test.sql -P 10 -c 4 -j 4 -T 60 -p 54321 -U system test
progress: 10.0 s, 409.5 tps, lat 9.752 ms stddev 5.002
progress: 20.0 s, 291.6 tps, lat 13.713 ms stddev 5.261
progress: 30.0 s, 301.5 tps, lat 13.266 ms stddev 4.506
progress: 40.0 s, 347.5 tps, lat 11.509 ms stddev 3.595
progress: 50.0 s, 327.0 tps, lat 12.217 ms stddev 3.086
progress: 60.0 s, 280.8 tps, lat 14.245 ms stddev 9.529
transaction type: test.sql
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 4
duration: 60 s
number of transactions actually processed: 19584
latency average = 12.254 ms
latency stddev = 5.647 ms
tps = 326.327810 (including connections establishing)
tps = 326.374293 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set id random(1,1000)
         0.440  delete from tab1 where id=:id;
         0.877  delete from tab2 where id=:id;
         0.873  delete from tab3 where id=:id;
         0.870  delete from tab4 where id=:id;
         0.875  delete from tab5 where id=:id;
         0.707  insert into tab1 values ((random()*(10^3))::int,md5(random()::text),now());
         0.614  insert into tab2 values ((random()*(10^3))::int,md5(random()::text),now());
         0.603  insert into tab3 values ((random()*(10^3))::int,md5(random()::text),now());
         0.600  insert into tab4 values ((random()*(10^3))::int,md5(random()::text),now());
         0.594  insert into tab5 values ((random()*(10^3))::int,md5(random()::text),now());
         0.195  update tab1 set crt_time=now() where id =:id;
         0.601  update tab2 set crt_time=now() where id =:id;
         0.611  update tab3 set crt_time=now() where id =:id;
         0.611  update tab4 set crt_time=now() where id =:id;
         0.617  update tab5 set crt_time=now() where id =:id;
         0.154  select * from tab1 where id=:id;
         0.596  select * from tab2 where id=:id;
         0.600  select * from tab3 where id=:id;
         0.601  select * from tab4 where id=:id;
         0.607  select * from tab5 where id=:id;

2.5 业务场景复现

对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）过程监控

--等待事件
select wait_event_type,wait_event,state,backend_type,substr(query,0,30) query,count(*) from sys_stat_activity group by wait_event_type,wait_event,backend_type,state,query order by count desc limit 5;
 wait_event_type |    wait_event    |        state        |   backend_type    |             query              | count
-----------------+------------------+---------------------+-------------------+--------------------------------+-------
                 |                  | active              | client backend    | insert into t1 values (floor(9 |     2
 Activity        | BgWriterMain     |                     | background writer |                                |     1
                 |                  | active              | client backend    | select wait_event_type,wait_ev |     1
 Activity        | CheckpointerMain |                     | checkpointer      |                                |     1
                 |                  | idle in transaction | client backend    | begin;                         |     1
(5 rows)

test=# \watch 2
                                     Fri 20 Oct 2023 10:58:59 PM PDT (every 2s)

 wait_event_type |    wait_event    |        state        |  backend_type  |             query              | count
-----------------+------------------+---------------------+----------------+--------------------------------+-------
                 |                  | active              | client backend | insert into t1 values (floor(9 |     2
 Lock            | extend           | active              | client backend | insert into t1 values (floor(9 |     2
 Activity        | CheckpointerMain |                     | checkpointer   |                                |     1
 Client          | ClientRead       | idle in transaction | client backend | insert into t1 values (floor(9 |     1
 Activity        | KshMain          | idle                | ksh collector  |                                |     1
(5 rows)


--表及索引大小
select relname,pg_size_pretty(pg_relation_size(oid)) from sys_class where relname like 't1%_idx' or relname like 't1%' order by relname;
test=# select relname,pg_size_pretty(pg_relation_size(oid)) from sys_class where relname like 'bt_idx_%' or relname like 'hash_idx_%' or relname like 't1%' order by relname;
  relname   | pg_size_pretty
------------+----------------
 bt_idx_1   | 9376 kB
 bt_idx_2   | 9304 kB
 hash_idx_1 | 12 MB
 t1         | 32 MB
(4 rows)


--火焰图
perf record -F 99 -a -g --sleep 300
/*
perf script > out.perf #生成火焰图
FlameGraph/stackcollapse-perf.pl out.perf > out.folded
FlameGraph/flamegraph.pl out.folded > bufferconten.svg
*/
--kwr报告
/*GUC参数*/
track_activities = on
track_counts = on
track_sql = on
track_io_timing = on
track_wait_timing = on
track_functions = 'all'
track_instance = on
sys_stat_statements = 'top'
/*产生报告*/
select perf.create_snapshot(); --snapshot1
运行业务....
select perf.create_snapshot(); --snapshot1
select perf.kwr_report(1,2); --收集报告

3. 进行压测

./Server/bin/kbbench -M prepared -n -r -f insert.sql -P 5 -c 200 -j 200 -T 300 -P 54321 -U system test