Re: Show WAL write and fsync stats in pg_stat_io

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

Best Regards,
Nitin Jadhav
Azure Database for PostgreSQL
Microsoft

On Tue, May 28, 2024 at 6:18 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, May 13, 2024 at 7:42 PM Bharath Rupireddy
> <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> >
> > On Fri, Apr 19, 2024 at 1:32 PM Nazir Bilal Yavuz <byavuz81(at)gmail(dot)com> wrote:
> > >
> > > > I wanted to inform you that the 73f0a13266 commit changed all WALRead
> > > > calls to read variable bytes, only the WAL receiver was reading
> > > > variable bytes before.
> > >
> > > I want to start working on this again if possible. I will try to
> > > summarize the current status:
> >
> > Thanks for working on this.
> >
> > > * With the 73f0a13266 commit, the WALRead() function started to read
> > > variable bytes in every case. Before, only the WAL receiver was
> > > reading variable bytes.
> > >
> > > * With the 91f2cae7a4 commit, WALReadFromBuffers() is merged. We were
> > > discussing what we have to do when this is merged. It is decided that
> > > WALReadFromBuffers() does not call pgstat_report_wait_start() because
> > > this function does not perform any IO [1]. We may follow the same
> > > logic by not including these to pg_stat_io?
> >
> > Right. WALReadFromBuffers doesn't do any I/O.
> >
> > Whoever reads WAL from disk (backends, walsenders, recovery process)
> > using pg_pread (XLogPageRead, WALRead) needs to be tracked in
> > pg_stat_io or some other view. If it were to be in pg_stat_io,
> > although we may not be able to distinguish WAL read stats at a backend
> > level (like how many times/bytes a walsender or recovery process or a
> > backend read WAL from disk), but it can help understand overall impact
> > of WAL read I/O at a cluster level. With this approach, the WAL I/O
> > stats are divided up - WAL read I/O and write I/O stats are in
> > pg_stat_io and pg_stat_wal respectively.
> >
> > This makes me think if we need to add WAL read I/O stats also to
> > pg_stat_wal. Then, we can also add WALReadFromBuffers stats
> > hits/misses there. With this approach, pg_stat_wal can be a one-stop
> > view for all the WAL related stats.
> >
>
> 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?
>
> --
> With Regards,
> Amit Kapila.
>
>