GBase 8s Database Performance Testing and Optimization Guide

Performance testing is a crucial part of database management and optimization. It not only helps us understand the current performance status of the system but also guides us in effective tuning. This article will provide a detailed introduction on how to conduct performance testing and optimization for the GBase8s database.

1. Creating Performance Test Data Space

You can directly run the attached run.sh to create the space.

(1) Create the BenchmarkSQL Performance Test Database Space

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) Create Temporary Table Space

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) Create Data Table Space with 20 Partitions per Table

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) Create Index Spaces with 20 Partitions per Index

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) Create Space for Physical Logs

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) Create Space for Logical Logs

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

II. Modify the onconfig Configuration File

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

After modifying the configuration file, restart the database service to apply the changes:

onmode -ky
onclean -ky
oninit -vy

III. Create the Performance Test Database

dbaccess - -
CREATE DATABASE benchmarksql IN dbs1 WITH BUFFERED LOG;

IV. Adapt Benchmark 5.0 for GBase

(1) Navigate to the src/client Directory

cd benchmark_path/src/client
vim jTPCC.java

Add the following to the if (iDB.equals("firebird")) branch in the jTPCC constructor:

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

(2) Navigate to the run Directory

cd benchmark_path/run
vim funcs.sh

Add the following to the function setCP() case branch:

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

And add gbase to the following case statement:

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

(3) Compile the Benchmark

Extract the Ant installation package and grant executable permissions to the contents in the ant_path/dist/bin directory. Place the Ant directory at the same level as the Benchmark directory, then run:

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

(4) Navigate to the lib Directory

cd benchmark_path/lib
mkdir gbase
cd gbase

Place the GBase JDBC driver in this directory.

(5) Modify the props.gbase Configuration File

db=gbase 
driver=com.gbasedbt.jdbc.Driver
conn=jdbc:gbasedbt-sqli://<Database_Server_IP>:<Service_Port>/benchmarksql:GBASEDBTSERVER=<Instance_Name>;IFX_SOC_TIMEOUT=36000000;IFX_USEPUT=1;IFX_ISOLATION_LEVEL=1U;IFX_LOCK_MODE_WAIT=100;OPTOFC=1;SOCKET_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) Create Table and Index SQL Files

Replace the corresponding files in run/sql.com with tableCreates.sql and indexCreates.sql provided in the attachment.

Performance testing and tuning is a continuous process that requires ongoing adjustments based on actual business needs and system performance. I hope this article provides a clear guide to help you efficiently perform performance testing and tuning for the GBase database.