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
From 9317e86e4b2b3ba202ce090f383ac48526b97955 Mon Sep 17 00:00:00 2001
From: Nazir Bilal Yavuz <byavu...@gmail.com>
Date: Wed, 21 Jun 2023 13:39:13 +0300
Subject: [PATCH v1] Flush WAL in bgwriter
bgwriter generates WAL but it doesn't flush them, so they can't be seen
in pg_stat_wal. Call pgstat_report_wal() to flush WAL in bgwriter.
---
src/backend/postmaster/bgwriter.c | 1 +
1 file changed, 1 insertion(+)
diff --git a/src/backend/postmaster/bgwriter.c b/src/backend/postmaster/bgwriter.c
index caad642ec9..f2e4f23d9f 100644
--- a/src/backend/postmaster/bgwriter.c
+++ b/src/backend/postmaster/bgwriter.c
@@ -241,6 +241,7 @@ BackgroundWriterMain(void)
/* Report pending statistics to the cumulative stats system */
pgstat_report_bgwriter();
+ pgstat_report_wal(true);
if (FirstCallSinceLastCheckpoint())
{
--
2.40.1
