特性实践：利用 PolarDB HTAP 加速 TPC-H

在本节，我们将利用 Polar PG 的 HTAP 能力来加速 TPC-H 的执行，实践将基于单机本地存储来运行。

前期准备

部署 PolarDB PG

在运行前默认已经通过文档 实例部署：基于单机本地存储 中部署好本地多节点 HTAP 实例，总计 1 个主节点（运行于 5432 端口），2 个只读节点（运行于 5433/5434 端口）。可以通过下面的命令来验证：

1. ps xf

会看到如下的三个进程，1 个主节点（运行于 5432 端口），2 个只读节点（运行于 5433/5434 端口）：

HTAP实例验证

生成 TPC-H 测试数据集

TPC-H特性实践：利用 PolarDB HTAP 加速 TPC-H - 图2 是专门测试数据库分析型场景性能的数据集，一共有 22 条分析型场景下的 SQL。用 TPC-H 可以有效测试 PolarDB 的 HTAP 的能力。我们将通过 tpch-dbgen特性实践：利用 PolarDB HTAP 加速 TPC-H - 图3 工具来生成任意大小的数据集。

1. # 下载 tpch-dbgen
2. git clone https://github.com/qiuyuhang/tpch-dbgen.git

4. # 编译代码
5. cd tpch-dbgen
6. make

执行如下命令，生成模拟数据：

提示

建议先按照该命令，从 10GB 大小的数据开始生成。体验完本案例后还可尝试 100GB 的数据，即将该命令行中的 10 替换为 100。这里需要注意不要超过本机外存容量。

1. # 生成 10GB 数据
2. ./dbgen -s 10

简单说明一下 tpch-dbgen 里面的各种文件：

  • 后缀为 .tbl 表示生成的表数据
  • queries/ 中存放的是 TPC-H 的 22 条 SQL
  • 含有 explain 的 .sql 文件只打印计划，并不实际执行
  • answers/ 中存储了 TPC-H 中 22 条 SQL 的执行结果

导入数据

通过 psql 导入 TPC-H 数据。

提示

注意，一直要在 tpch-dbgen/ 目录下执行

1. # 创建表
2. psql -f dss.ddl

4. # 进入 psql 命令行
5. psql

1. # 导入数据
2. \copy nation from 'nation.tbl' DELIMITER '|';
3. \copy region from 'region.tbl' DELIMITER '|';
4. \copy supplier from 'supplier.tbl' DELIMITER '|';
5. \copy part from 'part.tbl' DELIMITER '|';
6. \copy partsupp from 'partsupp.tbl' DELIMITER '|';
7. \copy customer from 'customer.tbl' DELIMITER '|';
8. \copy orders from 'orders.tbl' DELIMITER '|';
9. \copy lineitem from 'lineitem.tbl' DELIMITER '|';

数据导入完成后，逐行执行如下命令，对创建的表设置最大并行度：

1. # 对需要 PX 查询的表设置最大并行度（若不设置则不会进入 PX 查询）
2. alter table nation set (px_workers = 100);
3. alter table region set (px_workers = 100);
4. alter table supplier set (px_workers = 100);
5. alter table part set (px_workers = 100);
6. alter table partsupp set (px_workers = 100);
7. alter table customer set (px_workers = 100);
8. alter table orders set (px_workers = 100);
9. alter table lineitem set (px_workers = 100);

执行单机并行查询

模拟数据导入到 PolarDB 后，我们先执行单机并行查询，观测一下查询速度。

  1. psql 连入后，执行如下命令，开启计时。

1. \timing 2. 通过 max_parallel_workers_per_gather 参数设置单机并行度：

1. set max_parallel_workers_per_gather=2; -- 并行度设置为 2 3. 执行如下命令，查看执行计划。

1. \i queries/q18.explain.sql

可以看到如图所示的 2 个并行度的并行计划：

