Horton

Re: Show WAL write and fsync stats in pg_stat_io

Michael Paquier <michael@paquier.xyz>

View thread
From: Michael Paquier <michael@paquier.xyz>
To: Tom Lane <tgl@sss.pgh.pa.us>
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-06T03:35:28Z
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

Attachments

On Wed, Feb 05, 2025 at 09:52:14PM -0500, Tom Lane wrote:
> Michael Paquier <michael@paquier.xyz> writes:
> 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.

A second option I can think of for the reads is a SQL query in
pg_walinspect.  We are sure that we have a xlogreader context there,
forcing reads.

Anyway, I would just stick all that to TAP, like the attached in 027,
where we would rely on the startup process to read data, and the
checkpointer to initialize a segment for the primary.  Perhaps not the
best position, but we already have similar queries in this test, and
these two are cheap.  Thoughts about the attached?

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

Hmm.  Interesting.
--
Michael