Horton

Re: Show WAL write and fsync stats in pg_stat_io

Nazir Bilal Yavuz <byavuz81@gmail.com>

View thread
From: Nazir Bilal Yavuz <byavuz81@gmail.com>
To: Bertrand Drouvot <bertranddrouvot.pg@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-24T15:29:46Z
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,

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. I could not
understand the cause of the difference at first glance. I will look
into this and will come back to you.

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;'.

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.

-- 
Regards,
Nazir Bilal Yavuz
Microsoft