Horton

Re: Show WAL write and fsync stats in pg_stat_io

Bertrand Drouvot <bertranddrouvot.pg@gmail.com>

View thread
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Nazir Bilal Yavuz <byavuz81@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, Melanie Plageman <melanieplageman@gmail.com>, Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>, "bharath.rupireddyforpostgres@gmail.com" <bharath.rupireddyforpostgres@gmail.com>, Thomas Munro <thomas.munro@gmail.com>
Date: 2025-01-27T07:45:25Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix copy-paste error related to the autovacuum launcher in pgstat_io.c

  2. Move SQL tests of pg_stat_io for WAL data to recovery test 029_stats_restart

  3. Add data for WAL in pg_stat_io and backend statistics

  4. Improve comment on top of pgstat_count_io_op_time()

  5. Refactor pgstat_prepare_io_time() with an input argument instead of a GUC

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