TOAST和它对PostgreSQL并行的影响

PostgreSQL中文社区 2024-03-05

468

作者

Anthony Sotolongo

译者简介

崔鹏

PostgreSQL爱好者

校对者简介

吴伟略

CET中电技术研发工程师

PostgreSQL爱好者

引言

自从PostgreSQL 9.6版本发布以来，出现了并行查询相关的特性，并成为提高查询性能一个不错的选择。从那时起，并行性不断发展，从而不断提高数据库的性能。为了管理并行的行为，有一些参数可以调优，例如:

max_parallel_workers_per_gather:并行执行查询活动的并行工作者的数量(默认为2)，这些并行工作者来自max_worker_processes定义的进程池，受max_parallel_workers限制，值得一提的是有一个特殊的工作者:leader worker，它协调并收集每个并行工作者的扫描结果。这个leader worker可以参与扫描，也可以不参与扫描，这取决于其在协调活动中的负荷。此外，可以使用parallel_leader_participation参数来控制leader worker在扫描中的参与程度。

min_parallel_table_scan_size:并行扫描需要考虑的最小表数据量(size)(默认为8MB)

min_parallel_index_scan_size:并行扫描需要考虑的最小索引数据量(size)(默认512kB)

Parallel_setup_cost:并行查询启动worker进程的成本(默认值1000)

Parallel_tuple_cost:每个元组(行)从worker传递到leader后端的成本(默认值0.1)

parallel_workers:一个用于表的存储参数，允许改变worker数量的行为以并行执行查询活动，类似于max_parallel_workers_per_gather，但只针对特定的表;ALTER TABLE tabname SET (parallel_workers = N);

根据服务器的资源和数据库的工作负载，您可以调整这些参数以充分利用并行性。min_parallel_table_scan_size会影响Postgres代码判断查询中一个表扫描节点会使用多少worker，因为worker的数量是根据表的大小以3为几何级数计算的。你可以在这里（https://github.com/postgres/postgres/blob/8a300fc3afce4a0fe8a4c55bc22b2aeec092cf23/src/backend/optimizer/path/allpaths.c#L4210）查看代码。

< 8MB (3^0 * 8) -> 0 workers     表大小小于3MB时，启动0个workers
< 24MB (3^1 * 8) -> 1 workers
< 72MB (3^2 * 8) -> 2 workers
< 216MB (3^3 * 8) -> 3 workers
...

例如，一个大小为50MB的表将使用2个worker进程来扫描表，但是，如果表上有TOAST怎么办? 在估算worker工作进程的数量时是否考虑了TOAST的大小?这一点将在本博客中进行分析。

测试和结果

CREATE TABLE tab1 (i int, j text);
ALTER TABLE tab1 ALTER COLUMN j SET STORAGE EXTERNAL; -- to force to use store in toast without compression
INSERT INTO tab1 SELECT i, repeat('textvalue ',500) from generate_series (1,900000 ) AS i;
ANALYZE tab1;
postgres=# \dt+ tab1
                                   List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+------+-------+----------+-------------+---------------+---------+-------------
 public | tab1 | table | postgres | permanent   | heap          | 5126 MB | 
(1 row)

让我们看看规划器是如何生成执行计划的:

