kingbase数据库性能测试——tpcc

tpcc测试步骤，请参考以下步骤操作
1.操作系统设置
groupadd kingbase
useradd -m -d /home/kingbase -g kingbase kingbase

/home/kingbase/KingbaseES/SupTools/kb_modify_syscfg/optimize_system_conf.sh

2.磁盘IO调度策略，设置为deadline
lsblk
echo deadline > /sys/block/sda/queue/scheduler
cat /sys/block/sda/queue/scheduler
none
3.如果是裸金属上测，内存超过256GB，可以开启内存大页，具体步骤请参考pg官方文档，搜索huge_page，可以看到开启步骤。
4.数据库参数
单机参数:
shared_preload_libraries='liboracle_parser'
commit_delay = 10
commit_siblings = 16        
shared_buffers = 120GB #根据warehouse数及现场内存进行调整，调整原则尽量将数据都放入内存中   
listen_addresses = '*'
port = 54321
huge_pages = off                        
maintenance_work_mem = 2GB              
dynamic_shared_memory_type = posix      
bgwriter_delay = 20ms                   
bgwriter_lru_maxpages = 1000            
bgwriter_lru_multiplier = 10.0          
effective_io_concurrency = 2        
wal_level = minimal                    
synchronous_commit = off              
wal_sync_method = open_sync             
full_page_writes = off 
fsync = off     
wal_buffers = 1GB                       
wal_writer_delay = 20ms                 
checkpoint_timeout = 1d                
max_wal_size = 100GB                   
checkpoint_completion_target = 0.8     
effective_cache_size = 16GB
update_process_title = off
track_activities = off
autovacuum = off                        
log_statement = 'none'                
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.UTF-8'             
lc_monetary = 'en_US.UTF-8'             
lc_numeric = 'en_US.UTF-8'             
lc_time = 'en_US.UTF-8'                
max_connections = 600                 
page_prune_frequency=8
xloginsert_locks = 16
enable_auto_cursor=on
index_cursor_id=0 #查看具体的data的index_cursor_id
max_wal_senders = 0
bindcpulist='0-95'  #与lscpu保持一致
connect_pool_size=96
connect_delay_time=8

5.灌数
export PATH=$PATH:/home/kingbase/KingbaseES/Server/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/kingbase/benchmarksql-5.0/lib

source .bahsrc

probs.kingbase中连接串设置
conn=jdbc:kingbase8:///test?socketFactory=KBAFUNIXSocketFactory&socketFactoryArg=/tmp/.s.KINGBASE.54321
表设置为unlogged:
修改benmarksql-5.0/run/sql.common/tableCreate.sql
create table 修改为create unlogged table，序列create语句不需要修改。

灌数前每张表设置fillfactor=80，设置fillfactor的方法:
修改benmarksql-5.0/run/sql.common/tableCreate.sql文件，每个建表语句后加with (fillfactor=80)，注意，里边建序列的语句，建序列的语句不需要修改，示例:
create unlogged table bmsql_stock(
...
) with (fillfactor=80);

:%s/);/) with (fillfactor=80);/g

如果使用裸金属，操作系统有多块盘，并且IO性能一致，将bmsql_stock,bmsql_order_line两张表分散到其它的磁盘上
测io性能的方法:
cd /data_sda #data_sda是sda盘的挂载点，每块盘的挂载点lsblk可以看到
dd bs=8k count=20000 if=/dev/zero of=io.txt oflag=dsync
分盘的方法:
mkdir /tbs_dir  #/tbs_dir指向另外一块儿盘的挂载点
create tablespace tab_tbs location '/tbs_dir';  --创建表空指向这个盘
修改benmarksql-5.0/run/sql.common/tableCreate.sql文件，示例：
create unlogged table bmsql_stock(
...
) with (fillfactor=80) tablespace tb1; --指定表的数据存入自定义的表空间

cp /home/kingbase/KingbaseES/Interface/jdbc/kingbase8-8.6.0.jar /home/kingbase/benchmarksql-5.0/lib/

