云贝教育 |「技术文章」PostgreSQL日常维护(2)

作者：崔鹏

膨胀

一、什么是膨胀？

表膨胀是指表的数据和索引所占文件系统的空间，在有效数据量并未发生大的变化的情况下，不断增大。久而久之，关系文件被大量空洞填满，浪费了大量的磁盘空间。甚至某些特殊场景下，一个表中只有一条简单的数据，但是表对应的物理文件可能已经达到M级甚至G级。

1、空间持续上涨，到达某一个点后，需要执行一个高额代价的vacuum full(或者cluster等可以重组表的命令)，但vacuum full又是AccessExclusiveLock，8级锁，会阻塞一切访问，意味着在完成清理重组之前，都无法访问该表。

2、扫描的效率变低，即使所有记录都是dead状态，PostgreSQL的顺序扫描也会扫描对象所有的老版本，直到执行vacuum将dead的记录删除。

二、为什么会膨胀？

主流的MVCC 机制如下：

1.以Oracle和Innodb为代表的，写新数据时，把旧数据转移到一个单独的地方，如回滚段中，其他人读数据时，从回滚段中把旧的数据读出来，所以可以有效避免膨胀。

2. 以SQL Server为代表的，把旧版本的数据写入专门的临时表空间，新数据写入日志，然后去更改数据。这种方式，旧版本的数据放入了专门的临时表空间，所以也可以有效地避免膨胀。

3. 写新数据时，旧数据不删除，而是把新数据插入，将旧数据标记为无效，PostgreSQL就是使用的这种实现方法，新老数据存放在一起，在被清理之前，会一直占据着空间，所以会导致膨胀。

三、普通的vacuum

1.清除UPDATE或DELETE操作后留下的"死元组“

2.跟踪表块中可用空间，更新free space map

3.更新visibility map，index only scan以及后续vacuum都会利用到

4."冻结"表中的行，防止事务ID回卷

5.配合ANALYZE，定期更新统计信息

vacuum full会对表进行重组，也就意味着表的oid会变，所以不能我们在日常操作中，要定位表的oid的时候，不能通过pg_class的oid来找，得通过pg_class的relfilenode来找，这样才精确。而且，vacuum full最大会占据原来磁盘空间的两倍。

四、表膨胀的产生原因

vacuumlazy.c
bool TransactionIdPrecedes(TransactionId id1, TransactionId id2)
{
int32 diff;
if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2))
return (id1 < id2);
diff = (int32) (id1 - id2);
return (diff < 0);
}

TransactionIdPrecedes用于判断两个事务的大小，xmax就是删除、更新时的事务ID，OldestXmin是最老的事务ID

这个函数的逻辑是，如果 OldestXmin < xmax ，则返回 HEAPTUPLE_RECENTLY_DEAD，意味着保留此Tuple，不去进行删除。

活动事务中最小的事务ID为OldestXmin。

2.TransactionIdPrecedes用于判断两个事务的大小，xmax就是删除、更新时的事务ID，OldestXmin是最老的事务ID。

