1. 问题出现

晚上9点，现场报系统查询慢，运维查询zabbix后发现postgres最近几天的IOWait很大

2. 追踪问题

查询数据库，发现很多SQL堵住了

原因是真正创建index，导致表锁住了，其他所有操作都block住了。将这个操作取消掉后，发现系统自动将这个表进行autovacuum，很多SQL又堵住了。手工将vacuum停掉后，系统好了一点点，但还是较之前慢。

其中一个SQL 执行的频率很高，但一直需要执行很长时间:

19014 | sxacc-devices | AccessShareLock | PostgreSQL JDBC Driver | 2019-10-23 14:22:12.837273+00 | active | get_lock | 00:00:21.477812 |
SELECT COUNT(*) FROM "sxacc-devices" t WHERE (t.info->>'orgId')::text = '67572'
AND (t.info->>'modelName')::text = '804Mesh' AND (t.info->>'manufacturer')::text = 'Calix' AND (t.info->>'productClass')::text = '804Mesh'
AND (t.info->>'hardwareVersion')::text = '3000276410' AND (t.info->>'manufacturerOUI')::text = '44657F' AND (t.info->>'softwareVersion')::text = '1.1.0.100'

查询执行计划

cloud=# explain SELECT COUNT(*) FROM "sxacc-devices" t WHERE (t.info->>'orgId')::text = '7583' AND (t.info->>'modelName')::text = '804Mesh' AND (t.info->>'manufacturer')::text = 'Calix' AND (t.info->>'productClass')::text = '804Mesh' AND (t.info->>'hardwareVersion')::text = '3000276410' AND (t.info->>'manufacturerOUI')::text = 'CCBE59' AND (t.info->>'softwareVersion')::text = '2.0.1.112';

QUERY PLAN

Aggregate (cost=2368.56..2368.57 rows=1 width=8)
-> Bitmap Heap Scan on "sxacc-devices" t (cost=2273.58..2368.56 rows=1 width=0)
Recheck Cond: (((info ->> 'softwareVersion'::text) = '2.0.1.112'::text) AND ((info ->> 'modelName'::text) = '804Mesh'::text))
Filter: (((info ->> 'orgId'::text) = '7583'::text) AND ((info ->> 'productClass'::text) = '804Mesh'::text) AND ((info ->> 'manufacturer'::text) = 'Calix'::text) AND ((info ->> 'hardwareVersion'::text) = '3000276410'::text) AND ((info ->> 'manufacturerOUI'::text) = 'CCBE59'::text))
-> BitmapAnd (cost=2273.58..2273.58 rows=83 width=0)
-> Bitmap Index Scan on sv_idex (cost=0.00..460.69 rows=3418 width=0)
Index Cond: ((info ->> 'softwareVersion'::text) = '2.0.1.112'::text)
-> Bitmap Index Scan on idx_sxacc_devices_model_name (cost=0.00..1812.65 rows=19706 width=0)
Index Cond: ((info ->> 'modelName'::text) = '804Mesh'::text)

这个SQL还算可以，虽然不是每个字段都走index，但大部分也走索引了，但还是需要执行很长时间，而且Read很高

关键是查询select count(*) from "sxacc-devices"也非常慢，根本查不出来的样子。

根据系统表查询这个表，发现总数据量只有78万多，但占用磁盘高达43GB，真实数据只有16GB，相差悬殊。

根据这个表的每小时统计信息发现一个奇怪的现象：

基本每3小时增长1GB，这是一个疯狂的操作。

查询系统vacuum记录，发现这个表在昨天还做个autovacuum，查询

SELECT relname,n_tup_ins as "inserts",n_tup_upd as "updates",n_tup_del as "deletes", n_live_tup as "live_tuples", n_dead_tup as "dead_tuples" FROM pg_stat_user_tables where relname='sxacc-devices'

这个表更新太过于频繁，决定手工vacuum一次， vacuum full analyze “sxacc-devices”， vacuum结束后发现这个表从40GB降到1GB. 执行完之后系统就回归正常。

分析：

由于这个表操作特别频繁，特别是更新过多，导致这个表的中间状态特别庞大，而autovacuum只会针对标识为删除的记录数进行删除，其他的不会做，导致这张表增长比较大。应该经常性的执行vacuum full去释放空间，但需要特别小心的是，但执行vacuum full的时候会进行表锁，导致操作这张表的sql block住，要特别小心。

3.通用查询

--active 的sql及查询时间