灌数步骤:
cd benchmarksql-5.0/run/

6.删除外键
如果外键被创建了的话:
alter table bmsql_district drop constraint d_warehouse_fkey;
alter table bmsql_customer drop constraint c_district_fkey;
alter table bmsql_history drop constraint h_customer_fkey;
alter table bmsql_history drop constraint h_district_fkey;
alter table bmsql_new_order drop constraint no_order_fkey;
alter table bmsql_oorder drop constraint o_customer_fkey;
alter table bmsql_order_line drop constraint ol_order_fkey;
alter table bmsql_order_line drop constraint ol_stock_fkey;
alter table bmsql_stock drop constraint s_warehouse_fkey;                
alter table bmsql_stock drop constraint s_item_fkey;
7.删除一个主键约束
alter table bmsql_HISTORY drop constraint bmsql_HISTORY_PKEY;
8.创建一个索引
create index NDX_OORDER_2 on bmsql_oorder (o_w_id,o_d_id,o_c_id,o_id);

9.验证index_cursor_id是否正确
select oid from sys_class where relname ilike 'bmsql_new_order_pkey';
show index_cursor_id;

10.备份
先做checkpoint,等一会儿停库
cp -r data data.backup
如果有多个表空，表空也需要备份

11.预热
create extension sys_prewarm;
vacuum verbose;
select sys_extend('bmsql_customer',20000);
select sys_extend('bmsql_district',140);
select sys_extend('bmsql_history',13000);
select sys_extend('bmsql_new_order',6500);
select sys_extend('bmsql_oorder',10000);
select sys_extend('bmsql_order_line',200000);
select sys_extend('bmsql_stock',40000);
--table
select sys_prewarm('bmsql_config','bufferkeep');
select sys_prewarm('bmsql_warehouse','bufferkeep');
select sys_prewarm('bmsql_district','bufferkeep');
select sys_prewarm('bmsql_customer','bufferkeep');
select sys_prewarm('bmsql_history','bufferkeep');
select sys_prewarm('bmsql_new_order','bufferkeep');
select sys_prewarm('bmsql_oorder','bufferkeep');
select sys_prewarm('bmsql_order_line','bufferkeep');
select sys_prewarm('bmsql_item','bufferkeep');
select sys_prewarm('bmsql_stock','bufferkeep');
--index
select sys_prewarm('bmsql_config_pkey','bufferkeep');
select sys_prewarm('bmsql_warehouse_pkey','bufferkeep');
select sys_prewarm('bmsql_district_pkey','bufferkeep');
select sys_prewarm('bmsql_customer_pkey','bufferkeep');
select sys_prewarm('bmsql_customer_idx1','bufferkeep');
select sys_prewarm('bmsql_oorder_pkey','bufferkeep');
select sys_prewarm('bmsql_oorder_idx1','bufferkeep');
select sys_prewarm('bmsql_new_order_pkey','bufferkeep');
select sys_prewarm('bmsql_order_line_pkey','bufferkeep');
select sys_prewarm('bmsql_stock_pkey','bufferkeep');
select sys_prewarm('bmsql_item_pkey','bufferkeep');
select sys_prewarm('ndx_oorder_2','bufferkeep');
analyze;

11.运行
如果可以用自己的benchmarksql包，jdbcurl一定要使用unixdomain；
conn=jdbc:kingbase8:///test?socketFactory=KBAFUNIXSocketFactory&socketFactoryArg=/tmp/.s.KINGBASE.54321
运行前清理操作系统缓存:
sync
echo 3 > /proc/sys/vm/drop_caches

CPU性能模式:
现在主流的CPU，其默认主频并不是最高的，通过下述命令，可以将CPU的性能发挥到最大。
通过root执行下述脚本:
cpupower frequency-set -g performance
执行完即生效，不需要重启。

运行:
./runBenchmark.sh props.kingbase

12.如果结果不符合预期，需要提供运行期间的kwr和vmstat数据协助分析
kwr配置参考文档
vmstat 10 > vmstat.log

connect_pool_size=64
connect_delay_time=7