GBase8s数据库性能测试调优攻略

在数据库管理与优化的过程中，性能测试是一个不可或缺的环节。它不仅帮助我们了解系统当前的性能状况，还能指导我们进行有效的调优。本文将为您详细介绍如何对GBase8s数据库进行性能测试和调优。

一、创建性能测试数据空间，可直接运行附件中的run.sh创建空间

（1）创建性能测试数据库benchmarksql的数据空间

mkdir -p /data/othdbs
chown gbasedbt:gbasedbt /data/othdbs       
touch /data/othdbs/dbs1
chmod 660 /data/othdbs/dbs1
chown gbasedbt:gbasedbt /data/othdbs/dbs1
onspaces -c -d dbs1 -p /data/othdbs/dbs1 -o 0 -s 30000000 -k 4

（2）创建临时表空间

for i in {1..4};
    do
    touch /data/othdbs/temp$i;
    chmod 660 /data/othdbs/temp$i;
    chown gbasedbt:gbasedbt /data/othdbs/temp$i;
    onspaces -c -d temp$i -p /data/othdbs/temp$i -o 0 -s 2500000 -k 4 -t;
done

（3）创建数据表空间，每个表20分片测试     

mkdir -p /data/storage/tbdbs4;
chown gbasedbt:gbasedbt /data/storage/tbdbs4;
for i in {1..20};
do
    touch /data/storage/tbdbs4/district_dbs$i;
    chmod 660 /data/storage/tbdbs4/district_dbs$i;
    chown gbasedbt:gbasedbt /data/storage/tbdbs4/district_dbs$i;
    onspaces -c -d district_dbs$i -p /data/storage/tbdbs4/district_dbs$i -o 0 -s 100000 -k 4;
done 
                     
mkdir -p /data/storage/order_line;
chown gbasedbt:gbasedbt /data/storage/order_line;
for i in {1..20};
do
    touch /data/storage/order_line/order_line_dbs$i;
    chmod 660 /data/storage/order_line/order_line_dbs$i;
    chown gbasedbt:gbasedbt /data/storage/order_line/order_line_dbs$i;
    onspaces -c -d order_line_dbs$i -p /data/storage/order_line/order_line_dbs$i -o 0 -s 7000000 -k 4;
done 

mkdir -p /data/storage/tbdbs8;
chown gbasedbt:gbasedbt /data/storage/tbdbs8;             
for i in {1..20};
do
    touch /data/storage/tbdbs8/history_dbs$i;
    chmod 660 /data/storage/tbdbs8/history_dbs$i;
    chown gbasedbt:gbasedbt /data/storage/tbdbs8/history_dbs$i;
    onspaces -c -d history_dbs$i -p /data/storage/tbdbs8/history_dbs$i -o 0 -s 1000000 -k 4;
done 

mkdir -p /data/storage/tbdbs9;
chown gbasedbt:gbasedbt /data/storage/tbdbs9;                     
for i in {1..20};
do
    touch /data/storage/tbdbs9/warehouse_dbs$i;
    chmod 660 /data/storage/tbdbs9/warehouse_dbs$i;
    chown gbasedbt:gbasedbt /data/storage/tbdbs9/warehouse_dbs$i;
    onspaces -c -d warehouse_dbs$i -p /data/storage/tbdbs9/warehouse_dbs$i -o 0 -s 100000 -k 4;
done 

mkdir -p /data/storage/tbdbs3;
chown gbasedbt:gbasedbt /data/storage/tbdbs3;                     
for i in {1..20};
do
    touch /data/storage/tbdbs3/new_order_dbs$i;
    chmod 660 /data/storage/tbdbs3/new_order_dbs$i;
    chown gbasedbt:gbasedbt /data/storage/tbdbs3/new_order_dbs$i;
    onspaces -c -d new_order_dbs$i -p /data/storage/tbdbs3/new_order_dbs$i -o 0 -s 100000 -k 4;
