VACUUM

VACUUM

关注
发信
关注(28)粉丝(399)

一次vaccum导致的事故

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
Powered by LMLPHP ©2024 VACUUM 0.053923