膨胀真的不简单
xiongcc约 11 分钟1前言
今天原本是美好的假期 🥱，但是中午同事一通电话又把我薅起来处理问题了。同事反馈某业务突然开始大量变慢，历时一小时处理完毕，想着趁热打铁，怕明天写就忘记细节了，在此也记录一下这次案例的分析与处理过程。
2现象
以下是同事提供给我的截图
等待事件共有三个
buffer_mapping：Waiting to associate a data block with a buffer in the buffer pool. 类似 Oracle cbc 闩锁，具体原理参照 internal DB 就行了：当有多个后端进程来访问缓冲区的时候，首先通过计算哈希值，找到 HASH TABLE 的位置，然后获得相关的 BufMappingLock，才能去访问 HASH TABLE。如果两个后端进程访问的 Buffer 属于不同的分区锁控制的时候，不会产生冲突，反之就会产生冲突，这个分区锁默认是 128，有些测试表明增大该默认值可以提高性能 👉🏻 通过修改NUM_BUFFER_PARTITIONS提升PostgreSQL数据库的性能的一次测试，当碰到大量这样的等待事件的时候，说明热块竞争严重，可以考虑调小 fillfactor 让数据打散到更多的数据块（代价就是更多的存储）中或者使用哈希分区的方式，也有可能 IO 繁忙导致 evict 变慢了
buffer_io：Waiting for I/O on a data page. 对应的是 io_in_progress_lock，The io_in_progress lock is used to wait for I/O on a buffer to complete. When a PostgreSQL process loads/writes page data from/to storage, the process holds an exclusive io_in_progress lock of the corresponding descriptor while accessing the storage. io_in_progress 锁用于等待缓冲区上的 I/O 完成。当 PostgreSQL 进程从存储加载/写入页面数据时，该进程在访问存储时持有相应描述符的排它 io_in_progress 锁。
DataFileRead：Waiting for a read from a relation data file.
光从等待事件来看，好像和 IO 有关。我之前曾处理过类似的一起案例，原因是底层存储的一条链路坏了，于是用 iostat 看了下 IO 情况，但没有发现什么异常，操作系统的日志也没有什么有价值的信息。
那换个方向，问问开发有没有做什么，据开发反馈："我删除了某个表中的一千万数据，删除完之后立马就开始变慢了"，原来开发做了一个删除数据的操作，表中原本有 1 亿数据，删除了其中一千万数据，按照过往经验，会不会是执行计划发生了改变？于是我查了一下 pg_stat_user_tables 的 last_analyze 和 last_autoanalyze 字段，距离当前时间不久，说明就最近才做过统计信息的收集。
到这又碰壁了，没办法，只能分析一下执行计划了，用 PEV 看一下
各位眼尖的可能立马看到问题了，planning time 居然高达 6 秒，而执行时间只要 0.5 毫秒。为什么生成执行计划要这么久？按照之前的经验，可能是分区表子表过多，但是这个 SQL 并没有涉及到分区表，只是涉及到了一堆的索引扫描。回想一下开发做的操作，删除了一千万数据，这个操作势必会导致表和索引中产生大量的碎片，膨胀。另外我之前曾分析过 pathman 的一篇生产案例，在执行计划生成期间，会调用 lseek 设置偏移量，设置索引的偏移量（索引大小），假如索引越大，需要做 lseek 的操作也越多，简单验证一下
postgres=# create table t1(id int);
CREATE TABLE
postgres=# create table t2(id int,info text);
CREATE TABLE
postgres=# insert into t1 values(generate_series(1,10000));
INSERT 0 10000
postgres=# create index on t1(id);
CREATE INDEX
postgres=# insert into t2 select n,md5(random()::text) from generate_series(1,10000) as n;
INSERT 0 10000
postgres=# create index on t2(id);
CREATE INDEX
postgres=# select pg_relation_filepath('t1_id_idx');
pg_relation_filepath
----------------------
base/5/16460
(1 row)postgres=# select pg_relation_filepath('t2_id_idx');
pg_relation_filepath
----------------------
base/5/16461
(1 row)
postgres=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/5/16452
(1 row)
postgres=# select pg_relation_filepath('t2');
pg_relation_filepath
----------------------
base/5/16455
(1 row)
postgres=# select pg_relation_size('t1_id_idx');
pg_relation_size
------------------
245760
(1 row)
postgres=# select pg_relation_size('t2_id_idx');
pg_relation_size
------------------
245760
(1 row)
postgres=# select pg_relation_size('t1');
pg_relation_size
------------------
368640
(1 row)
postgres=# select pg_relation_size('t2');
pg_relation_size
------------------
688128
(1 row)
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
2020
(1 row)
用 strace 抓一下
[postgres@xiongcc ~]$ strace -p 2020
strace: Process 2020 attached
epoll_wait(4, [{EPOLLIN, {u32=20625936, u64=20625936}}], 1, -1) = 1
recvfrom(9, "Q\0\0\0=explain select t2.info from"..., 8192, 0, NULL, NULL) = 62
lseek(92, 0, SEEK_END) = 368640
lseek(93, 0, SEEK_END) = 245760
lseek(94, 0, SEEK_END) = 688128
lseek(95, 0, SEEK_END) = 245760
sendto(9, "T\0\0\0#\0\1QUERY PLAN\0\0\0\0\0\0\0\0\0\0\31\377\377\377\377"..., 341, 0, NULL, 0) = 341
recvfrom(9, 0x109f960, 8192, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable)
epoll_wait(4,
再看下句柄对应的文件
[postgres@xiongcc fd]$ ls -lrth | egrep '92|93|94|95'
lrwx------ 1 postgres postgres 64 Feb 11 15:46 95 -> /home/postgres/pgdata/base/5/16461
lrwx------ 1 postgres postgres 64 Feb 11 15:46 94 -> /home/postgres/pgdata/base/5/16455
lrwx------ 1 postgres postgres 64 Feb 11 15:46 93 -> /home/postgres/pgdata/base/5/16460
lrwx------ 1 postgres postgres 64 Feb 11 15:46 92 -> /home/postgres/pgdata/base/5/16452
一目了然，就是对应的表和索引。那再让我们加两个索引
[postgres@xiongcc ~]$ strace -p 2020
strace: Process 2020 attached
epoll_wait(4, [{EPOLLIN, {u32=20625936, u64=20625936}}], 1, -1) = 1
recvfrom(9, "Q\0\0\0=explain select t2.info from"..., 8192, 0, NULL, NULL) = 62
lseek(92, 0, SEEK_END) = 368640
lseek(93, 0, SEEK_END) = 245760
lseek(94, 0, SEEK_END) = 245760
lseek(96, 0, SEEK_END) = 688128
lseek(95, 0, SEEK_END) = 245760
lseek(97, 0, SEEK_END) = 245760
sendto(9, "T\0\0\0#\0\1QUERY PLAN\0\0\0\0\0\0\0\0\0\0\31\377\377\377\377"..., 343, 0, NULL, 0) = 343
recvfrom(9, 0x109f960, 8192, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable)
可以看到又多了两个 lseek 寻址的操作，没错正是我新增的两个索引。那么通过这个小实验，我们可以得出以下结论
explain 生成执行计划期间并不会发生真正的读，只是会调用 lseek 去设置文件偏移量，比如涉及到两个表的连接，每个表上各有 5 个索引，那么就会有 12 个 lseek 操作。 explain analyze 真正执行 SQL 的时候才会去读取文件，但是这个步骤只会读取执行计划显式的索引，而不会读取无关的索引。
回到这个案例，刚刚我们说了删除一千万肯定会产生很多碎片和膨胀率，检查一下索引，这个表上足足有 10 个索引！
赶紧检查一下索引膨胀率，使用如下 SQL 即可（更准确可以使用 pgstattuple 插件）
WITH btree_index_atts AS (
SELECT pg_namespace.nspname,
indexclass.relname AS index_name,
indexclass.reltuples,
indexclass.relpages,
pg_index.indrelid,
pg_index.indexrelid,
indexclass.relam,
tableclass.relname AS tablename,
(regexp_split_to_table((pg_index.indkey)::text, ' '::text))::smallint AS attnum,
pg_index.indexrelid AS index_oid
FROM ((((pg_index
JOIN pg_class indexclass ON ((pg_index.indexrelid = indexclass.oid)))
JOIN pg_class tableclass ON ((pg_index.indrelid = tableclass.oid)))
JOIN pg_namespace ON ((pg_namespace.oid = indexclass.relnamespace)))
JOIN pg_am ON ((indexclass.relam = pg_am.oid)))
WHERE ((pg_am.amname = 'btree'::name) AND (indexclass.relpages > 0))
), index_item_sizes AS (
SELECT ind_atts.nspname,
ind_atts.index_name,
ind_atts.reltuples,
ind_atts.relpages,
ind_atts.relam,
ind_atts.indrelid AS table_oid,
ind_atts.index_oid,
(current_setting('block_size'::text))::numeric AS bs,
8 AS maxalign,
24 AS pagehdr,
CASE
WHEN (max(COALESCE(pg_stats.null_frac, (0)::real)) = (0)::double precision) THEN 2
ELSE 6
END AS index_tuple_hdr,
sum((((1)::double precision - COALESCE(pg_stats.null_frac, (0)::real)) * (COALESCE(pg_stats.avg_width, 1024))::double precision)) AS nulldatawidth
FROM ((pg_attribute
JOIN btree_index_atts ind_atts ON (((pg_attribute.attrelid = ind_atts.indexrelid) AND (pg_attribute.attnum = ind_atts.attnum))))
JOIN pg_stats ON (((pg_stats.schemaname = ind_atts.nspname) AND (((pg_stats.tablename = ind_atts.tablename) AND ((pg_stats.attname)::text = pg_get_indexdef(pg_attribute.attrelid, (pg_attribute.attnum)::integer, true))) OR ((pg_stats.tablename = ind_atts.index_name) AND (pg_stats.attname = pg_attribute.attname))))))
WHERE (pg_attribute.attnum > 0)
GROUP BY ind_atts.nspname, ind_atts.index_name, ind_atts.reltuples, ind_atts.relpages, ind_atts.relam, ind_atts.indrelid, ind_atts.index_oid, (current_setting('block_size'::text))::numeric, 8::integer
), index_aligned_est AS (
SELECT index_item_sizes.maxalign,
index_item_sizes.bs,
index_item_sizes.nspname,
index_item_sizes.index_name,
index_item_sizes.reltuples,
index_item_sizes.relpages,
index_item_sizes.relam,
index_item_sizes.table_oid,
index_item_sizes.index_oid,
COALESCE(ceil((((index_item_sizes.reltuples * ((((((((6 + index_item_sizes.maxalign) -
CASE
WHEN ((index_item_sizes.index_tuple_hdr % index_item_sizes.maxalign) = 0) THEN index_item_sizes.maxalign
ELSE (index_item_sizes.index_tuple_hdr % index_item_sizes.maxalign)
END))::double precision + index_item_sizes.nulldatawidth) + (index_item_sizes.maxalign)::double precision) - (
CASE
WHEN (((index_item_sizes.nulldatawidth)::integer % index_item_sizes.maxalign) = 0) THEN index_item_sizes.maxalign
ELSE ((index_item_sizes.nulldatawidth)::integer % index_item_sizes.maxalign)
END)::double precision))::numeric)::double precision) / ((index_item_sizes.bs - (index_item_sizes.pagehdr)::numeric))::double precision) + (1)::double
precision)), (0)::double precision) AS expected
FROM index_item_sizes
), raw_bloat AS (
SELECT current_database() AS dbname,
index_aligned_est.nspname,
pg_class.relname AS table_name,
index_aligned_est.index_name,
(index_aligned_est.bs * ((index_aligned_est.relpages)::bigint)::numeric) AS totalbytes,
index_aligned_est.expected,
CASE
WHEN ((index_aligned_est.relpages)::double precision <= index_aligned_est.expected) THEN (0)::numeric
ELSE (index_aligned_est.bs * ((((index_aligned_est.relpages)::double precision - index_aligned_est.expected))::bigint)::numeric)
END AS wastedbytes,
CASE
WHEN ((index_aligned_est.relpages)::double precision <= index_aligned_est.expected) THEN (0)::numeric
ELSE (((index_aligned_est.bs * ((((index_aligned_est.relpages)::double precision - index_aligned_est.expected))::bigint)::numeric) * (100)::numeric) / (index_aligned_est.bs * ((index_aligned_est.relpages)::bigint)::numeric))
END AS realbloat,
pg_relation_size((index_aligned_est.table_oid)::regclass) AS table_bytes,
stat.idx_scan AS index_scans
FROM ((index_aligned_est
JOIN pg_class ON ((pg_class.oid = index_aligned_est.table_oid)))
JOIN pg_stat_user_indexes stat ON ((index_aligned_est.index_oid = stat.indexrelid)))
), format_bloat AS (
SELECT raw_bloat.dbname AS database_name,
raw_bloat.nspname AS schema_name,
raw_bloat.table_name,
raw_bloat.index_name,
round(raw_bloat.realbloat) AS bloat_pct,
round((raw_bloat.wastedbytes / (((1024)::double precision ^ (2)::double precision))::numeric)) AS bloat_mb,
round((raw_bloat.totalbytes / (((1024)::double precision ^ (2)::double precision))::numeric), 3) AS index_mb,
round(((raw_bloat.table_bytes)::numeric / (((1024)::double precision ^ (2)::double precision))::numeric), 3) AS table_mb,
raw_bloat.index_scans
FROM raw_bloat
)
SELECT format_bloat.database_name AS datname,
format_bloat.schema_name AS nspname,
format_bloat.table_name AS relname,
format_bloat.index_name AS idxname,
format_bloat.index_scans AS idx_scans,
format_bloat.bloat_pct,
format_bloat.table_mb,
(format_bloat.index_mb - format_bloat.bloat_mb) AS actual_mb,
format_bloat.bloat_mb,
format_bloat.index_mb AS total_mb
FROM format_bloat
ORDER BY format_bloat.bloat_mb DESC;
前面的膨胀率都达到了很惊人的八九十了，于是刻不容缓，使用 pg_repack -i xxx --no-kill-backend 的方式重建索引（记得加上 --no-kill-backend），重建完前面几个索引之后，再去查看执行计划，已经有明显好转！
执行计划的生成时间从 6 秒降低到 1 秒，开发也反馈响应时间明显提升。后续就慢慢针对这些膨胀较大的索引和表依次处理即可。
3小结
到这里各位应该知晓原因了，开发删除了大量数据，导致索引膨胀过大（或许本来就已经膨胀很严重了），导致索引占据的数据块更多，并且有足足十个索引，因此执行计划生成期间做 lseek 寻址的操作也需要更久，才会导致执行计划生成就要耗时 6 秒之久！这也是为什么建议索引不要过多的另一个深层次原因。
表膨胀/索引膨胀的学问真是太多了，说来也巧，正好今天下午群里小伙伴还在讨论这个话题，其实膨胀所涉及到的学问太多了，真要讲起来非三言两语便可讲清楚的，我的建议是可以多介绍介绍实际案例 👇🏻 pg_repack/pg_sequeeze/pgcompacttable一把梭当然都会啦