PolarDB:Teste de desempenho para consultas paralelas

Configurar o esquema e carregar dados

1. Crie o arquivo load.ddl no diretório dbgen para definir a sequência de carregamento de dados.

   vim load.ddl

   Pressione i e adicione:

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

   Pressione Esc, digite :wq e pressione Enter.

2. Modifique o dss.ri para definir chaves primárias e estrangeiras. Faça backup do arquivo original e limpe-o:

   cp dss.ri dss_bk.ri
   > dss.ri

   Abra o dss.ri e substitua seu conteúdo:

   vim dss.ri

   Pressione i e adicione:

   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;

   Pressione Esc, digite :wq e pressione Enter.

3. Instale o cliente MySQL.

   Este exemplo usa CentOS. Ajuste o comando para o seu sistema operacional.

   sudo yum install mysql

4. Conecte-se ao cluster PolarDB for MySQL usando a privileged account e o primary endpoint. Para mais informações, consulte Criar uma conta privilegiada e Conectar-se a um banco de dados.

   Se a mensagem Access denied for user 'xxx'@'xxx' (using password: YES) for exibida, o nome de usuário ou a senha estará incorreto.

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

5. Crie o banco de dados tpch100g e mude para ele.

   CREATE DATABASE tpch100g;
   use tpch100g;

6. Crie as tabelas usando o arquivo de esquema dss.ddl no diretório dbgen.

   source ./dss.ddl

7. Carregue os dados usando o arquivo load.ddl criado na etapa 1.

   source ./load.ddl

8. Aplique as chaves primárias e estrangeiras usando o arquivo dss.ri modificado na etapa 2.

   source ./dss.ri

Crie índices (opcional)

Os índices melhoram o desempenho de algumas das 22 consultas TPC-H. Execute exit; para sair do cliente MySQL e, em seguida, crie e execute o seguinte script na instância 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

Salve o script como create_indexes.sh, torne-o executável e execute-o:

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

Substitua os espaços reservados pelos detalhes do seu cluster:

Se a mensagem [Warning] Using a password on the command line interface can be insecure for exibida, isso é esperado. O comando passa a senha como argumento de linha de comando, visível para outros usuários no sistema por meio do comando ps . O aviso não afeta a operação. Após o teste, altere a senha do banco de dados no console PolarDB.

Pré-carregar índices na memória

Para medir o benefício total da consulta paralela, pré-carregue todos os índices no buffer pool antes de executar as consultas.

A consulta paralela ignora o buffer pool durante a execução. Portanto, execuções consecutivas não se beneficiam de dados em cache, e cada execução incorre no custo total de E/S. Isso significa que você pode observar E/S de leitura elevada durante o teste; esse comportamento é esperado e não indica problema. Pré-carregar os índices estabelece um estado inicial consistente para que todas as 22 consultas comecem da mesma linha de base de E/S.

Execute exit; para sair do cliente MySQL e, em seguida, crie e execute o seguinte 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

Execute as 22 consultas TPC-H

Execute exit; para sair do cliente MySQL e, em seguida, crie e execute o seguinte script no diretório 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

Salve o script como run_queries.sh, torne-o executável e execute-o:

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

Substitua os espaços reservados pelos detalhes do seu cluster:

Antes de executar o script, confirme o seguinte:

• O script possui permissões de execução (chmod +x run_queries.sh).

• O diretório atual é dbgen. O script referencia ./queries/Q${i}.sql. Se você executá-lo em um diretório diferente, atualize o caminho queryfile adequadamente. Um caminho incorreto causa o erro ERROR at line 1: Failed to open file 'QXX.sql'.

• Os 22 arquivos de consulta TPC-H estão presentes no diretório dbgen/queries. Baixe-os aqui caso ainda não o tenha feito.

Se a mensagem [Warning] Using a password on the command line interface can be insecure for exibida, isso é esperado. Consulte a nota na seção de criação de índices acima.