作者
digoal
日期
2018-11-25
标签
PostgreSQL ,
背景
数据库有两大块buffer，wal buffer和shared buffer。
wal buffer是预写日志缓冲区。
shared buffer是数据页缓冲区。
wal writer进程负责将wal buffer缓冲区的数据写入WAL FILE中。
background writer进程负责将shared buffer缓冲区的数据写入DATA FILE中。
如果写入量非常大，wal writer和background writer进程不足以满足系统负载时，用户进程（backend process）也会参与将BUFFER写到FILE的工作。
可以通过系统视图统计bgwriter,walwriter,backend process 写磁盘的实时指标。
1、pg_stat_bgwriter.buffers_clean bgwriter 每秒write多少shared buffer到disk(write-异步系统调用) , 说明了脏页产生较快，但是不影响业务。
2、pg_stat_bgwriter.buffers_backend backend process 每秒wirte多少shared buffer到disk(write-异步系统调用) , 说明产生脏页较快，并且bgwriter或checkpointer写脏页已经赶不上产生脏页的速度了，对业务开始有影响。
3、walwriter 每秒write多少wal buffer到disk(write-异步系统调用)
4、pg_stat_bgwriter.buffers_alloc 每秒分配多少新的shared buffer，说明了从磁盘读的频繁程度。
5、其他指标，参考pg_stat_bgwriter 视图的介绍
系统视图
1、pg_stat_bgwriter
https://www.postgresql.org/docs/11/monitoring-stats.html#MONITORING-STATS-VIEWS
postgres=# \d pg_stat_bgwriter
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
checkpoints_timed | bigint | | |
checkpoints_req | bigint | | |
checkpoint_write_time | double precision | | |
checkpoint_sync_time | double precision | | |
buffers_checkpoint | bigint | | |
buffers_clean | bigint | | |
maxwritten_clean | bigint | | |
buffers_backend | bigint | | |
buffers_backend_fsync | bigint | | |
buffers_alloc | bigint | | |
stats_reset | timestamp with time zone | | |
2、检测wal写入量的函数
pg_current_wal_lsn()，查看当前的WAL LSN位点。
pg_wal_lsn_diff(lsn,lsn)，计算两个LSN位点之间有多少字节。
例子
配置
shared_buffers = 3GB
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10.0
wal_writer_delay = 10ms
压测
```
pgbench -i -s 1000
pgbench -M prepared -n -r -P 1 -c 32 -j 32 -T 120000
```
监测
1、bgwriter 每秒write多少shared buffer到disk(write-异步系统调用)
```
postgres=# select buffers_clean*8/1024||' MB' bg from pg_stat_bgwriter;
bg
654155 MB
(1 row)
postgres=# \watch 1
Sun 25 Nov 2018 12:34:25 PM CST (every 1s)
bg复制
655538 MB
(1 row)
Sun 25 Nov 2018 12:34:26 PM CST (every 1s)
bg复制
655842 MB
(1 row)
Sun 25 Nov 2018 12:34:27 PM CST (every 1s)
bg复制
656139 MB
(1 row)
Sun 25 Nov 2018 12:34:28 PM CST (every 1s)
bg复制
656444 MB
(1 row)
```
2、backend process 每秒wirte多少shared buffer到disk(write-异步系统调用)
```
postgres=# select buffers_backend*8/1024||' MB' bg from pg_stat_bgwriter;
bg
1008428 MB
(1 row)
postgres=# \watch 1
Sun 25 Nov 2018 12:35:01 PM CST (every 1s)
bg复制
1009188 MB
(1 row)
Sun 25 Nov 2018 12:35:02 PM CST (every 1s)
bg复制
1009188 MB
(1 row)
Sun 25 Nov 2018 12:35:03 PM CST (every 1s)
bg复制
1009188 MB
(1 row)
Sun 25 Nov 2018 12:35:04 PM CST (every 1s)
bg复制
1009926 MB
(1 row)
Sun 25 Nov 2018 12:35:05 PM CST (every 1s)
bg复制
1009926 MB
(1 row)
```
3、walwriter 每秒write多少wal buffer到disk(write-异步系统调用)
```
postgres=# with a as (select pg_current_wal_lsn() lsn) select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), lsn)) from a, pg_sleep(1);
pg_size_pretty
31 MB
(1 row)
postgres=# \watch 0.001
Sun 25 Nov 2018 12:35:25 PM CST (every 0.001s)
pg_size_pretty
30 MB
(1 row)
Sun 25 Nov 2018 12:35:26 PM CST (every 0.001s)
pg_size_pretty
31 MB
(1 row)
Sun 25 Nov 2018 12:35:27 PM CST (every 0.001s)
pg_size_pretty
30 MB
(1 row)
Sun 25 Nov 2018 12:35:28 PM CST (every 0.001s)
pg_size_pretty
31 MB
(1 row)
```
4、每秒分配多少新的shared buffer
```
postgres=# select buffers_alloc*8/1024||' MB' bg from pg_stat_bgwriter;
bg
2001145 MB
(1 row)
postgres=# \watch 1
Sun 25 Nov 2018 12:35:57 PM CST (every 1s)
bg复制
2003212 MB
(1 row)
Sun 25 Nov 2018 12:35:58 PM CST (every 1s)
bg复制
2003979 MB
(1 row)
Sun 25 Nov 2018 12:35:59 PM CST (every 1s)
bg复制
2004769 MB
(1 row)
Sun 25 Nov 2018 12:36:00 PM CST (every 1s)
bg复制
2005554 MB
(1 row)
Sun 25 Nov 2018 12:36:01 PM CST (every 1s)
bg复制
2006329 MB
(1 row)
```
对比iotop与SQL监测数据是否一致
对比IOTOP的结果，以上统计方法，得到的结果与IOTOP一致。
otal DISK READ : 0.00 B/s | Total DISK WRITE : 654.63 M/s
Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 659.74 M/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
6438 be/4 postgres 0.00 B/s 30.98 M/s 0.00 % 1.01 % postgres: walwriter
6494 be/4 postgres 0.00 B/s 10.50 M/s 0.00 % 0.17 % postgres: postgres postgres [local] idle in transaction
6513 be/4 postgres 0.00 B/s 10.33 M/s 0.00 % 0.17 % postgres: postgres postgres [local] UPDATE
6496 be/4 postgres 0.00 B/s 10.52 M/s 0.00 % 0.17 % postgres: postgres postgres [local] UPDATE
6495 be/4 postgres 0.00 B/s 10.48 M/s 0.00 % 0.17 % postgres: postgres postgres [local] COMMIT
6509 be/4 postgres 0.00 B/s 10.41 M/s 0.00 % 0.16 % postgres: postgres postgres [local] idle in transaction
6491 be/4 postgres 0.00 B/s 10.75 M/s 0.00 % 0.16 % postgres: postgres postgres [local] COMMIT
6522 be/4 postgres 0.00 B/s 10.66 M/s 0.00 % 0.16 % postgres: postgres postgres [local] UPDATEn transaction
6505 be/4 postgres 0.00 B/s 10.66 M/s 0.00 % 0.16 % postgres: postgres postgres [local] COMMIT
6501 be/4 postgres 0.00 B/s 11.50 M/s 0.00 % 0.16 % postgres: postgres postgres [local] UPDATE
6507 be/4 postgres 0.00 B/s 9.95 M/s 0.00 % 0.15 % postgres: postgres postgres [local] UPDATE
6503 be/4 postgres 0.00 B/s 9.39 M/s 0.00 % 0.15 % postgres: postgres postgres [local] COMMIT
6493 be/4 postgres 0.00 B/s 9.48 M/s 0.00 % 0.15 % postgres: postgres postgres [local] idle in transaction
6523 be/4 postgres 0.00 B/s 11.36 M/s 0.00 % 0.15 % postgres: postgres postgres [local] UPDATEn transaction
6500 be/4 postgres 0.00 B/s 10.24 M/s 0.00 % 0.15 % postgres: postgres postgres [local] COMMIT
6498 be/4 postgres 0.00 B/s 10.45 M/s 0.00 % 0.15 % postgres: postgres postgres [local] idle in transaction
6519 be/4 postgres 0.00 B/s 10.66 M/s 0.00 % 0.15 % postgres: postgres postgres [local] idle in transaction
6508 be/4 postgres 0.00 B/s 10.24 M/s 0.00 % 0.15 % postgres: postgres postgres [local] idle
6510 be/4 postgres 0.00 B/s 10.13 M/s 0.00 % 0.15 % postgres: postgres postgres [local] BINDTE
6504 be/4 postgres 0.00 B/s 9.61 M/s 0.00 % 0.15 % postgres: postgres postgres [local] COMMIT
6520 be/4 postgres 0.00 B/s 11.16 M/s 0.00 % 0.14 % postgres: postgres postgres [local] UPDATE
6511 be/4 postgres 0.00 B/s 10.21 M/s 0.00 % 0.14 % postgres: postgres postgres [local] BINDTE
6499 be/4 postgres 0.00 B/s 9.81 M/s 0.00 % 0.14 % postgres: postgres postgres [local] INSERT
6517 be/4 postgres 0.00 B/s 11.31 M/s 0.00 % 0.14 % postgres: postgres postgres [local] BIND
6497 be/4 postgres 0.00 B/s 9.59 M/s 0.00 % 0.14 % postgres: postgres postgres [local] idle in transaction
6516 be/4 postgres 0.00 B/s 9.93 M/s 0.00 % 0.14 % postgres: postgres postgres [local] idle in transaction
6514 be/4 postgres 0.00 B/s 10.50 M/s 0.00 % 0.14 % postgres: postgres postgres [local] BIND
6521 be/4 postgres 0.00 B/s 10.58 M/s 0.00 % 0.13 % postgres: postgres postgres [local] BIND
6515 be/4 postgres 0.00 B/s 10.42 M/s 0.00 % 0.13 % postgres: postgres postgres [local] idle in transaction
6518 be/4 postgres 0.00 B/s 9.93 M/s 0.00 % 0.13 % postgres: postgres postgres [local] UPDATE
6502 be/4 postgres 0.00 B/s 9.63 M/s 0.00 % 0.12 % postgres: postgres postgres [local] COMMIT
6506 be/4 postgres 0.00 B/s 10.11 M/s 0.00 % 0.12 % postgres: postgres postgres [local] UPDATE
6492 be/4 postgres 0.00 B/s 10.52 M/s 0.00 % 0.11 % postgres: postgres postgres [local] BIND
6437 be/4 postgres 0.00 B/s 292.30 M/s 0.00 % 0.01 % postgres: background writer
参考
man write
https://www.postgresql.org/docs/11/monitoring-stats.html#MONITORING-STATS-VIEWS
《[未完待续] PostgreSQL 一键诊断项 - 珍藏级》
《PostgreSQL 实时健康监控 大屏 - 低频指标 - 珍藏级》
《PostgreSQL 实时健康监控 大屏 - 高频指标(服务器) - 珍藏级》
《PostgreSQL 实时健康监控 大屏 - 高频指标 - 珍藏级》
man iotop
《PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)》
《如何生成和阅读EnterpriseDB (PPAS(Oracle 兼容版)) AWR诊断报告》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