done 

mkdir -p /data/storage/tbdbs7;
chown gbasedbt:gbasedbt /data/storage/tbdbs7;                     
for i in {1..20};
do
    touch /data/storage/tbdbs7/stock_dbs$i;
    chmod 660 /data/storage/tbdbs7/stock_dbs$i;
    chown gbasedbt:gbasedbt /data/storage/tbdbs7/stock_dbs$i;
    onspaces -c -d stock_dbs$i -p /data/storage/tbdbs7/stock_dbs$i -o 0 -s 4000000 -k 4;
done 

mkdir -p /datat/storage/cus;
chown gbasedbt:gbasedbt /datat/storage/cus;                    
for i in {1..20};
do
    touch /datat/storage/cus/customer_dbs$i;
    chmod 660 /datat/storage/cus/customer_dbs$i;
    chown gbasedbt:gbasedbt /datat/storage/cus/customer_dbs$i;
    onspaces -c -d customer_dbs$i -p /datat/storage/cus/customer_dbs$i -o 0 -s 3000000 -k 4;
done 
                     
mkdir -p /data/storage/tbdbs5;
chown gbasedbt:gbasedbt /data/storage/tbdbs5;
for i in {1..20};
do
    touch /data/storage/tbdbs5/item_dbs$i;
    chmod 660 /data/storage/tbdbs5/item_dbs$i;
    chown gbasedbt:gbasedbt /data/storage/tbdbs5/item_dbs$i;
    onspaces -c -d item_dbs$i -p /data/storage/tbdbs5/item_dbs$i -o 0 -s 100000 -k 4;
done 

mkdir -p /data/storage/tbdbs6;
chown gbasedbt:gbasedbt /data/storage/tbdbs6;                   
for i in {1..20};
do
    touch /data/storage/tbdbs6/oorder_dbs$i;
    chmod 660 /data/storage/tbdbs6/oorder_dbs$i;
    chown gbasedbt:gbasedbt /data/storage/tbdbs6/oorder_dbs$i;
    onspaces -c -d oorder_dbs$i -p /data/storage/tbdbs6/oorder_dbs$i -o 0 -s 500000 -k 4;
done                

（4）创建索引对应的空间，每个索引20分片测试     

mkdir -p /data/storage/idxdbs5;
chown gbasedbt:gbasedbt /data/storage/idxdbs5;
for i in {1..20};
do
    touch /data/storage/idxdbs5/bopdbs$i;
    chmod 660 /data/storage/idxdbs5/bopdbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs5/bopdbs$i;
    onspaces -c -d bopdbs$i -p /data/storage/idxdbs5/bopdbs$i -o 0 -s 500000 -k 4;
done 

mkdir -p /data/storage/idxdbs1;
chown gbasedbt:gbasedbt /data/storage/idxdbs1;                     
for i in {1..20};
do
    touch /data/storage/idxdbs1/bwpdbs$i;
    chmod 660 /data/storage/idxdbs1/bwpdbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs1/bwpdbs$i;
    onspaces -c -d bwpdbs$i -p /data/storage/idxdbs1/bwpdbs$i -o 0 -s 100000 -k 4;
done 
                     
mkdir -p /data/storage/idxdbs7;
chown gbasedbt:gbasedbt /data/storage/idxdbs7;
for i in {1..20};
do
    touch /data/storage/idxdbs7/bnopdbs$i;
    chmod 660 /data/storage/idxdbs7/bnopdbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs7/bnopdbs$i;
    onspaces -c -d bnopdbs$i -p /data/storage/idxdbs7/bnopdbs$i -o 0 -s 100000 -k 4;
done 
 
mkdir -p /data/storage/idxdbs2;
chown gbasedbt:gbasedbt /data/storage/idxdbs2;                  
for i in {1..20};
do
    touch /data/storage/idxdbs2/bdpdbs$i;
    chmod 660 /data/storage/idxdbs2/bdpdbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs2/bdpdbs$i;
    onspaces -c -d bdpdbs$i -p /data/storage/idxdbs2/bdpdbs$i -o 0 -s 100000 -k 4;
