保姆教程｜PolarDB数据库决赛提交作品指南

digoal2024年12月07日约 11 分钟

参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;

PolarDB数据库创新设计国赛 - 决赛提交作品指南

看过
《PolarDB数据库创新设计国赛 - 初赛提交作品指南》的同学们一定知道我的风格, 这个指南肯定是保姆级的.

太长不看版 (保姆级提交决赛作品教程)

提交方法巨简单, 但是因为决赛比初赛略微复杂, 我已经把决赛要求的SQL都放在了我自己的一个分支里(快献上感谢吧), 你就不用去PDF文件里扣了, 真的是重复且无趣而且容易拷贝错误的操作. 同时我做了一些些的优化(后面会详细说到):

1、下载如下代码并压缩:

git clone -c core.symlinks=true --depth 1 -b polardb-competition-2024 https://gitee.com/digoal/PolarDB-for-PostgreSQL

zip -r PolarDB-for-PostgreSQL.zip PolarDB-for-PostgreSQL/

2、将PolarDB-for-PostgreSQL.zip代码文件提交到比赛平台

登陆比赛网站: https://tianchi.aliyun.com/competition/entrance/532261/submission/1365

依次点击如下即可提交比赛代码并进行评测:

提交结果 - 镜像路径(配置路径) - TCCFile(上传) PolarDB-for-PostgreSQL.zip - 上传完毕后点击确定 - 提交

正文

1、在你自己的电脑中准备docker desktop调试环境. (你都已经进决赛了, 这个环境相信大家都有了.)

略

2、下载开发镜像, 启动容器, 进入容器

# 下载开发镜像    
docker pull registry.cn-hangzhou.aliyuncs.com/polardb_pg/polardb_pg_devel:ubuntu20.04      

# 启动容器    
docker run -d -it -P --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:ubuntu20.04 bash      

# 进入容器    
docker exec -ti polardb_pg_devel bash

3、在容器中下载比赛分支代码 (下载我这个分支, 已经吧sql文件都放好了.)

cd /tmp    

git clone -c core.symlinks=true --depth 1 -b polardb-competition-2024 https://gitee.com/digoal/PolarDB-for-PostgreSQL

4、在容器中测试以上修改过的代码

生成测试数据若干

cd /tmp/PolarDB-for-PostgreSQL/tpch-dbgen    

make -f makefile.suite    

./dbgen -f -s 0.1

将测试数据放到与测试机一致的目录

cd /tmp/PolarDB-for-PostgreSQL/tpch-dbgen    
sudo mkdir /data    
sudo chown -R postgres:postgres /data    
sudo mv *.tbl /data/

初始化PolarDB数据库集群

cd /tmp/PolarDB-for-PostgreSQL    
chmod 700 polardb_build.sh       
./polardb_build.sh --without-fbl --debug=off

导入数据、创建索引

chmod 777 tpch_copy.sh     

# 清理已有的数据, 方便你多次测试时使用. 第一次执行清理时因为数据库里还没有表可能会抛出一些错误, 不用管.    
./tpch_copy.sh --clean  

# 执行数据导入、创建索引、增加fk等操作   
./tpch_copy.sh

5、决赛就看这22条SQL, 决赛计算这22条SQL的执行耗时, 越快越好.

试试运行22条query, 例如运行第1条sql:

cd /tmp/PolarDB-for-PostgreSQL/  
time psql -f sql/1.sql

SQL 优化小贴士:

根据SQL的执行计划来进行分析, 看SQL的耗时卡点是什么, 针对性的进行优化. 例如查看sql/1.sql的执行计划和各个耗时点位:
```
echo"explain (analyze,verbose,timing,costs,buffers) `cat sql/1.sql`" | psql -f -   
```
可以任意修改sql/$.sql文件, 在每一条sql的前面都可以set和unset已设置过的参数, 用以加速当前sql.
- 例如使用什么JOIN方法?hash/merge/nestloop join
- 设置多大的work_mem
- 设置多少个并行parallel
- 是否启用ePQ, 以及设置多大的并行
```
例如:
postgres=# show work_mem ;
 work_mem 
----------
 256MB
(1 row)

postgres=# set work_mem = '1GB';
SET

postgres=# show work_mem ;
 work_mem 
----------
 1GB
(1 row)

postgres=# set work_mem to default ;
SET

postgres=# show work_mem ;
 work_mem 
----------
 256MB
(1 row)
```
采用什么索引方法?btree/brin/pg_trgm(gin index)
使用hint固定执行计划? 参考: https://github.com/ossc-db/pg_hint_plan
是否要对数据按某个索引的顺序进行重排? 参看cluster语法
是否使用其他table access method?
是否使用其他计算引擎?custom scan provider? 参考: https://github.com/heterodb/pg-strom ORvops?《PostgreSQL 向量化执行插件(瓦片式实现-vops) 10x提速OLAP》

