From 6c349d83b607cfe20a685a5f66a4fa0e24dbe458 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Wed, 26 Feb 2025 09:49:59 +0900 Subject: Re-add GUC track_wal_io_timing This commit is a rework of 2421e9a51d20, about which Andres Freund has raised some concerns as it is valuable to have both track_io_timing and track_wal_io_timing in some cases, as the WAL write and fsync paths can be a major bottleneck for some workloads. Hence, it can be relevant to not calculate the WAL timings in environments where pg_test_timing performs poorly while capturing some IO data under track_io_timing for the non-WAL IO paths. The opposite can be also true: it should be possible to disable the non-WAL timings and enable the WAL timings (the previous GUC setups allowed this possibility). track_wal_io_timing is added back in this commit, controlling if WAL timings should be calculated in pg_stat_io for the read, fsync and write paths, as done previously with pg_stat_wal. pg_stat_wal previously tracked only the sync and write parts (now removed), read stats is new data tracked in pg_stat_io, all three are aggregated if track_wal_io_timing is enabled. The read part matters during recovery or if a XLogReader is used. Extra note: more control over if the types of timings calculated in pg_stat_io could be done with a GUC that lists pairs of (IOObject,IOOp). Reported-by: Andres Freund Author: Bertrand Drouvot Co-authored-by: Michael Paquier Discussion: https://postgr.es/m/3opf2wh2oljco6ldyqf7ukabw3jijnnhno6fjb4mlu6civ5h24@fcwmhsgmlmzu --- doc/src/sgml/config.sgml | 32 ++++++++++++++++++++++++++++++-- doc/src/sgml/monitoring.sgml | 31 ++++++++++++++++++++++++++----- doc/src/sgml/wal.sgml | 4 ++-- 3 files changed, 58 insertions(+), 9 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index a8354576108..e55700f35b8 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8378,9 +8378,11 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; displayed in pg_stat_database, - pg_stat_io, in the output of the + pg_stat_io (if object + is not wal), in the output of the - pg_stat_get_backend_io() function, in the + pg_stat_get_backend_io() function (if + object is not wal), in the output of when the BUFFERS option is used, in the output of when the VERBOSE option is used, by autovacuum @@ -8393,6 +8395,32 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + track_wal_io_timing (boolean) + + track_wal_io_timing configuration parameter + + + + + Enables timing of WAL I/O calls. This parameter is off by default, + as it will repeatedly query the operating system for the current time, + which may cause significant overhead on some platforms. + You can use the pg_test_timing tool to + measure the overhead of timing on your system. + I/O timing information is displayed in + + pg_stat_io for the + object wal and in the output of + the + pg_stat_get_backend_io() function for the + object wal. + Only superusers and users with the appropriate SET + privilege can change this setting. + + + + track_functions (enum) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 3dfd059b7ee..9178f1d34ef 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -183,6 +183,11 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser of block read, write, extend, and fsync times. + + The parameter enables monitoring + of WAL read, write and fsync times. + + Normally these parameters are set in postgresql.conf so that they apply to all server processes, but it is possible to turn @@ -2723,7 +2728,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage Time spent in read operations in milliseconds (if - is enabled, otherwise zero) + is enabled and + object is not wal, + or if is enabled + and object is wal, + otherwise zero) @@ -2757,7 +2766,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage Time spent in write operations in milliseconds (if - is enabled, otherwise zero) + is enabled and + object is not wal, + or if is enabled + and object is wal, + otherwise zero) @@ -2816,8 +2829,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage extend_time double precision - Time spent in extend operations in milliseconds (if - is enabled, otherwise zero) + Time spent in extend operations in milliseconds. (if + is enabled and + object is not wal, + or if is enabled + and object is wal, + otherwise zero) @@ -2887,7 +2904,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage Time spent in fsync operations in milliseconds (if - is enabled, otherwise zero) + is enabled and + object is not wal, + or if is enabled + and object is wal, + otherwise zero) diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml index 4fc09761115..f3b86b26be9 100644 --- a/doc/src/sgml/wal.sgml +++ b/doc/src/sgml/wal.sgml @@ -808,7 +808,7 @@ There are two internal functions to write WAL data to disk: XLogWrite and issue_xlog_fsync. - When is enabled, the total + When is enabled, the total amounts of time XLogWrite writes and issue_xlog_fsync syncs WAL data to disk are counted as write_time and fsync_time in @@ -828,7 +828,7 @@ fsync, or fsync_writethrough, the write operation moves WAL buffers to kernel cache and issue_xlog_fsync syncs them to disk. Regardless - of the setting of track_io_timing, the number + of the setting of track_wal_io_timing, the number of times XLogWrite writes and issue_xlog_fsync syncs WAL data to disk are also counted as writes and fsyncs -- cgit v1.2.3