PolarDB:Performance test for parallel queries

Set up the schema and load data

1. Create the load.ddl file in the dbgen directory to define the data load sequence.

   vim load.ddl

   Press i and add:

   load data local INFILE 'customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|';
   load data local INFILE 'region.tbl' INTO TABLE region FIELDS TERMINATED BY '|';
   load data local INFILE 'nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|';
   load data local INFILE 'supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|';
   load data local INFILE 'part.tbl' INTO TABLE part FIELDS TERMINATED BY '|';
   load data local INFILE 'partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|';
   load data local INFILE 'orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|';
   load data local INFILE 'lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|';

   Press Esc, type :wq, and press Enter.

2. Modify dss.ri to define primary and foreign keys. Back up the original file and clear it:

   cp dss.ri dss_bk.ri
   > dss.ri

   Open dss.ri and replace its content:

   vim dss.ri

   Press i and add:

   use tpch100g;
   -- ALTER TABLE REGION DROP PRIMARY KEY;
   -- ALTER TABLE NATION DROP PRIMARY KEY;
   -- ALTER TABLE PART DROP PRIMARY KEY;
   -- ALTER TABLE SUPPLIER DROP PRIMARY KEY;
   -- ALTER TABLE PARTSUPP DROP PRIMARY KEY;
   -- ALTER TABLE ORDERS DROP PRIMARY KEY;
   -- ALTER TABLE LINEITEM DROP PRIMARY KEY;
   -- ALTER TABLE CUSTOMER DROP PRIMARY KEY;
   -- For table REGION
   ALTER TABLE REGION
   ADD PRIMARY KEY (R_REGIONKEY);
   -- For table NATION
   ALTER TABLE NATION
   ADD PRIMARY KEY (N_NATIONKEY);
   ALTER TABLE NATION
   ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references REGION(R_REGIONKEY);
   COMMIT WORK;
   -- For table PART
   ALTER TABLE PART
   ADD PRIMARY KEY (P_PARTKEY);
   COMMIT WORK;
   -- For table SUPPLIER
   ALTER TABLE SUPPLIER
   ADD PRIMARY KEY (S_SUPPKEY);
   ALTER TABLE SUPPLIER
   ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references NATION(N_NATIONKEY);
   COMMIT WORK;
   -- For table PARTSUPP
   ALTER TABLE PARTSUPP
   ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
   COMMIT WORK;
   -- For table CUSTOMER
   ALTER TABLE CUSTOMER
   ADD PRIMARY KEY (C_CUSTKEY);
   ALTER TABLE CUSTOMER
   ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references NATION(N_NATIONKEY);
   COMMIT WORK;
   -- For table LINEITEM
   ALTER TABLE LINEITEM
   ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
   COMMIT WORK;
   -- For table ORDERS
   ALTER TABLE ORDERS
   ADD PRIMARY KEY (O_ORDERKEY);
   COMMIT WORK;
   -- For table PARTSUPP
   ALTER TABLE PARTSUPP
   ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references SUPPLIER(S_SUPPKEY);
   COMMIT WORK;
   ALTER TABLE PARTSUPP
   ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references PART(P_PARTKEY);
   COMMIT WORK;
   -- For table ORDERS
   ALTER TABLE ORDERS
   ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references CUSTOMER(C_CUSTKEY);
   COMMIT WORK;
   -- For table LINEITEM
   ALTER TABLE LINEITEM
   ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references ORDERS(O_ORDERKEY);
   COMMIT WORK;
   ALTER TABLE LINEITEM
   ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references
           PARTSUPP(PS_PARTKEY,PS_SUPPKEY);
   COMMIT WORK;

   Press Esc, type :wq, and press Enter.

3. Install the MySQL client.

   This example uses CentOS. Adjust the command for your operating system.

   sudo yum install mysql

4. Connect to the PolarDB for MySQL cluster using the privileged account and the primary endpoint. For more information, see Create a privileged account and Connect to a database.

   If you see Access denied for user 'xxx'@'xxx' (using password: YES), the username or password is incorrect.

   mysql -h <primary-endpoint> -P <port> -u <username> -p <password>

5. Create the tpch100g database and switch to it.

   CREATE DATABASE tpch100g;
   use tpch100g;

6. Create the tables using the dss.ddl schema file in the dbgen directory.

   source ./dss.ddl

7. Load the data using the load.ddl file created in step 1.

   source ./load.ddl

8. Apply primary and foreign keys using the dss.ri file modified in step 2.

   source ./dss.ri

Create indexes (optional)

Indexes improve query performance for some of the 22 TPC-H queries. Run exit; to leave the MySQL client, then create and run the following script on the ECS instance.

