Pg 数据库日常维护操作指南

本文主要用来记述 pg 数据库的相关操作和异常排查指南，继上一篇博客之后，异常的频繁更新，导致死亡元组指数级增长之后，空间占用也成倍增长，逻辑问题导致了数据库问题，但细想之下也发现,当 pg 在面对海量数据的更新删除之后，频繁的 autovacuum 会导致数据库大量的 I/O，完了又会影响其他进程，就参数配置来看，还是有蛮多优化的空间的，毕竟空间和时间是两个相生相克的关系。就目前的默认的配置来看，手动标记 60w 数据执行 vacuum 标记清理花了 6 分钟，直接清空死亡元组也差不多这个时间，当空间膨胀到 300g 的时候数据量达到 140w，vacuum 已经有点吃不消了执行了半个小时也没有看到执行结束，至少在频繁更新的情况下，可见 vacuum 还是有他的局限性，就像官网提示的:Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity. 而且默认配置的的自动间隔是 1 分钟，我觉得这里面有很大的优化空间，尤其是海量数据频繁更新和删除的时候，当 autovacuum 的执行时间超过 1 分钟之后，就需要注意系统的死亡元组数量了，类似于当我打扫垃圾的速度低于产生垃圾的速度此时垃圾只会越来越多，当然这是在大数据量特定频繁更新和删除场景的情况下，结合相关的配置产生的一种思考。需要注意的配置主要有 autovacuum_max_workers 可以根据 cpu 核心数配置，autovacuum_work_mem 工作内存和 vacuum_scale_factor 规模因子，

查看相关参数描述可以使用如下 sql，不得不说 pg 还是特别友好的，注释说明很全面，减少了学习成本

select * from pg_settings where name like 'autovacuum%';

查看表大小

 #查看指定表的大小  select pg_relation_size('table_name')/1024^3  as "SIZE G";   #查看所有表的大小 \d+  # 文件路径 select pg_relation_filepath('table_name');

查看死亡元组和事务数量河相关的一些命令

#查看当前每个进程的信息SELECT * FROM pg_stat_activity;SELECT count(*) FROM pg_stat_activity where state = 'active';
#当前镜像 SELECT txid_current_snapshot(); #查看死亡元组select *  from pg_stat_all_tables ORDER BY n_dead_tup limit 10; select *  from pg_stat_user_tables ORDER BY n_dead_tup limit 20; 
#查看活着和死亡元组SELECT schemaname,relname,n_live_tup,n_dead_tup, last_autovacuum, last_analyze,last_autoanalyze FROM pg_stat_user_tables  where relname='table_name' ORDER BY n_dead_tup;

分析自动清理的阈值计算

系统每隔 autovacuum_naptime 秒尝试在每个数据库中启动一个工作者，如果你有 N 个数据库就会有多个工作者，数据库会每隔 autovacuum_naptime/N 创建一个工作者，同一时间内最多允许 autovacuum_max_workers 个工作者，工作者是一个独立的进程，并不会影响到连接数，如果数据库的数量超过了 autovacuum_max_workers 那么当一个数据库清理完成之后，会立马清理未清理的数据库，这有点像线程池哈哈。如果某个表太大占用了太多时间，导致其他表无无法清理，当其他数据库的工作者清理完之后，会来清理这个数据库的其他未清理的表，工作者会避免做重复的工作，已经被清理的表不会再次经理，对一个数据库所拥有的最大工作者数量并没有限制，简单理解这个参数是针对这个进程的，不是某个单独的数据库的，工作者进程在执行清理前会检查每个表在合适的时间执行 VACUUM 和 ANALYZE,可以配置 log_autovacuum_min_duration 来监听自动清理者的工作活动。当 relfrozenxid 值比 autovacuum_freeze_max_age 事务更大的表会被清理，如果废弃的元祖超过阈值表也会被清理。失效的元祖数量会统计信息收集器里面获得，它是通过更新和删除命令更新的半准确的计数（它是半准确的，原因是在高负载情况下某些信息会丢失）。另一种情况是当 relfrozenxid 比 vacuum_freeze_table_age 旧事务 id 大的时候，执行 vacuum 冻结表的旧元祖增加 relfrozenxid，否则只对上次执行过 vacuum 的表进行扫描。分析阈值也是类似，该阈值将与上次被 ANALYZE 分析的新增删除和修改的元祖进行比较。分区表不能被自动清理，要统计和收集数据必须手动执行命令。

#清理阈值 = 清理基本阈值 + 清理缩放系数 * 元组数vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples#分析阈值 = 分析基本阈值 + 分析缩放系数 * 元组数analyze threshold = analyze base threshold + analyze scale factor * number of tuples

小插曲 count(*)和 count(1)

很多时候我都认为 count(*)就是统计一行不展开数据直到我看到 mysql 文档当中写到:InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.从官网的角度来看 COUNT(expr)是一个函数，参数是一个表达式，那么可以认为只要不是一个非空的数都可以，反正他是统计记录行，不是统计记录列。统计记录列还要判断是否非空，判断行就简单了，但是这是在有主键索引的情况下哈。

select proname, pronargs, prosrc from pg_proc where proname='count';

如上参考的是 pg 的数据库 count 函数，意思类似，这查看函数的功能太友好了!

总结

知道的越多不知道的越多，即使各种数据库直接都有相通的思想和不同的实现，关于 pg 数据库元祖分析和事务 xid 冻结，请持续关注我后续博客

引用

MySQL 中 count() 和 count(1) 有什么区别？哪个性能最好？

vacuum

12.20.1 Aggregate Function Descriptions

PostgreSQL的count(1)真的比count(*)快么？