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、压测