DEV Community

Cong Li
Cong Li

Posted on

GBase 8s Database Performance Testing and Optimization Guide

#database

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
Enter fullscreen mode Exit fullscreen mode

(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
Enter fullscreen mode Exit fullscreen mode

(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
Enter fullscreen mode Exit fullscreen mode

(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
Enter fullscreen mode Exit fullscreen mode

(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
Enter fullscreen mode Exit fullscreen mode

(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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

onmode -ky
onclean -ky
oninit -vy
Enter fullscreen mode Exit fullscreen mode

III. Create the Performance Test Database 

dbaccess - -
CREATE DATABASE benchmarksql IN dbs1 WITH BUFFERED LOG;
Enter fullscreen mode Exit fullscreen mode

IV. Adapt Benchmark 5.0 for GBase

(1) Navigate to the src/client Directory 

cd benchmark_path/src/client
vim jTPCC.java
Enter fullscreen mode Exit fullscreen mode

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

else if (iDB.equals("gbase"))
    dbType = DB_UNKNOWN;
Enter fullscreen mode Exit fullscreen mode

(2) Navigate to the run Directory 

cd benchmark_path/run
vim funcs.sh
Enter fullscreen mode Exit fullscreen mode

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

gbase)
cp="../lib/gbase/*:../lib/*"
;;
Enter fullscreen mode Exit fullscreen mode

And add gbase to the following case statement:

case "$(getProp db)" in
    firebird|oracle|postgres|gbase)
Enter fullscreen mode Exit fullscreen mode

(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
Enter fullscreen mode Exit fullscreen mode

(4) Navigate to the lib Directory 

cd benchmark_path/lib
mkdir gbase
cd gbase
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

(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.

Top comments (0)

Read next

spronin profile image

Deploy MongoDB on Kubernetes with ArgoCD and Helm charts

Sergey Pronin -

gaspardb profile image

What's coming to PostgreSQL ? 🐘

Gaspard Boursin -

dinko7 profile image

Beyond Code Generation: Crafting Custom Hive Adapters

Dinko Marinac -

orololuwa profile image

Setting up a Database Driver, Repository and Implementation of a transaction function for your Go App

Orololuwa -