PolarDB 开源基础教程系列 7.5 应用实践之 TPCH性能优化-阿里云开发者社区

5、tpch 测试

5.1、tpch 测试软件介绍

tpch是一种工业标准的数据库和硬件性能测试方法, 通常用来测试数据库或硬件在处理复杂SQL时的性能表现, 另一个更复杂一些的工业标准测试是tpc-ds.

本节内容使用这个章节搭建的开发环境进行说明: 《开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.9 PolarDB开源版本必学特性 - 玩转PolarDB 开源社区》

mkdir ~/pb11_1  
cd ~/pb11_1  
docker run -d -it -P --shm-size=1g \
--cap-add=SYS_PTRACE --cap-add SYS_ADMIN \
--privileged=true --name pb11_1 \
-v ${PWD}:/var/polardb \
registry.cn-hangzhou.aliyuncs.com/polardb_pg/polardb_pg_local_instance:11 bash

tpch也是PolarDB首届全国大学生数据库比赛的赛题, 可参考如下文章, 里面也有一些优化思路:

1、进入开发环境容器, 下载tpch代码

cd /tmp  
git clone --depth 1 https://github.com/ApsaraDB/tpch-dbgen.git

2、build.sh脚本用法

tpch数据生成、导入、SQL查询的功能已经封装到build.sh中, 如果你有定制化需求, 可以自行修改这个脚本.

cd /tmp/tpch-dbgen  
  
./build.sh --help  
  
  1) Use default configuration to build  
  ./build.sh  
  2) Use limited configuration to build  
  ./build.sh --user=postgres --db=postgres --host=localhost --port=5432 --scale=1  
  3) Run the test case  
  ./build.sh --run=-1   
  4) Run the target test case   
  ./build.sh --run=3    
  5) Run the target test case with option  
  ./build.sh --run --option="set polar_enable_px = on;"  
  6) Clean the test data. This step will drop the database or tables, remove csv  
  and tbl files   
  ./build.sh --clean  
  7) Quick build TPC-H with 100MB scale of data  
  ./build.sh --scale=0.1

3、build.sh脚本内容

cat build.sh  
  
#!/bin/bash  
  
# default configuration  
# user: "postgres"  
# database: "postgres"  
# host: "localhost"  
# primary port: "5432"  
# data scale: 1  
pg_user=postgres  
pg_database=postgres  
pg_host=localhost  
pg_port=5432  
data_scale=10  
is_run=-1  
test_case=18  
clean=  
option=""  
  
usage () {  
cat <<EOF  
  
  1) Use default configuration to build  
  ./build.sh  
  2) Use limited configuration to build  
  ./build.sh --user=postgres --db=postgres --host=localhost --port=5432 --scale=1  
  3) Run the test case  
  ./build.sh --run  
  4) Run the target test case  
  ./build.sh --run=3. run the 3rd case.  
  5) Run the target test case with option  
  ./build.sh --run --option="set polar_enable_px = on;"  
  6) Clean the test data. This step will drop the database or tables, remove csv  
  and tbl files  
  ./build.sh --clean  
  7) Quick build TPC-H with 100MB scale of data  
  ./build.sh --scale=0.1  
  
EOF  
  exit 0;  
}  
  
for arg do  
  val=`echo "$arg" | sed -e 's;^--[^=]*=;;'`  
  
  case "$arg" in  
    --user=*)                   pg_user="$val";;  
    --db=*)                     pg_database="$val";;  
    --host=*)                   pg_host="$val";;  
    --port=*)                   pg_port="$val";;  
    --scale=*)                  data_scale="$val";;  
    --run)                      is_run=on ;;  
    --run=*)                    is_run=on;  
                                test_case="$val"  
                                ;;  
    --option=*)                 option="$val";;  
    --clean)                    clean=on ;;  
    -h|--help)                  usage ;;  
    *)                          echo "wrong options : $arg";  
                                exit 1  
                                ;;  
  esac  
done  
  
export PGPORT=$pg_port  
export PGHOST=$pg_host  
export PGDATABASE=$pg_database  
export PGUSER=$pg_user  
  
