This topic describes how to manually run the TPC-H test in a tenant of OceanBase AI Database by using the TPC-H tool. The sample data size is SF1000 (about 1 TB).
What is TPC-H
TPC-H (Business Intelligence Computing Test) is a decision support benchmark developed by the Transaction Processing Performance Council (TPC) that simulates complex query workloads in business intelligence scenarios. TPC-H models a data warehouse in third normal form (3NF) with eight base tables. The primary metric is query response time—the interval from query submission to result return.
TPC-H evolved from TPC-D. Results are reported as Queries per Hour at a given scale (QphH@size), where H is the average number of complex queries executed per hour and size is the database scale. TPC-H is widely used for performance assessment in scenarios such as credit and card analysis in banking, telecom operations analysis, and tax analysis.
Note
This performance test method is tuned based only on basic parameters, so that developers can get a good database performance experience. The test parameters are based on the baseline version at the time of this document's publication, and may change in new versions.
Prepare the environment
Prepare an OceanBase AI Database test cluster, a test tenant, and OBClient. This document installs tools, generates data, and executes scripts on the OBServer host that stores data. The host must have gcc, make, unzip, dos2unix, Bash, and awk.
Taking a Linux system supporting yum as an example, install dependencies:
sudo yum install -y gcc make unzip dos2unix
Replace the following connection information with your actual environment settings: $host_ip is the OBServer address, $host_port is the SQL port (default 2881), $user uses root, and $tenant is the name of the test tenant.
The following example mounts the data disk at /data/1. Replace the data directory accordingly if the actual path is different.
Set the data scale and directory. SF1000 is approximately 1 TB; when testing 100G, change SF to 100:
export SF=1000
export WORK="$HOME/ob-tpch-sf$SF"
export DATA_DIR="/data/1/tpch-sf$SF"
umask 077
mkdir -p "$WORK"
cd "$WORK"
Perform a TPC-H test
The test process is as follows:
- Step 1: Optimize the environment.
- Step 2: Install the TPC-H tool.
- Step 3: Generate data.
- Step 4: Generate query SQL statements.
- Step 5: Create tables.
- Step 6: Load data.
- Step 7: Initiate a major compaction.
- Step 8: Wait for the major compaction to complete.
- Step 9: Collect statistics.
- Step 10: Run the benchmark.
Step 1: Optimize the environment
sys tenant
Connect to the sys tenant:
obclient -h$host_ip -P$host_port -uroot@sys -p -D oceanbase
ALTER SYSTEM SET enable_record_trace_log=false;
ALTER SYSTEM SET trace_log_slow_query_watermark='7d';
ALTER SYSTEM SET large_query_threshold='0ms';
ALTER SYSTEM SET enable_syslog_recycle=1;
ALTER SYSTEM SET max_syslog_file_count=300;
ALTER SYSTEM SET micro_block_merge_verify_level=0;
test tenant
Connect to the test tenant:
obclient -h$host_ip -P$host_port -u$user@$tenant -p -D oceanbase
ALTER SYSTEM SET default_table_store_format='column';
ALTER SYSTEM SET _io_callback_thread_count='64';
ALTER SYSTEM SET default_load_mode='DISABLED';
ALTER SYSTEM SET direct_load_allow_fallback=0;
ALTER SYSTEM SET _nested_loop_join_enabled=false;
ALTER SYSTEM SET _rtf_creator_max_row_count=2000000000;
ALTER SYSTEM SET default_table_organization='HEAP';
ALTER SYSTEM SET default_table_merge_engine='DELETE_INSERT';
ALTER SYSTEM SET _force_subquery_unnest=true;
ALTER SYSTEM SET max_partition_num=65536;
ALTER SYSTEM SET default_skip_index_level=1;
ALTER SYSTEM SET _rowsets_max_rows=512;
ALTER SYSTEM SET enable_rebalance='False';
ALTER SYSTEM SET _tempstore_read_alignment_size='1MB';
ALTER SYSTEM SET _io_read_batch_size='512KB';
ALTER SYSTEM SET inc_sstable_upload_thread_score=30;
ALTER SYSTEM SET _io_read_redundant_limit_percentage=50;
ALTER SYSTEM SET _object_storage_io_timeout='300s';
ALTER SYSTEM SET ha_high_thread_score=20;
ALTER SYSTEM SET _shared_gc_task_scan_batch_size=100;
ALTER SYSTEM SET ha_vector_index_thread_score=0;
SET GLOBAL ob_query_timeout=604800000000;
SET GLOBAL ob_trx_timeout=604800000000;
SET GLOBAL parallel_min_scan_time_threshold=10;
SET GLOBAL ob_sql_work_area_percentage=50;
SET GLOBAL parallel_degree_policy='AUTO';
SET GLOBAL collation_server='utf8mb4_bin';
SET GLOBAL collation_connection='utf8mb4_bin';
ALTER SYSTEM SET _no_logging=true;
ALTER SYSTEM SET _temporary_file_io_area_size=10;
SET GLOBAL parallel_servers_target=10000;
ALTER SYSTEM SET _max_px_workers_per_cpu=10;
Notice
After setting the GLOBAL variables, you must reconnect to the test tenant.
Step 2: Install the TPC-H Tool
Download the TPC-H tool.
For more information, see the TPC-H tool download page.
After downloading, extract the files and navigate to the TPC-H directory. Replace the package name with the actual downloaded file name.
cd "$WORK" unzip 7e965ead-8844-4efa-a275-34e35f8ab89b-tpc-h-tool.zip cd TPC-H_Tools_v3.0.0/dbgen cp Makefile.suite MakefileModify the
CC,DATABASE,MACHINE, andWORKLOADparameters in theMakefilefile.CC = gcc DATABASE = MYSQL MACHINE = LINUX WORKLOAD = TPCHModify the
tpcd.hfile and add the following macro definitions.#ifdef MYSQL #define GEN_QUERY_PLAN "" #define START_TRAN "START TRANSACTION" #define END_TRAN "COMMIT" #define SET_OUTPUT "" #define SET_ROWCOUNT "limit %d;\n" #define SET_DBASE "use %s;\n" #endifCompile the files.
make
Step 3: Generate data
Use the official dbgen to generate data in parallel by using 20 threads, with nation and region generated separately. The data scale is specified by the SF set in Prepare the environment. Use a new empty data directory.
(
set -e
SCALE_FACTOR="$SF"
CHUNK_COUNT=20
DATA_DIR="$DATA_DIR"
mkdir "$DATA_DIR"
cd "$DATA_DIR"
pids=()
for ((child=1; child<=CHUNK_COUNT; child++)); do
DSS_CONFIG="$WORK/TPC-H_Tools_v3.0.0/dbgen" \
"$WORK/TPC-H_Tools_v3.0.0/dbgen/dbgen" \
-s "$SCALE_FACTOR" -C "$CHUNK_COUNT" -S "$child" -vf \
> "$WORK/dbgen-${child}.log" 2>&1 &
pids+=("$!")
done
failed=0
for pid in "${pids[@]}"; do wait "$pid" || failed=1; done
[[ "$failed" == 0 ]] || { echo "Data generation failed. Check the dbgen logs"; exit 1; }
DSS_CONFIG="$WORK/TPC-H_Tools_v3.0.0/dbgen" \
"$WORK/TPC-H_Tools_v3.0.0/dbgen/dbgen" -s "$SCALE_FACTOR" -T l -vf
chmod 755 "$DATA_DIR"
chmod 644 "$DATA_DIR/"*.tbl*
)
Step 4: Generate query SQL
Copy the
qgenanddists.dssfiles to the query directory.mkdir -p "$WORK/queries" cp "$WORK/TPC-H_Tools_v3.0.0/dbgen/qgen" "$WORK/queries/" cp "$WORK/TPC-H_Tools_v3.0.0/dbgen/dists.dss" "$WORK/queries/" cd "$WORK/queries"Create the
gen.shscript to generate query SQL statements. Keep the data scale consistent with the one used to generate data.#!/bin/bash set -e export DSS_QUERY="$WORK/TPC-H_Tools_v3.0.0/dbgen/queries" for i in {1..22} do ./qgen -d "$i" -s "$SF" > db"$i".sql doneRun the
gen.shscript.chmod +x gen.sh ./gen.sh dos2unix db*.sqlModify the query SQL statements based on the actual concurrency number.
Add
/*+ PARALLEL(96) */to each query. Check whether the positive LIMIT clause is placed before the closing semicolon of the SELECT statement and retain its original value. If aLIMIT -1clause indicating an unlimited number of rows is generated, delete that LIMIT clause. ChangeDAY(3)(orDAY (3)) in Q1 toDAYwhile retaining the original date and number of days.For the query syntax, see the TPC-H query SQL provided on the official website. Replace
cpu_numwith the parallelism of 96 used in this example.
Step 5: Create a table
Use the root account of the test tenant to create a test database.
obclient -h$host_ip -P$host_port -u$user@$tenant -pCREATE DATABASE tpch_bench DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; USE tpch_bench;Connect to the sys tenant and set the data directory accessible to the test tenant.
obclient -h$host_ip -P$host_port -uroot@sys -p -D oceanbase \ -e "ALTER TENANT $tenant SET VARIABLES secure_file_priv='$DATA_DIR';"Create a table schema file
$WORK/schema.sqland write the following content into it.create tablegroup tpch_tg_lineitem_orders binding true partition by key 1 partitions 192; create tablegroup tpch_tg_part_partsupp binding true partition by key 1 partitions 192; CREATE TABLE lineitem ( l_orderkey bigint NOT NULL, l_partkey int(32) NOT NULL, l_suppkey int(32) NOT NULL, l_linenumber int(32) NOT NULL, l_quantity decimal(32,2) NOT NULL, l_extendedprice decimal(32,2) NOT NULL, l_discount decimal(15,2) NOT NULL, l_tax decimal(15,2) NOT NULL, l_returnflag char(1) DEFAULT NULL, l_linestatus char(1) DEFAULT NULL, l_shipdate date NOT NULL, l_commitdate date DEFAULT NULL, l_receiptdate date DEFAULT NULL, l_shipinstruct varchar(64) DEFAULT NULL, l_shipmode varchar(64) DEFAULT NULL, l_comment varchar(64) DEFAULT NULL, primary key(l_shipdate, l_orderkey, l_linenumber) ) ORGANIZATION INDEX MERGE_ENGINE = PARTIAL_UPDATE SKIP_INDEX_LEVEL = 0 DELTA_FORMAT = 'flat' row_format = condensed tablegroup = tpch_tg_lineitem_orders partition by key (l_orderkey) partitions 192 with column group(each column); alter table lineitem CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; CREATE TABLE orders ( o_orderkey bigint NOT NULL, o_custkey int(32) NOT NULL, o_orderstatus varchar(64) DEFAULT NULL, o_totalprice decimal(15,2) DEFAULT NULL, o_orderdate date NOT NULL, o_orderpriority varchar(15) DEFAULT NULL, o_clerk varchar(15) DEFAULT NULL, o_shippriority int(32) DEFAULT NULL, o_comment varchar(128) DEFAULT NULL, PRIMARY KEY (o_orderkey, o_orderdate) ) ORGANIZATION INDEX MERGE_ENGINE = PARTIAL_UPDATE SKIP_INDEX_LEVEL = 0 DELTA_FORMAT = 'flat' row_format = condensed tablegroup = tpch_tg_lineitem_orders partition by key(o_orderkey) partitions 192 with column group(each column); alter table orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; CREATE TABLE partsupp ( ps_partkey int(11) NOT NULL, ps_suppkey int(11) NOT NULL, ps_availqty int(11) DEFAULT NULL, ps_supplycost decimal(15,2) DEFAULT NULL, ps_comment varchar(199) DEFAULT NULL, PRIMARY KEY (ps_partkey, ps_suppkey)) ORGANIZATION INDEX MERGE_ENGINE = PARTIAL_UPDATE SKIP_INDEX_LEVEL = 0 DELTA_FORMAT = 'flat' row_format = condensed tablegroup tpch_tg_part_partsupp partition by key(ps_partkey) partitions 192 with column group(each column); alter table partsupp CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; CREATE TABLE part ( p_partkey int(11) NOT NULL, p_name varchar(55) DEFAULT NULL, p_mfgr varchar(25) DEFAULT NULL, p_brand varchar(10) DEFAULT NULL, p_type varchar(25) DEFAULT NULL, p_size int(11) DEFAULT NULL, p_container varchar(10) DEFAULT NULL, p_retailprice decimal(12,2) DEFAULT NULL, p_comment varchar(23) DEFAULT NULL, PRIMARY KEY (p_partkey)) ORGANIZATION INDEX MERGE_ENGINE = PARTIAL_UPDATE SKIP_INDEX_LEVEL = 0 DELTA_FORMAT = 'flat' row_format = condensed tablegroup tpch_tg_part_partsupp partition by key(p_partkey) partitions 192 with column group(each column); alter table part CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; CREATE TABLE customer ( c_custkey int(11) NOT NULL, c_name varchar(25) DEFAULT NULL, c_address varchar(40) DEFAULT NULL, c_nationkey int(11) DEFAULT NULL, c_phone varchar(15) DEFAULT NULL, c_acctbal decimal(15,2) DEFAULT NULL, c_mktsegment char(10) DEFAULT NULL, c_comment varchar(117) DEFAULT NULL, PRIMARY KEY (c_custkey)) ORGANIZATION INDEX MERGE_ENGINE = PARTIAL_UPDATE SKIP_INDEX_LEVEL = 0 DELTA_FORMAT = 'flat' row_format = condensed partition by key(c_custkey) partitions 192 with column group(each column); alter table customer CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; CREATE TABLE supplier ( s_suppkey int(11) NOT NULL, s_name varchar(25) DEFAULT NULL, s_address varchar(40) DEFAULT NULL, s_nationkey int(11) DEFAULT NULL, s_phone varchar(15) DEFAULT NULL, s_acctbal decimal(15,2) DEFAULT NULL, s_comment varchar(101) DEFAULT NULL, PRIMARY KEY (s_suppkey) ) ORGANIZATION INDEX MERGE_ENGINE = PARTIAL_UPDATE SKIP_INDEX_LEVEL = 0 DELTA_FORMAT = 'flat' row_format = condensed partition by key(s_suppkey) partitions 192 with column group(each column); alter table supplier CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; CREATE TABLE nation ( n_nationkey int(11) NOT NULL, n_name varchar(25) DEFAULT NULL, n_regionkey int(11) DEFAULT NULL, n_comment varchar(152) DEFAULT NULL, PRIMARY KEY (n_nationkey) ) ORGANIZATION INDEX MERGE_ENGINE = PARTIAL_UPDATE SKIP_INDEX_LEVEL = 0 DELTA_FORMAT = 'flat' row_format = condensed with column group(each column); alter table nation CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; CREATE TABLE region ( r_regionkey int(11) NOT NULL, r_name varchar(25) DEFAULT NULL, r_comment varchar(152) DEFAULT NULL, PRIMARY KEY (r_regionkey) ) ORGANIZATION INDEX MERGE_ENGINE = PARTIAL_UPDATE SKIP_INDEX_LEVEL = 0 DELTA_FORMAT = 'flat' row_format = condensed with column group(each column); alter table region CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;Execute the table creation file:
obclient -h$host_ip -P$host_port -u$user@$tenant -p \ -D tpch_bench < "$WORK/schema.sql"
Step 6: Load data
Write your own script based on the data and SQL generated in the previous steps. The following example shows how to load data. Save the script as
$WORK/load_data.sh; connection information and the data directory are passed at runtime.#!/bin/bash set -euo pipefail set +x umask 077 WORK="$(cd "$(dirname "$0")" && pwd)" host= port=2881 user= database= data_path= while (($#)); do case "$1" in -h) host="${2:?Please enter the host address}"; shift 2 ;; -P) port="${2:?Please enter a port}"; shift 2 ;; -u) user="${2:?Please enter the username@tenant}"; shift 2 ;; -D) database="${2:?Please enter the database name}"; shift 2 ;; --data-dir) data_path="${2:?Please specify the data directory}"; shift 2 ;; *) echo "Usage: bash load_data.sh -h host [-P 2881] -u user@tenant -D database --data-dir data_directory"; exit 2 ;; esac done [[ -n "$host" && -n "$user" && -n "$database" && -n "$data_path" ]] || { echo "Please specify -h, -u, -D, and --data-dir." exit 2 } [[ "$port" =~ ^[0-9]+$ ]] && ((10#$port >= 1 && 10#$port <= 65535)) || { echo "The port must be between 1 and 65,535" exit 2 } [[ "$data_path" == /* ]] || { echo "--data-dir Please enter an absolute path"; exit 2; } # Enter the password at runtime; do not write it into the script or command line. read -r -s -p "Test tenant root password: " password printf '\n' password="${password//\\/\\\\}" password="${password//\"/\\\"}" auth="$(printf '[client]\npassword="%s"\n' "$password")" unset password DB=(obclient --defaults-file=/dev/fd/3 -h "$host" -P "$port" -u "$user" -D "$database" -c --skip-reconnect) [[ "$data_path" != *"'"* && "$data_path" != *$'\n'* && "$data_path" != *$'\r'* ]] || { echo "The data path cannot contain single quotes or line breaks." exit 2 } shopt -s nullglob printf 'table\tparallel\tpattern\tload_sql_seconds\tstatus\n' > "$WORK/load_times.tsv" import_start=$(date +%s%N) function load_data { local table_name=$1 table=$1 pattern encoding_clause="" found=false local file output status load_start load_end seconds sql pattern="${table}[.]tbl([.][0-9]+)?" for file in "$data_path/"*; do if [[ -f "$file" && "${file##*/}" =~ ^${pattern}$ ]]; then found=true; break; fi done [[ "$found" == true ]] || { echo "Missing data file for $table_name"; exit 1; } sql="LOAD DATA /*+ parallel(32) direct(false,0) enable_parallel_dml */ FROM FILES(LOCATION='${data_path%/}/', FORMAT=(TYPE='CSV', FIELD_DELIMITER='|'${encoding_clause}, NULL_IF=('', 'NULL'), SKIP_BLANK_LINES=TRUE, IGNORE_LAST_EMPTY_COLUMN=TRUE), PATTERN='${pattern}') INTO TABLE ${table_name};" echo "Importing $table_name" load_start=$(date +%s%N) if output=$(printf '%s\n' "$sql" | "${DB[@]}" 3<<< "$auth" 2>&1); then status=0; else status=$?; fi load_end=$(date +%s%N) seconds=$(awk -v a="$load_start" -v b="$load_end" 'BEGIN {printf "%.6f",(b-a)/1000000000}') if [[ "$status" != 0 || "$output" == *"ERROR "* ]]; then printf '%s\t32\t%s\t%s\tfailed\n' "$table_name" "$pattern" "$seconds" >> "$WORK/load_times.tsv" printf '%s\n' "$output" exit 1 fi printf '%s\t32\t%s\t%s\tsuccess\n' "$table_name" "$pattern" "$seconds" >> "$WORK/load_times.tsv" } for table in region nation supplier customer part partsupp orders lineitem; do load_data "$table" done import_end=$(date +%s%N) total=$(awk -F '\t' 'NR>1 && $5=="success"{s+=$4} END{printf "%.6f",s}' "$WORK/load_times.tsv") wall=$(awk -v a="$import_start" -v b="$import_end" 'BEGIN{printf "%.6f",(b-a)/1000000000}') printf 'metric\tseconds\nsuccessful_load_calls\t%s\nimport_wall\t%s\n' "$total" "$wall" > "$WORK/load_summary.tsv" printf 'Cumulative successful LOAD calls: %s seconds\nTotal import process time: %s seconds\n' "$total" "$wall"Execute the loading script and enter the password as prompted. Press Enter for an empty password.
bash "$WORK/load_data.sh" \ -h "$host_ip" -P "$host_port" -u "$user@$tenant" \ -D tpch_bench --data-dir "$DATA_DIR"
The script imports from local CSV files. NULL_IF parses empty fields as NULL, and IGNORE_LAST_EMPTY_COLUMN handles trailing separators without requiring rewriting of the original data. load_times.tsv records the time consumed and status for each table; load_summary.tsv records the cumulative time spent on successful LOAD calls and the total import process time, excluding data generation, major compactions, and statistics collection. Failed calls are recorded separately, and the import stops if any fail.
Step 7: Initiate a major compaction
Use the root account of the test tenant to connect to the test database and prepare to initiate a major compaction.
obclient -h$host_ip -P$host_port -u$user@$tenant -p -D tpch_bench
First, record the FROZEN_SCN before the major compaction:
SELECT FROZEN_SCN, LAST_SCN FROM oceanbase.DBA_OB_MAJOR_COMPACTION;
Then initiate the current round of major compaction:
ALTER SYSTEM MAJOR FREEZE;
Step 8: Wait for the major compaction to complete
Switch to the sys tenant to view the major compaction progress. Replace bench_tenant in the following SQL with the actual test tenant name.
obclient -h$host_ip -P$host_port -uroot@sys -p -D oceanbase
SELECT t.TENANT_NAME, c.FROZEN_SCN, c.LAST_SCN
FROM oceanbase.DBA_OB_TENANTS t
JOIN oceanbase.CDB_OB_MAJOR_COMPACTION c
ON t.TENANT_ID=c.TENANT_ID
WHERE t.TENANT_NAME='bench_tenant';
Repeat the query until FROZEN_SCN is greater than the value recorded before the major compaction and FROZEN_SCN equals LAST_SCN, then continue collecting statistics. If no records for the test tenant are returned, first check the tenant name and the major compaction status. For field meanings, see Major compaction global information view.
Step 9: Collect statistics
After the major compaction is complete, connect to the test database using the root account of the test tenant and execute the following statistics collection statements.
obclient -h$host_ip -P$host_port -u$user@$tenant -p -D tpch_bench
CALL DBMS_STATS.GATHER_TABLE_STATS('tpch_bench','part',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpch_bench','lineitem',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpch_bench','customer',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpch_bench','orders',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpch_bench','partsupp',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpch_bench','supplier',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpch_bench','nation',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpch_bench','region',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
After statistics collection is complete, perform the test.
Step 10: Execute the test
The Hot and Cold tests use the following script. Execute the generated query files, with the compatibility modifications applied, in the order of Q1 to Q22.
Save the script as $WORK/run_test.sh. Connection information is passed in during execution; passwords for the test tenant and sys tenant are entered interactively. Press Enter for an empty password.
#!/usr/bin/env bash
# run_test.sh: Specifies the test method using the hot or cold option.
set -euo pipefail
set +x
umask 077
WORK="$(cd "$(dirname "$0")" && pwd)"
MODE="${1:-}"
case "$MODE" in
hot|cold) shift ;;
*) echo "Usage: bash run_test.sh hot|cold -h host [-P 2881] -u user@tenant -D database --allow-cache-reset"; exit 2 ;;
esac
DB_HOST=
DB_PORT=2881
DB_USER=
DB_NAME=
ALLOW_CACHE_RESET=false
while (($#)); do
case "$1" in
-h) DB_HOST="${2:?Please enter the host address}"; shift 2 ;;
-P) DB_PORT="${2:?Please enter a port}"; shift 2 ;;
-u) DB_USER="${2:?Please enter user@tenant}"; shift 2 ;;
-D) DB_NAME="${2:?Please enter the database name}"; shift 2 ;;
--allow-cache-reset) ALLOW_CACHE_RESET=true; shift ;;
*) echo "Supported parameters: -h, -P, -u, -D, --allow-cache-reset"; exit 2 ;;
esac
done
[[ -n "$DB_HOST" && -n "$DB_USER" && -n "$DB_NAME" ]] || {
echo "Please specify -h, -u, and -D."
exit 2
}
[[ "$DB_PORT" =~ ^[0-9]+$ ]] && ((10#$DB_PORT >= 1 && 10#$DB_PORT <= 65535)) || {
echo "The port must be between 1 and 65,535"
exit 2
}
[[ "$ALLOW_CACHE_RESET" == true ]] || {
echo "Please add --allow-cache-reset to allow clearing the test tenant cache"
exit 1
}
QUERY_COUNT=22
SAVE_RESULTS=true
# The password is transmitted only through the memory pipeline to OBClient and is not written to a file or command line.
read -r -s -p "Test tenant root password: " DB_PASSWORD
printf '\n'
DB_PASSWORD="${DB_PASSWORD//\\/\\\\}"
DB_PASSWORD="${DB_PASSWORD//\"/\\\"}"
DB_AUTH="$(printf '[client]\npassword="%s"\n' "$DB_PASSWORD")"
unset DB_PASSWORD
DB=(obclient --defaults-file=/dev/fd/3 -h "$DB_HOST" -P "$DB_PORT"
-u "$DB_USER" -D "$DB_NAME" -c --default-character-set=utf8mb4
-B -N -r --skip-reconnect)
for ((i = 1; i <= QUERY_COUNT; i++)); do
printf -v name 'db%d.sql' "$i"
[[ -s "$WORK/queries/$name" && ! -L "$WORK/queries/$name" ]] || {
echo "Missing approved query file $name"
exit 1
}
done
shopt -s nullglob
SQL_FILES=("$WORK/queries/"*.sql)
((${#SQL_FILES[@]} == QUERY_COUNT)) || {
echo "The number of files to query is incorrect"
exit 1
}
# After file count verification, reconstruct the numerical order to prevent glob from placing Q10 before Q2.
SQL_FILES=()
for ((i=1; i<=QUERY_COUNT; i++)); do
printf -v name 'db%d.sql' "$i"
SQL_FILES+=("$WORK/queries/$name")
done
read -r -s -p "sys tenant password: " SYS_PASSWORD
printf '\n'
SYS_PASSWORD="${SYS_PASSWORD//\\/\\\\}"
SYS_PASSWORD="${SYS_PASSWORD//\"/\\\"}"
SYS_AUTH="$(printf '[client]\npassword="%s"\n' "$SYS_PASSWORD")"
unset SYS_PASSWORD
SYS=(obclient --defaults-file=/dev/fd/3 -h "$DB_HOST" -P "$DB_PORT"
-u root@sys -D oceanbase --skip-reconnect)
# Extract the test tenant name from DB_USER. The cache cleanup will only affect this tenant.
login="$DB_USER"
[[ "$login" =~ ^[A-Za-z0-9_]+@([A-Za-z0-9_]+)$ ]] || {
echo "DB_USER should be user@tenant"
exit 1
}
TENANT="${BASH_REMATCH[1]}"
[[ "${TENANT,,}" != sys ]] || {
echo "Unable to clean the sys business cache"
exit 1
}
RESULT_DIR="$WORK/results/$MODE-$(date +%Y%m%d-%H%M%S)-$$"
mkdir -p "$RESULT_DIR/logs" "$RESULT_DIR/$MODE"
sha256sum "${SQL_FILES[@]}" > "$RESULT_DIR/query-sha256.txt"
CACHE_ACTIVE=0
sys_sql() {
local statement="$1" current_err="$RESULT_DIR/logs/cache-current.err"
if ! printf '%s\n' "$statement" | "${SYS[@]}" 3<<< "$SYS_AUTH" > /dev/null 2> "$current_err"; then
cat "$current_err" >> "$RESULT_DIR/logs/cache.err"
return 1
fi
cat "$current_err" >> "$RESULT_DIR/logs/cache.err"
if grep -Eq '^ERROR([[:space:]]|:)' "$current_err"; then
return 1
fi
}
time_query() {
local file="$1" phase="$2" sample="$3" name begin end output
local begin_file end_file begin_command end_command
name="${file##*/}"
output=/dev/null
[[ "$SAVE_RESULTS" != true ]] || output="$RESULT_DIR/$phase/$name.$sample.out"
begin_file="$RESULT_DIR/logs/$phase-$name.$sample.begin"
end_file="$RESULT_DIR/logs/$phase-$name.$sample.end"
rm -f -- "$begin_file" "$end_file"
printf -v begin_command 'date +%%s%%N > %q' "$begin_file"
printf -v end_command 'date +%%s%%N > %q' "$end_file"
# The system command is a local command of OBClient: it starts timing after connection and ends when the query returns.
{
printf 'system %s\n' "$begin_command"
cat "$file"
printf '\nsystem %s\n' "$end_command"
} | "${DB[@]}" --unbuffered 3<<< "$DB_AUTH" \
> "$output" 2> "$RESULT_DIR/logs/$phase-$name.$sample.err"
if grep -Eq '^ERROR([[:space:]]|:)' "$RESULT_DIR/logs/$phase-$name.$sample.err"; then
return 1
fi
[[ -s "$begin_file" && -s "$end_file" ]] || {
echo "Query timing not completed: $name"
return 1
}
begin="$(cat "$begin_file")"
end="$(cat "$end_file")"
[[ "$begin" =~ ^[0-9]+$ && "$end" =~ ^[0-9]+$ ]] && ((end >= begin)) || {
echo "Invalid query timestamp: $name"
return 1
}
QUERY_SECONDS="$(awk -v a="$begin" -v b="$end" 'BEGIN {printf "%.6f",(b-a)/1000000000}')"
}
clear_hot_caches() {
# Only the specified test tenant is cleaned up. The cleanup and waiting time are not included in the query duration.
sys_sql "ALTER SYSTEM FLUSH PLAN CACHE TENANT='$TENANT' GLOBAL;"
sleep 3
sys_sql "ALTER SYSTEM FLUSH KVCACHE TENANT='$TENANT';
ALTER SYSTEM FLUSH KVCACHE TENANT='$TENANT' CACHE 'user_row_cache';"
sleep 3
}
restore_cache() {
local status="$?"
trap - EXIT
if ((CACHE_ACTIVE)); then
if ! sys_sql "ALTER SYSTEM SET _local_cache_expiration_time='0s' TENANT='$TENANT';"; then
echo "Failed to restore cache parameters. Please have the administrator manually verify." >&2
status=1
fi
fi
exit "$status"
}
trap restore_cache EXIT
trap 'exit 130' INT
trap 'exit 143' TERM
if [[ "$MODE" == hot ]]; then
mkdir -p "$RESULT_DIR/warmup"
printf 'phase\tquery_file\tsample\tseconds\tused_in_hot_mean\n' > "$RESULT_DIR/hot-samples.tsv"
printf 'query_file\tseconds\n' > "$RESULT_DIR/hot.tsv"
sys_sql "ALTER SYSTEM SET _local_cache_expiration_time='0s' TENANT='$TENANT';"
# Preheat the system thoroughly first; preheating results are not included in the Hot score.
clear_hot_caches
for file in "${SQL_FILES[@]}"; do
time_query "$file" warmup 1
printf 'warmup\t%s\t1\t%s\tfalse\n' "${file##*/}" "$QUERY_SECONDS" >> "$RESULT_DIR/hot-samples.tsv"
done
# Only the plan, KV, and user_row caches are cleared before a formal hot data promotion; the local cache is not cleared.
clear_hot_caches
for file in "${SQL_FILES[@]}"; do
second=0
third=0
for sample in 1 2 3; do
time_query "$file" hot "$sample"
used=true
[[ "$sample" != 1 ]] || used=false
printf 'hot\t%s\t%s\t%s\t%s\n' "${file##*/}" "$sample" "$QUERY_SECONDS" "$used" >> "$RESULT_DIR/hot-samples.tsv"
[[ "$sample" != 2 ]] || second="$QUERY_SECONDS"
[[ "$sample" != 3 ]] || third="$QUERY_SECONDS"
done
average="$(awk -v a="$second" -v b="$third" 'BEGIN {printf "%.6f",(a+b)/2}')"
printf '%s\t%s\n' "${file##*/}" "$average" >> "$RESULT_DIR/hot.tsv"
done
else
# Cold: Clears the cache for each query and waits before executing it.
printf 'query_file\tseconds\n' > "$RESULT_DIR/cold.tsv"
for file in "${SQL_FILES[@]}"; do
name="${file##*/}"
CACHE_ACTIVE=1
# All cleanup commands explicitly specify the test tenant; cleanup and waiting are completed before timing begins.
sys_sql "ALTER SYSTEM SET _local_cache_expiration_time='1s' TENANT='$TENANT';
ALTER SYSTEM FLUSH PLAN CACHE TENANT='$TENANT' GLOBAL;
ALTER SYSTEM FLUSH KVCACHE TENANT='$TENANT';
ALTER SYSTEM FLUSH KVCACHE TENANT='$TENANT' CACHE 'user_row_cache';"
sleep 3
flushed=false
for attempt in 1 2 3; do
if sys_sql "ALTER SYSTEM FLUSH LOCAL CACHE TENANT='$TENANT';"; then
flushed=true
break
fi
[[ "$attempt" == 3 ]] || sleep 5
done
[[ "$flushed" == true ]] || {
echo "Failed to clear cache, stopping test"
exit 1
}
sleep 5
sys_sql "ALTER SYSTEM SET _local_cache_expiration_time='0s' TENANT='$TENANT';"
CACHE_ACTIVE=0
time_query "$file" cold 1
seconds="$QUERY_SECONDS"
printf '%s\t%s\n' "$name" "$seconds" >> "$RESULT_DIR/cold.tsv"
done
fi
printf 'phase\ttotal_seconds\n' > "$RESULT_DIR/summary.tsv"
awk -F '\t' -v phase="$MODE" 'NR>1{s+=$2} END{printf "%s\t%.6f\n",phase,s}' \
"$RESULT_DIR/$MODE.tsv" >> "$RESULT_DIR/summary.tsv"
printf 'complete\ttrue\nquery_files\t%s\n' "$QUERY_COUNT" > "$RESULT_DIR/status.tsv"
printf '%s completed, result directory: %s\n' "$MODE" "$RESULT_DIR"
cat "$RESULT_DIR/summary.tsv"
Query timing starts after the connection is established, including SQL execution and result reception, but excludes cache clearing and waiting. Multi-statement files are timed as a whole file.
Hot test
The script first warms up all queries, then executes each query three times in a row, and takes the average time of the second and third executions. Clear the plan, KV, and user row cache before warming up and before the formal test, retaining the Local Cache.
set -o pipefail
bash "$WORK/run_test.sh" hot \
-h "$host_ip" -P "$host_port" -u "$user@$tenant" \
-D tpch_bench --allow-cache-reset | tee "$WORK/hot.log"
At the end, the script prints the results directory. Here, hot.tsv contains the average time of each query, hot-samples.tsv contains the execution time of each run, and summary.tsv contains the total Hot time, all in seconds. Query return values are saved in the warmup and hot subdirectories of this directory.
Cold test
Before executing each query, clear the plan, KV, user row, and Local Cache, then execute the query once. When clearing the Local Cache, set the expiration time to 1s, and restore it to 0s after clearing; cache clearing and waiting time is not included in the query time.
set -o pipefail
bash "$WORK/run_test.sh" cold \
-h "$host_ip" -P "$host_port" -u "$user@$tenant" \
-D tpch_bench --allow-cache-reset | tee "$WORK/cold.log"
After the script finishes, it prints the results directory. Here, cold.tsv records the execution time of each query, and summary.tsv records the total cold execution time, both in seconds. The query return values are saved in the cold subdirectory of this directory.