--- v11-0001-Add-statistics-related-to-write-sync-wal-records.patch 2021-03-03 14:27:02.119713795 +0900 +++ v12-0001-Add-statistics-related-to-write-sync-wal-records.patch 2021-03-03 20:22:47.258763274 +0900 @@ -1,4 +1,4 @@ -From c97fff03cd2bd51b28f6c6fde56c48792683f44e Mon Sep 17 00:00:00 2001 +From d870cfa78e501097cc56780ebb3140db6b9261e5 Mon Sep 17 00:00:00 2001 From: Masahiro Ikeda Date: Fri, 12 Feb 2021 11:19:59 +0900 Subject: [PATCH 1/2] Add statistics related to write/sync wal records. @@ -30,10 +30,10 @@ doc/src/sgml/config.sgml | 23 +++++++- doc/src/sgml/monitoring.sgml | 56 ++++++++++++++++++ doc/src/sgml/wal.sgml | 12 +++- - src/backend/access/transam/xlog.c | 57 +++++++++++++++++++ + src/backend/access/transam/xlog.c | 59 +++++++++++++++++-- src/backend/catalog/system_views.sql | 4 ++ src/backend/postmaster/pgstat.c | 4 ++ - src/backend/postmaster/walwriter.c | 16 ++++-- + src/backend/postmaster/walwriter.c | 21 +++++++ src/backend/utils/adt/pgstatfuncs.c | 24 +++++++- src/backend/utils/misc/guc.c | 9 +++ src/backend/utils/misc/postgresql.conf.sample | 1 + @@ -41,7 +41,7 @@ src/include/catalog/pg_proc.dat | 6 +- src/include/pgstat.h | 10 ++++ src/test/regress/expected/rules.out | 6 +- - 14 files changed, 214 insertions(+), 15 deletions(-) + 14 files changed, 221 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index b5718fc136..c232f537b2 100644 @@ -182,7 +182,7 @@ from having to do writes. On such systems one should increase the number of WAL buffers by diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c -index fe56324439..98f558b4c7 100644 +index fe56324439..fafc1b7fac 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -110,6 +110,7 @@ int CommitDelay = 0; /* precommit delay in microseconds */ @@ -232,51 +232,60 @@ if (written <= 0) { char xlogfname[MAXFNAMELEN]; -@@ -10526,6 +10549,21 @@ void +@@ -10526,6 +10549,24 @@ void issue_xlog_fsync(int fd, XLogSegNo segno) { char *msg = NULL; -+ bool issue_fsync = false; + instr_time start; + -+ /* Check whether the WAL file was synced to disk right now */ -+ if (enableFsync && -+ (sync_method == SYNC_METHOD_FSYNC || -+ sync_method == SYNC_METHOD_FSYNC_WRITETHROUGH || -+ sync_method == SYNC_METHOD_FDATASYNC)) -+ { -+ /* Measure I/O timing to sync the WAL file */ -+ if (track_wal_io_timing) -+ INSTR_TIME_SET_CURRENT(start); -+ -+ issue_fsync = true; -+ } ++ /* ++ * Check whether the WAL file was synced to disk right now. ++ * ++ * If fsync is disabled, never issue fsync method. ++ * ++ * If sync_mothod is SYNC_METHOD_OPEN or SYNC_METHOD_OPEN_DSYNC, the WAL ++ * file is already synced. ++ */ ++ if (!enableFsync || ++ sync_method == SYNC_METHOD_OPEN || ++ sync_method == SYNC_METHOD_OPEN_DSYNC) ++ return; ++ ++ /* Measure I/O timing to sync the WAL file */ ++ if (track_wal_io_timing) ++ INSTR_TIME_SET_CURRENT(start); pgstat_report_wait_start(WAIT_EVENT_WAL_SYNC); switch (sync_method) -@@ -10570,6 +10608,25 @@ issue_xlog_fsync(int fd, XLogSegNo segno) +@@ -10546,10 +10587,6 @@ issue_xlog_fsync(int fd, XLogSegNo segno) + msg = _("could not fdatasync file \"%s\": %m"); + break; + #endif +- case SYNC_METHOD_OPEN: +- case SYNC_METHOD_OPEN_DSYNC: +- /* write synced it already */ +- break; + default: + elog(PANIC, "unrecognized wal_sync_method: %d", sync_method); + break; +@@ -10570,6 +10607,20 @@ issue_xlog_fsync(int fd, XLogSegNo segno) } pgstat_report_wait_end(); + + /* + * Increment the I/O timing and the number of times WAL files were synced. -+ * -+ * Check whether the WAL file was synced to disk right now because -+ * statistics must be incremented when syncing really occurred. + */ -+ if (issue_fsync) ++ if (track_wal_io_timing) + { -+ if (track_wal_io_timing) -+ { -+ instr_time duration; ++ instr_time duration; + -+ INSTR_TIME_SET_CURRENT(duration); -+ INSTR_TIME_SUBTRACT(duration, start); -+ WalStats.m_wal_sync_time += INSTR_TIME_GET_MICROSEC(duration); -+ } -+ WalStats.m_wal_sync++; ++ INSTR_TIME_SET_CURRENT(duration); ++ INSTR_TIME_SUBTRACT(duration, start); ++ WalStats.m_wal_sync_time += INSTR_TIME_GET_MICROSEC(duration); + } ++ ++ WalStats.m_wal_sync++; } /* @@ -311,38 +320,52 @@ /* ---------- diff --git a/src/backend/postmaster/walwriter.c b/src/backend/postmaster/walwriter.c -index 4f1a8e356b..08fa7032c0 100644 +index 4f1a8e356b..8491e6f6d6 100644 --- a/src/backend/postmaster/walwriter.c +++ b/src/backend/postmaster/walwriter.c -@@ -223,6 +223,7 @@ WalWriterMain(void) +@@ -78,6 +78,11 @@ int WalWriterFlushAfter = 128; + #define LOOPS_UNTIL_HIBERNATE 50 + #define HIBERNATE_FACTOR 25 + ++/* ++ * Minimum time between stats file updates; in milliseconds. ++ */ ++#define PGSTAT_STAT_INTERVAL 500 ++ + /* + * Main entry point for walwriter process + * +@@ -222,7 +227,12 @@ WalWriterMain(void) + */ for (;;) { ++ /* we assume this inits to all zeroes: */ ++ static TimestampTz last_report = 0; ++ long cur_timeout; + int rc; ++ TimestampTz now; /* * Advertise whether we might hibernate in this cycle. We do this -@@ -263,9 +264,16 @@ WalWriterMain(void) - else - cur_timeout = WalWriterDelay * HIBERNATE_FACTOR; - -- (void) WaitLatch(MyLatch, -- WL_LATCH_SET | WL_TIMEOUT | WL_EXIT_ON_PM_DEATH, -- cur_timeout, -- WAIT_EVENT_WAL_WRITER_MAIN); -+ rc = WaitLatch(MyLatch, -+ WL_LATCH_SET | WL_TIMEOUT | WL_EXIT_ON_PM_DEATH, -+ cur_timeout, -+ WAIT_EVENT_WAL_WRITER_MAIN); -+ +@@ -253,6 +263,17 @@ WalWriterMain(void) + else if (left_till_hibernate > 0) + left_till_hibernate--; + + /* -+ * Send WAL statistics only if WalWriterDelay has elapsed to minimize -+ * the overhead in WAL-writing. ++ * Don't send a message unless it's been at least PGSTAT_STAT_INTERVAL ++ * msec since we last sent one + */ -+ if (rc & WL_TIMEOUT) ++ now = GetCurrentTimestamp(); ++ if (TimestampDifferenceExceeds(last_report, now, PGSTAT_STAT_INTERVAL)) ++ { + pgstat_send_wal(); - } - } ++ last_report = now; ++ } ++ + /* + * Sleep until we are signaled or WalWriterDelay has elapsed. If we + * haven't done anything useful for quite some time, lengthen the diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 62bff52638..7296ef04ff 100644 --- a/src/backend/utils/adt/pgstatfuncs.c