Re: Show WAL write and fsync stats in pg_stat_io

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.pg@gmail.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.

> 
> Quick note: WAL stats in pg_stat_io view includes WAL initialization
> stats under object = 'wal' and context = 'init', your query may count
> these initialization stats too. So the correct query is:
> 
> 'select sum(write_bytes),stats_reset from pg_stat_io where object =
> 'wal' and context = 'normal' group by stats_reset;'.
>

Thanks for mentioning this filtering! 
 
> By saying that, this does not solve the problem; there is still a
> difference although you omit WAL initialization stats from the
> pg_stat_io.

Yeah. The "init" data was also empty on my side after the pg_stat_reset_shared()
and after the insertion in the table.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com