Horton

Re: Show WAL write and fsync stats in pg_stat_io

Nazir Bilal Yavuz <byavuz81@gmail.com>

View thread
From: Nazir Bilal Yavuz <byavuz81@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paquier <michael@paquier.xyz>, 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-05T20:02:15Z
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

Hi,

On Wed, 5 Feb 2025 at 21:32, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Michael Paquier <michael@paquier.xyz> writes:
> > At the end, we want this patch and this data, and my benchmarcking is
> > not showing much differences even if going through a workload with
> > many pages, so I've used the version relying entirely on
> > track_io_timing and applied it.
>
> Locally, the test added by this commit fails like so:
>
> diff -U3 /home/postgres/pgsql/src/test/regress/expected/stats.out /home/postgres
> /pgsql/src/test/regress/results/stats.out
> --- /home/postgres/pgsql/src/test/regress/expected/stats.out    2025-02-04 12:33
> :07.456393545 -0500
> +++ /home/postgres/pgsql/src/test/regress/results/stats.out     2025-02-05 13:08
> :30.605638432 -0500
> @@ -886,7 +886,7 @@
>    WHERE context = 'normal' AND object = 'wal';
>   ?column?
>  ----------
> - t
> + f
>  (1 row)
>
>  -----
>
> This is pretty repeatable (not perfectly so) in a build with
> --enable-debug --enable-cassert --enable-tap-tests --with-llvm
> but it usually passes without --with-llvm.  System is fairly
> up-to-date RHEL8 on x86_64.  No idea why the buildfarm isn't
> unhappy.  Any pointers where to look?

Thanks for the report!

My thoughts when adding this test was that startup process must do the
WAL read I/O while server is starting, i.e.:

'''
startup process ->
InitWalRecovery ->
ReadCheckpointRecord ->
ReadRecord ->
XLogPrefetcherReadRecord ->
lrq_complete_lsn ->
lrq_prefetch ->
lrq->next = XLogPrefetcherNextBlock ->
XLogReadAhead ->
XLogDecodeNextRecord ->
ReadPageInternal ->
state->routine.page_read = XLogPageRead()
'''

Is there a chance that the function chain above does not get triggered
while running the stats.sql test?

--
Regards,
Nazir Bilal Yavuz
Microsoft