国产数据库PolarDB公开课-7.1 TPCH测试
digoal约 38 分钟参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
PolarDB公开课来了, 国庆拿下这个国产信创数据库
系列文章导航
7、国产数据库PolarDB公开课-6 使用PostgreSQL开源工具/插件
补一小节TPCH的测试, PolarDB全国大学生数据库竞赛题目相关.
5、PolarDB TPCH 测试
5.1、TPCH 测试软件介绍
tpch是一种工业标准的数据库和硬件性能测试方法, 通常用来测试数据库或硬件在处理复杂SQL时的性能表现, 另一个更复杂一些的工业标准测试是tpc-ds.
本节内容使用这个章节搭建的开发环境进行说明: 《开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.9 PolarDB开源版本必学特性 - 玩转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延迟带来的性能损耗) 默认值可以修改 src/backend/utils/misc/guc.c实现, 仔细查阅该文件了解更多PolarDB定制参数开始导入数据前使用unlogged table, 在索引创建完成后再改成logged table. 提示: 通过修改pg_class.relpersistence可以实现(u=unlogged, p=persistence). 参数优化: 导入前关闭autovacuum, 可以降低autoanalyze带来的影响. 加大maintenance_work_mem参数值, 可以提高创建索引的速度. 加大shared_buffers可以提高导入速度 加大checkpoint窗口可以降低检查点对IO的影响 使用unix socket代替tcp连接可以提高导入速度
查询加速:
使用单机并行, 通过修改表的配置和参数可启用强制并行度. 参考: 《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方法相关配置、哈希表内存大小相关配置等) 参考: 《PostgreSQL 11 postgresql.conf 参数模板 - 珍藏级》 使用列存储和JIT, 能节约存储空间、加速导入、加速查询. (通常可以比行存储性能提升10倍以上). 需要修改内核, 可以参考数据库筑基课提到的一些开源项目, 参数优化: 加大shared_buffers可以提高查询速度 加大work_mem可以提高查询速度 通过索引可以提示某些sql的查询性能, 详见: https://github.com/digoal/gp_tpch/tree/master/dss 注意加索引也会导致占用更多的空间以及建索引本身的耗时. 对于极限测试, 每一条SQL都可以单独优化(例如使用不同的参数, JOIN方法, 索引等), 简单粗暴的话就调tpch的测试脚本, 复杂一点的话可以改hook, 最具实用价值的是改优化器实现通用的复杂SQL优化. 还有一些情况是可能同样的配置某些SQL可能会导致内存耗尽发生OOM, 可能是hash table过大造成, 可以考虑支持一下hash table split to disk, 多阶段join? 例如 《DuckDB 0.6.0 内存管理增强, 提升超出内存大小的大数据集hashjoin,sort,window function性能》
更多方法请参考:
《PolarDB for PostgreSQL TPCH 测试与优化 tips》 《PostgreSQL PoC测试优化 tips》
5.3、tpch 测试例子
下面使用build.sh这个脚本来进行测试.
0、可选步骤. 该脚本没有提供输入数据库密码的参数，需要通过设置 PGPASSWORD 为数据库用户的数据库密码来完成认证：
export PGPASSWORD=<your password>
1、清理已有表
./build.sh --user=postgres --db=postgres --host=/home/postgres/tmp_master_dir_polardb_pg_1100_bld --port=5432 --clean
2、生成指定大小的数据集(本例 100MB)并导入PolarDB
./build.sh --user=postgres --db=postgres --host=/home/postgres/tmp_master_dir_polardb_pg_1100_bld --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=/home/postgres/tmp_master_dir_polardb_pg_1100_bld --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=/home/postgres/tmp_master_dir_polardb_pg_1100_bld --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=/home/postgres/tmp_master_dir_polardb_pg_1100_bld --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 功能的开关
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 /home/postgres/tmp_master_dir_polardb_pg_1100_bld/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|^####" resultTime: 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.1/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.943913D 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第一梯队数据库的表现. 朝着这个目标准没错.
tpch数据导入: 433秒 有望优化到 47秒 tpch查询: 1073秒 有望优化到 36秒
5.6、参考
《PolarDB for PostgreSQL TPCH 测试与优化tips》 《DBA老司机都头痛的TPCH优化, AI 10秒就搞定了》 《PG被DuckDB碾压,该反省哪些方面? DuckDB v0.10.3 在Macmini 2023款上的tpch性能表现如何? PostgreSQL使用duckdb_fdw 的tpch加速性能表现如何?》 《DuckDB 0.9.2 TPC-DS 测试》 《DuckDB 采用外部 parquet 格式存储 - tpch 测试 - in_memory VS in_parquet》 《DuckDB vs PostgreSQL TPC-H 测试》 《DuckDB TPC-H 测试》 《DuckDB 定位OLAP方向的SQLite, 适合嵌入式数据分析 - tpch测试与简单试用》 《PostgreSQL 13 新增 hash 内存参数, 提高hash操作性能 - hash_mem_multiplier - work_mem的倍数 - 支持超越内存时采用disk 存储hash表, 支持更多场景使用hashagg, TPCH性能大幅提升》 《Deepgreen(Greenplum) 多机部署测试 , TPC-H VS citus》 《PostgreSQL sharding : citus 系列2 - TPC-H》 《[转载] TPCH 22条SQL语句分析》 《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》 《TPC-H测试 - PostgreSQL 10 vs Deepgreen(Greenplum)》 《PostgreSQL 并行计算tpc-h测试和优化分析》 《Greenplum,HAWQ interval parser带来的问题 - TPCH 测试注意啦》 https://apsaradb.github.io/PolarDB-for-PostgreSQL/zh/operation/tpch-test.html
彩蛋0:PolarDB 各个版本傻傻分不清???
信创名单查询:
http://www.itsec.gov.cn/aqkkcp/cpgg/202312/t20231226_162074.html http://www.itsec.gov.cn/aqkkcp/cpgg/202409/t20240930_194299.html
PolarDB 各个版本的介绍 (线下软件订阅版、云服务版、开源版):
https://www.aliyun.com/activity/database/polardb-v2
PolarDB 线下软件订阅版 询价:
https://market.aliyun.com/products/56024006/cmfw00066651.html
PolarDB 大使活动(分销赢奖励):
https://www.aliyun.com/activity/new/polardb-yunparter
彩蛋1:数据库生态工具 & 信创开源DB
用好周边工具, 数据库管理水平战胜90%老司机
1、管控软件
云猿生开源的kubeblocks, 如果你要管理很多套并且种类很多的数据库产品, 推荐选择.
https://github.com/apecloud/kubeblocks
乘数开源的clup, 专门用来管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 企业可以关注一下.
https://www.csudata.com/
若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套数据库, 并且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
海信聚好看的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
Bytebase 的目标非常远大, 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
D-Smart, Oracle老前辈白老大他们搞的, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
通过信创并且开源的数据库:
PolarDB for PostgreSQL
https://github.com/ApsaraDB/PolarDB-for-PostgreSQL
以下PG系国产数据库也非常值得关注: HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、ProtonBase(云原生分布式数仓. https://protonbase.com/ ).
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: