发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

使用TPC-H 进行GreatSQL并行查询测试

浅浅妈妈

4

主题

4

帖子

12

积分

新手上路

Rank: 1

积分
12
显示全部楼层
准备工作

数据库版本

GreatSQL-8.0.25-17
生成数据

使用 TPC-H 生成数据
  1. #TPC-H Population Generator (Version 3.0.0)
  3. #生成10G的数据
  4. $ ./dbgen -vf -s 10
复制代码
修改my.cnf
  1. vim /etc/my.cnf
  3. #设置IPB为8G
  4. innodb_buffer_pool_size = 8G
  6. #设置并行查询的使用最大内存(此处为8G，根据具体配置设置)
  7. parallel_memory_limit= 8G
  9. #打开并行查询
  10. force_parallel_execute=1
  12. #设置双1（方便导入数据）
  13. innodb_flush_log_at_trx_commit = 1
  14. sync_binlog = 1
  16. #关闭binlog
  17. skip-log_bin
  19. datadir    = /data/GreatSQL
  20. socket    = mysql.sock
复制代码
启动数据库后，可以检查配置是否生效
  1. mysql> show variables like '%double%';
  2. mysql> show variables like 'log_bin';
  3. mysql> show variables like 'sync_binlog';
  4. mysql> show variables like 'innodb_flush_log_at_trx_commit';
  5. mysql> show variables like 'innodb_buffer_pool_size';
复制代码
并行查询相关参数
  1. mysql> show global variables like '%parall%';
  2. +----------------------------------+----------------+
  3. | force_parallel_execute           | ON             |
  4. | innodb_parallel_dblwr_encrypt    | OFF            |
  5. | innodb_parallel_doublewrite_path | xb_doublewrite |
  6. | innodb_parallel_read_threads     | 4              |
  7. | parallel_cost_threshold          | 1000           |
  8. | parallel_default_dop             | 4              |
  9. | parallel_max_threads             | 64             |
  10. | parallel_memory_limit            | 8589934592     |
  11. | parallel_queue_timeout           | 0              |
  12. | slave_parallel_type              | LOGICAL_CLOCK  |
  13. | slave_parallel_workers           | 2              |
  14. +----------------------------------+----------------+
  15. 11 rows in set (0.01 sec)
复制代码
启动数据库

启动数据库：
  1. $ systemctl start greatsql.service
复制代码
文件准备

本次的工作在/data/tpch
可执行程序为dbgen,依赖一个数据分布文件dists.dss。可以将dbgen和dists.dss拷贝到同一目录使用
dss.ddl 和 dss.ri 文件
准备表结构和索引文件 dss.ddl 和 dss.ri 到工作目录
  1. $ cd /data/tpch/tpch_2.18.0/dbgen
  2. $ cp dss.ri /data/tpch/
  3. $ cp dss.ddl /data/tpch/
复制代码
load.sql
修改 load.sql 文件 修改文件的路径
  1. $ cd /data/tpch/
  2. $ cp load.sql loadfix.sql
  3. $ vim loadfix.sql
复制代码
导入数据
  1. $ /usr/localGreatSQL-8.0.25-17/bin/mysql -uroot -S /data/GreatSQL/mysql.sock
  3. #创建数据库
  4. mysql> create database tpch;
  5. mysql> use tpch;
  7. #导入表结构
  8. mysql> source /data/tpch/dss.ddl;
  10. #导入数据（文件见附录）
  11. mysql> sh loadfile
  13. #导入索引、外键等
  14. mysql> source /data/tpch/dssfix.ri
复制代码
注：binlog要关再导入,否则binlog会爆
  1. Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
复制代码
查看导入的表（20G数据）
  1. mysql> select table_name,table_rows
  2. from information_schema.tables
  3. where table_name in
  4. ('customer','lineitem','nation','orders','part','partsupp','region','supplier');
  6. +------------+------------+
  7. | TABLE_NAME | TABLE_ROWS |
  8. +------------+------------+
  9. | region     |          5 |
  10. | nation     |         25 |
  11. | part       |    3860136 |
  12. | supplier   |     197853 |
  13. | customer   |    2884322 |
  14. | partsupp   |   17084176 |
  15. | orders     |   29678499 |
  16. | lineitem   |   87786966 |
  17. +------------+------------+
  18. 8 rows in set (0.00 sec)
复制代码
编写并运行测试脚本
  1. #测试脚本见附录
  2. #这里是在tmux中运行，避免因为终端关闭导致测试终止
  3. $ sh auto.sh&
  5. #测试结束后在当前脚本的目录查看生成的日志
  6. $ cat tpch-PQ-******.log
