AI 助理
备案控制台
开发者社区 PolarDB开源 PolarDB PostgreSQL 版 文章 正文

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

2025-02-10 82 发布于浙江
版权
举报
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版，通用型 2核8GB 50GB
简介： PolarDB在复杂查询、大数据量计算与分析场景的测试和优化实践.

5、PolarDB/PostgreSQL TPCH测试

b站视频链接

Youtube视频链接


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个并行的活跃任务.
  • 开启PolarDB预分配功能(使用共享存储时效果比较明显, 预分配可以减少IO次数, 降低云盘IO延迟带来的性能损耗)
  • 开始导入数据前使用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. 由于时序数据的特点是数据量大、持续写入, 这个插件在数据高速写入、数据压缩和整理等方面都有改进, 获得了比较好的效果.
  • 对于数据量巨大的表, 也可以考虑通过分区表来降低每个分区的索引层级, 从而提升索引的检索和构建等性能. 索引本身也能通过并行构建.

查询加速:

  • 默认值可以修改src/backend/utils/misc/guc.c实现, 仔细查阅该文件了解更多PolarDB定制参数
  • 不开启/不支持SQL并行的情况下, 使用多任务并行(将22条SQL的执行快慢从慢到快启动select任务, 逐一启动任务并始终保持N个并行的活跃任务.)
  • 修改配置(例如优化器校准因子相关配置、优化器JOIN方法相关配置、哈希表内存大小相关配置等) 参考:
  • 使用列存储、JIT和向量化, 能节约存储空间、加速导入、加速查询. (通常可以比行存储性能提升10倍以上). 需要修改内核, 可以参考数据库筑基课提到的一些开源项目,
  • 参数优化:
  • 加大shared_buffers可以提高查询速度
  • 加大work_mem可以提高查询速度
  • 通过索引可以提示某些sql的查询性能, 详见:https://github.com/digoal/gp_tpch/tree/master/dss注意加索引也会导致占用更多的空间以及建索引本身的耗时, 还有索引引入了更多的random io, 效果好坏还要看硬件配置.
  • 对于极限测试, 每一条SQL都可以单独优化(例如使用不同的参数, JOIN方法, 索引等), 简单粗暴的话就调tpch的测试脚本, 复杂一点的话可以改hook, 最具实用价值的是改优化器实现通用的复杂SQL优化.

其他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)

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
云原生数据仓库AnalyticDB MySQL版
容器
SQL
关系型数据库
PolarDB
分布式数据库
关键词：
云原生数据库 PolarDB性能优化
云原生数据库 PolarDB开源
云原生数据库 PolarDB应用
云原生数据库 PolarDB开源应用
云原生数据库 PolarDB教程
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
+关注
2221文章246问答14视频
目录
打赏
0
8
8
0
20690
分享
相关文章
数据库社区运营小北
|
5天前
|
存储 关系型数据库 分布式数据库
登顶TPC-C｜云原生数据库PolarDB技术揭秘：单机性能优化篇
阿里云PolarDB云原生数据库在TPC-C基准测试中，以20.55亿tpmC的成绩打破性能与性价比世界纪录。此外，国产轻量版PolarDB已上线，提供更具性价比的选择。
数据库社区运营小北
128 0
德哥
|
1月前
|
存储 NoSQL 关系型数据库
PolarDB开源数据库进阶课17 集成数据湖功能
本文介绍了如何在PolarDB数据库中接入pg_duckdb、pg_mooncake插件以支持数据湖功能, 可以读写对象存储的远程数据, 支持csv, parquet等格式, 支持delta等框架, 并显著提升OLAP性能。
德哥
59 1
阿里云瑶池数据库_
|
4天前
|
存储 关系型数据库 分布式数据库
登顶TPC-C｜云原生数据库PolarDB技术揭秘：单机性能优化篇
日前，阿里云PolarDB云原生数据库以超越原记录2.5倍的性能一举登顶TPC-C基准测试排行榜，以每分钟20.55亿笔交易（tpmC）和单位成本0.8元人民币（price/tpmC）的成绩刷新TPC-C性能和性价比双榜的世界纪录。 每一个看似简单的数字背后，都蕴含着无数技术人对数据库性能、性价比和稳定性的极致追求，PolarDB的创新步伐从未止步。「阿里云瑶池数据库」公众号特此推出「PolarDB登顶TPC-C技术揭秘」系列硬核文章，为你讲述“双榜第一”背后的故事，敬请关注！
阿里云瑶池数据库_
56 0
登顶TPC-C｜云原生数据库PolarDB技术揭秘：单机性能优化篇
码农小达人
|
25天前
|
关系型数据库 分布式数据库 数据库
喜报｜PolarDB开源社区荣获“2024数据库国内活跃开源项目”奖
喜报｜PolarDB开源社区荣获“2024数据库国内活跃开源项目”奖
码农小达人
33 1
德哥
|
1月前
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课18 通过pg_bulkload适配pfs实现批量导入提速
本文介绍了如何修改 `pg_bulkload` 工具以适配 PolarDB 的 PFS（Polar File System），从而加速批量导入数据。实验环境依赖于 Docker 容器中的 loop 设备模拟共享存储。通过对 `writer_direct.c` 文件的修改，替换了一些标准文件操作接口为 PFS 对应接口，实现了对 PolarDB 15 版本的支持。测试结果显示，使用 `pg_bulkload` 导入 1000 万条数据的速度是 COPY 命令的三倍多。此外，文章还提供了详细的步骤和代码示例，帮助读者理解和实践这一过程。
德哥
48 0
德哥
|
1月前
|
存储 容灾 关系型数据库
PolarDB开源数据库进阶课11 激活容灾(Standby)节点
本文介绍了如何激活PolarDB容灾（Standby）节点，实验环境依赖于Docker容器中用loop设备模拟共享存储。通过`pg_ctl promote`命令可以将Standby节点提升为主节点，使其能够接收读写请求。激活后，原Standby节点不能再成为PolarDB集群的Standby节点。建议删除对应的复制槽位以避免WAL文件堆积。相关操作和配置请参考系列文章及视频教程。
德哥
37 1
德哥
|
1月前
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课15 集成DeepSeek等大模型
本文介绍了如何在PolarDB数据库中接入私有化大模型服务，以实现多种应用场景。实验环境依赖于Docker容器中的loop设备模拟共享存储，具体搭建方法可参考相关系列文章。文中详细描述了部署ollama服务、编译并安装http和openai插件的过程，并通过示例展示了如何使用这些插件调用大模型API进行文本分析和情感分类等任务。此外，还探讨了如何设计表结构及触发器函数自动处理客户反馈数据，以及生成满足需求的SQL查询语句。最后对比了不同模型的回答效果，展示了deepseek-r1模型的优势。
德哥
102 0
德哥
|
1月前
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课14 纯享单机版
PolarDB不仅支持基于“共享存储+多计算节点”的集群版，还提供类似开源PostgreSQL的单机版。单机版部署简单，适合大多数应用场景，并可直接使用PostgreSQL生态插件。通过Docker容器、Git克隆代码、编译软件等步骤，即可完成PolarDB单机版的安装与配置。具体操作包括启动容器、进入容器、克隆代码、编译软件、初始化实例、配置参数及启动数据库。此外，还有多个相关教程和视频链接供参考，帮助用户更好地理解和使用PolarDB单机版。
德哥
50 0
德哥
|
1月前
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课13 单机版转换为集群版
本文介绍如何将“本地存储实例”转换为“共享存储实例”，依赖于先前搭建的实验环境。主要步骤包括：准备PFS二进制文件、格式化共享盘为pfs文件系统、启动pfsd服务、停库并拷贝数据到pfs内、修改配置文件，最后启动实例。通过这些操作，成功实现了从本地存储到共享存储的转换，并验证了新实例的功能。相关系列文章和视频链接提供了更多背景信息和技术细节。
德哥
25 0
德哥
|
1月前
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课7 实时流式归档
本文介绍了如何在PolarDB RAC一写多读集群中实现实时归档，确保WAL日志的及时备份。实验依赖于Docker容器和loop设备模拟的共享存储环境。通过配置主节点的`pg_hba.conf`、创建复制槽以及使用`pg_receivewal`工具，实现实时接收并归档WAL文件。此外，还提供了详细的命令行帮助和相关文档链接，方便读者参考和操作。注意：如果已搭建容灾节点，则无需重复进行实时归档。
德哥
15 0
PolarDB开源

