About to add WAL write/fsync statistics to pg_stat_wal view
Masahiro Ikeda <ikedamsh@oss.nttdata.com>
From: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2020-12-08T05:06:52Z
Lists: pgsql-hackers
Attachments
- 0001_add_wal_io_activity_to_the_pg_stat_wal.patch (text/x-diff) patch
Hi,
I propose to add wal write/fsync statistics to pg_stat_wal view.
It's useful not only for developing/improving source code related to WAL
but also for users to detect workload changes, HW failure, and so on.
I introduce "track_wal_io_timing" parameter and provide the following
information on pg_stat_wal view.
I separate the parameter from "track_io_timing" to "track_wal_io_timing"
because IIUC, WAL I/O activity may have a greater impact on query
performance than database I/O activity.
```
postgres=# SELECT wal_write, wal_write_time, wal_sync, wal_sync_time
FROM pg_stat_wal;
-[ RECORD 1 ]--+----
wal_write | 650 # Total number of times WAL data was written to
the disk
wal_write_time | 43 # Total amount of time that has been spent in the
portion of WAL data was written to disk
# if track-wal-io-timing is enabled, otherwise
zero
wal_sync | 78 # Total number of times WAL data was synced to the
disk
wal_sync_time | 104 # Total amount of time that has been spent in the
portion of WAL data was synced to disk
# if track-wal-io-timing is enabled, otherwise
zero
```
What do you think?
Please let me know your comments.
Regards
--
Masahiro Ikeda
NTT DATA CORPORATION
Commits
-
Send statistics collected during shutdown checkpoint to the stats collector.
- b82640df0062 14.0 landed
-
Force to send remaining WAL stats to the stats collector at walwriter exit.
- 33394ee6f243 14.0 landed
-
Track total amounts of times spent writing and syncing WAL data to disk.
- ff99918c625a 14.0 landed
-
Retry short writes when flushing WAL.
- 79ce29c734c6 9.4.0 cited