Horton

Re: Show WAL write and fsync stats in pg_stat_io

Melanie Plageman <melanieplageman@gmail.com>

View thread
From: Melanie Plageman <melanieplageman@gmail.com>
To: Nazir Bilal Yavuz <byavuz81@gmail.com>
Cc: Nitin Jadhav <nitinjadhavpostgres@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, Michael Paquier <michael@paquier.xyz>, Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>, Thomas Munro <thomas.munro@gmail.com>
Date: 2024-06-17T14:53:27Z
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

On Thu, Jun 13, 2024 at 5:24 AM Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:
>
> 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?

Perhaps Nitin was thinking of a scenario in which WAL hits are counted
as hits on the same IOObject as shared buffer hits. Since this thread
has been going on for awhile and we haven't recently had a schema
overview, I could understand if there was some confusion. For clarity,
I will restate that the current proposal is to count WAL buffer hits
for IOObject WAL, which means they will not be mixed in with shared
buffer hits.

And I think it makes sense to count WAL IOObject hits since increasing
wal_buffers can lead to more hits, right?

- Melanie