PolarDB:Performance test for parallel queries

Configurar el esquema y cargar los datos

1. Cree el archivo load.ddl en el directorio dbgen para definir la secuencia de carga de datos.

   vim load.ddl

   Presione i y agregue lo siguiente:

   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 '|';

   Presione Esc, escriba :wq y presione Enter.

2. Modifique dss.ri para definir las claves primarias y foráneas. Haga una copia de seguridad del archivo original y vacíelo:

   cp dss.ri dss_bk.ri
   > dss.ri

   Abra dss.ri y reemplace su contenido:

   vim dss.ri

   Presione i y agregue lo siguiente:

   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;

   Presione Esc, escriba :wq y presione Enter.

3. Instale el cliente de MySQL.

   Este ejemplo utiliza CentOS. Ajuste el comando según su sistema operativo.

   sudo yum install mysql

4. Conéctese al clúster de PolarDB for MySQL mediante la privileged account y el primary endpoint. Para obtener más información, consulte Create a privileged account y Connect to a database.

   Si aparece el mensaje Access denied for user 'xxx'@'xxx' (using password: YES) , significa que el nombre de usuario o la contraseña son incorrectos.

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

5. Cree la base de datos tpch100g y acceda a ella.

   CREATE DATABASE tpch100g;
   use tpch100g;

6. Cree las tablas utilizando el archivo de esquema dss.ddl ubicado en el directorio dbgen.

   source ./dss.ddl

7. Cargue los datos con el archivo load.ddl creado en el paso 1.

   source ./load.ddl

8. Aplique las claves primarias y foráneas mediante el archivo dss.ri modificado en el paso 2.

   source ./dss.ri

Crear índices (opcional)

Los índices mejoran el rendimiento de algunas de las 22 consultas de TPC-H. Ejecute exit; para salir del cliente de MySQL y luego cree y ejecute el siguiente script en la instancia ECS.

#!/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

Guarde el script como create_indexes.sh, otórguele permisos de ejecución y ejecútelo:

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

Reemplace los marcadores de posición con los datos de su clúster:

Si aparece la advertencia [Warning] Using a password on the command line interface can be insecure , es un comportamiento esperado. El comando pasa la contraseña como argumento de la línea de comandos, por lo que otros usuarios del sistema podrían verla al ejecutar ps . Esta advertencia no afecta la operación. Una vez terminada la prueba, cambie la contraseña de la base de datos en la consola de PolarDB.

Precargar los índices en memoria

Para medir el beneficio total de la consulta en paralelo, precargue todos los índices en el buffer pool antes de ejecutar las consultas.

La consulta en paralelo omite el buffer pool durante la ejecución. Por ello, las ejecuciones consecutivas no aprovechan los datos almacenados en caché y cada ejecución asume el costo completo de E/S. Esto significa que podría observar un aumento en la E/S de lectura durante la prueba; dicho comportamiento es normal y no representa un problema. Precargar los índices establece un estado inicial consistente para que las 22 consultas partan de la misma línea base de E/S.

Ejecute exit; para salir del cliente de MySQL y luego cree y ejecute el siguiente 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

Ejecutar las 22 consultas de TPC-H

Ejecute exit; para salir del cliente de MySQL y luego cree y ejecute el siguiente script desde el directorio dbgen:

#!/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

Guarde el script como run_queries.sh, otórguele permisos de ejecución y ejecútelo:

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

Reemplace los marcadores de posición con los datos de su clúster:

Antes de ejecutar el script, compruebe lo siguiente:

• El script cuenta con permisos de ejecución (chmod +x run_queries.sh).

• El directorio actual es dbgen. El script hace referencia a ./queries/Q${i}.sql. Si lo ejecuta desde otro directorio, actualice la ruta de queryfile según corresponda. Una ruta incorrecta provoca el error ERROR at line 1: Failed to open file 'QXX.sql'.

• Los 22 archivos de consulta de TPC-H están presentes en el directorio dbgen/queries. Si aún no lo ha hecho, descárguelos aquí.

Si aparece la advertencia [Warning] Using a password on the command line interface can be insecure , es un comportamiento esperado. Consulte la nota en la sección anterior sobre la creación de índices.