Horton

Re: Show WAL write and fsync stats in pg_stat_io

Tom Lane <tgl@sss.pgh.pa.us>

View thread
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Paquier <michael@paquier.xyz>
Cc: Nazir Bilal Yavuz <byavuz81@gmail.com>, Bertrand Drouvot <bertranddrouvot.pg@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>, "bharath.rupireddyforpostgres@gmail.com" <bharath.rupireddyforpostgres@gmail.com>, Thomas Munro <thomas.munro@gmail.com>
Date: 2025-02-06T02:52:14Z
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

Michael Paquier <michael@paquier.xyz> writes:
> The queries for the normal context are not going to have this problem
> even if we have a pg_stat_reset_shared('io'), but the init context
> gets unstable, unfortunately.  I don't see a way through here in the
> main regression test suite, so how about moving these into
> 027_stream_regress.pl.  It is possible to query the WAL read on the
> standby of this test, and the write part for the init context on the
> primary.  The syncs are not relevant as TAP usually runs with
> fsync=off, so better to remove this part entirely.

Yeah, if we want to assume we can see stats counts left over from
initdb, we have to put this in a TAP test, though I dunno if that is
the most appropriate one.

Now that I've looked at the tests a bit, I'm also distressed
by this test pattern:

SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
SELECT pg_stat_reset_slru();
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';

This assumes that the execution time of pg_stat_reset_slru() is more
than the system clock resolution.  I won't be surprised to see that
fail in the future.  We did discover recently that gettimeofday is
good to the microsecond on most modern platforms [1], but it won't
get any better than that, while our machines keep getting faster.
Just for reference, on my hardly-bleeding-edge-anymore workstation:

regression=# select clock_timestamp(), pg_stat_reset_slru(), clock_timestamp();
        clock_timestamp        | pg_stat_reset_slru |        clock_timestamp        
-------------------------------+--------------------+-------------------------------
 2025-02-05 21:47:54.929221-05 |                    | 2025-02-05 21:47:54.929223-05
(1 row)

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/be0339cc-1ae1-4892-9445-8e6d8995a44d%40eisentraut.org