GBase 8s Database Performance Testing and Optimization Guide
Tags: database
Published: 2024-07-02 08:20:50

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

```sh
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

```sh
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

```sh
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

```sh
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

```bash
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

```bash
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

```ini
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:

```bash
onmode -ky
onclean -ky
oninit -vy
```

## III. Create the Performance Test Database

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

## IV. Adapt Benchmark 5.0 for GBase

### (1) Navigate to the `src/client` Directory

```bash
cd benchmark_path/src/client
vim jTPCC.java
```

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

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

### (2) Navigate to the `run` Directory

```bash
cd benchmark_path/run
vim funcs.sh
```

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

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

And add `gbase` to the following case statement:

```sh
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:

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

### (4) Navigate to the `lib` Directory

```bash
cd benchmark_path/lib
mkdir gbase
cd gbase
```

Place the GBase JDBC driver in this directory.

### (5) Modify the `props.gbase` Configuration File

```properties
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.