6、下面简单介绍一下我这个分支相比竞赛初始分支修改了哪些内容?

6.1、 polardb_build.sh

# 使用更大的数据块  
./configure --prefix=$pg_bld_basedir --with-pgport=$pg_bld_port --with-wal-blocksize=32 --with-blocksize=32 $common_configure_flag$configure_flag

# 使用SQL_ASCII编码, 关闭块校验  
su_eval "$pg_bld_basedir/bin/initdb -E SQL_ASCII --locale=C -U $pg_db_user -D $pg_bld_master_dir$tde_initdb_args"

# 一些参数设置  
# add by digoal BEGIN  
        random_page_cost = 1.1   
        shared_buffers = '8GB'
        work_mem = '256MB'
        autovacuum = off  
        checkpoint_timeout = 1d  
        synchronous_commit = off  
        full_page_writes = off  
        maintenance_work_mem = '1GB'
        polar_bulk_read_size = '128kB'
        polar_bulk_extend_size = '4MB'
        polar_index_create_bulk_extend_size = 512  
        parallel_leader_participation = off  
        max_parallel_workers = 6  
        max_parallel_workers_per_gather = 4  
        min_parallel_index_scan_size = 0  
        min_parallel_table_scan_size = 0  
        parallel_setup_cost = 0  
        parallel_tuple_cost = 0  
# add by digoal END

6.2、 tpch_copy.sh

# 使用float8 替代了numeric类型 ; 可能导致结果校验错误  
# sed -i "s/DECIMAL(15,2)/float8/g" ./tpch-dbgen/dss.ddl   

psql -f $tpch_dir/dss.ddl   

# 使用了unlogged table, 并设置几个大表的并行度  
echo"  
update pg_class set relpersistence ='u' where relnamespace='public'::regnamespace;  
alter table lineitem set (parallel_workers = 4);  
alter table orders set (parallel_workers = 3);  
alter table partsupp set (parallel_workers = 3);  
alter table customer set (parallel_workers = 3);  
alter table part set (parallel_workers = 3);" | psql -f -  

# 并行导入, 创建PK  
###################### PHASE 2: load data ######################  
# 读取file.txt中的命令，并使用&符号使其在后台执行  
while IFS= read -r cmd; do
# 当已经有N个命令在执行时，等待直到其中一个执行完毕  
# jobs -p -r  
while (( $(jobs -p -r | wc -l) >= 3 )); do
        sleep 0.1  
done
    {  
  psql -h ~/tmp_master_dir_polardb_pg_1100_bld -c "${cmd}"
    } &  
done < file.txt  

wait

# 增加了几个可能用得上的索引   
# 读取file.txt1中的命令，并使用&符号使其在后台执行  
while IFS= read -r cmd; do
# 当已经有N个命令在执行时，等待直到其中一个执行完毕  
# jobs -p -r  
while (( $(jobs -p -r | wc -l) >= 3 )); do
        sleep 0.1  
done
    {  
  psql -h ~/tmp_master_dir_polardb_pg_1100_bld -c "${cmd}"
    } &  
done < file.txt1  

wait

# 设置FK约束  
###################### PHASE 3: add primary and foreign key ######################  
# 读取file.txt2中的命令，并使用&符号使其在后台执行  
while IFS= read -r cmd; do
# 当已经有8个命令在执行时，等待直到其中一个执行完毕  
# jobs -p -r  
while (( $(jobs -p -r | wc -l) >= 8 )); do
        sleep 0.1  
done
    {  
  psql -h ~/tmp_master_dir_polardb_pg_1100_bld -c "${cmd}"
    } &  
done < file.txt2  

wait

# 约束生效、把unlogged table改为logged table, 生成表统计信息和vm文件.    
psql -h ~/tmp_master_dir_polardb_pg_1100_bld -c "update pg_constraint set convalidated=true where convalidated<>true;"
psql -h ~/tmp_master_dir_polardb_pg_1100_bld -c "update pg_class set relpersistence ='p' where relnamespace='public'::regnamespace;"
psql -h ~/tmp_master_dir_polardb_pg_1100_bld -c "vacuum analyze;"

6.3、新增sql目录及sql文件

sql/1~22.sql

6.4、新增3个文件, 分别是导入和建PK、建索引、加FK约束的SQL.

