【PostgreSQL】PG数据库表“膨胀”粗浅学习
文章目录
- 1 为什么需要关注表膨胀？
- 2 如何确定是否发生了表膨胀？
- 2.1 通过查询表的死亡元组占比情况来判断膨胀率
- 2.1.1 指定数据库和表名
- 2.1.2 查询数据库里面所有表的膨胀情况
- 3 膨胀的原理
- 3.1 什么是膨胀？膨胀率？
- 3.2 哪些数据库元素会发生膨胀？
- 3.3 为什么会产生膨胀的现象？
- 3.4 必须要做清理的原因？
- 3.5 VACUUM进度报告
- 4 如何处理表膨胀问题？
- 4.1 简单粗暴但是有效彻底的空间回收：“vacuum full 表名”-不推荐
- 4.1.1 FULL参数使用注意
- 4.1.2 FULL参数使用示例
- 4.2 相对优雅一些的回收方式：“vacuum 表名”-推荐
- 4.2.1 标准的vacuum（不带full参数的方式）
- 4.2.2 标准VACUUM回收清理使用示例
- 4.2.3 标准vacuum支持多个option命令方式：vacuum (verbose,analyze) mytest;
- 5 膨胀产生后的危害&如何长远应对？
- 5.1 产生哪些危害？
- 5.2 如何应对？
1 为什么需要关注表膨胀？
有一天，发现数据库所在的服务器上的空间在报不够用了，有一天发现某些和数据库有关的功能的莫名的变慢了，有一天发现某些功能时快时慢。。。
这些时候，我们可能要考虑一下是不是有表膨胀在发生?
2 如何确定是否发生了表膨胀？
2.1 通过查询表的死亡元组占比情况来判断膨胀率
pg_stat_all_tables里面的relname是表名。 可以看到如果类似最后一列dead_tup_ratior如果值很大，一般来说大于50了，那么一定要介入处理了。这个值相当于是无效数据的占比。
2.1.1 指定数据库和表名
下面的示例是：数据库名是test，数据库表是mytest
test=# select schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup*100/(n_live_tup+n_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup + n_dead_tup <> 0 and relname='mytest';
table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
---------------+------------+------------+------------+----------------
public.mytest | 1042 MB | 10000001 | 5999859 | 62.0
(1 row)
2.1.2 查询数据库里面所有表的膨胀情况
select schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup*100/(n_live_tup+n_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup + n_dead_tup <> 0
order by dead_tup_ratio desc;
3 膨胀的原理
3.1 什么是膨胀？膨胀率？
顾名思义，本来应该是一个大小，但是因为某种原因，它的大小变得更大了，甚至超乎想象的那么大。这就是发生了膨胀，而其中的膨胀的程度的体现，就是膨胀率。因此膨胀率可以直观的体现膨胀到底有多厉害。
反观数据库的膨胀的理解，一张A表或者A表的索引正常应该是占用一个大小的空间，但是查出来实际占用超过了这个空间的大小，往往随着时间的推移可能会占用的越来越来离谱。
3.2 哪些数据库元素会发生膨胀？
数据库表、索引
*索引实际会和表强关联，因此，可以比较泛指到就是表膨胀。
3.3 为什么会产生膨胀的现象？
之所以产生膨胀的原因跟PostgreSQL数据库的数据维护方式是息息相关的，
PG使用MVCC(多版本并发控制)，
实际上MVCC是oracle、sqlserver数据库也使用的核心原理，但是新旧版本的数据存储的机制有所不同，因此造就了各个数据库在膨胀现象的表现有所不同。
sqlserver数据库：旧版本的数据专门写入到临时表中，新数据写入日志，然后再去更改数据。新旧分别放在不同的地方，膨胀的情况相对低一点，但是也会有这种情况发生，发生后，也需要按照SQLSERVER相关的处理方式进行。
oracle数据库：旧版本的数据放到UNDO，新数据放到REDO，然后更改数据。oracle数据库本身对redo、undo文件有自动清理、扩展的机制，因此总体来说对膨胀的控制相对可控，但是也不排除处问题的情况。有的时候也需要有重建undo文件的事情发生。
PG数据库：旧版本标示为无效，新数据写入日志，执行成功后把新版本的数据写入到新的位置。旧版本的数据在仍然是存储在表中，只是标示为无效，如果不及时做清理，那么无效的数据会占用空间越来越大，这样就发生了膨胀。
对于PG数据库，它把旧版本、新版本的表数据都同时存储在业务实际访问的表中，也称为数据页中，只是对于就旧版本的数据元组做了一个标记。因此，如果没有及时清理的话，导致数据库表臃肿，会很容易导致正常访问表的操作因此发生异常的现象。
delete、update操作会引发数据上的变化，因此，如果实际使用表会频繁出现删除和更新数据，这种情况下会很容易出现死亡元组，如果经常有大量数据的删除和更新，那么这个表会比较容易出现大的膨胀现象。
3.4 必须要做清理的原因？
PostgreSQL的VACUUM命令出于几个原因必须定期处理每一个表：
恢复或重用被已更新或已删除行所占用的磁盘空间。
更新被PostgreSQL查询规划器使用的数据统计信息。
更新可见性映射，它可以加速只用索引的扫描。
保护老旧数据不会由于事务ID回卷或多事务ID回卷而丢失。
VACUUM后，会更新pg_class等其他的表，会优化索引查询、优化执行计划等，进而达到恢复数据库表相关操作的性能。
3.5 VACUUM进度报告
只要VACUUM正在运行，每一个当前正在清理的后端（包括autovacuum工作者进程）在pg_stat_progress_vacuum视图中都会有一行。下面的表描述了将被报告的信息并且提供了如何解释它们的信息。VACUUM FULL命令的进度是通过pg_stat_progress_cluster报告的，因为VACUUM FULL和CLUSTER都是重写表，而普通的VACUUM只是原地修改表。
pg_stat_progress_vacuum视图说明：
| 序号 | 列 | 类型 | 描述 |
|---|---|---|---|
| 1 | pid | integer | 后端的进程ID。 |
| 2 | datid | oid | 这个后端连接的数据库的OID。 |
| 3 | datname | name | 这个后端连接的数据库的名称。 |
| 4 | relid | oid | 被vacuum的表的OID。 |
| 5 | phase | text | vacuum的当前处理阶段。 |
| 6 | heap_blks_total | bigint | 该表中堆块的总数。这个数字在扫描开始时报告，之后增加的块将不会（并且不需要）被这个VACUUM访问。 |
| 7 | heap_blks_scanned | bigint | 被扫描的堆块数量。由于可见性映射被用来优化扫描，一些块将被跳过而不做检查，被跳过的块会被包括在这个总数中，因此当清理完成时这个数字最终将会等于heap_blks_total。仅当处于扫描堆阶段时这个计数器才会前进。 |
| 8 | heap_blks_vacuumed | bigint | 被清理的堆块数量。除非表没有索引，这个计数器仅在处于清理堆阶段时才会前进。不包含死亡元组的块会被跳过，因此这个计数器可能有时会向前跳跃一个比较大的增量。 |
| 9 | index_vacuum_count | bigint | 已完成的索引清理周期数。 |
| 10 | max_dead_tuples | bigint | 在需要执行一个索引清理周期之前我们可以存储的死亡元组数，取决于maintenance_work_mem。 |
| 11 | num_dead_tuples | bigint | 从上一个索引清理周期以来收集的死亡元组数。 |
VACUUM的阶段:
| 序号 | 阶段 | 描述 |
|---|---|---|
| 1 | 初始化 | VACUUM正在准备开始扫描堆。这个阶段应该很简短。 |
| 2 | 扫描堆 | VACUUM正在扫描堆。如果需要，它将会对每个页面进行修建以及碎片整理，并且可能会执行冻结动作。heap_blks_scanned列可以用来监控扫描的进度。 |
| 3 | 清理索引 | VACUUM当前正在清理索引。如果一个表拥有索引，那么每次清理时这个阶段会在堆扫描完成后至少发生一次。如果maintenance_work_mem不足以存放找到的死亡元组，则每次清理时会多次清理索引。 |
| 4 | 清理堆 | VACUUM当前正在清理堆。清理堆与扫描堆不是同一个概念，清理堆发生在每一次清理索引的实例之后。如果heap_blks_scanned小于heap_blks_total，系统将在这个阶段完成之后回去扫描堆；否则，系统将在这个阶段完成后开始清理索引。 |
| 5 | 清除索引 | VACUUM当前正在清除索引。这个阶段发生在堆被完全扫描并且对堆和索引的所有清理都已经完成以后。 |
| 6 | 截断堆 | VACUUM正在截断堆，以便把关系尾部的空页面返还给操作系统。这个阶段发生在清除完索引之后。 |
| 7 | 执行最后的清除 | VACUUM在执行最终的清除。在这个阶段中，VACUUM将清理空闲空间映射、更新pg_class中的统计信息并且将统计信息报告给统计收集器。当这个阶段完成时，VACUUM也就结束了。 |
4 如何处理表膨胀问题？
*注意考虑到业务忙时影响
4.1 简单粗暴但是有效彻底的空间回收：“vacuum full 表名”-不推荐
注意有失败的可能：
vacuum full 需要注意看看是否操作的表多大，磁盘空闲空间是否充足，否则会命令执行失败。
如果遇到了该问题，或者做之前，提前在服务器上进行磁盘空间释放，确保无用的文件再去删除。务必注意生产环境实施该操作要谨慎！高危！
4.1.1 FULL参数使用注意
| 序号 | vacuum full使用注意 | 具体说明 |
|---|---|---|
| 用途 | 通常用于需要从表中回收庞大的空间时候，而且最好是释放给操作系统使用，一般不建议使用。 | |
| 1 | 缺点 | 需要给清理的表加上排它锁，执行操作期间对该表的insert、update、select、delete等操作会被阻塞 |
| 2 | 缺点 | 需要比较长的时间，时间长短跟表本身的大小以及膨胀的程度相关 |
| 3 | 缺点 | 需要临时额外占用操作系统上的磁盘空间，用于创建该表的新的拷贝，在操作执行完成前不会释放旧的拷贝。 |
| 1 | 优点 | 可以回收更多空间 |
| 2 | 优点 | 回收到的空间可以释放给操作系统，真正的物理上清理释放 |
选择“FULL”清理，它可以收回更多空间，并且需要更长时间和表上的排他锁。这种方法还需要额外的磁盘空间，因为它会创建该表的一个新拷贝，并且在操作完成之前都不会释放旧的拷贝。通常这种方法只用于需要从表中收回数量庞大的空间时。
不引入其他插件的情况下，如果能够挑选到业务不繁忙的时候或者说能够接受短时间内的锁表、功能卡顿的话，可以执行下面的语句：VACUUM FULL 具体表名;
使用前，务必关注上述列出的缺点。
4.1.2 FULL参数使用示例
膨胀的时候的空间占用，1个G左右：
test=# select schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup*100/(n_live_tup+n_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup + n_dead_tup <> 0 and relname='mytest';
table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
---------------+------------+------------+------------+----------------
public.mytest | 1042 MB | 10000001 | 5999859 | 62.0
(1 row)
完成回收后，空间占用下降到了391MB
test=# VACUUM FULL mytest;
VACUUM
test=#
test=# select schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup*100/(n_live_tup+n_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup + n_dead_tup <> 0 and relname='mytest';
table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
---------------+------------+------------+------------+----------------
public.mytest | 391 MB | 0 | 7481453 | 0.0
(1 row)
vacuum命令帮助：
http://postgres.cn/docs/12/sql-vacuum.html
4.2 相对优雅一些的回收方式：“vacuum 表名”-推荐
注意可能有失败的可能：
1、长事务，如果当存在长时间运行的事务（长事务）对表进行操作（如insert、delete、update等操作且未提交），VACUUM操作会失效，因为此时的死元组对长事务是可见的，尽管对其他事务不可见，但仍然无法删除死元组。
如果碰到了，解决的办法就是，杀死长事务或者以后合理设置idle_in_transaction_session_timeout 参数，不要让连接不必要地“闲置在事务中”。配置参数idle_in_transaction_session_timeout可以被用来自动断开拖延会话的连接。
实际上，执行的失败提示信息中会写明有更旧的xmin水平
SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
查询到后，pg_terminate_backend(pid)来做终止事务
2、 废弃的复制槽 复制延迟或者备服务器关闭
SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;
使用pg_drop_replication_slot()删除复制槽
test=# select pg_drop_replication_slot('aaaa');
3、孤立的准备事务 某种异常情况下，导致事务处于准备好的状态但是未继续提交或者中止。
SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
ROLLBACK PREPARED transaction_id
例如：ROLLBACK PREPARED '123'
4.2.1 标准的vacuum（不带full参数的方式）
| 序号 | 标准vacuum使用注意 | 使用说明 |
|---|---|---|
| 1 | 用途 | 推荐日常使用，设置合理定时在非忙时执行，但是VACUUM使用过程会带来I/O流量的的产生，会导致数据库其他会话的操作性能变差，因此使用过程中需要搭配一些策略参数来防护后台清理工作给数据库性能带来的冲击。例如：vacuum_cost_delay、vacuum_cost_page_hit 、vacuum_cost_page_miss 、vacuum_cost_page_dirty 、vacuum_cost_limit 将导致清理进程休眠的累计代价。默认值为200。 |
| 1 | 缺点 | 标准的vacuum完成回收后的空间不会实际释放给操作系统，只是标记为继续给当前的表或者索引复用。当膨胀的程度很高的时候，该方式无法释放占用空间给物理服务器操作系统使用。除非在特殊的情况中表尾部的一个或多个页面变成完全空闲并且能够很容易地得到一个排他表锁。 |
| 2 | 缺点 | 执行过程中会可能产生大量I/O流量，对数据库处理性能产生一定负面影响。 |
| 1 | 优点 | 不阻塞基础数据库表增删改查，标准形式的VACUUM可以和生产数据库操作并行运行（SELECT、INSERT、UPDATE和DELETE等命令将继续正常工作，但在清理期间你无法使用ALTER TABLE等命令来更新表的定义） |
| 2 | 优点 | 执行耗时相对FULL参数方式较短 |
4.2.2 标准VACUUM回收清理使用示例
回收清理前，表占用的大小空间table_size是781M，膨胀比例为50%，
执行标准的回收后，表占用的大小空间table_size还是781M，死亡元组为0。
pg_stat_all_tables字段 last_autovacuum 表示最近一次自动回收的时间。
test=# select schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup*100/(n_live_tup+n_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup + n_dead_tup <> 0 and relname='mytest';
table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
---------------+------------+------------+------------+----------------
public.mytest | 781 MB | 6000001 | 5995257 | 50.0
(1 row)
test=# \timing on
Timing is on.
test=# vacuum mytest;
VACUUM
Time: 29512.550 ms (00:29.513)
test=# select schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup*100/(n_live_tup+n_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup + n_dead_tup <> 0 and relname='mytest';
table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
---------------+------------+------------+------------+----------------
public.mytest | 781 MB | 0 | 2997602 | 0.0
(1 row)
Time: 124.583 ms
test=#
再次执行vacuum full mytest，再查询表占用大小table_size为391M，空间直接释放了一半，这是把空间释放到了操作系统的效果。
test=# vacuum full mytest;
VACUUM
Time: 54052.374 ms (00:54.052)
test=# select schemaname||'.'||relname as table_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup*100/(n_live_tup+n_dead_tup),1) as dead_tup_ratio
from pg_stat_all_tables
where n_live_tup + n_dead_tup <> 0 and relname='mytest';
table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio
---------------+------------+------------+------------+----------------
public.mytest | 391 MB | 0 | 5972470 | 0.0
(1 row)
Time: 22.492 ms
test=#
4.2.3 标准vacuum支持多个option命令方式：vacuum (verbose,analyze) mytest;
VERBOSE
为每个表打印一份详细的清理活动报告。
ANALYZE
更新优化器用以决定最有效执行一个查询的方法的统计信息。
test=# vacuum (verbose,analyze) mytest;
INFO: vacuuming "public.mytest"
INFO: index "mytest_pkey" now contains 6000001 row versions in 32905 pages
DETAIL: 0 index row versions were removed.
16450 index pages have been deleted, 16450 are currently reusable.
CPU: user: 0.10 s, system: 0.27 s, elapsed: 0.65 s.
INFO: "mytest": found 0 removable, 2 nonremovable row versions in 1 out of 100001 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 853
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 50000 frozen pages.
0 pages are entirely empty.
CPU: user: 0.11 s, system: 0.27 s, elapsed: 0.66 s.
INFO: vacuuming "pg_toast.pg_toast_16779"
INFO: index "pg_toast_16779_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "pg_toast_16779": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 853
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "public.mytest"
INFO: "mytest": scanned 30000 of 100001 pages, containing 1793880 live rows and 0 dead rows; 30000 rows in sample, 5979660 estimated total rows
VACUUM
Time: 2096.122 ms (00:02.096)
test=#
5 膨胀产生后的危害&如何长远应对？
5.1 产生哪些危害？
如果对于膨胀的情况听之任之的化，看得见的危害有哪些呢？按照出现的时间序来看：
第一、数据库表占用空间越来越大
第二、数据库对执行计划的选择不是最高效的
第三、普通的数据库表插入、删除、查询都变慢了
第四、突然有一天发现整个系统都慢起来了
第五、发现数据库磁盘配额报告警了
第六、扩展磁盘配额后，过段时间又继续报数据库磁盘配额不足
第七、数据库所在服务器磁盘空间不足
第八、所有和数据库部署在同一个服务器上的应用无法正常创建文件
第九、整个服务器系统宕机了
看起来就是慢慢恶化的过程，实际情况的确也会劣化的非常快。
5.2 如何应对？
| 序号 | 产生膨胀的可能原因 | 应对举措 |
|---|---|---|
| 1 | delete | 减少大量的删除动作，优化删除语句效率，减少耗时。常规建议开启autovacuum进行清理回收。 |
| 2 | update | 减少大量更新动作，优化更新语句效率，减少耗时。常规建议开启autovacuum进行清理回收。 |
| 3 | select长时间占用 | 优化select查询语句，降低执行查询耗时。减少针对大表的全表扫描。 |
| 4 | 索引创建 | 常规建议开启autovacuum进行清理回收。 |
| 5 | 数据库事务回滚 | 常规建议开启autovacuum进行清理回收。 |
| 6 | 系统异常导致的其他意外情况 | 常规建议开启autovacuum进行清理回收。必要的时候执行vacuum full手动回收。 |
| 7 | IO性能较差 | 优化磁盘使用计划，避免磁盘I/O浪费，减少任务堆积访问数据库配置SSD，或者调整raid模式到I/O更好的方式。 |
| 8 | 内存资源紧张 | 建议根据业务特性情况，考虑到业务峰值情况，尽量分配充分的内存资源。且内存的分配要充分考虑PG数据库系统参数的各个值，包括work_mem、shared_buffer等 |
| 9 | CPU资源紧张 | 建议根据业务特性情况，考虑到业务峰值情况，尽量分配充分的CPU资源。 |
| 10 | 磁盘资源不足 | max_wal_size和wal_segment_size，特别是前面的max_wal_size在业务量存在突发海量更新数据的情况下，这个参数需要扩大，具体可以扩大到多少需要充分考虑一下磁盘的空闲空间。 如果这个值设置不当会引起主备PG同步复制流中断。 |
| 10 | PG系统参数设置不合理 | 适当针对部分PG参数进行调优，shared_buffers 、temp_buffers 、max_prepared_transactions 、work_mem 、maintenance_work_mem 等PG参数调优 |
| 11 | 没有开启自动回收清理 | 建议开启autovacuum，但是需要根据各自的业务特性确定自动运行回收的策略。另外注意，这个开关可以控制到具体的表级别。执行命令show autovacuum查看开启状态。它的目的是自动执行VACUUM和ANALYZE 命令。当它被启用时，自动清理会检查被大量插入、更新或删除元组的表。这些检查会利用统计信息收集功能，因此除非track_counts被设置为true，自动清理不能被使用。在默认配置下，自动清理是被启用的并且相关配置参数已被正确配置。 |
| 12 | 操作系统或者服务器或者数据库异常关闭 | 检查是否有发生膨胀，可以使用vacuum手动清理。 |
修改具体表的自动回收开关autovacuum，例如：
test=# show autovacuum;
autovacuum
------------
on
(1 row)
test=#
test=# alter table mytest set (autovacuum_enabled=on);
ALTER TABLE
test=# \d+ mytest
Table "public.mytest"
Column | Type | Collation | Nullable | Default | Storage | Stats target | D
escription
--------+---------+-----------+----------+---------+----------+--------------+--
-----------
a | integer | | not null | | plain | |
b | text | | | | extended | |
Indexes:
"mytest_pkey" PRIMARY KEY, btree (a)
Options: autovacuum_enabled=on