1. QUERY PLAN 2. ----------------------------------------------------------------------------------------------------------------------------------------------------------------- 3. Limit (cost=9364138.51..9364141.51 rows=100 width=71) 4. -> GroupAggregate (cost=9364138.51..9380736.94 rows=553281 width=71) 5. Group Key: orders.o_totalprice, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey 6. -> Sort (cost=9364138.51..9365521.71 rows=553281 width=44) 7. Sort Key: orders.o_totalprice DESC, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey 8. -> Hash Join (cost=6752588.87..9294341.50 rows=553281 width=44) 9. Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) 10. -> Seq Scan on lineitem (cost=0.00..1724338.96 rows=59979696 width=9) 11. -> Hash (cost=6749642.22..6749642.22 rows=138372 width=43) 12. -> Hash Join (cost=6110531.76..6749642.22 rows=138372 width=43) 13. Hash Cond: (orders.o_custkey = customer.c_custkey) 14. -> Hash Join (cost=6032162.96..6658785.84 rows=138372 width=24) 15. Hash Cond: (orders.o_orderkey = lineitem_1.l_orderkey) 16. -> Seq Scan on orders (cost=0.00..410917.44 rows=15000544 width=20) 17. -> Hash (cost=6029892.31..6029892.31 rows=138372 width=4) 18. -> Finalize GroupAggregate (cost=5727599.96..6028508.59 rows=138372 width=4) 19. Group Key: lineitem_1.l_orderkey 20. Filter: (sum(lineitem_1.l_quantity) > '313'::numeric) 21. -> Gather Merge (cost=5727599.96..6016055.08 rows=830234 width=36) 22. Workers Planned: 2 23. -> Partial GroupAggregate (cost=5726599.94..5919225.45 rows=415117 width=36) 24. Group Key: lineitem_1.l_orderkey 25. -> Sort (cost=5726599.94..5789078.79 rows=24991540 width=9) 26. Sort Key: lineitem_1.l_orderkey 27. -> Parallel Seq Scan on lineitem lineitem_1 (cost=0.00..1374457.40 rows=24991540 width=9) 28. -> Hash (cost=50827.80..50827.80 rows=1500080 width=23) 29. -> Seq Scan on customer (cost=0.00..50827.80 rows=1500080 width=23) 30. (27 rows) 4. 执行 SQL，可以看到部分结果（按 q 不查看全部结果）和运行时间，运行时间为 1 分 23 秒：

1. \i queries/q18.sql

q18的单机并行结果

提示

如果单机并行度太高，可能会出现如下的错误提示：pq: could not resize shared memory segment "/PostgreSQL.2058389254" to 12615680 bytes: No space left on device。原因是 Docker 预设的 shared memory 空间不足，可以参考 该链接特性实践：利用 PolarDB HTAP 加速 TPC-H - 图5 设置参数并重启 Docker 进行解决。

执行 PolarDB HTAP 跨机并行查询

在体验完单机并行查询后，我们开启跨机并行查询。然后使用相同的数据，重新体验一下查询性能。

  1. 在 psql 后，执行如下命令，开启计时（若已开启，可跳过）。

1. \timing 2. 执行如下命令，开启跨机并行查询（PX）。

1. set polar_enable_px=on; 3. 设置每个节点的并行度为 1。

1. set polar_px_dop_per_node=1; 4. 执行如下命令，查看执行计划。

1. \i queries/q18.explain.sql

该引擎集群带有 2 个 RO 节点，开启 PX 后默认并行度为 2x1=2 个：

