GBase 8s Database Performance Testing and Optimization Guide

Cong Li - Jul 2 - - Dev Community

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.

Image
5 Laws That Can Help With The Class Action Lawsuit Asbestos Exposure Industry
Image
My Hacktoberfest Journey: From First Pull Request to the Hall of Fame

devchallenge, hacktoberfestchallenge, hacktoberfest, opensource
Image
provider slots online terpilih yang patut dicoba beberapa betaruh
Image
Why Asbestos Lawsuits Is Quickly Becoming The Hottest Trend For 2023
Image
9 Things Your Parents Teach You About Asbestos Attorney Cancer Lawyer Mesothelioma
Image
How to Make ChatGPT-Generated Content Undetectable by AI Detectors.

aihumanizer, texthumanizer, humanizeaitext, ai
Image
Request for Access to Claude 3 Paid Version for a College Student from Somalia
Image
Seven Explanations On Why Asbestos Compensation Payouts Is So Important
Image
12 Companies Are Leading The Way In Crypto Casino
Image
Crypto Casino: What's The Only Thing Nobody Has Discussed
Image
15 Terms That Everyone Within The Asbestos Poisoning Compensation Industry Should Know
Image
20 Things That Only The Most Devoted Average Payout For Asbestos Claims Fans Know
Image
What is JavaScript ?

javascript, programming, learning, career
Image
What You Should Be Focusing On Improving Accident Lawyer Phoenix
Image
tanda-tanda situs bandar judi slots online sah bisa dipercaya
Image
15 Things You Didn't Know About Average Payout For Asbestos Claims
Image
5 Qualities That People Are Looking For In Every Asbestos Attorneys Oklahoma
Image
How to Enable IIS Management Console in Windows 11?

winsides, windows11, tips, tutorials
Image
Asbestos And Peritoneal Mesothelioma Tips From The Top In The Industry
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player