复制代码
测试运行时，观察相关指标。
  1. mysql> show global status like '%PQ%';
  2. +--------------------+-------+
  3. | Variable_name      | Value |
  4. +--------------------+-------+
  5. | PQ_memory_refused  | 0     |
  6. | PQ_memory_used     | 0     |
  7. | PQ_threads_refused | 0     |
  8. | PQ_threads_running | 0     |
  9. +--------------------+-------+
  10. 4 rows in set (0.00 sec)
  13. mysql> show processlist;
  14. mysql> explain for connection **;
复制代码
测试结果

开启并行查询（16线程）的执行时间，与不开启并行查询的执行时间如下：
SQL1SQL3SQL5SQL6SQL10SQL12SQL19PQ161m25.645s1m5.514s8m56.306s35.451s44.564s59.115s5.771sNOPQ6m1.724s5m19.083s37m42.078s2m16.331s1m57.998s2m39.672s24.907s注：本文章重点讲测试过程，具体的测试结果就不展开了。
**
**
附录-相关文件
导入脚本
  1. $ cat loadfile
  2. /usr/localGreatSQL-8.0.25-17/bin/mysql -S /data/GreatSQL/mysql.sock -Dtpch -f -e "set session foreign_key_checks=0;load data infile '/data/tpch/data/region.tbl' into table region FIELDS TERMINATED BY '|';" tpch &
  3. /usr/localGreatSQL-8.0.25-17/bin/mysql -S /data/GreatSQL/mysql.sock -Dtpch -f -e "set session foreign_key_checks=0;load data infile '/data/tpch/data/nation.tbl' into table nation FIELDS TERMINATED BY '|';" tpch &
  4. /usr/localGreatSQL-8.0.25-17/bin/mysql -S /data/GreatSQL/mysql.sock -Dtpch-f -e "set session foreign_key_checks=0;load data infile '/data/tpch/data/supplier.tbl' into table supplier FIELDS TERMINATED BY '|';" tpch &
  5. /usr/localGreatSQL-8.0.25-17/bin/mysql -S /data/GreatSQL/mysql.sock -Dtpch -f -e "set session foreign_key_checks=0;load data infile '/data/tpch/data/part.tbl' into table part FIELDS TERMINATED BY '|';" tpch &
  6. /usr/localGreatSQL-8.0.25-17/bin/mysql -S /data/GreatSQL/mysql.sock -Dtpch -f -e "set session foreign_key_checks=0;load data infile '/data/tpch/data/customer.tbl' into table customer FIELDS TERMINATED BY '|';" tpch &
  7. /usr/localGreatSQL-8.0.25-17/bin/mysql -S /data/GreatSQL/mysql.sock -Dtpch -f -e "set session foreign_key_checks=0;load data infile '/data/tpch/data/partsupp.tbl' into table partsupp FIELDS TERMINATED BY '|';" tpch &
  8. /usr/localGreatSQL-8.0.25-17/bin/mysql -S /data/GreatSQL/mysql.sock -Dtpch-f -e "set session foreign_key_checks=0;load data infile '/data/tpch/data/orders.tbl' into table orders FIELDS TERMINATED BY '|';" tpch &
  9. /usr/localGreatSQL-8.0.25-17/bin/mysql -S /data/GreatSQL/mysql.sock -Dtpch-f -e "set session foreign_key_checks=0;load data infile '/data/tpch/data/lineitem.tbl' into table lineitem FIELDS TERMINATED BY '|';" tpch &
复制代码
测试脚本：

脚本是东拼西凑的，写的不好，希望有大佬能指导一下
  1. $ cat auto.sh
  2. #include <iostream>TH=$PATH:/usr/local/bin
  3. export PATH
  4. #set -u
  5. #set -x
  6. #set -e
  7. . ~/.bash_profile > /dev/null 2>&1
  8. exec 3>&1 4>&2 1>> tpch-PQ-`date +'%Y%m%d%H%M%S'`.log 2>&1
  10. # 定义要执行的SQL文件存放的目录
  11. SQL_DIR="/data/tpch/SQLs"
  13. # 判断目录是否存在
  14. if [ ! -d "$SQL_DIR" ]; then
  15.     echo "SQL文件目录不存在！"
  16.     exit 1
  17. fi
  19. # 进入SQL文件目录
  20. cd $SQL_DIR
  23. I=1
  24. II=3
  25. while [ $I -le $II ]
  26. do
  27.     # 执行SQL文件
  28.     for file in `ls *.sql`
  29.     do
  30.         echo "正在执行：$file"
  31.         time /usr/localGreatSQL-8.0.25-17/bin/mysql -uroot -S /data/GreatSQL/mysql.sock -Dtpch < $file
  32.         
  33.         echo "SQL：$file,执行完成"
  34.         echo -e
  35.         echo "休息100s"
  36.         sleep 100
  37.         echo -e
  38.     done
  39.     echo "第$I次循环执行完成！"
  40. I=`expr $I + 1`
  41. done
  42. echo "脚本结束"