done 

mkdir -p /data/storage/idxdbs8;
chown gbasedbt:gbasedbt /data/storage/idxdbs8;                     
for i in {1..20};
do
    touch /data/storage/idxdbs8/bolpdbs$i;
    chmod 660 /data/storage/idxdbs8/bolpdbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs8/bolpdbs$i;
    onspaces -c -d bolpdbs$i -p /data/storage/idxdbs8/bolpdbs$i -o 0 -s 7000000 -k 4;
done 

mkdir -p /data/storage/idxdbs3;
chown gbasedbt:gbasedbt /data/storage/idxdbs3;                     
for i in {1..20};
do
    touch /data/storage/idxdbs3/bcpdbs$i;
    chmod 660 /data/storage/idxdbs3/bcpdbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs3/bcpdbs$i;
    onspaces -c -d bcpdbs$i -p /data/storage/idxdbs3/bcpdbs$i -o 0 -s 3000000 -k 4;
done 

mkdir -p /data/storage/idxdbs10;
chown gbasedbt:gbasedbt /data/storage/idxdbs10;                   
for i in {1..20};
do
    touch /data/storage/idxdbs10/bipdbs$i;
    chmod 660 /data/storage/idxdbs10/bipdbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs10/bipdbs$i;
    onspaces -c -d bipdbs$i -p /data/storage/idxdbs10/bipdbs$i -o 0 -s 100000 -k 4;
done 

mkdir -p /data/storage/idxdbs9;
chown gbasedbt:gbasedbt /data/storage/idxdbs9;                    
for i in {1..20};
do
    touch /data/storage/idxdbs9/bspdbs$i;
    chmod 660 /data/storage/idxdbs9/bspdbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs9/bspdbs$i;
    onspaces -c -d bspdbs$i -p /data/storage/idxdbs9/bspdbs$i -o 0 -s 4000000 -k 4;
done 

mkdir -p /data/storage/idxdbs6;
chown gbasedbt:gbasedbt /data/storage/idxdbs6;                     
for i in {1..20};
do
    touch /data/storage/idxdbs6/boidbs$i;
    chmod 660 /data/storage/idxdbs6/boidbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs6/boidbs$i;
    onspaces -c -d boidbs$i -p /data/storage/idxdbs6/boidbs$i -o 0 -s 500000 -k 4;
done 

mkdir -p /data/storage/idxdbs4;
chown gbasedbt:gbasedbt /data/storage/idxdbs4;                    
for i in {1..20};
do
    touch /data/storage/idxdbs4/bcidbs$i;
    chmod 660 /data/storage/idxdbs4/bcidbs$i;
    chown gbasedbt:gbasedbt /data/storage/idxdbs4/bcidbs$i;
    onspaces -c -d bcidbs$i -p /data/storage/idxdbs4/bcidbs$i -o 0 -s 3000000 -k 4;
done                      

（5）创建物理日志的空间

mkdir -p  /data/plogdbs
chown gbasedbt:gbasedbt /data/plogdbs
touch /data/plogdbs/plog
chmod 660 /data/plogdbs/plog
chown gbasedbt:gbasedbt /data/plogdbs/plog
onspaces -c -d plog -p /data/plogdbs/plog -o 0 -s 51000000
onparams -p -s 50000000 -d plog -y     

（6）创建逻辑日志的空间

mkdir -p  /data/llogdbs
chown gbasedbt:gbasedbt /data/llogdbs
touch /data/llogdbs/llog
chmod 660 /data/llogdbs/llog
chown gbasedbt:gbasedbt /data/llogdbs/llog
onspaces -c -d llog -p /data/llogdbs/llog -o 0 -s 101000000   
for i in {1..50};
    do
        onparams -a -d llog -s 2000000;