SELECT t.relname, l.locktype, page, virtualtransaction, l.pid, mode, granted,a.state,a.client_addr,age(clock_timestamp(), a.query_start), a.usename, a.query
FROM pg_locks l, pg_stat_all_tables t,pg_stat_activity a
WHERE l.relation = t.relid and l.pid=a.pid ORDER BY relation asc;

--表大小统计
select pg_size_pretty (pg_total_relation_size ('"sxacc-devices"')) as total,pg_size_pretty(pg_relation_size('"sxacc-devices"')) as relsize, pg_size_pretty (pg_indexes_size('"sxacc-devices"')) as idx

--表查询效率统计
SELECT relname,n_tup_ins as "inserts",n_tup_upd as "updates",n_tup_del as "deletes", n_live_tup as "live_tuples", n_dead_tup as "dead_tuples" FROM pg_stat_user_tables where relname='sxacc-devices'

select * from pg_stat_bgwriter;

benchmarksql2=# select pg_current_xlog_location();

pg_current_xlog_location

4/E9B61648
(1 row)

benchmarksql2=# select pg_xlog_location_diff('4/E9B61648','4/7027C648')/(60*5);
-[ RECORD 1 ]------------------
?column? | 6797899.093333333333

iotop

iostat -x 60 5

5. 关于Vacuum

Vacuum是postgres维护磁盘空间的工具，主要是删除标记为删除的数据并释放空间。 postgres执行delete操作后，数据库只是将该记录标识为delete状态，并不会立即清理空间，在后续的update或insert的时候，该空间不能被使用，只有经过vacuum清理后才能释放并重用。

vacuum的语法结构

VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, …] ) ] [ table_name [ (column_name [, …] ) ] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table_name ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table_name [ (column_name [, …] ) ] ]

注意：

Vacuum 不能在事务块内执行。
对于有GIN索引的表，VACUUM（以任何形式）也完成任何挂起索引插入内容，通过移动挂起索引条目到主GIN索引结构中相应的位置。
建议生产数据库经常清理(至少每晚一次)，以保证不断地删除死行。尤其是在增删了大量记录之后，对受影响的表执行VACUUM ANALYZE命令是一个很好的习惯。这样做将更新系统目录为最近的更改，并且允许PostgreSQL 查询优化器在规划用户查询时有更好的选择。
不建议日常使用FULL选项，但是可以在特殊情况下使用。一个例子就是在你删除或更新了一个表的大部分行之后，希望从物理上缩小该表以减少磁盘空间占用并且允许更快的表扫描。VACUUM FULL 通常要比单纯的VACUUM收缩更多的表尺寸。
VACUUM导致 I/O 流量增加，可能会导致其它活动会话的性能恶劣。因此，有时候会建议使用基于开销的 vacuum 延迟特性。参阅第 18.4.4 节获取细节。
PostgreSQL包含一个"autovacuum"设施，它可以自动进行日常的 vacuum 维护。关于手动和自动清理的更多细节，参见第 23.1 节。

6. PG 产生IO的总结

WAL写入
- 每个事务执行都会产生IO，同步提交时还会刷新磁盘。checkpoint后脏页的首次刷出是全页写，最糟糕的情况是每个小事务产生8K WAL写入，后面的WAL写入量可以粗略认为和数据更新两相当。
- 有个后台WAL写入进程专门把WAL buffer 写到磁盘，后台WAL刷的速度赶不上WAL Buffer写入速度时，postgres进程会直接把WAL Buffer写入磁盘。
后台写入器
- 后台写入器的目标为postgres进程预留足够的缓冲区，默认是最近所需的平均值的2倍（bgwriter_lru_multiplier），但默认每200毫秒（bgwriter_delay）不超过100个缓冲区（bgwriter_lru_maxpages）
Checkpoint
- 每次checkpoint会刷出缓冲区中的所有脏页。触发checkpoint的时间有2个：一个默认5分钟（checkpoint_timeout）的定期检查点，另外一个是产生的WAL文件数超过checkpoint_segments。
- 9.5以后checkpoint_segments参数被取消掉了，改成了max_wal_size，WAL写入量超过max_wal_size的1/3～1/2时触发checkpoint
自动清理
- 自动清理可能会从磁盘读取数据块。每次自动清理的量可以通过相关参数控制。
postgres进程
- 读入缓冲区中缺失的页时会产生磁盘读。
- WAL后台写入忙不过来时，pg进程会直接写和刷WAL
- 后台写入器未能预留足够的缓冲区时，刷出脏的缓冲区。
- work_mem不足时生成临时文件也会产生IO