复制代码
dss.ddl
  1. -- Sccsid:     @(#)dss.ddl    2.1.8.1
  2. drop database tpch;
  3. create database tpch;
  4. use tpch;
  5. CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
  6.                             N_NAME       CHAR(25) NOT NULL,
  7.                             N_REGIONKEY  INTEGER NOT NULL,
  8.                             N_COMMENT    VARCHAR(152));
  10. CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
  11.                             R_NAME       CHAR(25) NOT NULL,
  12.                             R_COMMENT    VARCHAR(152));
  14. CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
  15.                           P_NAME        VARCHAR(55) NOT NULL,
  16.                           P_MFGR        CHAR(25) NOT NULL,
  17.                           P_BRAND       CHAR(10) NOT NULL,
  18.                           P_TYPE        VARCHAR(25) NOT NULL,
  19.                           P_SIZE        INTEGER NOT NULL,
  20.                           P_CONTAINER   CHAR(10) NOT NULL,
  21.                           P_RETAILPRICE DECIMAL(15,2) NOT NULL,
  22.                           P_COMMENT     VARCHAR(23) NOT NULL );
  24. CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
  25.                              S_NAME        CHAR(25) NOT NULL,
  26.                              S_ADDRESS     VARCHAR(40) NOT NULL,
  27.                              S_NATIONKEY   INTEGER NOT NULL,
  28.                              S_PHONE       CHAR(15) NOT NULL,
  29.                              S_ACCTBAL     DECIMAL(15,2) NOT NULL,
  30.                              S_COMMENT     VARCHAR(101) NOT NULL);
  32. CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
  33.                              PS_SUPPKEY     INTEGER NOT NULL,
  34.                              PS_AVAILQTY    INTEGER NOT NULL,
  35.                              PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
  36.                              PS_COMMENT     VARCHAR(199) NOT NULL );
  38. CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
  39.                              C_NAME        VARCHAR(25) NOT NULL,
  40.                              C_ADDRESS     VARCHAR(40) NOT NULL,
  41.                              C_NATIONKEY   INTEGER NOT NULL,
  42.                              C_PHONE       CHAR(15) NOT NULL,
  43.                              C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
  44.                              C_MKTSEGMENT  CHAR(10) NOT NULL,
  45.                              C_COMMENT     VARCHAR(117) NOT NULL);
  47. CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
  48.                            O_CUSTKEY        INTEGER NOT NULL,
  49.                            O_ORDERSTATUS    CHAR(1) NOT NULL,
  50.                            O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
  51.                            O_ORDERDATE      DATE NOT NULL,
  52.                            O_ORDERPRIORITY  CHAR(15) NOT NULL,  
  53.                            O_CLERK          CHAR(15) NOT NULL,
  54.                            O_SHIPPRIORITY   INTEGER NOT NULL,
  55.                            O_COMMENT        VARCHAR(79) NOT NULL);
  57. CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
  58.                              L_PARTKEY     INTEGER NOT NULL,
  59.                              L_SUPPKEY     INTEGER NOT NULL,
  60.                              L_LINENUMBER  INTEGER NOT NULL,
  61.                              L_QUANTITY    DECIMAL(15,2) NOT NULL,
  62.                              L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
  63.                              L_DISCOUNT    DECIMAL(15,2) NOT NULL,
  64.                              L_TAX         DECIMAL(15,2) NOT NULL,
  65.                              L_RETURNFLAG  CHAR(1) NOT NULL,
  66.                              L_LINESTATUS  CHAR(1) NOT NULL,
  67.                              L_SHIPDATE    DATE NOT NULL,
  68.                              L_COMMITDATE  DATE NOT NULL,
  69.                              L_RECEIPTDATE DATE NOT NULL,
  70.                              L_SHIPINSTRUCT CHAR(25) NOT NULL,
  71.                              L_SHIPMODE     CHAR(10) NOT NULL,
  72.                              L_COMMENT      VARCHAR(44) NOT NULL);
