Re: Show WAL write and fsync stats in pg_stat_io

Hi,

On Mon, 27 Jan 2025 at 10:45, Bertrand Drouvot
<bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Fri, Jan 24, 2025 at 06:29:46PM +0300, Nazir Bilal Yavuz wrote:
> > Hi,
> >
> > Thanks for looking into this!
> >
> > On Fri, 24 Jan 2025 at 17:20, Bertrand Drouvot
> > <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
> > >
> > > I did not look at the code yet but did a few tests.
> > > I can see diff between pg_stat_wal and pg_stat_io, for example:
> > >
> > > "
> > > postgres=# select pg_stat_reset_shared();
> > > pg_stat_reset_shared
> > > ----------------------
> > >
> > > (1 row)
> > >
> > > postgres=# insert into bdt select a from generate_series(1,200000) a ;
> > > INSERT 0 200000
> > >
> > > postgres=# select wal_bytes,stats_reset from pg_stat_wal;
> > > wal_bytes | stats_reset
> > > -----------+-------------------------------
> > > 11800088 | 2025-01-24 14:17:28.507994+00
> > > (1 row)
> > >
> > > postgres=# select sum(write_bytes),stats_reset from pg_stat_io where object = 'wal' group by stats_reset;
> > > sum | stats_reset
> > > ----------+-------------------------------
> > > 12853248 | 2025-01-24 14:17:28.507988+00
> > > (1 row)
> > >
> > > Is that expected?
> >
> > I am not sure, I think they need to be the same.
>
> Yeah I think so (or at least we should document the reason(s) why (and how)
> they could differ).
>
> > I could not
> > understand the cause of the difference at first glance. I will look
> > into this and will come back to you.
>
> Thanks.

Sorry for replying late. It turns out that the difference is expected.
It is because pg_stat_wal.wal_bytes tracks the total amount of WAL
generated, while pg_stat_io tracks the I/Os that are written to disk.

From the docs of pg_stat_io: pg_stat_io view will contain one row for
each combination of backend type, target I/O object, and I/O context,
showing cluster-wide I/O statistics. Combinations which do not make
sense are omitted.

From the docs of pg_stat_wal.wal_bytes: Total amount of WAL generated in bytes.

It is explicitly said that pg_stat_io tracks I/O operations and
pg_stat_wal.wal_bytes tracks total amount of WAL generated in bytes. I
think this is clear enough. Do you think we still need additional
explanation?

--
Regards,
Nazir Bilal Yavuz
Microsoft