分区表锁粒度差异 - pg_pathman VS native partition table

3 minute read

背景

PostgreSQL 内置分区相比pg_pathman分区插件性能要差一大截:

《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》

主要原因:

1、与优化器有关,内置分区表,plan\bind时需要分析所有子表,所以子表越多,性能会越差。例如下面这个CASE就是分区过多引起的性能问题。

《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》

PPAS 10和pg_pathman插件都没有这个问题。

我们也可以通过这个profiling来对比 pg_pathman和内置分区的性能差异。

《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》

除了性能差异,实际上还有一个问题是锁的差异。

分区表锁粒度差异 - pg_pathman VS native partition table

观察锁

1、pg_pathman,仅锁目标分区。对非目标分区执行DDL,不需要等待。

create table tbl_range(id int not null, info text, crt_time timestamp);      
select create_range_partitions('tbl_range', 'id', 0, 100, 128);     
  
begin;  
insert into tbl_range values (1, 'test', now()) returning tableoid::regclass,*;  
  
  tableoid   | id | info |          crt_time            
-------------+----+------+----------------------------  
 tbl_range_1 |  1 | test | 2018-02-06 20:15:23.305754  
(1 row)  
  
INSERT 0 1  
postgres=# select relation::regclass,* from pg_locks ;  
  relation   |   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | fastpath   
-------------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------  
 pg_locks    | relation      |    20699 |    11577 |      |       |            |               |         |       |          | 4/18               | 41234 | AccessShareLock  | t       | t  
             | virtualxid    |          |          |      |       | 4/18       |               |         |       |          | 4/18               | 41234 | ExclusiveLock    | t       | t  
 tbl_range_1 | relation      |    20699 |    44464 |      |       |            |               |         |       |          | 3/8819             | 41174 | RowExclusiveLock | t       | t  
 tbl_range   | relation      |    20699 |    44456 |      |       |            |               |         |       |          | 3/8819             | 41174 | RowExclusiveLock | t       | t  
             | virtualxid    |          |          |      |       | 3/8819     |               |         |       |          | 3/8819             | 41174 | ExclusiveLock    | t       | t  
             | transactionid |          |          |      |       |            |     227217433 |         |       |          | 3/8819             | 41174 | ExclusiveLock    | t       | f  
(6 rows)  

2、native partition table,锁所有分区。导致对任意分区执行DDL都需要等待。

CREATE TABLE orders (      
    order_id     bigint not null,      
    cust_id      bigint not null,      
    status       text      
) PARTITION BY HASH (order_id);      
CREATE TABLE orders_p1 PARTITION OF orders      
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);      
CREATE TABLE orders_p2 PARTITION OF orders      
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);      
CREATE TABLE orders_p3 PARTITION OF orders      
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);      
CREATE TABLE orders_p4 PARTITION OF orders      
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);         
begin;  
insert into orders values (1, 1, 'test') returning tableoid::regclass,*;  
  
 tableoid  | order_id | cust_id | status   
-----------+----------+---------+--------  
 orders_p1 |        1 |       1 | test  
(1 row)  
  
INSERT 0 1  
postgres=# select relation::regclass,* from pg_locks ;  
    relation    |   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |           mode           | granted | fastpath   
----------------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------  
 orders_p4      | relation      |    16461 | 75121104 |      |       |            |               |         |       |          | 3/57883            | 41256 | RowExclusiveLock         | t       | t  
 orders_p3      | relation      |    16461 | 75121098 |      |       |            |               |         |       |          | 3/57883            | 41256 | RowExclusiveLock         | t       | t  
 orders_p2      | relation      |    16461 | 75121092 |      |       |            |               |         |       |          | 3/57883            | 41256 | RowExclusiveLock         | t       | t  
 orders_p1      | relation      |    16461 | 75121086 |      |       |            |               |         |       |          | 3/57883            | 41256 | RowExclusiveLock         | t       | t  
 orders         | relation      |    16461 | 75121080 |      |       |            |               |         |       |          | 3/57883            | 41256 | AccessShareLock          | t       | t  
 orders         | relation      |    16461 | 75121080 |      |       |            |               |         |       |          | 3/57883            | 41256 | RowExclusiveLock         | t       | t  
                | virtualxid    |          |          |      |       | 3/57883    |               |         |       |          | 3/57883            | 41256 | ExclusiveLock            | t       | t  
 pg_locks       | relation      |    16461 |    11621 |      |       |            |               |         |       |          | 4/58165            | 41325 | AccessShareLock          | t       | t  
                | virtualxid    |          |          |      |       | 4/58165    |               |         |       |          | 4/58165            | 41325 | ExclusiveLock            | t       | t  

维护子表

在对主表有查询、插入时,维护子表,看看子表的反应。

insert into 主表 values (1,xxx);  -- 写入A子表  

1、TRUNCATE 非A子表

内置分区,锁冲突。

pg_pathman,无冲突。

2、detach 非A子表

内置分区,锁冲突。

pg_pathman,无冲突。

小结

内置分区表,只要操作主表,就需要对所有子表加锁(加相应的锁,例如sharedLock).

pg_pathman,仅仅对主表,以及被访问调度子表加锁。

因此,当我们需要使用DDL来维护子表时(例如truncate, attach, detach子表),使用pg_pathman不会与操作主表的SQL冲突,而使用内置分区的话,读主表由于锁所有子表,所以与子表DDL操作会发生冲突。需要特别注意。

参考

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》

《PostgreSQL 商用版本EPAS(阿里云ppas) - 分区表性能优化 (堪比pg_pathman)》

《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》

Flag Counter

digoal’s 大量PostgreSQL文章入口