性能诊断PostgreSQL中attach partition越来越慢一案例？（pg_partman）

by

分区表(partition)在大型数据库中是较为常用的技术，PostgreSQL中 v10版本后支持了原生分区语法，之前多是约束注册方式，v11后又至此了default分区，近日一客户反馈他们的PostgreSQL在分区使用pg_partman管理分区增加空分区时越来越慢(≈3sec一个分区), 这里简单记录原因。

去年记录过一篇<PostgreSQL 分区表管理的最佳实践>中记录过pg_partman管理分区，如果追踪的话实际是遍历执行的一条条SQL，做attach partition操作。

导致attach partition的分区慢的常见原因：

  • 系统资源银行，如当时CPU资源争用
  • 业务高峰，存在锁资源争用（如访问排它锁）
  • 增加的分区表不为空，attach过程中在自动创建与父表相同的列索引
  • 分区个数过大
  • default 分区扫描

这个案例客户是说业务空闲，无锁，新分区表为空，查看表结构发现，存在一个45GB的default分区，这可能是问题所在，因为attach partition过程中既要校验新分区表，也要校验default数据是否与新分区条件冲突包含。下面使用试验证明。

— env postgresql v17

D:\postgresql\pgsql\bin>psql postgres
psql (17rc1)
输入 "help" 来获取帮助信息.