file.txt  
file.txt1  
file.txt2

7、在容器中压缩代码分支

先停库

pg_ctl stop -m fast -D ~/tmp_master_dir_polardb_pg_1100_bld

清理编译过程产生的内容

cd /tmp/PolarDB-for-PostgreSQL/tpch-dbgen    
make clean -f makefile.suite    

cd /tmp/PolarDB-for-PostgreSQL    
make clean    
make distclean

打包

sudo apt-get install -y zip    
cd /tmp    
zip -r PolarDB-for-PostgreSQL.zip PolarDB-for-PostgreSQL/

8、在宿主机操作, 将容器中打包好的代码文件拷贝到宿主机

cd ~/Downloads    
docker cp polardb_pg_devel:/tmp/PolarDB-for-PostgreSQL.zip ./

9、将代码文件提交到比赛平台

打开网站: https://tianchi.aliyun.com/competition/entrance/532261/submission/1365

依次点击

提交结果
镜像路径 - 配置路径 - TCCFile 上传~/Downloads/PolarDB-for-PostgreSQL.zip- 确定
- 如果之前已经提交了, 要先点击删除再上传
上传完毕后, 点击提交

组委会终于把运行日志开放给同学们了, 在这个提交页面, 你可以刷新查看实时运行的日志, 方便同学们针对慢的SQL进行调试.

10、查看成绩

https://tianchi.aliyun.com/competition/entrance/532261/score

平台查询分数和对应的代码分支好像不太方便, 每次提交出成绩后建议自己保存一下分数以及对应的代码版本. 方便未来基于最优的版本继续迭代.

11、提交代码说明和方案(千万不要忘记这步, 否则即使成绩能进决赛也会被认为放弃决赛资格)

根据初赛评测方案, 将代码说明和方案按标准的邮件标题发送到邮箱[email protected]

决赛赛题和评测方案

参考

《开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.9 PolarDB开源版本必学特性 - 玩转PolarDB 开源社区》
《开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.7 PolarDB开源版本必学特性 - PolarDB 应用实践实验》
https://gitlab.eduxiji.net/csc1/csc-pb/2024-pb/-/tree/main
https://tianchi.aliyun.com/specials/promotion/2024-csdcc-polardb
https://tianchi.aliyun.com/competition/entrance/532261/submission/1365

PS

更多优化方法请参考如下文章, 注意并不是所有的优化tips都是大赛允许的, 所以还请自行甄别:

《PolarDB for PostgreSQL TPCH 测试与优化tips》
《PostgreSQL PoC测试优化 tips》
《性能爽翻了, 在PolarDB plpython中使用DuckDB》
《PostgreSQL 向量化执行插件(瓦片式实现-vops) 10x提速OLAP》
《PolarDB 开源版通过 duckdb_fdw 支持 parquet 列存数据文件以及高效OLAP / TPCH》
《开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.7 PolarDB开源版本必学特性 - PolarDB 应用实践实验》
《PostgreSQL 并行计算tpc-h测试和优化分析》

文末彩蛋:国产数据库周边生态

当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.

1、管控软件

鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.

https://github.com/apecloud/kubeblocks

PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.

https://www.csudata.com/

若航老司机开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套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/

PawSQL, SQL优化和诊断产品.

D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.

https://www.modb.pro/db/567140

3、国产数据库IDE

IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:

https://www.deskui.com

4、数据同步&迁移&备份恢复

NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.

https://www.ninedata.cloud/home

DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.

https://www.dsgdata.com/

公开课

如果你对PolarDB学习感兴趣可以阅读这个公开课系列:

除了PolarDB还非常值得关注的几款PG栈国产数据库:

HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、
IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、
ProtonBase(云原生分布式数仓. https://protonbase.com/ )、
成都文武数据库（https://ww-it.cn）

参考文档点击阅读原文获得

感谢关注我的github (https://github.com/digoal/blog) 及视频号:

参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;

PolarDB数据库创新设计国赛 - 决赛提交作品指南

太长不看版 (保姆级提交 决赛作品 教程)

正文

参考

PS

文末彩蛋:国产数据库周边生态

公开课

除了PolarDB还非常值得关注的几款PG栈国产数据库:

来自同一个声音

OPC 最难的是选题, 但 Marvis 直接搞定自媒体全流程

三年了，我从一罐假蜂蜜里醒了过来

震惊库圈的"删库跑路"后续来了, 数据找回了

PG 之父 Stonebraker 发震撼论文 Chronos : 给 Agent 时代数据库打"分支补丁"

太长不看版 (保姆级提交决赛作品教程)