On Wed, 21 Jun 2023 at 18:03, Matthias van de Meent <boekewurm+postgres@gmail.com> wrote: > > On Wed, 21 Jun 2023 at 13:04, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote: > > 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 was quite confused here, as XLogWrite() does not generate any WAL; > it only writes existing WAL from buffers to disk. > In a running PostgreSQL instance, WAL is only generated through > XLogInsert(xloginsert.c) and serialized / written to buffers in its > call to XLogInsertRecord(xlog.c); XLogFlush and XLogWrite are only > responsible for writing those buffers to disk.
Yes, you are right. Correct explanation should be "bgwriter writes existing WAL from buffers to disk but pg_stat_wal doesn't count them because bgwriter doesn't call pgstat_report_wal() to update WAL statistics".
> I also got confused with your included views; they're not included in > the patch and the current master branch doesn't emit object=wal, so I > can't really check that the patch works as intended.
I attached a WIP patch for showing WAL stats in pg_stat_io.
After applying patch, I used these queries for the getting views I shared in the first mail;
Query for the first view: SELECT 'pg_stat_wal' AS view_name, SUM(wal_write) AS total_wal_write FROM pg_stat_wal UNION ALL SELECT 'pg_stat_io' AS view_name, SUM(writes) AS total_wal_write FROM pg_stat_io WHERE object = 'wal';
Query for the second view: SELECT backend_type, object, writes FROM pg_stat_io where object = 'wal';
I also changed the description on the patch file and attached it.