done 
 
get_smallog_num=`onstat -l|awk '{if($6==5000) print $2}'`
start="`onstat -l|grep C|awk '{if($6==5000) print $2}'`"
len="`echo $get_smallog_num|awk '{print NF}'`"

for i in `seq ${start} ${len}`
do
    onmode -l;
done
for j in $get_smallog_num
do
    onparams -d -l  $j -y;
done

二、修改onconfig配置文件

PHYSBUFF 65534
LOGBUFF 65534
NETTYPE soctcp,10,150,NET
LISTEN_TIMEOUT 60
MAX_INCOMPLETE_CONNECTIONS 1024
VPCLASS cpu,num=64,aff=(0-63),noage   
AUTO_TUNE 1
AUTO_CKPTS  0
AUTO_READAHEAD  0
AUTO_LRU_TUNING 1
CLEANERS 128
DIRECT_IO 1
LOCKS 100000000
DEF_TABLE_LOCKMODE row
SHMVIRTSIZE 31200000
SHMADD 102400
EXTSHMADD 102400
CKPTINTVL 60
DS_MAX_QUERIES  4
DS_TOTAL_MEMORY 4096000
DS_MAX_SCANS 1048576
DS_NONPDQ_QUERY_MEM 1024000
DUMPSHMEM 0
BUFFERPOOL 
size=4k,buffers=204800000,lrus=128,lru_min_dirty=90,lru_max_dirty=95

配置文件修改后需要重启数据库服务生效：onmode -ky;onclean -ky;oninit -vy

三、创建性能测试数据库

dbaccess - -
Create database benchmarksql in dbs1 with buffered log;

四、Benchmark5.0适配gbase

（1）cd benchmark路径/src/client

vim jTPCC.java

在jTPCC构造器：if (iDB.equals("firebird"))分支中添加

else if (iDB.equals("gbase"))
    dbType = DB_UNKNOWN;

（2）cd benchmark路径/run

vim funcs.sh

在function setCP()中的case分支中添加：

gbase)
cp="../lib/gbase/*:../lib/*"
;;

并在以下case中添加gbase:

case "$(getProp db)" in
    firebird|oracle|postgres|gbase)

（3）编译benchmark

解压缩ant安装包

ant路径/dist/bin中的内容都赋X可执行权限

ant放置到benchmark的同级目录

在benchmark路径下执行：

../apache-ant-1.10.9/dist/bin/ant

（4）cd benchmark路径/lib

mkdir gbase
cd gbase

将gbase的jdbc放到该目录

（5）修改props配置文件props.gbase，也可基于附件中的props.gbase修改

db=gbase 
driver=com.gbasedbt.jdbc.Driver
conn=jdbc:gbasedbt-sqli://测数据库服务器ip:服务端口/benchmarksql:GBASEDBTSERVER=实例名;IFX_SOC_TIMEOUT=36000000;IFX_USEPUT=1;IFX_ISOLATION_LEVEL=1U;IFX_LOCK_MODE_WAIT=100;OPTOFC=1;S
OCKET_REC_BUF=1000000 
user=gbasedbt
password=****** 
warehouses=1000 
loadWorkers=20 
terminals=1000
runTxnsPerTerminal=0 
runMins=10 
limitTxnsPerMin=300000000 
terminalWarehouseFixed=true 
newOrderWeight=45 
paymentWeight=43 
orderStatusWeight=4 
deliveryWeight=4 
stockLevelWeight=4 

（6）建表和创建索引sql文件

使用附件中的tableCreates.sql 和 indexCreates.sql替换run/sql.com中为对应文件。

性能测试和调优是一个持续的过程，需要根据实际的业务需求和系统表现不断地进行调整。希望本文能为您提供一个清晰的指南，帮助您高效地完成GBase8s数据库的性能测试和调优工作。