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: Nitin Jadhav <nitinjadhavpostgres@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, Michael Paquier <michael@paquier.xyz>, Melanie Plageman <melanieplageman@gmail.com>, Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>, Thomas Munro <thomas.munro@gmail.com>
Date: 2024-06-13T09:24:36Z
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,

Thank you for looking into this!

On Sun, 9 Jun 2024 at 18:05, Nitin Jadhav <nitinjadhavpostgres@gmail.com> wrote:
>
> > If possible, let's have all the I/O stats (even for WAL) in
> > pg_stat_io. Can't we show the WAL data we get from buffers in the hits
> > column and then have read_bytes or something like that to know the
> > amount of data read?
>
> The ‘hits’ column in ‘pg_stat_io’ is a vital indicator for adjusting a
> database. It signifies the count of cache hits, or in other words, the
> instances where data was located in the ‘shared_buffers’. As a result,
> keeping an eye on the ‘hits’ column in ‘pg_stat_io’ can offer useful
> knowledge about the buffer cache’s efficiency and assist users in
> making educated choices when fine-tuning their database. However, if
> we include the hit count of WAL buffers in this, it may lead to
> misleading interpretations for database tuning. If there’s something
> I’ve overlooked that’s already been discussed, please feel free to
> correct me.

I think counting them as a hit makes sense. We read data from WAL
buffers instead of reading them from disk. And, WAL buffers are stored
in shared memory so I believe they can be counted as hits in the
shared buffers. Could you please explain how this change can 'lead to
misleading interpretations for database tuning' a bit more?

-- 
Regards,
Nazir Bilal Yavuz
Microsoft