Hi,
I was trying to add WAL stats to pg_stat_io. While doing that I was comparing pg_stat_wal and pg_stat_io's WAL stats and there was some inequality between the total number of WALs. I found that the difference comes from bgwriter's WALs. bgwriter generates WAL but it doesn't flush them because the pgstat_report_wal() function isn't called in bgwriter. I attached a small patch for calling the pgstat_report_wal() function in bgwriter.
bgwriter generates WAL by calling functions in this order:
bgwriter.c -> BackgroundWriterMain() -> BgBufferSync() -> SyncOneBuffer() -> FlushBuffer() -> XLogFlush() -> XLogWrite()
I used a query like BEGIN; followed by lots of(3000 in my case) INSERT, DELETE, or UPDATE, followed by a COMMIT while testing.
Example output before patch applied:
┌─────────────┬─────────────────┐
│ view_name │ total_wal_write │
├─────────────┼─────────────────┤
│ pg_stat_wal │ 10318 │
│ pg_stat_io │ 10321 │
└─────────────┴─────────────────┘
┌─────────────────────┬────────┬────────┐
│ backend_type │ object │ writes │
├─────────────────────┼────────┼────────┤
│ autovacuum launcher │ wal │ 0 │
│ autovacuum worker │ wal │ 691 │
│ client backend │ wal │ 8170 │
│ background worker │ wal │ 0 │
│ background writer │ wal │ 3 │
│ checkpointer │ wal │ 1 │
│ standalone backend │ wal │ 737 │
│ startup │ wal │ 0 │
│ walsender │ wal │ 0 │
│ walwriter │ wal │ 719 │
└─────────────────────┴────────┴────────┘
After the patch has been applied, there are no differences between pg_stat_wal and pg_stat_io.
I appreciate any comment/feedback on this patch.
Regards,
Nazir Bilal Yavuz
Microsoft