Re: Show WAL write and fsync stats in pg_stat_io

Nazir Bilal Yavuz <byavuz81@gmail.com>

From: Nazir Bilal Yavuz <byavuz81@gmail.com>

To: Michael Paquier <michael@paquier.xyz>

Cc: Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>, "bharath.rupireddyforpostgres@gmail.com" <bharath.rupireddyforpostgres@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>

Date: 2023-12-01T09:02:05Z

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 →

Fix copy-paste error related to the autovacuum launcher in pgstat_io.c
- 17d8bba6dad1 18.0 landed
Move SQL tests of pg_stat_io for WAL data to recovery test 029_stats_restart
- 428fadb7e97e 18.0 landed
Add data for WAL in pg_stat_io and backend statistics
- a051e71e28a1 18.0 landed
Improve comment on top of pgstat_count_io_op_time()
- b998fedab74c 18.0 landed
Refactor pgstat_prepare_io_time() with an input argument instead of a GUC
- 3c9d9acae0bc 17.0 landed

Attachments

v5-0003-Add-IOOBJECT_WAL-IOCONTEXT_INIT-write-and-fsync-t.patch (text/x-diff) patch v5-0003
v5-0002-Add-IOOBJECT_WAL-IOCONTEXT_NORMAL-write-and-fsync.patch (text/x-diff) patch v5-0002
v5-0001-Show-WAL-stats-on-pg_stat_io-except-streaming-rep.patch (text/x-diff) patch v5-0001
v5-0004-Add-IOOBJECT_WAL-IOCONTEXT_NORMAL-read-tests.patch (text/x-diff) patch v5-0004

Hi,

Thanks for all the feedback. I am sharing the new version of the patchset.

Current status of the patchset is:
- IOOBJECT_WAL / IOCONTEXT_NORMAL / read, write, fsync stats and their
tests are added.
- IOOBJECT_WAL / IOCONTEXT_INIT stats and their tests are added.
- Documentation is updated.
- pg_stat_io shows different op_bytes for the IOOBJECT_WAL operations.
- PendingWalStats.wal_sync and PendingWalStats.wal_write_time /
PendingWalStats.wal_sync_time are moved to pgstat_count_io_op_n() /
pgstat_count_io_op_time() respectively.

Updates & Discussion items:
- Try to set op_bytes for BackendType / IOContext: I think we don't
need this now, we will need this when we add streaming replication WAL
IOs.

- Decide which 'BackendType / IOContext / IOOp' should not be tracked:
-- IOOBJECT_WAL / IOCONTEXT_INIT + IOCONTEXT_NORMAL / write and fsync
IOs can be done on every backend that tracks IO statistics. Because of
that and since we have a pgstat_tracks_io_bktype(bktype) check, I
didn't add another check for this.
-- I found that only the standalone backend and startup backend do
IOOBJECT_WAL / IOCONTEXT_NORMAL / read IOs. So, I added a check for
that but I am not sure if there are more backends that do WAL reads on
WAL recovery.

- For the IOOBJECT_WAL / IOCONTEXT_INIT and IOOBJECT_WAL /
IOCONTEXT_NORMAL / read tests, I used initial WAL IOs to check these
stats. I am not sure if that is the correct way or enough to test
these stats.

- To not calculate WAL timings on pg_stat_wal and pg_stat_io view,
pg_stat_wal view's WAL timings are fetched from pg_stat_io. Since
these timings are fetched from pg_stat_io, pg_stat_reset_shared('io')
will reset pg_stat_wal's timings too.

- I didn't move 'PendingWalStats.wal_sync' out from the
'pgstat_count_io_op_n' function because they count the same thing
(block vs system calls) but I agree that this doesn't look good.

Any kind of feedback would be appreciated.

-- 
Regards,
Nazir Bilal Yavuz
Microsoft