Re: bgwriter doesn't flush WAL stats

Hi,

Thanks for the explanation.

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.

Regards,
Nazir Bilal Yavuz
Microsoft