Horton

Re: Show WAL write and fsync stats in pg_stat_io

Bertrand Drouvot <bertranddrouvot.pg@gmail.com>

View thread
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Nazir Bilal Yavuz <byavuz81@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-03T10:37:54Z
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

On Mon, Feb 03, 2025 at 08:50:15AM +0000, Bertrand Drouvot wrote:
> === 6
> 
> I'll also do some benchmark on my side.

So, I did some tests using:

c=1 && psql -c checkpoint -c 'select pg_switch_wal()' &&
pgbench -n -M prepared -c$c -j$c -f <(echo "SELECT pg_logical_emit_message(true, 'test', repeat('0', 8192));";) -P1 -t 20000

With 2 message size: 8192 and 10.

Here are the results (outliers removed and tsc clock source):

+------------+-------------+-------------+
| Version    | Msg Size 10 | Msg Size 8K |
+------------+-------------+-------------+
| With PATCH |    980 TPS  |    910 TPS  |
| On Master  |    980 TPS  |    910 TPS  |
+------------+-------------+-------------+

So the patch does not produce perf regression according to those tests.

Out of curiosity I also played a bit with the IO tracking (and hpet clock source)
and got:

+---------------------------------+-------------+-------------+-----------+
| Test Configuration              |    PATCH    |   MASTER    | % Change |
+---------------------------------+-------------+-------------+-----------+
| track_io_timing                 |                                      |
|   Message size 8192             |    805 TPS  |    810 TPS  |   -0.6%  |
|   Message size 10               |    860 TPS  |    860 TPS  |    0.0%  |
+---------------------------------+-------------+-------------+-----------+
| track_wal_io_timing             |                                      |
|   Message size 8192             |    810 TPS  |    810 TPS  |    0.0%  |
|   Message size 10               |    860 TPS  |    860 TPS  |    0.0%  |
+---------------------------------+-------------+-------------+-----------+
| track_wal_io + track_io         |                                      |
|   Message size 8192             |    800 TPS  |    800 TPS  |    0.0%  |
|   Message size 10               |    855 TPS  |    860 TPS  |   -0.6%  |
+---------------------------------+-------------+-------------+-----------+

Based on those results the patch does not show a noticable impact when IO timing
tracking is/are enabled.

FYI, It’s also worth noticing that if hpet is set then it also affect negatively
even if no timing tracking is set. It means that when track IO timing is/are
enabled the perf regression seen above are not fully related to having then
enabled but also (for a large part) to hpet vs tsc.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com