postgres=# create table t1 (
postgres(#   id INTEGER NOT NULL,
postgres(#   name INTEGER NOT NULL,
postgres(#   marka varchar(2000),
postgres(#   PRIMARY KEY(id)
postgres(# ) PARTITION BY RANGE (id);
CREATE TABLE
postgres=#
postgres=# create table t1_p1 (
postgres(#   id INTEGER NOT NULL,
postgres(#   name INTEGER NOT NULL,
postgres(#   marka varchar(2000)
postgres(# );
CREATE TABLE
postgres=# create table t1_other (
postgres(#   id INTEGER NOT NULL,
postgres(#   name INTEGER NOT NULL,
postgres(#   marka varchar(2000)
postgres(# );
CREATE TABLE
postgres=# \timing
启用计时功能.
postgres=# alter table  t1  attach partition t1_p1 for values from (10000001) to (20000000);
ALTER TABLE
时间：5.101 ms
postgres=# ALTER TABLE t1 ATTACH PARTITION t1_other DEFAULT;
ALTER TABLE
时间：2.396 ms
postgres=# alter table  t1  detach partition t1_p1;
ALTER TABLE
时间：1.383 ms

attach partition 几乎是5ms 内。 下面向default分区增加数据。

postgres=# INSERT INTO t1
postgres-#              SELECT x,x,'xxxxxxxx' FROM generate_series(1, 10000000) as x;
INSERT 0 10000000
时间：32561.257 ms (00:32.561)


postgres=# \d+ t1
                                        分区表 "public.t1"
 栏位  |          类型           | 校对规则 |  可空的  | 预设 |   存储   | 压缩 | 统计目标 | 描述
-------+-------------------------+----------+----------+------+----------+------+----------+------
 id    | integer                 |          | not null |      | plain    |      |          |
 name  | integer                 |          | not null |      | plain    |      |          |
 marka | character varying(2000) |          |          |      | extended |      |          |
分区键值: RANGE (id)
索引：
    "t1_pkey" PRIMARY KEY, btree (id)
分区: t1_other DEFAULT



postgres=# \dt+ t1*
                                   关联列表
 架构模式 |   名称   |  类型  | 拥有者 | 持续的 | 访问方法 |    大小    | 描述
----------+----------+--------+--------+--------+----------+------------+------
 public   | t1       | 分区表 | weejar | 永久的 |          | 0 bytes    |
 public   | t1_other | 数据表 | weejar | 永久的 | heap     | 498 MB     |
 public   | t1_p1    | 数据表 | weejar | 永久的 | heap     | 8192 bytes |
(3 行记录)

postgres=# alter table  t1  attach partition t1_p1 for values from (10000001) to (20000000);
ALTER TABLE
时间：964.566 ms (00:00.965)
postgres=# alter table  t1  detach partition t1_p1;
ALTER TABLE
时间：1.872 ms


postgres=# INSERT INTO t1
postgres-#              SELECT x,x,'xxxxxxxx' FROM generate_series(10000001, 20000000) as x;
INSERT 0 10000000
时间：31893.960 ms (00:31.894)
postgres=# alter table  t1  attach partition t1_p1 for values from (10000001) to (20000000);
错误:  某些行将违反默认分区"t1_other"的更新分区约束
时间：1783.005 ms (00:01.783)
postgres=# alter table  t1  attach partition t1_p1 for values from (20000001) to (30000000);
ALTER TABLE
时间：1657.180 ms (00:01.657)
postgres=# alter table  t1  detach partition t1_p1;
ALTER TABLE
时间：1.345 ms

postgres=# INSERT INTO t1
postgres-#              SELECT x,x,'xxxxxxxx' FROM generate_series(20000001, 30000000) as x;
INSERT 0 10000000
时间：32094.934 ms (00:32.095)
postgres=# \dt+ t1*
                                   关联列表
 架构模式 |   名称   |  类型  | 拥有者 | 持续的 | 访问方法 |    大小    | 描述
----------+----------+--------+--------+--------+----------+------------+------
 public   | t1       | 分区表 | weejar | 永久的 |          | 0 bytes    |
 public   | t1_other | 数据表 | weejar | 永久的 | heap     | 1493 MB    |
 public   | t1_p1    | 数据表 | weejar | 永久的 | heap     | 8192 bytes |
(3 行记录)


postgres=# alter table  t1  attach partition t1_p1 for values from (30000001) to (40000000);
ALTER TABLE
时间：2479.354 ms (00:02.479)

postgres=# alter table  t1  detach partition t1_p1;
ALTER TABLE
时间：1008.689 ms (00:01.009)

postgres=# SET client_min_messages =error;
调试:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
时间：2.622 ms

postgres=# \d+ t1
                                        分区表 "public.t1"
 栏位  |          类型           | 校对规则 |  可空的  | 预设 |   存储   | 压缩 | 统计目标 | 描述
-------+-------------------------+----------+----------+------+----------+------+----------+------
 id    | integer                 |          | not null |      | plain    |      |          |
 name  | integer                 |          | not null |      | plain    |      |          |
 marka | character varying(2000) |          |          |      | extended |      |          |
分区键值: RANGE (id)
索引：
    "t1_pkey" PRIMARY KEY, btree (id)
分区: t1_p1 FOR VALUES FROM (30000001) TO (40000000),
      t1_other DEFAULT


postgres=# \d+ t1_p1
                                      数据表 "public.t1_p1"
 栏位  |          类型           | 校对规则 |  可空的  | 预设 |   存储   | 压缩 | 统计目标 | 描述
-------+-------------------------+----------+----------+------+----------+------+----------+------
 id    | integer                 |          | not null |      | plain    |      |          |
 name  | integer                 |          | not null |      | plain    |      |          |
 marka | character varying(2000) |          |          |      | extended |      |          |
分区: t1 FOR VALUES FROM (30000001) TO (40000000)
分区约束: ((id IS NOT NULL) AND (id >= 30000001) AND (id < 40000000))
索引：
    "t1_p1_pkey" PRIMARY KEY, btree (id)
访问方法 heap

Note: 可见随着default 分区 （t1_other）越来越大，attach时的时间就越长, (新分区会级联创建check和索引，但是因为是空表，时间暂时忽略不记)。可能在您的硬件环境时间会有些差异。下面启用debug跟踪。

postgres=# SET client_min_messages =debug5;
调试:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
时间：3.305 ms
postgres=#  alter table  t1  attach partition t1_p1 for values from (30000001) to (40000000);
调试:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
调试:  verifying table "t1_p1"
调试:  verifying table "t1_other"
调试:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 856/1/3
ALTER TABLE
时间：2222.615 ms (00:02.223)

Note: 可以看到有两个verifying的操作。 这时如果新分区的数据不为空，attach partiton时的时间将会再次增加。

postgres=# INSERT INTO t1_p1
postgres-#              SELECT x,x,'xxxxxxxx' FROM generate_series(30000001, 40000000) as x;
错误:  关系 "t1_p1" 的新列违反了分区约束
描述:  失败, 行包含(40000000, 40000000, xxxxxxxx).
时间：30694.528 ms (00:30.695)
postgres=# alter table  t1  detach partition t1_p1;
ALTER TABLE
时间：2.018 ms
postgres=# \d+ t1_p1
                                      数据表 "public.t1_p1"
 栏位  |          类型           | 校对规则 |  可空的  | 预设 |   存储   | 压缩 | 统计目标 | 描述
-------+-------------------------+----------+----------+------+----------+------+----------+------
 id    | integer                 |          | not null |      | plain    |      |          |
 name  | integer                 |          | not null |      | plain    |      |          |
 marka | character varying(2000) |          |          |      | extended |      |          |
索引：
    "t1_p1_pkey" PRIMARY KEY, btree (id)
访问方法 heap


postgres=# alter table  t1  attach partition t1_p1 for values from (30000001) to (40000000);
ALTER TABLE
时间：3685.918 ms (00:03.686)
postgres=#

如果减少attach时 校验新表t1_p1的时间，可以在attach前手动创建check.

postgres=# alter table  t1  detach partition t1_p1;
ALTER TABLE
时间：1012.928 ms (00:01.013)
postgres=# ALTER TABLE t1_p1
postgres-#    ADD CHECK (id IS NOT NULL AND id >= 30000001 AND id < 40000000);
ALTER TABLE
时间：167.658 ms
postgres=# alter table  t1  attach partition t1_p1 for values from (30000001) to (40000000);
ALTER TABLE
时间：2113.409 ms (00:02.113)

而如果attach前把default partition 卸载，再attach那速度又恢复空表时的时间。

postgres=# alter table  t1  detach partition t1_p1;
ALTER TABLE
时间：1006.707 ms (00:01.007)
postgres=# alter table  t1  detach partition t1_other;
ALTER TABLE
时间：1.420 ms
postgres=# alter table  t1  attach partition t1_p1 for values from (30000001) to (40000000);
ALTER TABLE
时间：1.662 ms

总结：

如果还在使用attach partition方式增加分区时，当前新分区和default分区的数据大小都会影响attach过程中在内部自动”verifying table”的时间。

— over —

Leave a Comment