typedef enum
{
HEAPTUPLE_DEAD,  /* 元组是死的和可删除的 */
HEAPTUPLE_LIVE,   /* 元组处于活动状态（已提交，没删除）*/
HEAPTUPLE_RECENTLY_DEAD, /* 死元组,但是不能删除 */
HEAPTUPLE_INSERT_IN_PROGRESS, /* 插入xact仍在进行中 */
HEAPTUPLE_DELETE_IN_PROGRESS /* 正在删除xact */
} HTSV_Result;
switch (HeapTupleSatisfiesVacuum(&tuple, OldestXmin, buf))
{
case HEAPTUPLE_DEAD:
... ...

3. 所以，如果系统中含有很久之前开启而未提交的事务，并且这个事务由于执行过更新，创建了事务ID，那么计算出来的OldestXmin会非常小，vacuum做上述这个判断时，结果通常为true，即返回HEAPTUPLE_RECENTLY_DEAD，这样就会保留此tuple(旧版本)，导致回收无法完成，表膨胀由此发生。

五、表膨胀优化建议

1. 一定要开启autovacuum。

2. 提高系统的IO能力，越高越好。

3.设置idle_in_transaction_session_timeout参数，控制长事务的存活时间。

4.对于大表，建议使用分区，可以加快vacuum的速度。

5. 应用程序设计时，避免使用大批量的更新，删除操作，可以切分为多个事务进行。

vacuum full

vacuum full或者cluster等可以重组表的方式，不过都是8级锁，access exclusive lock，会阻塞一切访问，重组表，意味着表在磁盘上的物理位置会发生改变，同时最多会使用两倍表空间的存储大小。

pg_repack

pg_repack，http://reorg.github.io/pg_repack/，它会为待重建的表创建一份副本。首先取一份全量快照，将所有活元组写入新表，并通过触发器将所有针对原表的变更同步至新表，最后通过重命名，使用新的紧实副本替换老表。而对于索引，则是通过PostgreSQL的CREATE(DROP) INDEX CONCURRENTLY完成的。

注意点：

1. 重整开始之前，最好取消掉所有正在进行的Vacuum任务。

2. 对索引做重整之前，最好能手动清理掉可能正在使用该索引的查询

3. 如果出现异常的情况(譬如中途强制退出)，有可能会留下未清理的垃圾，需要手工清理。

4. 当完成重整，进行重命名替换时，会产生巨量的WAL，有可能会导致复制延迟，而且无法取消。

5. 重整特别大的表时，需要预留至少与该表及其索引相同大小的磁盘空间，需要特别小心，手动检查。

六、reindex

REINDEX使用索引的表里存储的数据重建一个索引，并且替换该索引的旧拷贝。有一些场景需要使用REINDEX：

一个索引已经损坏，并且不再包含合法数据。尽管理论上这不会发生，实际上索引会因为软件缺陷或硬件失效损坏。 REINDEX提供了一种恢复方法。

一个索引变得“臃肿”，其中包含很多空的或者近乎为空的页面。 PostgreSQL中的 B-树索引在特定的非常规访问模式下可能会发生这种情况。REINDEX 提供了一种方法来减少索引的空间消耗，即制造一个新版本的索引，其中没有死亡页面。

修改了一个索引的存储参数（例如填充因子），并且希望确保这种修改完全生效。

如果索引在用CONCURRENTLY选项创建失败，该索引保留为一个“invalid”。这类索引是无用的，但是可以方便的用REINDEX来重建它们。注意，只有REINDEX INDEX可以在无效的索引上执行并发创建。

七、详细数据和索引页面监控

pgstattuple模块提供多种函数来获得元组层的统计信息。 pgstattuple(regclass) pgstattuple返回一个关系的物理长度、"死亡"元组的百分比以及其他信息。这可以帮助用户决定是否需要清理。参数是目标关系的名称（可以有选择地用模式限定）或者 OID。

八、详细数据和索引页面监控

pg_freespacemap 可以看到表或索引的每个页面和对应的空闲空间映射(FSM)的内容。

CREATE EXTENSION pg_freespacemap;
--查询表文件空闲率
SELECT count(*) as "number of pages",
       pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
       round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
       FROM pg_freespace('tb1_b');
--查询每页空闲率
SELECT *, round(100 * avail/8192 ,2) as "freespace ratio"
                FROM pg_freespace('tb1_b');
--eg exec vacuum
mydb=# delete from tb1_b where id % 10 !=0;
DELETE 4500
mydb=# SELECT count(*) as "number of pages",
       pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
       round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
       FROM pg_freespace('tb1_b');
 number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
              23 | 0 bytes            |                0.00
(1 row)

九、慢查询 pg_stat_statements模块

追踪数据库执行的所有 SQL 语句的执行统计信息，统计数据库的资源开销，分析TOP SQL。

在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来载入。需要额外的共享内存。增加或移除该模块需要一次服务器重启。

视图 pg_stat_statements以及函数pg_stat_statements_reset 用于访问和操纵这些统计信息。

这些视图和函数不是全局可用的。

可以用CREATE EXTENSION pg_stat_statements 为特定数据库启用它们。

pg_stat_statements.max = 1000000
# 在pg_stat_statements中最多保留多少条统计信息，通过LRU算法，覆盖老的记录。
pg_stat_statements.track = all
# all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)
pg_stat_statements.track_utility = off
# 是否跟踪非DML语句 (例如DDL，DCL)，on表示跟踪, off表示不跟踪  
pg_stat_statements.save = on
# 重启后是否保留统计信息 
shared_preload_libraries='pg_stat_statements'
track_io_timing = on #如果要跟踪IO消耗的时间，需要打开如上参数
track_activity_query_size = 2048 #设置单条SQL的最长长度，超过被截断显示（可选）
 
create extension pg_stat_statements;
 
select * from pg_stat_statements;

十、慢查询

shared_preload_libraries='pg_stat_statements'

#加载pg_stat_statements模块

track_io_timing = on

#如果要跟踪IO消耗的时间，需要打开如上参数

track_activity_query_size = 2048

#设置单条SQL的最长长度，超过被截断显示（可选）

#以下配置pg_stat_statements采样参数

pg_stat_statements.max = 10000

# 在pg_stat_statements中最多保留多少条统计信息，通过LRU算法，覆盖老的记录。

pg_stat_statements.track = all

# all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)

pg_stat_statements.track_utility = off

# 是否跟踪非DML语句 (例如DDL，DCL)，on表示跟踪, off表示不跟踪

pg_stat_statements.save = on

# 重启后是否保留统计信息

十一、慢查询常用的统计sql参考

最耗IO SQL，单次调用最耗IO SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;

总最耗IO SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;

最耗时 SQL，单次调用最耗时 SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;

总最耗时 SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;

响应时间抖动最严重 SQL

select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;

最耗共享内存 SQL

select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;

最耗临时空间 SQL

select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit

相关推荐

取消回复欢迎你发表评论:

Google 黑客常用搜索语句一览原力计划

npx简介（npxvip是哪国的）

在 Android 模拟器上运行 ARM 应用（android模拟器原理）

GB28181,B接口协议之SIPRTSPRTPRTMP协议从入门到精通

安装使用Hoppscotch构建API请求访问与测试

手机实时提取SIM卡打电话的信令和声音-辅助外设与商用通话方案

Python自动化办公——后台截图（python 自动截图）

轻松转换!AppleNumbers到Excel的快捷教程

电脑端腾讯文档如何导出excel

用OpenCV测量图像中物体的大小（基于opencv的物体尺寸检测算法实现）