``` 1. QUERY PLAN 2. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  1. Limit (cost=0.00..93628.34 rows=100 width=47)
  2. -> PX Coordinator 2:1 (slice1; segments: 2) (cost=0.00..93628.33 rows=100 width=47)
  3. Merge Key: orders.o_totalprice, orders.o_orderdate
  4. -> Limit (cost=0.00..93628.31 rows=50 width=47)
  5. -> GroupAggregate (cost=0.00..93628.31 rows=11995940 width=47)
  6. Group Key: orders.o_totalprice, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey
  7. -> Sort (cost=0.00..92784.19 rows=11995940 width=44)
  8. Sort Key: orders.o_totalprice DESC, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey
  9. -> Hash Join (cost=0.00..22406.63 rows=11995940 width=44)
  10. Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
  11. -> PX Hash 2:2 (slice2; segments: 2) (cost=0.00..4301.49 rows=29989848 width=9)
  12. Hash Key: lineitem.l_orderkey
  13. -> Partial Seq Scan on lineitem (cost=0.00..2954.65 rows=29989848 width=9)
  14. -> Hash (cost=10799.35..10799.35 rows=83024 width=39)
  15. -> PX Hash 2:2 (slice3; segments: 2) (cost=0.00..10799.35 rows=83024 width=39)
  16. Hash Key: orders.o_orderkey
  17. -> Hash Join (cost=0.00..10789.21 rows=83024 width=39)
  18. Hash Cond: (customer.c_custkey = orders.o_custkey)
  19. -> PX Hash 2:2 (slice4; segments: 2) (cost=0.00..597.52 rows=750040 width=23)
  20. Hash Key: customer.c_custkey
  21. -> Partial Seq Scan on customer (cost=0.00..511.44 rows=750040 width=23)
  22. -> Hash (cost=9993.50..9993.50 rows=83024 width=20)
  23. -> PX Hash 2:2 (slice5; segments: 2) (cost=0.00..9993.50 rows=83024 width=20)
  24. Hash Key: orders.o_custkey
  25. -> Hash Semi Join (cost=0.00..9988.30 rows=83024 width=20)
  26. Hash Cond: (orders.o_orderkey = lineitem_1.l_orderkey)
  27. -> Partial Seq Scan on orders (cost=0.00..1020.90 rows=7500272 width=20)
  28. -> Hash (cost=7256.00..7256.00 rows=166047 width=4)
  29. -> PX Broadcast 2:2 (slice6; segments: 2) (cost=0.00..7256.00 rows=166047 width=4)
  30. -> Result (cost=0.00..7238.62 rows=83024 width=4)
  31. Filter: ((sum(lineitem_1.l_quantity)) > '313'::numeric)
  32. -> Finalize HashAggregate (cost=0.00..7231.79 rows=207559 width=12)
  33. Group Key: lineitem_1.l_orderkey
  34. -> PX Hash 2:2 (slice7; segments: 2) (cost=0.00..7205.20 rows=207559 width=12)
  35. Hash Key: lineitem_1.l_orderkey
  36. -> Partial HashAggregate (cost=0.00..7197.41 rows=207559 width=12)
  37. Group Key: lineitem_1.l_orderkey
  38. -> Partial Seq Scan on lineitem lineitem_1 (cost=0.00..2954.65 rows=29989848 width=9)
  39. Optimizer: PolarDB PX Optimizer
  40. (39 rows) ```
  41. 执行 SQL：

1. \i queries/q18.sql

可以看到部分结果（按 q 不查看全部结果）和运行时间，运行时间为 1 分钟，比单机并行的结果降低了 27.71% 的运行时间。感兴趣的同学也可加大并行度或者数据量查看提升程度。

q18的HTAP跨机并行结果

跨机并行查询会去获取全局一致性视图，因此得到的数据是一致的，无需担心数据正确性。可以通过如下方式手动设置跨机并行查询的并行度：

1. set polar_px_dop_per_node = 1;
2. \i queries/q18.sql

4. set polar_px_dop_per_node = 2;
5. \i queries/q18.sql

7. set polar_px_dop_per_node = 4;
8. \i queries/q18.sql

在 GitHub 上编辑此页 特性实践：利用 PolarDB HTAP 加速 TPC-H - 图7

贡献者: 何柯文, 棠羽