2012/10/24 1:12, Alvaro Herrera wrote:
Satoshi Nagayasu escribió:
With this patch, walwriter process and each backend process
would sum up dirty writes, and send it to the stat collector.
So, the value could be saved in the stat file, and could be
kept on restarting.
The statistics could be retreive with using
pg_stat_get_xlog_dirty_writes() function, and could be reset
with calling pg_stat_reset_shared('walwriter').
Now, I have one concern.
The reset time could be captured in globalStats.stat_reset_timestamp,
but this value is the same with the bgwriter one.
So, once pg_stat_reset_shared('walwriter') is called,
stats_reset column in pg_stat_bgwriter does represent
the reset time for walwriter, not for bgwriter.
How should we handle this? Should we split this value?
And should we have new system view for walwriter?
I think the answer to the two last questions is yes. It doesn't seem to
make sense, to me, to have a single reset timings for what are
effectively two separate things.
Please submit an updated patch to next CF. I'm marking this one
returned with feedback. Thanks.
I attached the latest one, which splits the reset_time
for bgwriter and walwriter, and provides new system view,
called pg_stat_walwriter, to show the dirty write counter
and the reset time.
Regards,
--
Satoshi Nagayasu <sn...@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index b4fcbaf..0ae885b 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1907,6 +1907,13 @@ include 'filename'
results in most cases.
</para>
+ <para>
+ When you see pg_stat_walwriter.dirty_write, which means number
+ of buffer flushing at buffer full, is continuously increasing
+ in your running server, you may need to enlarge this buffer
+ size.
+ </para>
+
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 39ccfbb..3117f91 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -278,6 +278,14 @@ postgres: <replaceable>user</> <replaceable>database</>
<replaceable>host</> <re
</row>
<row>
+
<entry><structname>pg_stat_walwriter</><indexterm><primary>pg_stat_walwriter</primary></indexterm></entry>
+ <entry>One row only, showing statistics about the wal writer
+ process's activity. See <xref linkend="pg-stat-walwriter-view">
+ for details.
+ </entry>
+ </row>
+
+ <row>
<entry><structname>pg_stat_database</><indexterm><primary>pg_stat_database</primary></indexterm></entry>
<entry>One row per database, showing database-wide statistics. See
<xref linkend="pg-stat-database-view"> for details.
@@ -735,6 +743,39 @@ postgres: <replaceable>user</> <replaceable>database</>
<replaceable>host</> <re
single row, containing global data for the cluster.
</para>
+ <table id="pg-stat-walwriter-view" xreflabel="pg_stat_walwriter">
+ <title><structname>pg_stat_walwriter</structname> View</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>dirty_writes</></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Number of dirty writes, which means flushing wal buffers
+ because of its full.</entry>
+ </row>
+ <row>
+ <entry><structfield>stats_reset</></entry>
+ <entry><type>timestamp with time zone</type></entry>
+ <entry>Time at which these statistics were last reset</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_stat_walwriter</structname> view will always have a
+ single row, containing global data for the cluster.
+ </para>
+
<table id="pg-stat-database-view" xreflabel="pg_stat_database">
<title><structname>pg_stat_database</structname> View</title>
<tgroup cols="3">
diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index d251d08..631a0af 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -1347,6 +1347,7 @@ AdvanceXLInsertBuffer(bool new_segment)
WriteRqst.Write = OldPageRqstPtr;
WriteRqst.Flush = 0;
XLogWrite(WriteRqst, false, false);
+ WalWriterStats.m_xlog_dirty_writes++;
LWLockRelease(WALWriteLock);
TRACE_POSTGRESQL_WAL_BUFFER_WRITE_DIRTY_DONE();
}
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 607a72f..40f0c34 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -671,6 +671,11 @@ CREATE VIEW pg_stat_bgwriter AS
pg_stat_get_buf_alloc() AS buffers_alloc,
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
+CREATE VIEW pg_stat_walwriter AS
+ SELECT
+ pg_stat_get_xlog_dirty_writes() AS dirty_writes,
+ pg_stat_get_wal_stat_reset_time() AS stats_reset;
+
CREATE VIEW pg_user_mappings AS
SELECT
U.oid AS umid,
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index be3adf1..5be78c6 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -125,6 +125,15 @@ char *pgstat_stat_tmpname = NULL;
*/
PgStat_MsgBgWriter BgWriterStats;
+/*
+ * WalWriter global statistics counter.
+ * Despite its name, this counter is actually used not only in walwriter,
+ * but also in each backend process to sum up xlog dirty writes.
+ * Those processes would increment this counter in each XLogWrite call,
+ * then send it to the stat collector process.
+ */
+PgStat_MsgWalWriter WalWriterStats;
+
/* ----------
* Local data
* ----------
@@ -279,6 +288,7 @@ static void pgstat_recv_autovac(PgStat_MsgAutovacStart
*msg, int len);
static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
+static void pgstat_recv_walwriter(PgStat_MsgWalWriter *msg, int len);
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int
len);
@@ -762,6 +772,9 @@ pgstat_report_stat(bool force)
/* Now, send function statistics */
pgstat_send_funcstats();
+
+ /* Now, send wal buffer flush statistics */
+ pgstat_send_walwriter();
}
/*
@@ -1188,11 +1201,13 @@ pgstat_reset_shared_counters(const char *target)
if (strcmp(target, "bgwriter") == 0)
msg.m_resettarget = RESET_BGWRITER;
+ else if (strcmp(target, "walwriter") == 0)
+ msg.m_resettarget = RESET_WALWRITER;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized reset target: \"%s\"",
target),
- errhint("Target must be \"bgwriter\".")));
+ errhint("Target must be \"bgwriter\" or
\"walwriter\".")));
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETSHAREDCOUNTER);
pgstat_send(&msg, sizeof(msg));
@@ -2988,6 +3003,38 @@ pgstat_send_bgwriter(void)
MemSet(&BgWriterStats, 0, sizeof(BgWriterStats));
}
+/* ----------
+ * pgstat_send_walwriter() -
+ *
+ * Send walwriter statistics to the collector
+ * ----------
+ */
+void
+pgstat_send_walwriter(void)
+{
+ /* We assume this initializes to zeroes */
+ static const PgStat_MsgBgWriter all_zeroes;
+
+ /*
+ * This function can be called even if nothing at all has happened. In
+ * this case, avoid sending a completely empty message to the stats
+ * collector.
+ */
+ if (memcmp(&WalWriterStats, &all_zeroes, sizeof(PgStat_MsgWalWriter))
== 0)
+ return;
+
+ /*
+ * Prepare and send the message
+ */
+ pgstat_setheader(&WalWriterStats.m_hdr, PGSTAT_MTYPE_WALWRITER);
+ pgstat_send(&WalWriterStats, sizeof(WalWriterStats));
+
+ /*
+ * Clear out the statistics buffer, so it can be re-used.
+ */
+ MemSet(&WalWriterStats, 0, sizeof(WalWriterStats));
+}
+
/* ----------
* PgstatCollectorMain() -
@@ -3209,6 +3256,10 @@ PgstatCollectorMain(int argc, char *argv[])
pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
break;
+ case PGSTAT_MTYPE_WALWRITER:
+
pgstat_recv_walwriter((PgStat_MsgWalWriter *) &msg, len);
+ break;
+
case PGSTAT_MTYPE_FUNCSTAT:
pgstat_recv_funcstat((PgStat_MsgFuncstat *) &msg, len);
break;
@@ -3638,7 +3689,8 @@ pgstat_read_statsfile(Oid onlydb, bool permanent)
* Set the current timestamp (will be kept only in case we can't load an
* existing statsfile).
*/
- globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+ globalStats.stat_bgw_reset_timestamp = GetCurrentTimestamp();
+ globalStats.stat_wal_reset_timestamp = GetCurrentTimestamp();
/*
* Try to open the status file. If it doesn't exist, the backends simply
@@ -4381,8 +4433,23 @@
pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
if (msg->m_resettarget == RESET_BGWRITER)
{
/* Reset the global background writer statistics for the
cluster. */
- memset(&globalStats, 0, sizeof(globalStats));
- globalStats.stat_reset_timestamp = GetCurrentTimestamp();
+ globalStats.timed_checkpoints = 0;
+ globalStats.requested_checkpoints = 0;
+ globalStats.checkpoint_write_time = 0;
+ globalStats.checkpoint_sync_time = 0;
+ globalStats.buf_written_checkpoints = 0;
+ globalStats.buf_written_clean = 0;
+ globalStats.maxwritten_clean = 0;
+ globalStats.buf_written_backend = 0;
+ globalStats.buf_fsync_backend = 0;
+ globalStats.buf_alloc = 0;
+ globalStats.stat_bgw_reset_timestamp = GetCurrentTimestamp();
+ }
+ else if (msg->m_resettarget == RESET_WALWRITER)
+ {
+ /* Reset the global walwriter statistics for the cluster. */
+ globalStats.xlog_dirty_writes = 0;
+ globalStats.stat_wal_reset_timestamp = GetCurrentTimestamp();
}
/*
@@ -4536,6 +4603,18 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
}
/* ----------
+ * pgstat_recv_walwriter() -
+ *
+ * Process a WALWRITER message.
+ * ----------
+ */
+static void
+pgstat_recv_walwriter(PgStat_MsgWalWriter *msg, int len)
+{
+ globalStats.xlog_dirty_writes += msg->m_xlog_dirty_writes;
+}
+
+/* ----------
* pgstat_recv_recoveryconflict() -
*
* Process a RECOVERYCONFLICT message.
diff --git a/src/backend/postmaster/walwriter.c
b/src/backend/postmaster/walwriter.c
index c3e15ef..cd294fb 100644
--- a/src/backend/postmaster/walwriter.c
+++ b/src/backend/postmaster/walwriter.c
@@ -290,6 +290,8 @@ WalWriterMain(void)
else if (left_till_hibernate > 0)
left_till_hibernate--;
+ pgstat_send_walwriter();
+
/*
* 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 7d4059f..fc25dda 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -97,6 +97,7 @@ extern Datum
pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_buf_alloc(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_wal_stat_reset_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_xact_numscans(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_xact_tuples_returned(PG_FUNCTION_ARGS);
@@ -118,6 +119,8 @@ extern Datum pg_stat_reset_shared(PG_FUNCTION_ARGS);
extern Datum pg_stat_reset_single_table_counters(PG_FUNCTION_ARGS);
extern Datum pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_xlog_dirty_writes(PG_FUNCTION_ARGS);
+
/* Global bgwriter statistics, from bgwriter.c */
extern PgStat_MsgBgWriter bgwriterStats;
@@ -1443,7 +1446,7 @@ pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS)
Datum
pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
{
- PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_reset_timestamp);
+ PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_bgw_reset_timestamp);
}
Datum
@@ -1465,6 +1468,12 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
}
Datum
+pg_stat_get_wal_stat_reset_time(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_wal_reset_timestamp);
+}
+
+Datum
pg_stat_get_xact_numscans(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
@@ -1701,3 +1710,9 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+
+Datum
+pg_stat_get_xlog_dirty_writes(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_INT64(pgstat_fetch_global()->xlog_dirty_writes);
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f935eb1..d6edb5a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2702,6 +2702,8 @@ DATA(insert OID = 3063 ( pg_stat_get_buf_fsync_backend
PGNSP PGUID 12 1 0 0 0 f
DESCR("statistics: number of backend buffer writes that did their own fsync");
DATA(insert OID = 2859 ( pg_stat_get_buf_alloc PGNSP PGUID 12
1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_
pg_stat_get_buf_alloc _null_ _null_ _null_ ));
DESCR("statistics: number of buffer allocations");
+DATA(insert OID = 2860 ( pg_stat_get_wal_stat_reset_time PGNSP PGUID 12 1 0 0
0 f f f f t f s 0 0 1184 "" _null_ _null_ _null_ _null_
pg_stat_get_wal_stat_reset_time _null_ _null_ _null_ ));
+DESCR("statistics: last reset for the wal");
DATA(insert OID = 2978 ( pg_stat_get_function_calls PGNSP PGUID 12
1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_
pg_stat_get_function_calls _null_ _null_ _null_ ));
DESCR("statistics: number of function calls");
@@ -2746,6 +2748,9 @@ DESCR("statistics: reset collected statistics for a
single table or index in the
DATA(insert OID = 3777 ( pg_stat_reset_single_function_counters PGNSP
PGUID 12 1 0 0 0 f f f f f f v 1 0 2278 "26" _null_ _null_ _null_ _null_
pg_stat_reset_single_function_counters _null_ _null_ _null_ ));
DESCR("statistics: reset collected statistics for a single function in the
current database");
+DATA(insert OID = 3766 ( pg_stat_get_xlog_dirty_writes PGNSP PGUID 12 1 0 0
0 f f f f f f v 0 0 20 "" _null_ _null_ _null_ _null_
pg_stat_get_xlog_dirty_writes _null_ _null_ _null_ ));
+DESCR("statistics: get xlog dirty buffer write statistics");
+
DATA(insert OID = 3163 ( pg_trigger_depth PGNSP
PGUID 12 1 0 0 0 f f f f t f s 0 0 23 "" _null_ _null_ _null_ _null_
pg_trigger_depth _null_ _null_ _null_ ));
DESCR("current trigger depth");
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 613c1c2..f71c538 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -45,6 +45,7 @@ typedef enum StatMsgType
PGSTAT_MTYPE_VACUUM,
PGSTAT_MTYPE_ANALYZE,
PGSTAT_MTYPE_BGWRITER,
+ PGSTAT_MTYPE_WALWRITER,
PGSTAT_MTYPE_FUNCSTAT,
PGSTAT_MTYPE_FUNCPURGE,
PGSTAT_MTYPE_RECOVERYCONFLICT,
@@ -102,7 +103,8 @@ typedef struct PgStat_TableCounts
/* Possible targets for resetting cluster-wide shared values */
typedef enum PgStat_Shared_Reset_Target
{
- RESET_BGWRITER
+ RESET_BGWRITER,
+ RESET_WALWRITER
} PgStat_Shared_Reset_Target;
/* Possible object types for resetting single counters */
@@ -372,6 +374,17 @@ typedef struct PgStat_MsgBgWriter
} PgStat_MsgBgWriter;
/* ----------
+ * PgStat_MsgWalWriter Sent by the walwriter to update
statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgWalWriter
+{
+ PgStat_MsgHdr m_hdr;
+
+ PgStat_Counter m_xlog_dirty_writes;
+} PgStat_MsgWalWriter;
+
+/* ----------
* PgStat_MsgRecoveryConflict Sent by the backend upon recovery conflict
* ----------
*/
@@ -499,6 +512,7 @@ typedef union PgStat_Msg
PgStat_MsgVacuum msg_vacuum;
PgStat_MsgAnalyze msg_analyze;
PgStat_MsgBgWriter msg_bgwriter;
+ PgStat_MsgWalWriter msg_walwriter;
PgStat_MsgFuncstat msg_funcstat;
PgStat_MsgFuncpurge msg_funcpurge;
PgStat_MsgRecoveryConflict msg_recoveryconflict;
@@ -622,7 +636,10 @@ typedef struct PgStat_GlobalStats
PgStat_Counter buf_written_backend;
PgStat_Counter buf_fsync_backend;
PgStat_Counter buf_alloc;
- TimestampTz stat_reset_timestamp;
+ TimestampTz stat_bgw_reset_timestamp;
+
+ PgStat_Counter xlog_dirty_writes;
+ TimestampTz stat_wal_reset_timestamp;
} PgStat_GlobalStats;
@@ -730,6 +747,8 @@ extern char *pgstat_stat_filename;
*/
extern PgStat_MsgBgWriter BgWriterStats;
+extern PgStat_MsgWalWriter WalWriterStats;
+
/*
* Updated by pgstat_count_buffer_*_time macros
*/
@@ -858,6 +877,7 @@ extern void pgstat_twophase_postabort(TransactionId xid,
uint16 info,
void *recdata, uint32 len);
extern void pgstat_send_bgwriter(void);
+extern void pgstat_send_walwriter(void);
/* ----------
* Support functions for the SQL-callable functions to
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
