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(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.

>
> 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