Hi,
Jeff Janes has pointed out that my previous patch could hold
a number of the dirty writes only in single local backend, and
it could not hold all over the cluster, because the counter
was allocated in the local process memory.
That's true, and I have fixed it with moving the counter into
the shared memory, as a member of XLogCtlWrite, to keep total
dirty writes in the cluster.
Regards,
2012/07/07 21:00, Satoshi Nagayasu wrote:
> Hi all,
>
> I've created new patch to get/reset statistics of WAL buffer
> writes (flushes) caused by WAL buffer full.
>
> This patch provides two new functions, pg_stat_get_xlog_dirty_write()
> and pg_stat_reset_xlog_dirty_write(), which have been designed to
> determine an appropriate value for WAL buffer size.
>
> If this counter is increasing in the production environment,
> it would mean that the WAL buffer size is too small to hold
> xlog records generated the transactions. So, you can increase
> your WAL buffer size to keep xlog records and to reduce WAL writes.
>
> I think this patch would not affect to WAL write performance,
> but still paying attention to it.
>
> Any comments or suggestions?
>
> Regards,
>
> -----------------------------------------------------------
> [snaga@devvm03 src]$ psql -p 15432 postgres
> psql (9.3devel)
> Type "help" for help.
>
> postgres=# SELECT pg_stat_get_xlog_dirty_write();
> pg_stat_get_xlog_dirty_write
> ------------------------------
> 0
> (1 row)
>
> postgres=# \q
> [snaga@devvm03 src]$ pgbench -p 15432 -s 10 -c 32 -t 1000 postgres
> Scale option ignored, using pgbench_branches table count = 10
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 10
> query mode: simple
> number of clients: 32
> number of threads: 1
> number of transactions per client: 1000
> number of transactions actually processed: 32000/32000
> tps = 141.937738 (including connections establishing)
> tps = 142.123457 (excluding connections establishing)
> [snaga@devvm03 src]$ psql -p 15432 postgres
> psql (9.3devel)
> Type "help" for help.
>
> postgres=# SELECT pg_stat_get_xlog_dirty_write();
> pg_stat_get_xlog_dirty_write
> ------------------------------
> 0
> (1 row)
>
> postgres=# begin;
> BEGIN
> postgres=# DELETE FROM pgbench_accounts;
> DELETE 1000000
> postgres=# commit;
> COMMIT
> postgres=# SELECT pg_stat_get_xlog_dirty_write();
> pg_stat_get_xlog_dirty_write
> ------------------------------
> 19229
> (1 row)
>
> postgres=# SELECT pg_stat_reset_xlog_dirty_write();
> pg_stat_reset_xlog_dirty_write
> --------------------------------
>
> (1 row)
>
> postgres=# SELECT pg_stat_get_xlog_dirty_write();
> pg_stat_get_xlog_dirty_write
> ------------------------------
> 0
> (1 row)
>
> postgres=# \q
> [snaga@devvm03 src]$
> -----------------------------------------------------------
>
>
--
Satoshi Nagayasu <sn...@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index 642c129..893acf8 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -370,6 +370,11 @@ typedef struct XLogCtlWrite
{
int curridx; /* cache index of next
block to write */
pg_time_t lastSegSwitchTime; /* time of last xlog
segment switch */
+
+ /*
+ * Counter for WAL dirty buffer writes.
+ */
+ uint64 WalBufferWriteDirtyCount;
} XLogCtlWrite;
/*
@@ -1504,6 +1509,8 @@ AdvanceXLInsertBuffer(bool new_segment)
}
else
{
+ XLogCtlWrite *Write = &XLogCtl->Write;
+
/*
* Have to write buffers while holding insert
lock. This is
* not good, so only write as much as we
absolutely must.
@@ -1512,6 +1519,10 @@ AdvanceXLInsertBuffer(bool new_segment)
WriteRqst.Write = OldPageRqstPtr;
WriteRqst.Flush = 0;
XLogWrite(WriteRqst, false, false);
+ /*
+ * XLogCtrlWrite must be protected with
WALWriteLock.
+ */
+ Write->WalBufferWriteDirtyCount++;
LWLockRelease(WALWriteLock);
TRACE_POSTGRESQL_WAL_BUFFER_WRITE_DIRTY_DONE();
}
@@ -10492,3 +10503,26 @@ SetWalWriterSleeping(bool sleeping)
xlogctl->WalWriterSleeping = sleeping;
SpinLockRelease(&xlogctl->info_lck);
}
+
+uint64
+xlog_dirty_write_counter_get()
+{
+ XLogCtlWrite *Write = &XLogCtl->Write;
+ uint64 count;
+
+ LWLockAcquire(WALWriteLock, LW_SHARED);
+ count = Write->WalBufferWriteDirtyCount;
+ LWLockRelease(WALWriteLock);
+
+ return count;
+}
+
+void
+xlog_dirty_write_counter_reset()
+{
+ XLogCtlWrite *Write = &XLogCtl->Write;
+
+ LWLockAcquire(WALWriteLock, LW_EXCLUSIVE);
+ Write->WalBufferWriteDirtyCount = 0;
+ LWLockRelease(WALWriteLock);
+}
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index 7c0705a..d544a5b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -117,6 +117,9 @@ 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_write(PG_FUNCTION_ARGS);
+extern Datum pg_stat_reset_xlog_dirty_write(PG_FUNCTION_ARGS);
+
/* Global bgwriter statistics, from bgwriter.c */
extern PgStat_MsgBgWriter bgwriterStats;
@@ -1700,3 +1703,16 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+
+Datum
+pg_stat_get_xlog_dirty_write(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_INT64(xlog_dirty_write_counter_get());
+}
+
+Datum
+pg_stat_reset_xlog_dirty_write(PG_FUNCTION_ARGS)
+{
+ xlog_dirty_write_counter_reset();
+ PG_RETURN_VOID();
+}
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index ec79870..01343b9 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -325,6 +325,9 @@ extern XLogRecPtr do_pg_start_backup(const char
*backupidstr, bool fast, char **
extern XLogRecPtr do_pg_stop_backup(char *labelfile, bool waitforarchive);
extern void do_pg_abort_backup(void);
+extern uint64 xlog_dirty_write_counter_get(void);
+extern void xlog_dirty_write_counter_reset(void);
+
/* File path names (all relative to $PGDATA) */
#define BACKUP_LABEL_FILE "backup_label"
#define BACKUP_LABEL_OLD "backup_label.old"
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index bee7154..e21f57e 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2736,6 +2736,11 @@ 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_write 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_write _null_ _null_ _null_ ));
+DESCR("statistics: get xlog dirty buffer write statistics");
+DATA(insert OID = 3767 ( pg_stat_reset_xlog_dirty_write PGNSP PGUID 12 1 0 0
0 f f f f f f v 0 0 2278 "" _null_ _null_ _null_ _null_
pg_stat_reset_xlog_dirty_write _null_ _null_ _null_ ));
+DESCR("statistics: reset 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");
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
