This topic describes how to manually run the TPC-DS test in a tenant of OceanBase AI Database by using the TPC-DS tool. The sample data volume is SF1000 (about 1 TB).
What is TPC-DS
TPC-DS (Transaction Processing Performance Council Decision Support Benchmark) is a data warehouse performance testing standard established by the international organization TPC. It adopts a hybrid design of the snowflake and star schemas, comprising 7 fact tables and 17 dimension tables, covering business scenarios such as retail and finance. The test process includes: generating scalable test data (supporting a scale factor from 1 TB to 100 TB), and executing 99 complex SQL queries (covering scenarios such as statistics, reporting, and data mining). Ultimately, it evaluates the system's processing capability under high concurrency and large data volumes through metrics such as QphDS (Queries Per Hour).
As an upgraded version of TPC-H, TPC-DS features a more complex data model (TPC-H has only 8 tables), richer query types, and support for OLAP operations, making it the current mainstream decision support benchmark.
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
Before you perform the test, prepare a test cluster, a test tenant, and OBClient for OceanBase AI Database. In this procedure, tools are installed, data is generated, and scripts are executed on the OBServer host where the data is stored. The host must have gcc, make, unzip, dos2unix, Bash, awk, flex, Bison, and byacc.
This example uses a Linux system with yum support to install dependencies:
sudo yum install -y gcc make unzip dos2unix flex bison byacc
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 is 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 100 GB, change SF to 100:
export SF=1000
export WORK="$HOME/ob-tpcds-sf$SF"
export DATA_DIR="/data/1/tpcds-sf$SF"
umask 077
mkdir -p "$WORK"
cd "$WORK"
Perform a TPC-DS test
The test process is as follows:
- Step 1: Optimize the environment.
- Step 2: Install the TPC-DS 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: Execute the test.
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;
ALTER SYSTEM SET_TP tp_no=571, error_code=4016, frequency=1;
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;
Note
After setting the GLOBAL variable, you need to reconnect to the test tenant.
Step 2: Install the TPC-DS Tool
Download the TPC-DS Tool from the TPC-DS Tool download page.
After downloading, extract the files and navigate to the
toolsdirectory under the extracted TPC-DS directory. Replace the package name with the actual downloaded file name.cd "$WORK" unzip 49FA3DBA-FE6C-463C-952B-62B8E9D43372-TPC-DS-Tool.zip cd DSGen-software-code-3.2.0rc1/toolsCompile the files. Add
-fcommonto the CFLAGS inmakefileto ensure compatibility with GCC 10 and later.sed -i -E '/^CFLAGS[[:space:]]*=/ s/=/= -fcommon/' makefile makeAfter successful compilation, the binary executables
dsdgenanddsqgenare generated in thetoolsdirectory.
Step 3: Generate data
Use the official dsdgen to generate data in parallel by using 20 threads. The data scale is specified by SF set in Prepare the environment. Use a new empty data directory. Do not replace empty fields or dates in the data.
(
set -e
SCALE_FACTOR="$SF"
PARALLEL=20
DATA_DIR="$DATA_DIR"
mkdir "$DATA_DIR"
cd "$WORK/DSGen-software-code-3.2.0rc1/tools"
pids=()
for ((child=1; child<=PARALLEL; child++)); do
./dsdgen -scale "$SCALE_FACTOR" -parallel "$PARALLEL" \
-child "$child" -dir "$DATA_DIR" \
> "$WORK/dsdgen-${child}.log" 2>&1 &
pids+=("$!")
done
failed=0
for pid in "${pids[@]}"; do wait "$pid" || failed=1; done
[[ "$failed" == 0 ]] || { echo "Failed to generate data. Please check the dsdgen logs."; exit 1; }
chmod 755 "$DATA_DIR"
chmod 644 "$DATA_DIR/"*.dat
)
Step 4: Generate query SQL
Create a directory for storing the test SQL statements.
mkdir -p "$WORK/queries" cd "$WORK/DSGen-software-code-3.2.0rc1/query_templates"Modify the template files by adding
define _END = "";. Create and executequery.sh.#!/bin/bash for i in query*.tpl do echo 'define _END = "";' >> "$i" donebash query.shUse dsqgen to generate test SQL statements. Go to the
toolsdirectory and createsql-ds.sh.cd "$WORK/DSGen-software-code-3.2.0rc1/tools"#!/bin/bash set -e for i in $(seq 1 99) do ./dsqgen -directory ../query_templates/ -template "query${i}.tpl" \ -dialect netezza -scale "$SF" -output "$WORK/queries/" cp "$WORK/queries/query_0.sql" "$WORK/queries/sql${i}.sql" done rm "$WORK/queries/query_0.sql"bash sql-ds.shAdjust the query SQL statements.
- Increase parallelism. Add
/*+ parallel(96) */to the query statement. - Adjust date functions. Modify the date function in the SQL statement from
(cast('2025-03-04' as date) + 30 days)todate_add(cast('2025-03-04' as date),interval 30 day)or(cast('2025-03-04' as date) + INTERVAL 30 day). - Similarly, adjust
(cast ('2025-04-18' as date) - 30 days)todate_sub(cast ('2025-04-18' as date),interval 30 day)or(cast('2025-04-18' as date) - INTERVAL 30 day). - Adjust the usage of the rollup function. Change the rollup function usage in the SQL statement to
group by <col_name> with rollup. - Replace the string concatenation operator
||withCONCAT(). For example, change'store' || s_store_idtoCONCAT('store', s_store_id). In Q84, retain the originalCOALESCE()as a parameter forCONCAT(). - Replace
c_last_review_date_skin Q30 withc_last_review_datein this table structure, and modify the references in SELECT and ORDER BY accordingly.
The generated files are
sql1.sqlthroughsql99.sql.- Increase parallelism. Add
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 tpcds_bench DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; USE tpcds_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 the table schema file
$WORK/schema.sqland write the following content.CREATE TABLE `dbgen_version` ( dv_version varchar(16) , dv_create_date date , dv_create_time varchar(20) , dv_cmdline_args varchar(200) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `customer_address` ( ca_address_sk integer not null, ca_address_id char(16) not null, ca_street_number char(10) , ca_street_name varchar(60) , ca_street_type char(15) , ca_suite_number char(10) , ca_city varchar(60) , ca_county varchar(30) , ca_state char(2) , ca_zip char(10) , ca_country varchar(20) , ca_gmt_offset decimal(5,2) , ca_location_type char(20) , primary key (ca_address_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(ca_address_sk) PARTITIONS 192 WITH COLUMN GROUP(each column); CREATE TABLE `customer_demographics` ( cd_demo_sk integer not null, cd_gender char(1) , cd_marital_status char(1) , cd_education_status char(20) , cd_purchase_estimate integer , cd_credit_rating char(10) , cd_dep_count integer , cd_dep_employed_count integer , cd_dep_college_count integer , primary key (cd_demo_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(cd_demo_sk) PARTITIONS 192 WITH COLUMN GROUP(each column); CREATE TABLE `date_dim` ( d_date_sk integer not null, d_date_id char(16) not null, d_date date not null, d_month_seq integer not null, d_week_seq integer not null, d_quarter_seq integer not null, d_year integer not null, d_dow integer not null, d_moy integer not null, d_dom integer not null, d_qoy integer not null, d_fy_year integer not null, d_fy_quarter_seq integer not null, d_fy_week_seq integer not null, d_day_name char(9) not null, d_quarter_name char(6) not null, d_holiday char(1) not null, d_weekend char(1) not null, d_following_holiday char(1) not null, d_first_dom integer not null, d_last_dom integer not null, d_same_day_ly integer not null, d_same_day_lq integer not null, d_current_day char(1) not null, d_current_week char(1) not null, d_current_month char(1) not null, d_current_quarter char(1) not null, d_current_year char(1) not null, primary key (d_date_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `warehouse` ( w_warehouse_sk integer not null, w_warehouse_id char(16) not null, w_warehouse_name varchar(20) , w_warehouse_sq_ft integer , w_street_number char(10) , w_street_name varchar(60) , w_street_type char(15) , w_suite_number char(10) , w_city varchar(60) , w_county varchar(30) , w_state char(2) , w_zip char(10) , w_country varchar(20) , w_gmt_offset decimal(5,2) , primary key (w_warehouse_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `ship_mode` ( sm_ship_mode_sk integer not null, sm_ship_mode_id char(16) not null, sm_type char(30) , sm_code char(10) , sm_carrier char(20) , sm_contract char(20) , primary key (sm_ship_mode_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `time_dim` ( t_time_sk integer not null, t_time_id char(16) not null, t_time integer , t_hour integer , t_minute integer , t_second integer , t_am_pm char(2) , t_shift char(20) , t_sub_shift char(20) , t_meal_time char(20) , primary key (t_time_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `reason` ( r_reason_sk integer not null, r_reason_id char(16) not null, r_reason_desc char(100) , primary key (r_reason_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `income_band` ( ib_income_band_sk integer not null, ib_lower_bound integer , ib_upper_bound integer , primary key (ib_income_band_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `item` ( i_item_sk integer not null, i_item_id char(16) not null, i_rec_start_date date , i_rec_end_date date , i_item_desc varchar(200) , i_current_price decimal(7,2) , i_wholesale_cost decimal(7,2) , i_brand_id integer , i_brand char(50) , i_class_id integer , i_class char(50) , i_category_id integer , i_category char(50) , i_manufact_id integer , i_manufact char(50) , i_size char(20) , i_formulation char(20) , i_color char(20) , i_units char(10) , i_container char(10) , i_manager_id integer , i_product_name char(50) , primary key (i_item_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `store` ( s_store_sk integer not null, s_store_id char(16) not null, s_rec_start_date date , s_rec_end_date date , s_closed_date_sk integer , s_store_name varchar(50) , s_number_employees integer , s_floor_space integer , s_hours char(20) , s_manager varchar(40) , s_market_id integer , s_geography_class varchar(100) , s_market_desc varchar(100) , s_market_manager varchar(40) , s_division_id integer , s_division_name varchar(50) , s_company_id integer , s_company_name varchar(50) , s_street_number varchar(10) , s_street_name varchar(60) , s_street_type char(15) , s_suite_number char(10) , s_city varchar(60) , s_county varchar(30) , s_state char(2) , s_zip char(10) , s_country varchar(20) , s_gmt_offset decimal(5,2) , s_tax_precentage decimal(5,2) , primary key (s_store_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `call_center` ( cc_call_center_sk int not null, cc_call_center_id char(16) not null, cc_rec_start_date date , cc_rec_end_date date , cc_closed_date_sk int , cc_open_date_sk int , cc_name varchar(50) , cc_class varchar(50) , cc_employees int , cc_sq_ft int , cc_hours char(20) , cc_manager varchar(40) , cc_mkt_id int , cc_mkt_class char(50) , cc_mkt_desc varchar(100) , cc_market_manager varchar(40) , cc_division int , cc_division_name varchar(50) , cc_company int , cc_company_name char(50) , cc_street_number char(10) , cc_street_name varchar(60) , cc_street_type char(15) , cc_suite_number char(10) , cc_city varchar(60) , cc_county varchar(30) , cc_state char(2) , cc_zip char(10) , cc_country varchar(20) , cc_gmt_offset decimal(5,2) , cc_tax_percentage decimal(5,2) , primary key (cc_call_center_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `customer` ( c_customer_sk integer not null, c_customer_id char(16) not null, c_current_cdemo_sk integer , c_current_hdemo_sk integer , c_current_addr_sk integer , c_first_shipto_date_sk integer , c_first_sales_date_sk integer , c_salutation char(10) , c_first_name char(20) , c_last_name char(30) , c_preferred_cust_flag char(1) , c_birth_day integer , c_birth_month integer , c_birth_year integer , c_birth_country varchar(20) , c_login char(13) , c_email_address char(50) , c_last_review_date char(10) , primary key (c_customer_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(c_customer_sk) PARTITIONS 192 WITH COLUMN GROUP(each column); CREATE TABLE `web_site` ( web_site_sk integer not null, web_site_id char(16) not null, web_rec_start_date date , web_rec_end_date date , web_name varchar(50) , web_open_date_sk integer , web_close_date_sk integer , web_class varchar(50) , web_manager varchar(40) , web_mkt_id integer , web_mkt_class varchar(50) , web_mkt_desc varchar(100) , web_market_manager varchar(40) , web_company_id integer , web_company_name char(50) , web_street_number char(10) , web_street_name varchar(60) , web_street_type char(15) , web_suite_number char(10) , web_city varchar(60) , web_county varchar(30) , web_state char(2) , web_zip char(10) , web_country varchar(20) , web_gmt_offset decimal(5,2) , web_tax_percentage decimal(5,2) , primary key (web_site_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `store_returns` ( sr_returned_date_sk integer , sr_return_time_sk integer , sr_item_sk integer not null, sr_customer_sk integer , sr_cdemo_sk integer , sr_hdemo_sk integer , sr_addr_sk integer , sr_store_sk integer , sr_reason_sk integer , sr_ticket_number integer not null, sr_return_quantity integer , sr_return_amt decimal(7,2) , sr_return_tax decimal(7,2) , sr_return_amt_inc_tax decimal(7,2) , sr_fee decimal(7,2) , sr_return_ship_cost decimal(7,2) , sr_refunded_cash decimal(7,2) , sr_reversed_charge decimal(7,2) , sr_store_credit decimal(7,2) , sr_net_loss decimal(7,2) ) ORGANIZATION HEAP CLUSTER BY (`sr_returned_date_sk`, `sr_item_sk`, `sr_ticket_number`) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(sr_item_sk, sr_ticket_number) PARTITIONS 192 WITH COLUMN GROUP(each column); CREATE TABLE `household_demographics` ( hd_demo_sk integer not null, hd_income_band_sk integer , hd_buy_potential char(15) , hd_dep_count integer , hd_vehicle_count integer , primary key (hd_demo_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `web_page` ( wp_web_page_sk integer not null, wp_web_page_id char(16) not null, wp_rec_start_date date , wp_rec_end_date date , wp_creation_date_sk integer , wp_access_date_sk integer , wp_autogen_flag char(1) , wp_customer_sk integer , wp_url varchar(100) , wp_type char(50) , wp_char_count integer , wp_link_count integer , wp_image_count integer , wp_max_ad_count integer , primary key (wp_web_page_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `promotion` ( p_promo_sk integer not null, p_promo_id char(16) not null, p_start_date_sk integer , p_end_date_sk integer , p_item_sk integer , p_cost decimal(15,2) , p_response_target integer , p_promo_name char(50) , p_channel_dmail char(1) , p_channel_email char(1) , p_channel_catalog char(1) , p_channel_tv char(1) , p_channel_radio char(1) , p_channel_press char(1) , p_channel_event char(1) , p_channel_demo char(1) , p_channel_details varchar(100) , p_purpose char(15) , p_discount_active char(1) , primary key (p_promo_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `catalog_page` ( cp_catalog_page_sk int not null, cp_catalog_page_id char(16) not null, cp_start_date_sk int , cp_end_date_sk int , cp_department varchar(50) , cp_catalog_number int , cp_catalog_page_number int , cp_description varchar(100) , cp_type varchar(100) , primary key (cp_catalog_page_sk) ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column); CREATE TABLE `inventory` ( inv_date_sk integer not null, inv_item_sk integer not null, inv_warehouse_sk integer not null, inv_quantity_on_hand integer ) ORGANIZATION HEAP CLUSTER BY (`inv_date_sk`, `inv_item_sk`, `inv_warehouse_sk`) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(inv_item_sk) PARTITIONS 192 WITH COLUMN GROUP(each column); CREATE TABLE `catalog_returns` ( cr_returned_date_sk integer , cr_returned_time_sk integer , cr_item_sk integer not null, cr_refunded_customer_sk integer , cr_refunded_cdemo_sk integer , cr_refunded_hdemo_sk integer , cr_refunded_addr_sk integer , cr_returning_customer_sk integer , cr_returning_cdemo_sk integer , cr_returning_hdemo_sk integer , cr_returning_addr_sk integer , cr_call_center_sk integer , cr_catalog_page_sk integer , cr_ship_mode_sk integer , cr_warehouse_sk integer , cr_reason_sk integer , cr_order_number integer not null, cr_return_quantity integer , cr_return_amount decimal(7,2) , cr_return_tax decimal(7,2) , cr_return_amt_inc_tax decimal(7,2) , cr_fee decimal(7,2) , cr_return_ship_cost decimal(7,2) , cr_refunded_cash decimal(7,2) , cr_reversed_charge decimal(7,2) , cr_store_credit decimal(7,2) , cr_net_loss decimal(7,2) ) ORGANIZATION HEAP CLUSTER BY (`cr_returned_date_sk`, `cr_item_sk`, `cr_order_number`) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(cr_item_sk, cr_order_number) PARTITIONS 192 WITH COLUMN GROUP(each column); CREATE TABLE `web_returns` ( wr_returned_date_sk integer , wr_returned_time_sk integer , wr_item_sk integer not null, wr_refunded_customer_sk integer , wr_refunded_cdemo_sk integer , wr_refunded_hdemo_sk integer , wr_refunded_addr_sk integer , wr_returning_customer_sk integer , wr_returning_cdemo_sk integer , wr_returning_hdemo_sk integer , wr_returning_addr_sk integer , wr_web_page_sk integer , wr_reason_sk integer , wr_order_number integer not null, wr_return_quantity integer , wr_return_amt decimal(7,2) , wr_return_tax decimal(7,2) , wr_return_amt_inc_tax decimal(7,2) , wr_fee decimal(7,2) , wr_return_ship_cost decimal(7,2) , wr_refunded_cash decimal(7,2) , wr_reversed_charge decimal(7,2) , wr_account_credit decimal(7,2) , wr_net_loss decimal(7,2) ) ORGANIZATION HEAP CLUSTER BY (`wr_returned_date_sk`, `wr_item_sk`, `wr_order_number`) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(wr_item_sk, wr_order_number) PARTITIONS 192 WITH COLUMN GROUP(each column); CREATE TABLE `web_sales` ( ws_sold_date_sk integer , ws_sold_time_sk integer , ws_ship_date_sk integer , ws_item_sk integer not null, ws_bill_customer_sk integer , ws_bill_cdemo_sk integer , ws_bill_hdemo_sk integer , ws_bill_addr_sk integer , ws_ship_customer_sk integer , ws_ship_cdemo_sk integer , ws_ship_hdemo_sk integer , ws_ship_addr_sk integer , ws_web_page_sk integer , ws_web_site_sk integer , ws_ship_mode_sk integer , ws_warehouse_sk integer , ws_promo_sk integer , ws_order_number integer not null, ws_quantity integer , ws_wholesale_cost decimal(7,2) , ws_list_price decimal(7,2) , ws_sales_price decimal(7,2) , ws_ext_discount_amt decimal(7,2) , ws_ext_sales_price decimal(7,2) , ws_ext_wholesale_cost decimal(7,2) , ws_ext_list_price decimal(7,2) , ws_ext_tax decimal(7,2) , ws_coupon_amt decimal(7,2) , ws_ext_ship_cost decimal(7,2) , ws_net_paid decimal(7,2) , ws_net_paid_inc_tax decimal(7,2) , ws_net_paid_inc_ship decimal(7,2) , ws_net_paid_inc_ship_tax decimal(7,2) , ws_net_profit decimal(7,2) ) ORGANIZATION HEAP CLUSTER BY (`ws_sold_date_sk`, `ws_item_sk`, `ws_order_number`) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(ws_item_sk, ws_order_number) PARTITIONS 192 WITH COLUMN GROUP(each column); CREATE TABLE `catalog_sales` ( cs_sold_date_sk integer , cs_sold_time_sk integer , cs_ship_date_sk integer , cs_bill_customer_sk integer , cs_bill_cdemo_sk integer , cs_bill_hdemo_sk integer , cs_bill_addr_sk integer , cs_ship_customer_sk integer , cs_ship_cdemo_sk integer , cs_ship_hdemo_sk integer , cs_ship_addr_sk integer , cs_call_center_sk integer , cs_catalog_page_sk integer , cs_ship_mode_sk integer , cs_warehouse_sk integer , cs_item_sk integer not null, cs_promo_sk integer , cs_order_number integer not null, cs_quantity integer , cs_wholesale_cost decimal(7,2) , cs_list_price decimal(7,2) , cs_sales_price decimal(7,2) , cs_ext_discount_amt decimal(7,2) , cs_ext_sales_price decimal(7,2) , cs_ext_wholesale_cost decimal(7,2) , cs_ext_list_price decimal(7,2) , cs_ext_tax decimal(7,2) , cs_coupon_amt decimal(7,2) , cs_ext_ship_cost decimal(7,2) , cs_net_paid decimal(7,2) , cs_net_paid_inc_tax decimal(7,2) , cs_net_paid_inc_ship decimal(7,2) , cs_net_paid_inc_ship_tax decimal(7,2) , cs_net_profit decimal(7,2) ) ORGANIZATION HEAP CLUSTER BY (`cs_sold_date_sk`, `cs_item_sk`, `cs_order_number`) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(cs_item_sk) PARTITIONS 192 WITH COLUMN GROUP(each column); CREATE TABLE `store_sales` ( ss_sold_date_sk integer , ss_sold_time_sk integer , ss_item_sk integer not null, ss_customer_sk integer , ss_cdemo_sk integer , ss_hdemo_sk integer , ss_addr_sk integer , ss_store_sk integer , ss_promo_sk integer , ss_ticket_number integer not null, ss_quantity integer , ss_wholesale_cost decimal(7,2) , ss_list_price decimal(7,2) , ss_sales_price decimal(7,2) , ss_ext_discount_amt decimal(7,2) , ss_ext_sales_price decimal(7,2) , ss_ext_wholesale_cost decimal(7,2) , ss_ext_list_price decimal(7,2) , ss_ext_tax decimal(7,2) , ss_coupon_amt decimal(7,2) , ss_net_paid decimal(7,2) , ss_net_paid_inc_tax decimal(7,2) , ss_net_profit decimal(7,2) ) ORGANIZATION HEAP CLUSTER BY (`ss_sold_date_sk`, `ss_item_sk`, `ss_ticket_number`) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 PARTITION BY KEY(ss_item_sk) PARTITIONS 192 WITH COLUMN GROUP(each column);Execute the table creation file:
obclient -h$host_ip -P$host_port -u$user@$tenant -p \ -D tpcds_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}(_[0-9]+_[0-9]+)?[.]dat" 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; } if [[ "$table_name" == customer || "$table_name" == store ]]; then encoding_clause=", ENCODING='latin1'" fi 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 dbgen_version date_dim warehouse ship_mode time_dim reason income_band item call_center web_site household_demographics web_page promotion catalog_page store customer_address customer_demographics customer store_returns inventory catalog_returns web_returns web_sales catalog_sales store_sales; 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 when prompted; press Enter for an empty password.
bash "$WORK/load_data.sh" \ -h "$host_ip" -P "$host_port" -u "$user@$tenant" \ -D tpcds_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 compaction, and statistical information 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 tpcds_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 statement 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 definitions, see Global information views for major compaction.
Step 9: Collect statistics
After the major compaction is complete, use the root account of the test tenant to connect to the test database and execute the following statements to collect statistics.
obclient -h$host_ip -P$host_port -u$user@$tenant -p -D tpcds_bench
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','call_center',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','catalog_page',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','catalog_returns',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','catalog_sales',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','customer',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','customer_address',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','customer_demographics',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','date_dim',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','household_demographics',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','income_band',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','inventory',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','item',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','promotion',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','reason',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','ship_mode',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','store',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','store_returns',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','store_sales',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','time_dim',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','warehouse',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','web_page',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','web_returns',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','web_sales',degree=>128,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE 128');
CALL DBMS_STATS.GATHER_TABLE_STATS('tpcds_bench','web_site',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 following script is used for both Hot and Cold tests. Use the query files generated earlier with compatibility modifications applied, and execute them in the order of Q1 to Q99.
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, or you can 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 the -h, -u, and -D options."
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=99
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 'sql%d.sql' "$i"
[[ -s "$WORK/queries/$name" && ! -L "$WORK/queries/$name" ]] || {
echo "Missing audited 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 'sql%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 up 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. Stop the 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 flushing and waiting. Multi-statement files are timed as a whole.
Hot test
The script first preheats all queries, then executes each query three times in a row, and takes the average time of the second and third executions. Before preheating and before the formal test, clean up the plan, KV, and user row caches, while retaining the Local Cache.
set -o pipefail
bash "$WORK/run_test.sh" hot \
-h "$host_ip" -P "$host_port" -u "$user@$tenant" \
-D tpcds_bench --allow-cache-reset | tee "$WORK/hot.log"
After the script finishes, it prints the result directory. Here, hot.tsv contains the average time for each query, hot-samples.tsv contains the execution times for each run, and summary.tsv contains the total Hot test time, all in seconds. Query return values are saved in the warmup and hot subdirectories within this directory.
Cold test
Before executing each query, clean up the plan, KV, user row, and Local caches, then execute the query once. When clearing the Local Cache, set the expiration time to 1s, and reset it to 0s after clearing; cache flushing and waiting time are not included in the query execution time.
set -o pipefail
bash "$WORK/run_test.sh" cold \
-h "$host_ip" -P "$host_port" -u "$user@$tenant" \
-D tpcds_bench --allow-cache-reset | tee "$WORK/cold.log"
After the script finishes, it prints the result directory. Here, cold.tsv contains the execution time for each query, and summary.tsv contains the total Cold test time, both in seconds. Query return values are saved in the cold subdirectory within this directory.