postgres=# EXPLAIN ANALYZE select i, j from tab1 where i%2=0;
                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..12808.00 rows=4500 width=22) (actual time=0.338..54.463 rows=450000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tab1  (cost=0.00..11358.00 rows=1875 width=22) (actual time=0.038..23.329 rows=150000 loops=3)
         Filter: ((i % 2) = 0)
         Rows Removed by Filter: 150000
 Planning Time: 0.093 ms
 Execution Time: 65.269 ms
(8 rows)

规划器使用两个并行的worker，因为max_parallel_workers_per_gather使用默认值2，但如果我们设置max_parallel_workers_per_gather=3时又会发生什么？是否会有更多的worker会被使用?

postgres=# set max_parallel_workers_per_gather = 3;
SET
postgres=# EXPLAIN ANALYZE select i, j from tab1 where i%2=0;
                    QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..12808.00 rows=4500 width=22) (actual time=0.363..59.643 rows=450000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tab1  (cost=0.00..11358.00 rows=1875 width=22) (actual time=0.041..24.766 rows=150000 loops=3)
         Filter: ((i % 2) = 0)
         Rows Removed by Filter: 150000
 Planning Time: 0.070 ms
 Execution Time: 71.027 ms
(8 rows)

结论是规划器还是只使用了两个并行worker，但如果表的大小是5GB，又会怎么样?

我们将使用一个更具体的查询来详细了解表的大小:

postgres=# SELECT
  format(
    $$
      Normal table %s:
        oid: %s
        size: %s 
      Toast table %s:
        oid: %s
        size: %s 
    $$,
    relname,
    oid,
    pg_size_pretty(pg_relation_size(oid)),
    c.reltoastrelid::regclass,
    c.reltoastrelid,
    pg_size_pretty(pg_relation_size(c.reltoastrelid))
  ) as  sizes
FROM pg_class as c
WHERE relname = 'tab1';


                    sizes                     
----------------------------------------------
                                             +
       Normal table tab1:                    +
         oid: 2717534                        +
         size: 45 MB                         +
       Toast table pg_toast.pg_toast_2717534:+
         oid: 2717537                        +
         size: 5022 MB                       +


(1 row)

表自身的数据大小只有45MB，其余的都属于toast表（5GB），基于默认值为min_parallel_table_scan_size=8MB，计划器将使用两个worker进行计算。这表明在执行计划的生成和worker启动数量的计算中并没有考虑toast的大小。然而，我们可以修改此参数，强制计划器计算并比较不同并行worker的值，看看是否可以提高性能。

postgres=# set min_parallel_table_scan_size = '4MB';-- this will force to use 3 workers
SET 
postgres=# EXPLAIN ANALYZE select i, j from tab1 where i%2=0;
                    QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..11537.84 rows=4500 width=22) (actual time=0.186..38.858 rows=450000 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   ->  Parallel Seq Scan on tab1  (cost=0.00..10087.84 rows=1452 width=22) (actual time=0.021..19.218 rows=112500 loops=4)
         Filter: ((i % 2) = 0)
         Rows Removed by Filter: 112500
 Planning Time: 0.035 ms
 Execution Time: 49.475 ms
(8 rows)

计划的成本降低了，实际时间也降低了(约25%)，当然，这种行为是根据cpu和磁盘的类型而定的，并不是所有类型的硬件都类似;因此，建议在您的环境中执行这样的测试。

也可以尝试修改表的存储参数:parallel_workers

postgres=# ALTER TABLE tab1 SET (parallel_workers = 4);
postgres=# EXPLAIN ANALYZE select i, j from tab1 where i%2=0;
           QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
Gather  (cost=1000.00..10558.00 rows=4500 width=22) (actual time=0.363..30.522 rows=450000 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Parallel Seq Scan on tab1  (cost=0.00..9108.00 rows=1125 width=22) (actual time=0.026..13.946 rows=90000 loops=5)
        Filter: ((i % 2) = 0)
        Rows Removed by Filter: 90000
Planning Time: 0.104 ms
Execution Time: 44.707 ms
(8 rows)

总结

上面的结果意味着toast大小与并行性无关，然后你需要分析toast在表toast中是否有显著的大小（译者注：指的是虽然表很大，但是大部分是toast占用的情况），以修改规划器行为以获得更好的性能。只有在会话期间更改一些配置参数 (max_parallel_workers_per_gather 或 min_parallel_table_scan_size)或使用表的存储参数（parallel_workers）才能在某些查询中产生差异，但您必须意识到更多的worker会使用更多的资源，在某些情况下可能会变得更糟。

原文请点击“文章底部”阅读原文查看。

文章转载自PostgreSQL中文社区，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

TOAST和它对PostgreSQL并行的影响

评论