function gen_query_sql() {  
    DIR=.  
    rm -rf $DIR/finals  
    mkdir $DIR/finals  
    cp $DIR/queries/*.sql $DIR  
    for FILE in $(find $DIR -maxdepth 1 -name "[0-9]*.sql")  
    do  
        DIGIT=$(echo $FILE | tr -cd '[[:digit:]]')  
        ./qgen $DIGIT > $DIR/finals/$DIGIT.sql  
        sed 's/^select/explain select/' $DIR/finals/$DIGIT.sql > $DIR/finals/$DIGIT.explain.sql  
    done  
    rm *.sql  
}  
  
function run_query_sql() {  
    DIR=.  
    if [[ $test_case -ne "-1" && $test_case -ne "on" ]]  
    then  
        echo "####################### $test_case.sql ###########################"  
        echo "####################### $test_case.sql ###########################" >> $DIR/result  
        psql -c "$option" -c "\timing" -f $DIR/finals/$test_case.explain.sql -qa >> $DIR/result  
        psql -c "$option" -c "\timing" -f $DIR/finals/$test_case.sql -qa >> $DIR/result  
    else  
        for i in `seq 1 22`  
        do  
            echo "####################### $i.sql ###########################"  
            echo "####################### $i.sql ###########################" >> $DIR/result  
            psql -c "$option" -c "\timing" -f $DIR/finals/$i.explain.sql -qa >> $DIR/result  
            psql -c "$option" -c "\timing" -f $DIR/finals/$i.sql -qa >> $DIR/result  
        done  
    fi  
}  
  
# run the tpch test  
if [[ $is_run == "on" ]];  
then  
  run_query_sql;  
  exit;  
fi  
  
# clean the tpch test data  
if [[ $clean == "on" ]];  
then  
  make clean  
  if [[ $pg_database == "postgres" ]];  
  then  
    echo "drop all the tpch tables"  
    psql -c "drop table customer cascade"  
    psql -c "drop table lineitem cascade"  
    psql -c "drop table nation cascade"  
    psql -c "drop table orders cascade"  
    psql -c "drop table part cascade"  
    psql -c "drop table partsupp cascade"  
    psql -c "drop table region cascade"  
    psql -c "drop table supplier cascade"  
  else  
    echo "drop the tpch database: $PGDATABASE"  
    psql -c "drop database $PGDATABASE" -d postgres  
  fi  
  exit;  
fi  
  
###################### PHASE 1: compile ######################  
make -f makefile.suite  
  
##################### PHASE 2: generate data ######################  
rm -rf *.tbl  
./dbgen -s $data_scale  
  
###################### PHASE 3: create table ######################  
if [[ $PGDATABASE != "postgres" ]];  
then  
  echo "create the tpch database: $PGDATABASE"  
  psql -c "create database $PGDATABASE" -d postgres  
fi  
psql -f dss.ddl  
  
###################### PHASE 4: load data ######################  
psql -c "\COPY nation FROM 'nation.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY region FROM 'region.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY part FROM 'part.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY supplier FROM 'supplier.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY partsupp FROM 'partsupp.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY customer FROM 'customer.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY orders FROM 'orders.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY lineitem FROM 'lineitem.tbl' WITH (FORMAT csv, DELIMITER '|');"  
  
###################### PHASE 5: add primary and foreign key ######################  
psql -f dss.ri  
  
##################### PHASE 6: generate query sql in final dir ######################  
gen_query_sql;

通过设置不同的参数，可以定制化地创建不同规模的 TPC-H 数据集。build.sh 脚本中各个参数的含义如下：

--user：数据库用户名  
--db：数据库名  
--host：数据库主机地址  
--port：数据库服务端口  
--run：执行所有 TPC-H 查询，或执行某条特定的 TPC-H 查询  
--option：额外指定数据库 GUC 参数  
--scale：生成 TPC-H 数据集的规模，单位为 GB

该脚本没有提供输入数据库密码的参数，需要通过设置 PGPASSWORD 为数据库用户的数据库密码来完成认证：

export PGPASSWORD=<your password>

4、dbgen (生成数据) 用法

./dbgen -h  
TPC-H Population Generator (Version 2.14.0 build 0)  
Copyright Transaction Processing Performance Council 1994 - 2010  
USAGE:  
dbgen [-{vf}][-T {pcsoPSOL}]  
  [-s <scale>][-C <procs>][-S <step>]  
dbgen [-v] [-O m] [-s <scale>] [-U <updates>]  
  
Basic Options  
===========================  
-C <n> -- separate data set into <n> chunks (requires -S, default: 1)  
-f     -- force. Overwrite existing files  
-h     -- display this message  
-q     -- enable QUIET mode  
-s <n> -- set Scale Factor (SF) to  <n> (default: 1)   
-S <n> -- build the <n>th step of the data/update set (used with -C or -U)  
-U <n> -- generate <n> update sets  
-v     -- enable VERBOSE mode  
  
Advanced Options  
===========================  
-b <s> -- load distributions for <s> (default: dists.dss)  
-d <n> -- split deletes between <n> files (requires -U)  
-i <n> -- split inserts between <n> files (requires -U)  
-T c   -- generate cutomers ONLY  
-T l   -- generate nation/region ONLY  
-T L   -- generate lineitem ONLY  
-T n   -- generate nation ONLY  
-T o   -- generate orders/lineitem ONLY  
-T O   -- generate orders ONLY  
-T p   -- generate parts/partsupp ONLY  
-T P   -- generate parts ONLY  
-T r   -- generate region ONLY  
-T s   -- generate suppliers ONLY  
-T S   -- generate partsupp ONLY  
  
To generate the SF=1 (1GB), validation database population, use:  
  dbgen -vf -s 1  
  
To generate updates for a SF=1 (1GB), use:  
  dbgen -v -U 1 -s 1

5、qgen (生成查询SQL语句) 用法

./qgen -h  
TPC-H Parameter Substitution (v. 2.14.0 build 0)  
Copyright Transaction Processing Performance Council 1994 - 2010  
USAGE: ./qgen <options> [ queries ]  
Options:  
  -a    -- use ANSI semantics.  
  -b <str>  -- load distributions from <str>  
  -c    -- retain comments found in template.  
  -d    -- use default substitution values.  
  -h    -- print this usage summary.  
  -i <str>  -- use the contents of file <str> to begin a query.  
  -l <str>  -- log parameters to <str>.  
  -n <str>  -- connect to database <str>.  
  -N    -- use default rowcounts and ignore :n directive.  
  -o <str>  -- set the output file base path to <str>.  
  -p <n>    -- use the query permutation for stream <n>  
  -r <n>    -- seed the random number generator with <n>  
  -s <n>    -- base substitutions on an SF of <n>  
  -v    -- verbose.  
  -t <str>  -- use the contents of file <str> to complete a query  
  -x    -- enable SET EXPLAIN in each query.

5.2、加速Tips

生成数据加速:

修改build.sh. 使用dbgen按表并行生成tpch数据, 按表的大小从大到小启动dbgen任务, 逐一启动任务并始终保持N个并行的活跃任务.

导入数据加速:

修改build.sh. 按表并行执行COPY导入任务, 按表的大小从大到小启动COPY导入任务, 逐一启动任务并始终保持N个并行的活跃任务.

控制并行度参考: https://www.cnblogs.com/zclinux/p/14268273.html
https://developer.aliyun.com/article/1370454

开启PolarDB预分配功能(使用共享存储时效果比较明显, 预分配可以减少IO次数, 降低云盘IO延迟带来的性能损耗)

默认值可以修改src/backend/utils/misc/guc.c实现, 仔细查阅该文件了解更多PolarDB定制参数. 参考开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.5 PolarDB开源版本必学特性 - PolarDB 特性解读与体验

开始导入数据前使用unlogged table, 在索引创建完成后再改成logged table. 提示: 通过修改pg_class.relpersistence可以实现(u=unlogged, p=persistence). 有一个常见的外部开源工具pg_bulkload也有跳过wal进行导入的功效. 需要适配polardb, 方法如下:

参数优化:

导入前关闭autovacuum, 可以降低autoanalyze带来的影响.
加大maintenance_work_mem参数值, 可以提高创建索引的速度.
加大shared_buffers可以提高导入速度
加大checkpoint窗口可以降低检查点对IO的影响

使用unix socket代替tcp连接可以提高导入速度
constraint valid trick
还可以参考一下PG的时序插件timescaledb. 由于时序数据的特点是数据量大、持续写入, 这个插件在数据高速写入、数据压缩和整理等方面都有改进, 获得了比较好的效果.
对于数据量巨大的表, 也可以考虑通过分区表来降低每个分区的索引层级, 从而提升索引的检索和构建等性能. 索引本身也能通过并行构建.

查询加速:

使用单机并行, 通过修改表的配置和参数可启用强制并行度. 参考: 《PostgreSQL 11 并行计算算法，参数，强制并行度设置》
使用多机并行(PolarDB ePQ是greenplum orca优化器的适配共享存储架构的版本, OLAP业务场景优化显著. (注意ePQ这个功能仅仅在使用了一写多读PolarDB启动了多个计算节点时生效)), PolarDB的配置. 参考: 《开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.5 PolarDB开源版本必学特性 - PolarDB 特性解读与体验》
开启PolarDB预读功能(使用共享存储时效果比较明显, 预读可以减少IO次数, 降低云盘IO延迟带来的性能损耗)

默认值可以修改src/backend/utils/misc/guc.c实现, 仔细查阅该文件了解更多PolarDB定制参数

不开启/不支持SQL并行的情况下, 使用多任务并行(将22条SQL的执行快慢从慢到快启动select任务, 逐一启动任务并始终保持N个并行的活跃任务.)
修改配置(例如优化器校准因子相关配置、优化器JOIN方法相关配置、哈希表内存大小相关配置等) 参考:

使用列存储、JIT和向量化, 能节约存储空间、加速导入、加速查询. (通常可以比行存储性能提升10倍以上). 需要修改内核, 可以参考数据库筑基课提到的一些开源项目,

《PostgreSQL 向量化执行插件(瓦片式实现-vops) 10x提速OLAP》

参数优化:

加大shared_buffers可以提高查询速度
加大work_mem可以提高查询速度

通过索引可以提示某些sql的查询性能, 详见:https://github.com/digoal/gp_tpch/tree/master/dss注意加索引也会导致占用更多的空间以及建索引本身的耗时, 还有索引引入了更多的random io, 效果好坏还要看硬件配置.

优化方法参考: 《PolarDB数据库创新设计国赛 - 决赛提交作品指南》
索引有单列,多列(还要考虑顺序),表达式,partial index,index type等. 可以参考《2024-应用开发者数据库筑基课》

对于极限测试, 每一条SQL都可以单独优化(例如使用不同的参数, JOIN方法, 索引等), 简单粗暴的话就调tpch的测试脚本, 复杂一点的话可以改hook, 最具实用价值的是改优化器实现通用的复杂SQL优化.

pg_plan_hint / 参数开关(例如enable_seqscan,...) , 用来控制SQL执行计划.
改写SQL, 例如不改变语义的前提下, 调整join顺序. (当然优化器自动实现是最好的).
《[转载] TPCH 22条SQL语句分析》
还有一些情况是可能同样的配置某些SQL可能会导致内存耗尽发生OOM, 可能是hash table过大造成, 可以考虑支持一下hash table split to disk, 多阶段join? 例如《DuckDB 0.6.0 内存管理增强, 提升超出内存大小的大数据集hashjoin,sort,window function性能》

其他tips:

减少cache/buffer换进换出
提高变长类型的压缩算法效率
提高类型相关运算算子效率. 例如内置numeric效率提升(如fixeddecimal项目)。
泡一下PostgreSQL hacker maillist、github看看有没有其他思路

更多方法请参考:

5.3、tpch 测试例子

下面使用build.sh这个脚本来进行测试.

0、可选步骤. 该脚本没有提供输入数据库密码的参数，需要通过设置 PGPASSWORD 为数据库用户的数据库密码来完成认证：

export PGPASSWORD=<your password>

1、清理已有表

./build.sh --user=postgres --db=postgres --host=127.0.0.1 --port=5432 --clean

2、生成指定大小的数据集(本例 100MB)并导入PolarDB

./build.sh --user=postgres --db=postgres --host=127.0.0.1 --port=5432 --scale=0.1

日志如下

...  
  
TPC-H Population Generator (Version 2.14.0)  
Copyright Transaction Processing Performance Council 1994 - 2010  
CREATE TABLE  
CREATE TABLE  
CREATE TABLE  
CREATE TABLE  
CREATE TABLE  
CREATE TABLE  
CREATE TABLE  
CREATE TABLE  
COPY 25  
COPY 5  
COPY 20000  
COPY 1000  
COPY 80000  
COPY 15000  
COPY 150000  
COPY 600572  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE  
ALTER TABLE

生成的数据、查询语句、explain语句等就在/tmp/tpch-dbgen当前目录中:

ll -rth  
...  
  
-rw-r--r--  1 postgres postgres 136K Sep 24 15:20 supplier.tbl  
-rw-r--r--  1 postgres postgres  384 Sep 24 15:20 region.tbl  
-rw-r--r--  1 postgres postgres 2.3M Sep 24 15:20 part.tbl  
-rw-r--r--  1 postgres postgres  12M Sep 24 15:20 partsupp.tbl  
-rw-r--r--  1 postgres postgres  16M Sep 24 15:20 orders.tbl  
-rw-r--r--  1 postgres postgres 2.2K Sep 24 15:20 nation.tbl  
-rw-r--r--  1 postgres postgres  71M Sep 24 15:20 lineitem.tbl  
-rw-r--r--  1 postgres postgres 2.3M Sep 24 15:20 customer.tbl  
drwxr-xr-x  2 postgres postgres 4.0K Sep 24 15:20 finals/

查询语句、explain语句

ll finals/  
total 184  
drwxr-xr-x  2 postgres postgres 4096 Sep 24 15:20 ./  
drwxr-xr-x 10 postgres postgres 4096 Sep 24 15:20 ../  
-rw-r--r--  1 postgres postgres  558 Sep 24 15:20 10.explain.sql  
-rw-r--r--  1 postgres postgres  550 Sep 24 15:20 10.sql  
-rw-r--r--  1 postgres postgres  546 Sep 24 15:20 11.explain.sql  
-rw-r--r--  1 postgres postgres  538 Sep 24 15:20 11.sql  
-rw-r--r--  1 postgres postgres  627 Sep 24 15:20 12.explain.sql  
-rw-r--r--  1 postgres postgres  619 Sep 24 15:20 12.sql  
-rw-r--r--  1 postgres postgres  390 Sep 24 15:20 13.explain.sql  
-rw-r--r--  1 postgres postgres  382 Sep 24 15:20 13.sql  
-rw-r--r--  1 postgres postgres  379 Sep 24 15:20 14.explain.sql  
-rw-r--r--  1 postgres postgres  371 Sep 24 15:20 14.sql  
-rw-r--r--  1 postgres postgres  558 Sep 24 15:20 15.explain.sql  
-rw-r--r--  1 postgres postgres  550 Sep 24 15:20 15.sql  
-rw-r--r--  1 postgres postgres  517 Sep 24 15:20 16.explain.sql  
-rw-r--r--  1 postgres postgres  509 Sep 24 15:20 16.sql  
-rw-r--r--  1 postgres postgres  328 Sep 24 15:20 17.explain.sql  
-rw-r--r--  1 postgres postgres  320 Sep 24 15:20 17.sql  
-rw-r--r--  1 postgres postgres  481 Sep 24 15:20 18.explain.sql  
-rw-r--r--  1 postgres postgres  473 Sep 24 15:20 18.sql  
-rw-r--r--  1 postgres postgres 1003 Sep 24 15:20 19.explain.sql  
-rw-r--r--  1 postgres postgres  995 Sep 24 15:20 19.sql  
-rw-r--r--  1 postgres postgres  571 Sep 24 15:20 1.explain.sql  
-rw-r--r--  1 postgres postgres  563 Sep 24 15:20 1.sql  
-rw-r--r--  1 postgres postgres  633 Sep 24 15:20 20.explain.sql  
-rw-r--r--  1 postgres postgres  625 Sep 24 15:20 20.sql  
-rw-r--r--  1 postgres postgres  686 Sep 24 15:20 21.explain.sql  
-rw-r--r--  1 postgres postgres  678 Sep 24 15:20 21.sql  
-rw-r--r--  1 postgres postgres  686 Sep 24 15:20 22.explain.sql  
-rw-r--r--  1 postgres postgres  678 Sep 24 15:20 22.sql  
-rw-r--r--  1 postgres postgres  711 Sep 24 15:20 2.explain.sql  
-rw-r--r--  1 postgres postgres  703 Sep 24 15:20 2.sql  
-rw-r--r--  1 postgres postgres  453 Sep 24 15:20 3.explain.sql  
-rw-r--r--  1 postgres postgres  445 Sep 24 15:20 3.sql  
-rw-r--r--  1 postgres postgres  397 Sep 24 15:20 4.explain.sql  
-rw-r--r--  1 postgres postgres  389 Sep 24 15:20 4.sql  
-rw-r--r--  1 postgres postgres  529 Sep 24 15:20 5.explain.sql  
-rw-r--r--  1 postgres postgres  521 Sep 24 15:20 5.sql  
-rw-r--r--  1 postgres postgres  299 Sep 24 15:20 6.explain.sql  
-rw-r--r--  1 postgres postgres  291 Sep 24 15:20 6.sql  
-rw-r--r--  1 postgres postgres  851 Sep 24 15:20 7.explain.sql  
-rw-r--r--  1 postgres postgres  843 Sep 24 15:20 7.sql  
-rw-r--r--  1 postgres postgres  818 Sep 24 15:20 8.explain.sql  
-rw-r--r--  1 postgres postgres  810 Sep 24 15:20 8.sql  
-rw-r--r--  1 postgres postgres  636 Sep 24 15:20 9.explain.sql  
-rw-r--r--  1 postgres postgres  628 Sep 24 15:20 9.sql

3、运行某一条SQL, 例如运行第1条sql

./build.sh --user=postgres --db=postgres --host=127.0.0.1 --port=5432 --run=1

查看sql运行结果(日志文件result)

cat result   
  
  
####################### 1.sql ###########################  
  
timing  
-- using 1727162422 as a seed to the RNG  
explain select  
  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_order  
from  
  lineitem  
where  
  l_shipdate <= date '1998-12-01' - interval '105' day  
group by  
  l_returnflag,  
  l_linestatus  
order by  
  l_returnflag,  
  l_linestatus;  
                                              QUERY PLAN                                                
------------------------------------------------------------------------------------------------------  
 Finalize GroupAggregate  (cost=25719.09..25721.16 rows=6 width=236)  
   Group Key: l_returnflag, l_linestatus  
   ->  Gather Merge  (cost=25719.09..25720.49 rows=12 width=236)  
         Workers Planned: 2  
         ->  Sort  (cost=24719.06..24719.08 rows=6 width=236)  
               Sort Key: l_returnflag, l_linestatus  
               ->  Partial HashAggregate  (cost=24718.82..24718.98 rows=6 width=236)  
                     Group Key: l_returnflag, l_linestatus  
                     ->  Parallel Seq Scan on lineitem  (cost=0.00..14903.98 rows=245371 width=25)  
                           Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)  
(10 rows)  
  
Time: 12.224 ms  
--LIMIT -1  
  
timing  
-- using 1727162422 as a seed to the RNG  
select  
  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_order  
from  
  lineitem  
where  
  l_shipdate <= date '1998-12-01' - interval '105' day  
group by  
  l_returnflag,  
  l_linestatus  
order by  
  l_returnflag,  
  l_linestatus;  
 l_returnflag | l_linestatus |  sum_qty   | sum_base_price | sum_disc_price  |     sum_charge     |       avg_qty       |     avg_price      |        avg_disc        | count_order   
--------------+--------------+------------+----------------+-----------------+--------------------+---------------------+--------------------+------------------------+-------------  
 A            | F            | 3774200.00 |  5320753880.69 | 5054096266.6828 |  5256751331.449234 | 25.5375871168549970 | 36002.123829014142 | 0.05014459706340077136 |      147790  
 N            | F            |   95257.00 |   133737795.84 |  127132372.6512 |   132286291.229445 | 25.3006640106241700 | 35521.326916334661 | 0.04939442231075697211 |        3765  
 N            | O            | 7380026.00 | 10399900746.26 | 9879518563.1885 | 10274573876.700519 | 25.5454381823342495 | 35998.521091388656 | 0.05009446240541644456 |      288898  
 R            | F            | 3785523.00 |  5337950526.47 | 5071818532.9420 |  5274405503.049367 | 25.5259438574251017 | 35994.029214030924 | 0.04998927856184381764 |      148301  
(4 rows)  
  
Time: 148.787 ms  
--LIMIT -1

4、运行所有22条SQL

./build.sh --user=postgres --db=postgres --host=127.0.0.1 --port=5432 --run=-1

5、运行SQL, 并指定数据库GUC参数(例如开启PolarDB ePQ多机并行选项(注意这个功能仅仅在使用了一写多读PolarDB启动了多个计算节点时生效), 设置work_mem.).

首先需要对 TPC-H 产生的八张表设置 ePQ 的最大查询并行度：

ALTER TABLE nation SET (px_workers = 4);  
ALTER TABLE region SET (px_workers = 4);  
ALTER TABLE supplier SET (px_workers = 4);  
ALTER TABLE part SET (px_workers = 4);  
ALTER TABLE partsupp SET (px_workers = 4);  
ALTER TABLE customer SET (px_workers = 4);  
ALTER TABLE orders SET (px_workers = 4);  
ALTER TABLE lineitem SET (px_workers = 4);

测试sql 17如下

./build.sh --user=postgres --db=postgres --host=127.0.0.1 --port=5432 --run=17 --option="set polar_enable_px = on; SET polar_px_dop_per_node = 1; set work_mem = '16MB'; set statement_timeout='60min';"

options解释, 更多epq相关参数的介绍参考《PolarDB 架构解读》

-- 打开 ePQ 功能的开关  
SET polar_enable_px = ON;  
-- 设置每个节点的 ePQ 并行度为 1  
SET polar_px_dop_per_node = 1;  -- 设高了容易OOM
-- 设置work_mem为16MB  
set work_mem = '16MB';
-- 设置SQL运行超时时间, 仅用于测试. 太久放弃
set statement_timeout='60min';

查看result可以看到开启ePQ后执行计划确实不一样:

QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=0.00..1530.85 rows=1 width=8)
   ->  PX Coordinator 2:1  (slice1; segments: 2)  (cost=0.00..1530.85 rows=1 width=8)
         ->  Partial Aggregate  (cost=0.00..1530.85 rows=1 width=8)
               ->  Hash Join  (cost=0.00..1530.58 rows=300286 width=8)
                     Hash Cond: (lineitem.l_partkey = part.p_partkey)
                     Join Filter: (lineitem.l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
                     ->  Partial Seq Scan on lineitem  (cost=0.00..460.23 rows=300286 width=20)
                     ->  Hash  (cost=940.02..940.02 rows=5746 width=12)
                           ->  PX Broadcast 2:2  (slice2; segments: 2)  (cost=0.00..940.02 rows=5746 width=12)
                                 ->  Hash Left Join  (cost=0.00..938.18 rows=2873 width=12)
                                       Hash Cond: (part.p_partkey = lineitem_1.l_partkey)
                                       ->  PX Hash 2:2  (slice3; segments: 2)  (cost=0.00..432.90 rows=2845 width=4)
                                             Hash Key: part.p_partkey
                                             ->  Partial Seq Scan on part  (cost=0.00..432.86 rows=2845 width=4)
                                                   Filter: ((p_brand = 'Brand#51'::bpchar) AND (p_container = 'JUMBO DRUM'::bpchar))
                                       ->  Hash  (cost=504.47..504.47 rows=500 width=12)
                                             ->  Finalize HashAggregate  (cost=0.00..504.47 rows=500 width=12)
                                                   Group Key: lineitem_1.l_partkey
                                                   ->  PX Hash 2:2  (slice4; segments: 2)  (cost=0.00..504.40 rows=500 width=12)
                                                         Hash Key: lineitem_1.l_partkey
                                                         ->  Partial HashAggregate  (cost=0.00..504.39 rows=500 width=12)
                                                               Group Key: lineitem_1.l_partkey
                                                               ->  Partial Seq Scan on lineitem lineitem_1  (cost=0.00..460.23 rows=300286 width=12)
 Optimizer: PolarDB PX Optimizer
(24 rows)

6、你也可以从finals文件夹直接执行SQL

postgres@a39da606d3ad:/tmp/tpch-dbgen$ psql  
psql (11.9)  
Type "help" for help.  
  
postgres=# \timing on  
Timing is on.  
postgres=# \i ./finals/1.explain.sql  
                                              QUERY PLAN                                                
------------------------------------------------------------------------------------------------------  
 Finalize GroupAggregate  (cost=25719.09..25721.16 rows=6 width=236)  
   Group Key: l_returnflag, l_linestatus  
   ->  Gather Merge  (cost=25719.09..25720.49 rows=12 width=236)  
         Workers Planned: 2  
         ->  Sort  (cost=24719.06..24719.08 rows=6 width=236)  
               Sort Key: l_returnflag, l_linestatus  
               ->  Partial HashAggregate  (cost=24718.82..24718.98 rows=6 width=236)  
                     Group Key: l_returnflag, l_linestatus  
                     ->  Parallel Seq Scan on lineitem  (cost=0.00..14903.98 rows=245371 width=25)  
                           Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)  
(10 rows)  
  
Time: 18.816 ms  
postgres=# \i ./finals/1.sql  
 l_returnflag | l_linestatus |  sum_qty   | sum_base_price | sum_disc_price  |     sum_charge     |       avg_qty       |     avg_price      |        avg_disc        | count_order   
--------------+--------------+------------+----------------+-----------------+--------------------+---------------------+--------------------+------------------------+-------------  
 A            | F            | 3774200.00 |  5320753880.69 | 5054096266.6828 |  5256751331.449234 | 25.5375871168549970 | 36002.123829014142 | 0.05014459706340077136 |      147790  
 N            | F            |   95257.00 |   133737795.84 |  127132372.6512 |   132286291.229445 | 25.3006640106241700 | 35521.326916334661 | 0.04939442231075697211 |        3765  
 N            | O            | 7380026.00 | 10399900746.26 | 9879518563.1885 | 10274573876.700519 | 25.5454381823342495 | 35998.521091388656 | 0.05009446240541644456 |      288898  
 R            | F            | 3785523.00 |  5337950526.47 | 5071818532.9420 |  5274405503.049367 | 25.5259438574251017 | 35994.029214030924 | 0.04998927856184381764 |      148301  
(4 rows)  
  
Time: 188.724 ms

5.4、tpch 20G 导入性能测试示例

以macOS宿主机(macBook pro M2 16G 512G, docker资源限制4Ccpu 8Gmem 4Gswap)为例, 测试一下tpch 20G 导入性能, 挂载宿主机目录~/pb2024到容器中使用, 在生成tpch原始数据时可能性能会更好一点, 同时将PolarDB集群也初始化到宿主机中, 还可以防止测试过程中容器文件过大.

1、在宿主机操作, 创建目录, 创建容器并挂载容器

mkdir ~/pb2024   
sudo xattr -r -c ~/pb2024   
cd ~/pb2024  
docker run -d -it -P -v $PWD:/home/pb2024 --shm-size=1g --cap-add=SYS_PTRACE --cap-add SYS_ADMIN --privileged=true --name polardb_pg_devel registry.cn-hangzhou.aliyuncs.com/polardb_pg/polardb_pg_devel:ubuntu22.04 bash

2、在宿主机操作, 进入容器shell环境

docker exec -ti polardb_pg_devel bash

3、在容器内操作, 将HOME目录移动到宿主机目录中, (因为polardb build脚本很多变量配置写死了将集群都放在了HOME目录中)

cd /tmp  
sudo mv /home/postgres /home/pb2024/  
sudo ln -s /home/pb2024/postgres /home/postgres

4、在容器内操作, 克隆PolarDB开源项目和tpch开源项目

cd ~  
git clone -b POLARDB_11_STABLE --depth 1 https://github.com/ApsaraDB/PolarDB-for-PostgreSQL.git  
git clone --depth 1 https://github.com/ApsaraDB/tpch-dbgen.git

5、在容器内操作, 编译PolarDB并启动

# unix socket监听在宿主机目录中创建报错, 暂时不知道什么原因, 先绕过它, 方法如下:  
# unix_socket_directories='.'  
# 改成  
# unix_socket_directories='/tmp'  
  
cd ~/PolarDB-for-PostgreSQL   
sed -i "s/unix_socket_directories='\.'/unix_socket_directories='\/tmp'/g" ./polardb_build.sh
# 如果你使用的是ubuntu20.04, 请使用如下sed,mv,chmod替换以上sed -i. 
# sed "s/unix_socket_directories='\.'/unix_socket_directories='\/tmp'/g" ./polardb_build.sh > /tmp/polardb_build.sh
# mv -f /tmp/polardb_build.sh ./
# chmod u+x polardb_build.sh 
  
chmod 700 polardb_build.sh   
./polardb_build.sh --without-fbl --debug=off

6、在容器内操作, 进入PolarDB数据库执行配置, 开启SQL时间记录, 便于记录tpch的导入耗时

psql   
  
alter system set log_duration=on;  
alter system set log_min_duration_statement=0;  
alter system set log_destination = 'csvlog';  
alter system set logging_collector = on;  
select pg_reload_conf();  
\q

7、在容器内操作, 生成20GB tpch数据, 并导入到PolarDB

没有做任何优化(macBook pro M2 16G 512G):

cd ~/tpch-dbgen  
time ./build.sh --user=postgres --db=postgres --host=/tmp --port=5432 --scale=20

8、在容器内操作, 查看PolarDB日志, 查看耗时如下

cd /var/polardb/primary_datadir/pg_log  
  
ll  
total 2324  
drwx------  7 postgres postgres     224 Sep 26 15:13 ./  
drwx------ 34 postgres postgres    1088 Sep 26 15:13 ../  
-rw-------  1 postgres postgres    6505 Sep 26 15:12 postgresql-2024-09-26_143149_0_audit.log  
-rw-------  1 postgres postgres 1227265 Sep 26 15:13 postgresql-2024-09-26_143149_error.log  
-rw-------  1 postgres postgres    1627 Sep 26 15:13 postgresql-2024-09-26_143149_slow.log  
-rw-------  1 postgres postgres  137329 Sep 26 15:17 postgresql-2024-09-26_151358.csv  
-rw-------  1 postgres postgres    7361 Sep 26 15:16 postgresql-2024-09-26_151358_error.log  
  
  
grep duration postgresql-2024-09-26_151358.csv|grep -E "COPY|ALTER"|awk -F "duration" '{print $2}'  
  
: 4.853 ms  statement: COPY  nation FROM STDIN WITH (FORMAT csv, DELIMITER '|');",,,,,,,,,"psql"
: 2.233 ms  statement: COPY  region FROM STDIN WITH (FORMAT csv, DELIMITER '|');",,,,,,,,,"psql"
: 10749.966 ms  statement: COPY  part FROM STDIN WITH (FORMAT csv, DELIMITER '|');",,,,,,,,,"psql"
: 613.278 ms  statement: COPY  supplier FROM STDIN WITH (FORMAT csv, DELIMITER '|');",,,,,,,,,"psql"
: 44542.276 ms  statement: COPY  partsupp FROM STDIN WITH (FORMAT csv, DELIMITER '|');",,,,,,,,,"psql"
: 10149.517 ms  statement: COPY  customer FROM STDIN WITH (FORMAT csv, DELIMITER '|');",,,,,,,,,"psql"
: 62556.924 ms  statement: COPY  orders FROM STDIN WITH (FORMAT csv, DELIMITER '|');",,,,,,,,,"psql"
: 304549.278 ms  statement: COPY  lineitem FROM STDIN WITH (FORMAT csv, DELIMITER '|');",,,,,,,,,"psql"
: 27.093 ms  statement: ALTER TABLE REGION
: 6.349 ms  statement: ALTER TABLE NATION
: 5.093 ms  statement: ALTER TABLE NATION
: 6723.202 ms  statement: ALTER TABLE PART
: 160.005 ms  statement: ALTER TABLE SUPPLIER
: 17.801 ms  statement: ALTER TABLE SUPPLIER
: 37537.154 ms  statement: ALTER TABLE PARTSUPP
: 8071.863 ms  statement: ALTER TABLE CUSTOMER
: 817.030 ms  statement: ALTER TABLE CUSTOMER
: 280717.594 ms  statement: ALTER TABLE LINEITEM
: 64279.606 ms  statement: ALTER TABLE ORDERS
: 3569.714 ms  statement: ALTER TABLE PARTSUPP
: 9856.238 ms  statement: ALTER TABLE PARTSUPP
: 20967.200 ms  statement: ALTER TABLE ORDERS
: 83385.421 ms  statement: ALTER TABLE LINEITEM
: 109378.358 ms  statement: ALTER TABLE LINEITEM

总耗时约1059秒(数据导入433秒, 索引和约束626秒)

9、调优后想再测一遍看看效果? 清理一下再导入即可.

在容器内操作

psql  
drop schema public cascade;  
create schema public;  
grant all on schema public to public;  
\q  
  
  
cd ~/tpch-dbgen  
  
psql -f dss.ddl  
  
psql -c "\COPY nation FROM 'nation.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY region FROM 'region.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY part FROM 'part.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY supplier FROM 'supplier.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY partsupp FROM 'partsupp.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY customer FROM 'customer.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY orders FROM 'orders.tbl' WITH (FORMAT csv, DELIMITER '|');"  
psql -c "\COPY lineitem FROM 'lineitem.tbl' WITH (FORMAT csv, DELIMITER '|');"  
  
psql -f dss.ri

5.5、tpch 20G 查询性能测试示例

以macOS宿主机(macBook pro M2 16G 512G, docker资源限制4Ccpu 8Gmem 4Gswap)为例, 测试一下tpch 20G 查询性能, 并对比开启ePQ与关闭ePQ时的性能差异. 挂载宿主机目录~/pb2024到容器中使用, 在生成tpch原始数据时可能性能会更好一点, 同时将PolarDB集群也初始化到宿主机中, 还可以防止测试过程中容器文件过大.

1、在宿主机操作, 创建目录, 创建容器并挂载容器

mkdir ~/pb2024     
sudo xattr -r -c ~/pb2024     
cd ~/pb2024    
docker run -d -it -P -v $PWD:/home/pb2024 --shm-size=6g --cap-add=SYS_PTRACE --cap-add SYS_ADMIN --privileged=true --name polardb_pg_devel registry.cn-hangzhou.aliyuncs.com/polardb_pg/polardb_pg_devel:ubuntu22.04 bash

2、在宿主机操作, 进入容器shell环境

docker exec -ti polardb_pg_devel bash

3、在容器内操作, 将HOME目录移动到宿主机目录中, (因为polardb build脚本很多变量配置写死了将集群都放在了HOME目录中)

cd /tmp    
sudo mv /home/postgres /home/pb2024/    
sudo ln -s /home/pb2024/postgres /home/postgres

4、在容器内操作, 克隆PolarDB开源项目和tpch开源项目

cd ~    
git clone -b POLARDB_11_STABLE --depth 1 https://github.com/ApsaraDB/PolarDB-for-PostgreSQL.git    
git clone --depth 1 https://github.com/ApsaraDB/tpch-dbgen.git

5、在容器内操作, 编译PolarDB并启动1写2读的集群

# unix socket监听在宿主机目录中创建报错, 暂时不知道什么原因, 先绕过它, 方法如下:    
# unix_socket_directories='.'    
# 改成    
# unix_socket_directories='/tmp'    
    
cd ~/PolarDB-for-PostgreSQL     
sed -i "s/unix_socket_directories='\.'/unix_socket_directories='\/tmp'/g" ./polardb_build.sh
# 如果你使用的是ubuntu20.04, 请使用如下sed,mv,chmod替换以上sed -i. 
# sed "s/unix_socket_directories='\.'/unix_socket_directories='\/tmp'/g" ./polardb_build.sh > /tmp/polardb_build.sh
# mv -f /tmp/polardb_build.sh ./
# chmod u+x polardb_build.sh 
    
chmod 700 polardb_build.sh     
./polardb_build.sh --without-fbl --debug=off --withrep --initpx --storage=localfs

6、在容器内操作, 进入PolarDB数据库执行配置, 开启SQL时间记录, 便于记录tpch的导入耗时

psql     
    
alter system set log_duration=on;    
alter system set log_min_duration_statement=0;    
alter system set log_destination = 'csvlog';    
alter system set logging_collector = on;    
select pg_reload_conf();    
\q

7、在容器内操作, 生成20GB tpch数据, 并导入到PolarDB 数据库集群

cd ~/tpch-dbgen    
time ./build.sh --user=postgres --db=postgres --host=/tmp --port=5432 --scale=20

real  29m32.128s  
user  4m57.483s  
sys 2m24.077s

8、在容器内操作, 对 TPC-H 产生的八张表设置 ePQ 的最大查询并行度：

psql  
  
ALTER TABLE nation SET (px_workers = 4);    
ALTER TABLE region SET (px_workers = 4);    
ALTER TABLE supplier SET (px_workers = 4);    
ALTER TABLE part SET (px_workers = 4);    
ALTER TABLE partsupp SET (px_workers = 4);    
ALTER TABLE customer SET (px_workers = 4);    
ALTER TABLE orders SET (px_workers = 4);    
ALTER TABLE lineitem SET (px_workers = 4);    
\q

如果你需要同时修改3个节点的参数, 可以使用如下方法:

echo "shared_buffers='1GB'" >> ~/tmp_master_dir_polardb_pg_1100_bld/postgresql.auto.conf
echo "shared_buffers='1GB'" >> ~/tmp_replica_dir_polardb_pg_1100_bld1/postgresql.auto.conf
echo "shared_buffers='1GB'" >> ~/tmp_replica_dir_polardb_pg_1100_bld2/postgresql.auto.conf  
pg_ctl restart -m fast -D ~/tmp_master_dir_polardb_pg_1100_bld
pg_ctl restart -m fast -D ~/tmp_replica_dir_polardb_pg_1100_bld1
pg_ctl restart -m fast -D ~/tmp_replica_dir_polardb_pg_1100_bld2

9、挑1条跑得比较慢的SQL(Q17), 对比开启ePQ和关闭ePQ的性能差异:

9.1、开启ePQ测试

cd ~/tpch-dbgen    
./build.sh --user=postgres --db=postgres --host=/tmp --port=5432 --run=17 --option="set polar_enable_px = on; SET polar_px_dop_per_node = 1; set work_mem = '16MB'; set statement_timeout='60min';"

耗时

tail -n 2 result  
  
Time: 93674.348 ms (01:33.674)

9.2、关闭ePQ测试 ( Tips: 即使不开启ePQ跨机并行, 也可以使用单机并行, 单机并行的设置方法请参考: 《PostgreSQL 11 并行计算算法，参数，强制并行度设置》 )

设置单机并行度

psql  
  
ALTER TABLE nation SET (parallel_workers = 4);    
ALTER TABLE region SET (parallel_workers = 4);    
ALTER TABLE supplier SET (parallel_workers = 4);    
ALTER TABLE part SET (parallel_workers = 4);    
ALTER TABLE partsupp SET (parallel_workers = 4);    
ALTER TABLE customer SET (parallel_workers = 4);    
ALTER TABLE orders SET (parallel_workers = 4);    
ALTER TABLE lineitem SET (parallel_workers = 4);    
\q

开启单机并行测试

cd ~/tpch-dbgen     
./build.sh --user=postgres --db=postgres --host=/tmp --port=5432 --option="set polar_enable_px=off; set work_mem='16MB'; set statement_timeout='60min'; set min_parallel_index_scan_size=0; set min_parallel_table_scan_size=0; set max_parallel_workers_per_gather=4; set parallel_leader_participation=0; set parallel_setup_cost=0; set parallel_tuple_cost=0;" --run=17

options解释

-- 关闭 ePQ 功能 
SET polar_enable_px = off;  
-- 设置每个节点的 ePQ 并行度为 1
-- 强制单机并行
set min_parallel_index_scan_size=0;
set min_parallel_table_scan_size=0;
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
-- 单机并行度为 min(max_parallel_workers_per_gather, parallel_workers, max_parallel_workers, max_worker_processes) 
set max_parallel_workers_per_gather=4;
-- leader不参与并行
set parallel_leader_participation=0;

耗时

grep -E "^Time|^####" result
Time: 31223.427 ms (00:31.223)
Time: 15963.171 ms (00:15.963)
Time: 35749.031 ms (00:35.749)
Time: 61383.210 ms (01:01.383)
Time: 40366.896 ms (00:40.367)
Time: 25500.051 ms (00:25.500)
Time: 32717.880 ms (00:32.718)
Time: 32240.570 ms (00:32.241)
Time: 52082.570 ms (00:52.083)
Time: 39573.756 ms (00:39.574)
Time: 7920.625 ms (00:07.921)
Time: 39995.829 ms (00:39.996)
Time: 16497.227 ms (00:16.497)
Time: 28636.686 ms (00:28.637)
Time: 53713.010 ms (00:53.713)
Time: 14875.466 ms (00:14.875)
// Time: 93674.348 ms (01:33.674)   -- q17 ePQ并行;  关闭ePQ并行时大于60分钟
Time: 90327.818 ms (01:30.328)
Time: 28491.345 ms (00:28.491)
// Time: 41250.308 ms (00:41.250)   -- q20 ePQ并行;  关闭ePQ并行时大于60分钟
Time: 317478.966 ms (05:17.479)
Time: 10635.676 ms (00:10.636)

总耗时约1110秒

10、开启ePQ, 跑完22条查询要多久?

cd ~/tpch-dbgen    
./build.sh --user=postgres --db=postgres --host=/tmp --port=5432 --run=-1 --option="set polar_enable_px = on; SET polar_px_dop_per_node = 1; set work_mem = '16MB'; set statement_timeout='60min';"

耗时 ( PS: 开启ePQ时第18条sql会导致OOM, 所以第18条使用了单机并行: ./build.sh --user=postgres --db=postgres --host=/tmp --port=5432 --option="set polar_enable_px=off; set work_mem='16MB'; set statement_timeout='60min'; set min_parallel_index_scan_size=0; set min_parallel_table_scan_size=0; set max_parallel_workers_per_gather=4; set parallel_leader_participation=0; set parallel_setup_cost=0; set parallel_tuple_cost=0;" --run=18 )

grep -E "^Time|^####" result  
  
Time: 61261.347 ms (01:01.261)
Time: 11129.968 ms (00:11.130)
Time: 49289.413 ms (00:49.289)
Time: 67216.454 ms (01:07.216)
Time: 49391.736 ms (00:49.392)
Time: 31664.415 ms (00:31.664)
Time: 73454.578 ms (01:13.455)
Time: 42278.018 ms (00:42.278)
Time: 69275.566 ms (01:09.276)
Time: 47604.021 ms (00:47.604)
Time: 6288.017 ms (00:06.288)
Time: 48324.040 ms (00:48.324)
Time: 19890.075 ms (00:19.890)
Time: 34170.097 ms (00:34.170)
Time: 32600.810 ms (00:32.601)
Time: 8994.104 ms (00:08.994)
Time: 96417.525 ms (01:36.418)
// Time: 81338.202 ms (01:21.338)  -- q18 单机并行;  开启ePQ时发生OOM
Time: 30276.696 ms (00:30.277)
Time: 41250.308 ms (00:41.250)
Time: 155613.165 ms (02:35.613)
Time: 15536.692 ms (00:15.537)

总耗时约1073秒.

对比单机并行和跨机并行, 针对不同的sql各有千秋.

11、如果要优化PolarDB的TPCH性能, 如何定目标? 我认为可以锚定专业OLAP数据库第一梯队的表现, 例如DuckDB(parquet:列存+非内存, 向量化执行引擎)在该macOS宿主机上的表现.

下载duckdb最新版本(v1.1)

https://duckdb.org/docs/installation/

mkdir ~/Downloads/duckdb  
cd ~/Downloads/duckdb  
curl https://github.com/duckdb/duckdb/releases/download/v1.1.3/duckdb_cli-osx-universal.zip -o ./duckdb_cli-osx-universal.zip   
unzip duckdb_cli-osx-universal.zip

启动duckdb, 限制内存用量(8G), 限制CPU线程不超过4个

./duckdb  
SET memory_limit = '8GB';  
SET max_memory = '8GB';  
SET threads = 4;  
.timer on

加载tpch插件

load tpch;

生成数据并导入到表中(duckdb没有使用索引和约束)

CALL dbgen(sf = 20);

耗时 47秒

D CALL dbgen(sf = 20);  
100% ▕████████████████████████████████████████████████████████████▏   
┌─────────┐  
│ Success │  
│ boolean │  
├─────────┤  
│ 0 rows  │  
└─────────┘  
Run Time (s): real 47.064 user 134.427192 sys 18.943913
D select * from duckdb_indexes();
┌───────────────┬──────────────┬─────────────┬────────────┬────────────┬───────────┬────────────┬───────────┬─────────┬───────────────────────┬───────────┬────────────┬─────────────┬─────────┐
│ database_name │ database_oid │ schema_name │ schema_oid │ index_name │ index_oid │ table_name │ table_oid │ comment │         tags          │ is_unique │ is_primary │ expressions │   sql   │
│    varchar    │    int64     │   varchar   │   int64    │  varchar   │   int64   │  varchar   │   int64   │ varchar │ map(varchar, varchar) │  boolean  │  boolean   │   varchar   │ varchar │
├───────────────┴──────────────┴─────────────┴────────────┴────────────┴───────────┴────────────┴───────────┴─────────┴───────────────────────┴───────────┴────────────┴─────────────┴─────────┤
│                                                                                            0 rows                                                                                            │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

导出到parquet文件(duckdb查询性能参考使用parquet文件的性能)

EXPORT DATABASE './data' (FORMAT PARQUET);   -- 使用相对目录. 默认使用snappy压缩, 压缩级别为3  
  
100% ▕████████████████████████████████████████████████████████████▏   
Run Time (s): real 113.294 user 86.570580 sys 64.863062

关闭duckdb数据库

.quit

重启duckdb数据库, 创建parquet文件视图

./duckdb  
  
CREATE VIEW lineitem AS SELECT * FROM read_parquet('./data/lineitem.parquet');          
CREATE VIEW orders AS SELECT * FROM read_parquet('./data/orders.parquet');          
CREATE VIEW partsupp AS SELECT * FROM read_parquet('./data/partsupp.parquet');          
CREATE VIEW part AS SELECT * FROM read_parquet('./data/part.parquet');          
CREATE VIEW customer AS SELECT * FROM read_parquet('./data/customer.parquet');          
CREATE VIEW supplier AS SELECT * FROM read_parquet('./data/supplier.parquet');          
CREATE VIEW nation AS SELECT * FROM read_parquet('./data/nation.parquet');          
CREATE VIEW region AS SELECT * FROM read_parquet('./data/region.parquet');

抽查parquet数据

D select count(*) from lineitem;  
┌──────────────┐  
│ count_star() │  
│    int64     │  
├──────────────┤  
│    119994608 │  
└──────────────┘  
Run Time (s): real 0.059 user 0.079419 sys 0.007059

生成22条 tpch 查询语句

load tpch;  
copy (select query from tpch_queries()) to './data/tpch.sql' with (quote '', HEADER false);

限制内存用量(8G), 限制CPU线程不超过4个

SET memory_limit = '8GB';  
SET max_memory = '8GB';  
SET threads = 4;  
.timer on

测试tpch 查询

.output ./data/tpch.result       
.read ./data/tpch.sql

耗时

Run Time (s): real 2.485 user 8.296543 sys 0.689389  
Run Time (s): real 0.418 user 1.224057 sys 0.127164  
Run Time (s): real 1.509 user 5.161808 sys 0.437914  
Run Time (s): real 1.336 user 4.446016 sys 0.285473  
Run Time (s): real 1.527 user 5.263083 sys 0.538828  
Run Time (s): real 1.271 user 4.208857 sys 0.601743  
Run Time (s): real 1.685 user 6.208771 sys 0.418602  
Run Time (s): real 2.207 user 7.576238 sys 0.853328  
Run Time (s): real 3.081 user 9.865340 sys 1.105854  
Run Time (s): real 1.775 user 5.751877 sys 0.653938  
Run Time (s): real 0.303 user 0.984823 sys 0.147369  
Run Time (s): real 1.760 user 5.147289 sys 0.688219  
Run Time (s): real 1.670 user 5.876605 sys 0.385250  
Run Time (s): real 1.215 user 3.879712 sys 0.476071  
Run Time (s): real 1.164 user 3.800713 sys 0.515444  
Run Time (s): real 0.296 user 0.975069 sys 0.056834  
Run Time (s): real 1.998 user 6.785169 sys 0.680043  
Run Time (s): real 2.059 user 6.593445 sys 1.041294  
Run Time (s): real 1.894 user 6.337675 sys 0.652705  
Run Time (s): real 1.556 user 5.613203 sys 0.442104  
Run Time (s): real 4.623 user 14.570412 sys 1.678793  
Run Time (s): real 0.395 user 1.260649 sys 0.090190

总耗时约36秒.

这就是OLAP第一梯队数据库的表现. 朝着这个目标准没错. PS: 笔记本测试, 使用电池性能会略差于接电源

tpch数据导入: 433秒有望优化到 47秒
tpch查询: 1073秒有望优化到 36秒

5.6、参考

更多PolarDB 应用实践实验请参考: PolarDB gitee 实验仓库 whudb-course / digoal github

PolarDB 开源基础教程系列 7.5 应用实践之 TPCH性能优化

5、tpch 测试

5.1、tpch 测试软件介绍

5.2、加速Tips

5.3、tpch 测试例子

5.4、tpch 20G 导入性能测试示例

5.5、tpch 20G 查询性能测试示例

5.6、参考

PolarDB PostgreSQL 版

热门文章

最新文章

相关电子书