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: Michael Paquier <michael@paquier.xyz>
Cc: "bharath.rupireddyforpostgres@gmail.com" <bharath.rupireddyforpostgres@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2023-11-06T12:35:01Z
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 Tue, 31 Oct 2023 at 16:57, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:
> On Thu, 26 Oct 2023 at 09:28, Michael Paquier <michael@paquier.xyz> wrote:
> >
> > And perhaps just putting that everything that calls
> > pgstat_count_io_op_time() under track_io_timing is just natural?
> > What's the performance regression you would expect if both WAL and
> > block I/O are controlled by that, still one would expect only one of
> > them?
>
> I will check these and I hope I will come back with something meaningful.

I applied the patches on upstream postgres and then run pgbench for each
available clock sources couple of times:
# Set fsync = off and track_io_timing = on
# pgbench -i -s 100 test
# pgbench -M prepared -c16 -j8 -f <( echo "SELECT
pg_logical_emit_message(true, \:client_id::text, '1234567890');") -T60 test

Results are:

╔═════════╦═══════════════════════════════╦════════╗
║         ║      track_wal_io_timing      ║        ║
╠═════════╬═══════════════╦═══════════════╬════════╣
║  clock  ║       on      ║      off      ║ change ║
║ sources ║               ║               ║        ║
╠═════════╬═══════════════╬═══════════════╬════════╣
║   tsc   ║               ║               ║        ║
║         ║ 514814.459170 ║ 519826.284139 ║   %1   ║
╠═════════╬═══════════════╬═══════════════╬════════╣
║   hpet  ║               ║               ║        ║
║         ║ 132116.272121 ║ 141820.548447 ║   %7   ║
╠═════════╬═══════════════╬═══════════════╬════════╣
║ acpi_pm ║               ║               ║        ║
║         ║ 394793.092255 ║ 403723.874719 ║   %2   ║
╚═════════╩═══════════════╩═══════════════╩════════╝

Regards,
Nazir Bilal Yavuz
Microsoft