#!/usr/bin/bash
host=$1
port=$2
user=$3
password=$4
db=$5
sqls=("create index i_s_nationkey on supplier (s_nationkey);"
"create index i_ps_partkey on partsupp (ps_partkey);"
"create index i_ps_suppkey on partsupp (ps_suppkey);"
"create index i_c_nationkey on customer (c_nationkey);"
"create index i_o_custkey on orders (o_custkey);"
"create index i_o_orderdate on orders (o_orderdate);"
"create index i_l_orderkey on lineitem (l_orderkey);"
"create index i_l_partkey on lineitem (l_partkey);"
"create index i_l_suppkey on lineitem (l_suppkey);"
"create index i_l_partkey_suppkey on lineitem (l_partkey, l_suppkey);"
"create index i_l_shipdate on lineitem (l_shipdate);"
"create index i_l_commitdate on lineitem (l_commitdate);"
"create index i_l_receiptdate on lineitem (l_receiptdate);"
"create index i_n_regionkey on nation (n_regionkey);"
"analyze table supplier"
"analyze table part"
"analyze table partsupp"
"analyze table customer"
"analyze table orders"
"analyze table lineitem"
"analyze table nation"
"analyze table region")
for sql in "${sqls[@]}"
do
    mysql -h$host -P$port -u$user -p$password -D$db  -e "$sql"
done

Save the script as create_indexes.sh, make it executable, and run it:

chmod +x create_indexes.sh
./create_indexes.sh <host> <port> <user> <password> <database>

Replace the placeholders with your cluster details:

If you see [Warning] Using a password on the command line interface can be insecure, this is expected. The command passes the password as a command-line argument, which other users on the system can view by running ps. The warning does not affect the operation. After the test, change the database password in the PolarDB console.

Preload indexes into memory

To measure the full benefit of parallel query, preload all indexes into the buffer pool before running the queries.

Parallel query bypasses the buffer pool during execution, so consecutive query runs do not benefit from cached data — each run incurs the full I/O cost. This means you may see elevated read I/O during the test; that is expected behavior, not a problem. Preloading the indexes establishes a consistent starting state so that all 22 queries begin from the same I/O baseline.

Run exit; to leave the MySQL client, then create and run the following script:

#!/bin/bash
host=$1
port=$2
user=$3
password=$4
dbname=$5
MYSQL="mysql -h$host -P$port -u$user -p$password -D$dbname"
if [ -z ${dbname} ]; then
    echo "dbname not defined."
    exit 1
fi
table_indexes=(
        "supplier PRIMARY"
        "supplier i_s_nationkey"
        "part PRIMARY"
        "partsupp PRIMARY"
        "partsupp i_ps_partkey"
        "partsupp i_ps_suppkey"
        "customer PRIMARY"
        "customer i_c_nationkey"
        "orders PRIMARY"
        "orders i_o_custkey"
        "orders i_o_orderdate"
        "lineitem PRIMARY"
        "lineitem i_l_orderkey"
        "lineitem i_l_partkey"
        "lineitem i_l_suppkey"
        "lineitem i_l_partkey_suppkey"
        "lineitem i_l_shipdate"
        "lineitem i_l_commitdate"
        "lineitem i_l_receiptdate"
        "nation i_n_regionkey"
        "nation PRIMARY"
        "region PRIMARY"
)
for table_index in "${table_indexes[@]}"
do
    ti=($table_index)
    table=${ti[0]}
    index=${ti[1]}
    SQL="select count(*) from ${table} force index(${index})"
    echo "$MYSQL -e '$SQL'"
    $MYSQL -e "$SQL"
done

Run the 22 TPC-H queries

Run exit; to leave the MySQL client, then create and run the following script from the dbgen directory:

#!/usr/bin/env bash
host=$1
port=$2
user=$3
password=$4
database=$5
resfile=$6
echo "start test run at"`date "+%Y-%m-%d %H:%M:%S"`|tee -a ${resfile}.out
for (( i=1; i<=22;i=i+1 ))
do
queryfile="./queries/Q"${i}".sql"
start_time=`date "+%s.%N"`
echo "run query ${i}"|tee -a ${resfile}.out
mysql -h ${host}  -P${port} -u${user} -p${password} $database -e" source $queryfile;" |tee -a ${resfile}.out
end_time=`date "+%s.%N"`
start_s=${start_time%.*}
start_nanos=${start_time#*.}
end_s=${end_time%.*}
end_nanos=${end_time#*.}
if [ "$end_nanos" -lt "$start_nanos" ];then
        end_s=$(( 10#$end_s -1 ))
        end_nanos=$(( 10#$end_nanos + 10 ** 9))
fi
time=$(( 10#$end_s - 10#$start_s )).`printf "%03d\n" $(( (10#$end_nanos - 10#$start_nanos)/10**6 ))`
echo ${queryfile} "the "${j}" run cost "${time}" second start at"`date -d @$start_time "+%Y-%m-%d %H:%M:%S"`" stop at"`date -d @$end_time "+%Y-%m-%d %H:%M:%S"` >> ${resfile}.time
done

Save the script as run_queries.sh, make it executable, and run it:

chmod +x run_queries.sh
./run_queries.sh <host> <port> <user> <password> <database> <resfile>

Replace the placeholders with your cluster details:

Before running the script, confirm the following:

• The script has execute permissions (chmod +x run_queries.sh).

• The current directory is dbgen. The script references ./queries/Q${i}.sql. If you run it from a different directory, update the queryfile path accordingly. An incorrect path causes the error ERROR at line 1: Failed to open file 'QXX.sql'.

• The 22 TPC-H query files are present in the dbgen/queries directory. Download them here if you have not already.

If you see [Warning] Using a password on the command line interface can be insecure, this is expected. See the note in the index creation section above.