更新时间:2024-09-06 GMT+08:00

验证并行查询效果

本章节使用TPCH测试工具测试并行查询对22条QUERY的性能提升情况。

测试的实例信息如下:

  • 实例规格:32 vCPUs | 256 GB
  • 内核版本:2.0.26.1
  • 并行线程数:16
  • 测试数据量:100GB

操作步骤

  1. 生成测试数据。

    1. 请在https://github.com/electrum/tpch-dbgen下载TPCH共用源码。
    2. 请在下载的源码文件中,找到makefile.suite文件,并按照如下内容进行修改,修改完成后进行保存。
      CC      = gcc
      # Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
      #                                  SQLSERVER, SYBASE, ORACLE
      # Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
      #                                  SGI, SUN, U2200, VMS, LINUX, WIN32
      # Current values for WORKLOAD are:  TPCH
      DATABASE= SQLSERVER
      MACHINE = LINUX
      WORKLOAD = TPCH
    3. 在源码根目录下,执行下列命令,编译生成TPCH数据工具dbgen。

      make -f makefile.suite

    4. 使用dbgen执行如下命令,生成TPCH数据100G。

      ./dbgen -s 100

  2. 登录目标GaussDB(for MySQL)实例,创建目标数据库,并使用如下命令创建TPCH的表。

    CREATE TABLE nation  ( N_NATIONKEY  INTEGER NOT NULL,
                           N_NAME       CHAR(25) NOT NULL,
                           N_REGIONKEY  INTEGER NOT NULL,
                           N_COMMENT    VARCHAR(152));
    CREATE TABLE region  ( R_REGIONKEY  INTEGER NOT NULL,
                           R_NAME       CHAR(25) NOT NULL,
                           R_COMMENT    VARCHAR(152));
    CREATE TABLE part    ( P_PARTKEY     INTEGER NOT NULL,
                           P_NAME        VARCHAR(55) NOT NULL,
                           P_MFGR        CHAR(25) NOT NULL,
                           P_BRAND       CHAR(10) NOT NULL,
                           P_TYPE        VARCHAR(25) NOT NULL,
                           P_SIZE        INTEGER NOT NULL,
                           P_CONTAINER   CHAR(10) NOT NULL,
                           P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                           P_COMMENT     VARCHAR(23) NOT NULL );
    CREATE TABLE supplier  ( S_SUPPKEY     INTEGER NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR(40) NOT NULL,
                             S_NATIONKEY   INTEGER NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     DECIMAL(15,2) NOT NULL,
                             S_COMMENT     VARCHAR(101) NOT NULL);
    CREATE TABLE partsupp  ( PS_PARTKEY     INTEGER NOT NULL,
                             PS_SUPPKEY     INTEGER NOT NULL,
                             PS_AVAILQTY    INTEGER NOT NULL,
                             PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
                             PS_COMMENT     VARCHAR(199) NOT NULL );
    CREATE TABLE customer  ( C_CUSTKEY     INTEGER NOT NULL,
                             C_NAME        VARCHAR(25) NOT NULL,
                             C_ADDRESS     VARCHAR(40) NOT NULL,
                             C_NATIONKEY   INTEGER NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR(117) NOT NULL);
    CREATE TABLE orders  ( O_ORDERKEY       INTEGER NOT NULL,
                           O_CUSTKEY        INTEGER NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,
                           O_CLERK          CHAR(15) NOT NULL,
                           O_SHIPPRIORITY   INTEGER NOT NULL,
                           O_COMMENT        VARCHAR(79) NOT NULL);
    CREATE TABLE lineitem ( L_ORDERKEY    INTEGER NOT NULL,
                            L_PARTKEY     INTEGER NOT NULL,
                            L_SUPPKEY     INTEGER NOT NULL,
                            L_LINENUMBER  INTEGER NOT NULL,
                            L_QUANTITY    DECIMAL(15,2) NOT NULL,
                            L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                            L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                            L_TAX         DECIMAL(15,2) NOT NULL,
                            L_RETURNFLAG  CHAR(1) NOT NULL,
                            L_LINESTATUS  CHAR(1) NOT NULL,
                            L_SHIPDATE    DATE NOT NULL,
                            L_COMMITDATE  DATE NOT NULL,
                            L_RECEIPTDATE DATE NOT NULL,
                            L_SHIPINSTRUCT CHAR(25) NOT NULL,
                            L_SHIPMODE     CHAR(10) NOT NULL,
                            L_COMMENT      VARCHAR(44) NOT NULL);

  3. 使用如下命令,将生成的数据导入到TPCH的表中。

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

  4. 创建TPCH表的索引。

    alter table region add primary key (r_regionkey);
    alter table nation add primary key (n_nationkey);
    alter table part add primary key (p_partkey);
    alter table supplier add primary key (s_suppkey);
    alter table partsupp add primary key (ps_partkey,ps_suppkey);
    alter table customer add primary key (c_custkey);
    alter table lineitem add primary key (l_orderkey,l_linenumber);
    alter table orders add primary key (o_orderkey);

  5. 请在https://github.com/dragansah/tpch-dbgen/tree/master/tpch-queries获取TPCH 22个查询query语句,并进行相应操作。

测试结果

通过采用16线程并行查询,其中的17条QUERY的性能得到明显提升,查询速度平均提升10倍以上。TPCH性能测试结果如下图所示。

图1 测试结果