1. 问题出现

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

一次vaccum导致的事故-LMLPHP

2. 追踪问题

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

一次vaccum导致的事故-LMLPHP

原因是真正创建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很高

一次vaccum导致的事故-LMLPHP

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

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

一次vaccum导致的事故-LMLPHP

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

一次vaccum导致的事故-LMLPHP

基本每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'

一次vaccum导致的事故-LMLPHP

这个表更新太过于频繁,决定手工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 [, ...] ) ] ]

一次vaccum导致的事故-LMLPHP

注意:

  • 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

7. PG 间隔大量写IO的解决方法

为了保证数据的可靠性,pg通常将脏页写入磁盘前,先将WAL日志写入磁盘,然后将修改的数据异步分批写入。

为了保证好的读写性能,修改的数据先写到Shared buffer中,而不是直接写入磁盘,因为数据页很离散(修改的数据分布在不同的表中)。数据库会把WAL日志顺序写入磁盘。

postgres两种写方式:write和fsync

write:数据库会将buffer中的脏页数据根据写入策略将老化的脏页写道OS,OS再根据调度算法写入磁盘。
fsync:数据库直接调用OS的fsync函数,直接写入磁盘。

OS 内核参数:

参数设置方法含义
dirty_background_ratio

sysctl -a|grep vm.dirty_background_ratio

sysctl -p sysctl -a|grep vm.dirty_background_ratio 10

修改文件/proc/sys/vm/dirty_background_ratio
  • 在尝试执行writeback操作(即OS触发background flush线程刷新脏页)之前内存脏页面比例
  • 控制文件系统的pdflush进程,在何时刷新磁盘
  • 单位是百分比,但写缓冲使用到系统内存多少时,pdflush开始向磁盘写出数据
  • 增大会使用更多系统内存用于磁盘写缓冲,也可极大提高系统的写性能
  • 但需要持续,恒定的写入场合时,应降低数值,默认为5
 dirty_expire_centisecs

sysctl -a|grep vm.dirty_writeback_centisecs

修改文件 /proc/sys/vm/dirty_expire_centisecs

  • background flush线程将存活时间超过该数值的脏页刷入磁盘(LRU)
  • 数据可以保持为dirty状态的最大时间
  • 单位是1/100s。缺省30000,即30s
  • 对于特别重载的写操作来说,这个值适当缩小也是好的,但不能太小,因为缩小会导致IO提高太快
  • 建议设置1500,也就是15s。
 dirty_ratio

sysctl -a|grep vm.dirty_ratio

sysctl -p vm.dirty_ratio

vim /proc/sys/vm/dirty_ratio

  • 控制文件系统的文件系统写缓冲区的大小,单位是百分比
  • 当脏页比例达到该值,用户进程在调用write时,会触发flush磁盘的操作。表示当写缓冲使用到系统内存多少时,开始向磁盘写出数据。
  • 当一个任务(或者进程)在脏页过多的环境中执行文件写操作的时候,如果脏页面占用中内存的百分比高于dirty_ratio值,那么系统就执行脏页面写入磁盘操作。
  • 增大会使用更多系统内存用于磁盘写缓冲,也可以极大提高系统的写性能
  • 但当需要持续,恒定的写入场景时,应该降低该值。默认10
 dirty_writeback_centisecs

sysctl -a|grep vm.dirty_writeback_centisecs

vim /proc/sys/vm/dirty_writeback_centisecs

  • 控制内核的脏数据刷新进程pdflush的运行间隔,执行background flush线程的唤醒间隔。
  • 单位是1/100s,默认500即5s
  • 适当减少该值有写操作削峰的作用,如果系统是持续写入动作,那么降低该值比较好,可以把尖峰写操作削减成多次些操作。
  • 该参数应小于dirty_expire_centisecs,但太小IO太频繁,反而使系统性能降低。
  • 据说1:6(dirty_expire_centisecs:dirty_writeback_centisecs)的比例比较好
 dirty_background_bytessysctl -p vm.dirtybackgroundbytes 
  • 如果内存非常大,当触发后台线程刷新脏页时,可能需要刷新很多脏页,导致尖锐的IO需求。
  • vm.dirtybackgroundbytes = 102400000 当脏页数达到了100MB,系统触发background flush线程刷脏页

数据库

参数描述
fsync
  • fsync=on
  • 开启后强制把wal日志同步更新到磁盘,可以保证数据库将在OS或者硬件崩溃的后恢复到一个一致的状态。
  • 虽然关闭,可以提升数据库性能,但无法保证数据库崩溃后数据一致性。
  • 通常情况下需要打开这个参数,除非能经受掉电或硬件故障带来的数据丢失,否则不要关闭
 backend_flush_after
  • 单位:BLCKSZ
  • 当某backend process脏数据超过配置阈值时,触发调用OS sync_file_range,告诉os backend flush线程异步刷盘。
  • 从而削减os dirty page堆积。
 bgwrite_flush_after
  • 单位:BLCKSZ
  • 当bgwriter process脏数据超过配置阈值时,触发调用OS sync_file_range,告诉os backend flush线程异步刷盘。
  • 从而削减os dirty page堆积。
 checkpoint_flush_after
  • 单位:BLCKSZ
  • 当checkpointer process脏数据超过配置阈值时,触发调用OS sync_file_range,告诉os backend flush线程异步刷盘。
  • 从而削减os dirty page堆积。
 wal_write_flush_after
  • 单位:BLCKSZ
  • 当wal writer process脏数据超过配置阈值时,触发调用OS sync_file_range,告诉os backend flush线程异步刷盘。
  • 从而削减os dirty page堆积。

Postgres IO:

https://blog.csdn.net/liyingke112/article/details/78844759

http://m.blog.chinaunix.net/uid-20726500-id-5741651.html

Postgres Vacuum:

https://www.cnblogs.com/gaojian/p/3272620.html

https://blog.csdn.net/pg_hgdb/article/details/79490875

https://confluence.atlassian.com/kb/optimize-and-improve-postgresql-performance-with-vacuum-analyze-and-reindex-885239781.html

https://www.percona.com/blog/2018/08/10/tuning-autovacuum-in-postgresql-and-autovacuum-internals/

https://www.postgresql.org/docs/9.5/routine-vacuuming.html

https://wiki.postgresql.org/wiki/VACUUM_FULL

https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT

https://www.postgresql.org/docs/current/mvcc.html

GP:

https://gp-docs-cn.github.io/docs/best_practices/bloat.html#topic_gft_h11_bp

05-11 20:30