复制代码
dss.ri
  1. -- Sccsid:     @(#)dss.ri    2.1.8.1
  2. -- tpch Benchmark Version 8.0
  4. -- For table REGION
  5. ALTER TABLE tpch.REGION
  6. ADD PRIMARY KEY (R_REGIONKEY);
  8. -- For table NATION
  9. ALTER TABLE tpch.NATION
  10. ADD PRIMARY KEY (N_NATIONKEY);
  12. ALTER TABLE tpch.NATION
  13. ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references tpch.REGION(R_REGIONKEY);
  15. COMMIT WORK;
  17. -- For table PART
  18. ALTER TABLE tpch.PART
  19. ADD PRIMARY KEY (P_PARTKEY);
  21. COMMIT WORK;
  23. -- For table SUPPLIER
  24. ALTER TABLE tpch.SUPPLIER
  25. ADD PRIMARY KEY (S_SUPPKEY);
  27. ALTER TABLE tpch.SUPPLIER
  28. ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references tpch.NATION(N_NATIONKEY);
  30. COMMIT WORK;
  32. -- For table PARTSUPP
  33. ALTER TABLE tpch.PARTSUPP
  34. ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
  36. COMMIT WORK;
  38. -- For table CUSTOMER
  39. ALTER TABLE tpch.CUSTOMER
  40. ADD PRIMARY KEY (C_CUSTKEY);
  42. ALTER TABLE tpch.CUSTOMER
  43. ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references tpch.NATION(N_NATIONKEY);
  45. COMMIT WORK;
  47. -- For table LINEITEM
  48. ALTER TABLE tpch.LINEITEM
  49. ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
  51. COMMIT WORK;
  53. -- For table ORDERS
  54. ALTER TABLE tpch.ORDERS
  55. ADD PRIMARY KEY (O_ORDERKEY);
  57. COMMIT WORK;
  59. -- For table PARTSUPP
  60. ALTER TABLE tpch.PARTSUPP
  61. ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references tpch.SUPPLIER(S_SUPPKEY);
  63. COMMIT WORK;
  65. ALTER TABLE tpch.PARTSUPP
  66. ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references tpch.PART(P_PARTKEY);
  68. COMMIT WORK;
  70. -- For table ORDERS
  71. ALTER TABLE tpch.ORDERS
  72. ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references tpch.CUSTOMER(C_CUSTKEY);
  74. COMMIT WORK;
  76. -- For table LINEITEM
  77. ALTER TABLE tpch.LINEITEM
  78. ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references tpch.ORDERS(O_ORDERKEY);
  80. COMMIT WORK;
  82. ALTER TABLE tpch.LINEITEM
  83. ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references
  84.         tpch.PARTSUPP(PS_PARTKEY,PS_SUPPKEY);
  86. COMMIT WORK;
复制代码
SQL语句

[code]--SQL1 select /*+ PQ(16) */    l_returnflag,    l_linestatus,    sum(l_quantity) as sum_qty,    sum(l_extendedprice) as sum_base_price,    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,    avg(l_quantity) as avg_qty,    avg(l_extendedprice) as avg_price,    avg(l_discount) as avg_disc,    count(*) as count_orderfrom    lineitemwhere    l_shipdate  date '1995-03-01'group by    l_orderkey,    o_orderdate,    o_shippriorityorder by    revenue desc,    o_orderdate limit 10;--SQL6select /*+ PQ(16) */    sum(l_extendedprice * l_discount) as revenuefrom    lineitemwhere    l_shipdate >= date '1993-01-01'    and l_shipdate < date '1993-01-01' + interval '1' year    and l_discount between 0.02 - 0.01 and 0.02 + 0.01    and l_quantity < 24 limit 1;    --SQL10select /*+ PQ(16) */    c_custkey,    c_name,    sum(l_extendedprice * (1 - l_discount)) as revenue,    c_acctbal,    n_name,    c_address,    c_phone,    c_commentfrom    customer,    orders,    lineitem,    nationwhere    c_custkey = o_custkey    and l_orderkey = o_orderkey    and o_orderdate >= date '1994-05-01'    and o_orderdate < date '1994-05-01' + interval '3' month    and l_returnflag = 'R'    and c_nationkey = n_nationkeygroup by    c_custkey,    c_name,    c_acctbal,    c_phone,    n_name,    c_address,    c_commentorder by    revenue desc limit 20;--SQL12select /*+ PQ(16) */    l_shipmode,    sum(case        when o_orderpriority = '1-URGENT'            or o_orderpriority = '2-HIGH'            then 1        else 0    end) as high_line_count,    sum(case        when o_orderpriority  '1-URGENT'            and o_orderpriority  '2-HIGH'            then 1        else 0    end) as low_line_countfrom    orders,    lineitemwhere    o_orderkey = l_orderkey    and l_shipmode in ('TRUCK', 'FOB')    and l_commitdate < l_receiptdate    and l_shipdate < l_commitdate    and l_receiptdate >= date '1996-01-01'    and l_receiptdate < date '1996-01-01' + interval '1' yeargroup by    l_shipmodeorder by    l_shipmode limit 1 ;--SQL19select /*+ PQ(16) */    sum(l_extendedprice* (1 - l_discount)) as revenuefrom    lineitem,    partwhere    (        p_partkey = l_partkey        and p_brand = 'Brand#12'        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')        and l_quantity >= 10 and l_quantity = 15 and l_quantity = 22 and l_quantity

上一篇: MySQL 8.0中InnoDB buffer pool size进度更透明

下一篇: 一条SQL如何被MySQL架构中的各个组件操作执行的？

发表于 2023-5-4 17:41:59

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4