PolarDB PostgreSQL 版
+关注

热门文章

最新文章

  • 1
    PolarDB 开源基础教程系列 3 安装部署
    59
  • 2
    PolarDB 开源版 使用TimescaleDB 实现时序数据高速写入、压缩、实时聚合计算、自动老化等
    32
  • 3
    PolarDB 开源基础教程系列 1 架构解读
    20
  • 4
    PolarDB开源数据库进阶课15 集成DeepSeek等大模型
    20
  • 5
    如何一键本地部署PolarDB for PostgreSQL
    19
  • 6
    沉浸式学习PostgreSQL|PolarDB 12: 如何快速构建 海量 逼真 测试数据
    18
  • 7
    沉浸式学习PostgreSQL|PolarDB 16: 植入通义千问大模型+文本向量化模型, 让数据库具备AI能力
    18
  • 8
    若依框架数据源切换为pg库
    18
  • 9
    PolarDB-X 动手实践系列第一讲：如何一键部署开源 PolarDB-X
    18
  • 10
    PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍（上）
    14
  • 1
    PolarDB开源数据库进阶课18 通过pg_bulkload适配pfs实现批量导入提速
    48
  • 2
    PolarDB开源数据库进阶课17 集成数据湖功能
    59
  • 3
    PolarDB开源数据库进阶课16 接入PostGIS全功能及应用举例
    42
  • 4
    PolarDB开源数据库进阶课15 集成DeepSeek等大模型
    102
  • 5
    PolarDB开源数据库进阶课14 纯享单机版
    50
  • 6
    PolarDB开源数据库进阶课13 单机版转换为集群版
    25
  • 7
    PolarDB开源数据库进阶课12 集群版转换为单机版
    28
  • 8
    PolarDB开源数据库进阶课11 激活容灾(Standby)节点
    37
  • 9
    PolarDB开源数据库进阶课9 读写分离
    43
  • 10
    PolarDB开源数据库进阶课10 计算节点全毁, 灾难恢复
    35

    • 相关产品

  • 云原生数据库 PolarDB
    文档详情 产品详情

    • 相关课程

    更多
  • PolarDB for PostgreSQL 开源人才初级认证培训课程
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • 关系型数据库ACP认证课程
  • 关系型数据库线上预习视频
  • PolarDB for PostgreSQL 入门

    • 相关电子书

    更多
  • PolarDB开发者大会：PolarDB产品演进和未来展望
  • PolarDB+AnalyticDB助力交通物流行业系统升级
  • PolarDB NL2SQL: 帮助您写出准确、优化的SQL

    • 相关实验场景

    更多
  • 体验PolarDB-X透明分布式
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • 阿里云百炼xAnalyticDB PostgreSQL构建AIGC应用
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 使用CloudLens采集PolarDB日志并进行审计分析
    • 下一篇
    阿里云oss简介和如何对接使用
    目录