Thread

Re: Show WAL write and fsync stats in pg_stat_io

Nitin Jadhav <nitinjadhavpostgres@gmail.com> — 2024-07-06T07:28:56Z

> 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

Yes. I was considering a scenario where WAL hits are counted as hits
on the same IOObject as shared buffer hits.

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

Thank you for the clarification. I agree with the proposal to count
WAL buffer hits for IOObject WAL separately from shared buffer hits.
This distinction will provide a more accurate representation.

Best Regards,
Nitin Jadhav
Azure Database for PostgreSQL
Microsoft

On Mon, Jun 17, 2024 at 8:23 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> 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