Hi all,
I have fixed my previous patch for pg_stat_lwlocks view, and
as Josh commented, it now supports local and global (shared)
statistics in the same system view.
Local statistics means the counters are only effective in the
same session, and shared ones means the counters are shared within
the entire cluster.
Also the global statistics would be collected via pgstat collector
process like other statistics do.
Now, the global statistics struct has been splitted into two parts
for different use, for bgwriter stats and lwlock stats.
Therefore, calling pg_stat_reset_shared('bgwriter') or
pg_stat_reset_shared('lwlocks') would reset dedicated struct,
not entire PgStat_GlobalStats.
Comments and review are always welcome.
Regards,
------------------------------------------------------------------------------
postgres=# SELECT * FROM pg_stat_lwlocks;
lwlockid | local_calls | local_waits | local_time_ms | shared_calls |
shared_waits | shared_time_ms
----------+-------------+-------------+---------------+--------------+--------------+----------------
0 | 0 | 0 | 0 | 4268 |
0 | 0
1 | 43 | 0 | 0 | 387 |
0 | 0
2 | 0 | 0 | 0 | 19 |
0 | 0
3 | 0 | 0 | 0 | 28 |
0 | 0
4 | 3 | 0 | 0 | 315 |
0 | 0
5 | 0 | 0 | 0 | 24 |
0 | 0
6 | 1 | 0 | 0 | 76 |
0 | 0
7 | 0 | 0 | 0 | 16919 |
0 | 0
8 | 0 | 0 | 0 | 0 |
0 | 0
9 | 0 | 0 | 0 | 0 |
0 | 0
10 | 0 | 0 | 0 | 0 |
0 | 0
11 | 0 | 0 | 0 | 75 |
0 | 0
12 | 0 | 0 | 0 | 0 |
0 | 0
13 | 0 | 0 | 0 | 0 |
0 | 0
14 | 0 | 0 | 0 | 0 |
0 | 0
15 | 0 | 0 | 0 | 0 |
0 | 0
16 | 0 | 0 | 0 | 0 |
0 | 0
17 | 0 | 0 | 0 | 61451 |
6 | 0
18 | 0 | 0 | 0 | 0 |
0 | 0
19 | 0 | 0 | 0 | 0 |
0 | 0
20 | 0 | 0 | 0 | 0 |
0 | 0
21 | 1 | 0 | 0 | 9 |
0 | 0
22 | 0 | 0 | 0 | 0 |
0 | 0
23 | 0 | 0 | 0 | 0 |
0 | 0
24 | 0 | 0 | 0 | 1 |
0 | 0
25 | 0 | 0 | 0 | 0 |
0 | 0
26 | 2 | 0 | 0 | 18 |
0 | 0
27 | 0 | 0 | 0 | 0 |
0 | 0
28 | 0 | 0 | 0 | 0 |
0 | 0
29 | 0 | 0 | 0 | 0 |
0 | 0
30 | 0 | 0 | 0 | 0 |
0 | 0
31 | 0 | 0 | 0 | 0 |
0 | 0
32 | 0 | 0 | 0 | 0 |
0 | 0
33 | 4 | 0 | 0 | 207953 |
0 | 0
50 | 8 | 0 | 0 | 33388 |
0 | 0
67 | 0 | 0 | 0 | 0 |
0 | 0
(36 rows)
postgres=#
------------------------------------------------------------------------------
2012/06/26 21:11, Satoshi Nagayasu wrote:
> Hi all,
>
> I've modified the pg_stat_lwlocks patch to be able to work with
> the latest PostgreSQL Git code.
>
> This patch provides:
> pg_stat_lwlocks New system view to show lwlock statistics.
> pg_stat_get_lwlocks() New function to retrieve lwlock statistics.
> pg_stat_reset_lwlocks() New function to reset lwlock statistics.
>
> Please try it out.
>
> Regards,
>
> 2012/06/26 5:29, Satoshi Nagayasu wrote:
>> Hi all,
>>
>> I've been working on a new system view, pg_stat_lwlocks, to observe
>> LWLock, and just completed my 'proof-of-concept' code that can work
>> with version 9.1.
>>
>> Now, I'd like to know the possibility of this feature for future
>> release.
>>
>> With this patch, DBA can easily determine a bottleneck around lwlocks.
>> --------------------------------------------------
>> postgres=# SELECT * FROM pg_stat_lwlocks ORDER BY time_ms DESC LIMIT 10;
>> lwlockid | calls | waits | time_ms
>> ----------+--------+-------+---------
>> 49 | 193326 | 32096 | 23688
>> 8 | 3305 | 133 | 1335
>> 2 | 21 | 0 | 0
>> 4 | 135188 | 0 | 0
>> 5 | 57935 | 0 | 0
>> 6 | 141 | 0 | 0
>> 7 | 24580 | 1 | 0
>> 3 | 3282 | 0 | 0
>> 1 | 41 | 0 | 0
>> 9 | 3 | 0 | 0
>> (10 rows)
>>
>> postgres=#
>> --------------------------------------------------
>>
>> In this view,
>> 'lwlockid' column represents LWLockId used in the backends.
>> 'calls' represents how many times LWLockAcquire() was called.
>> 'waits' represents how many times LWLockAcquire() needed to wait
>> within it before lock acquisition.
>> 'time_ms' represents how long LWLockAcquire() totally waited on
>> a lwlock.
>>
>> And lwlocks that use a LWLockId range, such as BufMappingLock or
>> LockMgrLock, would be grouped and summed up in a single record.
>> For example, lwlockid 49 in the above view represents LockMgrLock
>> statistics.
>>
>> Now, I know there are some considerations.
>>
>> (1) Performance
>>
>> I've measured LWLock performance both with and without the patch,
>> and confirmed that this patch does not affect the LWLock perfomance
>> at all.
>>
>> pgbench scores with the patch:
>> tps = 900.906658 (excluding connections establishing)
>> tps = 908.528422 (excluding connections establishing)
>> tps = 903.900977 (excluding connections establishing)
>> tps = 910.470595 (excluding connections establishing)
>> tps = 909.685396 (excluding connections establishing)
>>
>> pgbench scores without the patch:
>> tps = 909.096785 (excluding connections establishing)
>> tps = 894.868712 (excluding connections establishing)
>> tps = 910.074669 (excluding connections establishing)
>> tps = 904.022770 (excluding connections establishing)
>> tps = 895.673830 (excluding connections establishing)
>>
>> Of course, this experiment was not I/O bound, and the cache hit ratio
>> was>99.9%.
>>
>> (2) Memory space
>>
>> In this patch, I added three new members to LWLock structure
>> as uint64 to collect statistics.
>>
>> It means that those members must be held in the shared memory,
>> but I'm not sure whether it's appropriate.
>>
>> I think another possible option is holding those statistics
>> values in local (backend) process memory, and send them through
>> the stat collector process (like other statistics values).
>>
>> (3) LWLock names (or labels)
>>
>> Now, pg_stat_lwlocks view shows LWLockId itself. But LWLockId is
>> not easy for DBA to determine actual lock type.
>>
>> So, I want to show LWLock names (or labels), like 'WALWriteLock'
>> or 'LockMgrLock', but how should I implement it?
>>
>> Any comments?
>>
>> Regards,
>
>
--
Satoshi Nagayasu <sn...@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 607a72f..2f84940 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -671,6 +671,17 @@ 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_lwlocks AS
+ SELECT
+ S.lwlockid,
+ S.local_calls,
+ S.local_waits,
+ S.local_time_ms,
+ S.shared_calls,
+ S.shared_waits,
+ S.shared_time_ms
+ FROM pg_stat_get_lwlocks() AS S;
+
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 8389d5c..970e8bd 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -282,6 +282,7 @@ static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *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);
+static void pgstat_recv_lwlockstat(PgStat_MsgLWLockstat *msg, int len);
static void pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len);
static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
@@ -1188,6 +1189,8 @@ pgstat_reset_shared_counters(const char *target)
if (strcmp(target, "bgwriter") == 0)
msg.m_resettarget = RESET_BGWRITER;
+ else if (strcmp(target, "lwlocks") == 0)
+ msg.m_resettarget = RESET_LWLOCKSTAT;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1344,6 +1347,72 @@ pgstat_report_recovery_conflict(int reason)
}
/* --------
+ * pgstat_report_lwlockstat() -
+ *
+ * Tell the collector about lwlock statistics.
+ * --------
+ */
+void
+pgstat_report_lwlockstat(void)
+{
+ PgStat_MsgLWLockstat msg;
+
+ int32 lockid = 0;
+ int need_continue = 0;
+
+ report_continue:
+ memset(&msg, 0, sizeof(PgStat_MsgLWLockstat));
+
+ for ( ; lockid<NumFixedLWLocks+1 ; lockid++)
+ {
+ uint64 calls, waits, time_ms;
+
+ calls = waits = time_ms = 0;
+
+ calls = lwlock_get_stat_calls_global(lockid);
+ waits = lwlock_get_stat_waits_global(lockid);
+ time_ms = lwlock_get_stat_time_ms_global(lockid);
+
+ if ( calls>0 || waits>0 || time_ms>0 )
+ {
+ msg.m_entry[msg.m_nentries].lockid = lockid;
+ msg.m_entry[msg.m_nentries].calls = calls;
+ msg.m_entry[msg.m_nentries].waits = waits;
+ msg.m_entry[msg.m_nentries].waited_time = time_ms;
+
+ msg.m_nentries++;
+
+ lwlock_reset_stat_global(lockid);
+
+ /*
+ * Need to keep a message packet smaller than
PGSTAT_MSG_PAYLOAD.
+ * So, going to split a report into multiple messages.
+ */
+ if ( msg.m_nentries>=MAX_LWLOCKSTAT_ENTRIES )
+ {
+ need_continue = 1;
+ break;
+ }
+ }
+ }
+
+ if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+ return;
+
+ pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_LWLOCKSTAT);
+ pgstat_send(&msg, sizeof(msg));
+
+ /*
+ * Need to continue because of the larger report?
+ */
+ if ( need_continue )
+ {
+ need_continue = 0;
+ goto report_continue;
+ }
+}
+
+/* --------
* pgstat_report_deadlock() -
*
* Tell the collector about a deadlock detected.
@@ -3219,6 +3288,10 @@ PgstatCollectorMain(int argc, char *argv[])
pgstat_recv_recoveryconflict((PgStat_MsgRecoveryConflict *) &msg, len);
break;
+ case PGSTAT_MTYPE_LWLOCKSTAT:
+
pgstat_recv_lwlockstat((PgStat_MsgLWLockstat *) &msg, len);
+ break;
+
case PGSTAT_MTYPE_DEADLOCK:
pgstat_recv_deadlock((PgStat_MsgDeadlock *) &msg, len);
break;
@@ -4379,8 +4452,15 @@
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();
+ memset(&globalStats.bgwriterstats, 0,
sizeof(globalStats.bgwriterstats));
+ globalStats.bgwriterstats.reset_timestamp =
GetCurrentTimestamp();
+ }
+
+ if (msg->m_resettarget == RESET_LWLOCKSTAT)
+ {
+ /* Reset the global lwlock statistics for the cluster. */
+ memset(&globalStats.lwlockstats, 0,
sizeof(globalStats.lwlockstats));
+ globalStats.lwlockstats.reset_timestamp = GetCurrentTimestamp();
}
/*
@@ -4521,16 +4601,16 @@ pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
static void
pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
{
- globalStats.timed_checkpoints += msg->m_timed_checkpoints;
- globalStats.requested_checkpoints += msg->m_requested_checkpoints;
- globalStats.checkpoint_write_time += msg->m_checkpoint_write_time;
- globalStats.checkpoint_sync_time += msg->m_checkpoint_sync_time;
- globalStats.buf_written_checkpoints += msg->m_buf_written_checkpoints;
- globalStats.buf_written_clean += msg->m_buf_written_clean;
- globalStats.maxwritten_clean += msg->m_maxwritten_clean;
- globalStats.buf_written_backend += msg->m_buf_written_backend;
- globalStats.buf_fsync_backend += msg->m_buf_fsync_backend;
- globalStats.buf_alloc += msg->m_buf_alloc;
+ globalStats.bgwriterstats.timed_checkpoints += msg->m_timed_checkpoints;
+ globalStats.bgwriterstats.requested_checkpoints +=
msg->m_requested_checkpoints;
+ globalStats.bgwriterstats.checkpoint_write_time +=
msg->m_checkpoint_write_time;
+ globalStats.bgwriterstats.checkpoint_sync_time +=
msg->m_checkpoint_sync_time;
+ globalStats.bgwriterstats.buf_written_checkpoints +=
msg->m_buf_written_checkpoints;
+ globalStats.bgwriterstats.buf_written_clean += msg->m_buf_written_clean;
+ globalStats.bgwriterstats.maxwritten_clean += msg->m_maxwritten_clean;
+ globalStats.bgwriterstats.buf_written_backend +=
msg->m_buf_written_backend;
+ globalStats.bgwriterstats.buf_fsync_backend += msg->m_buf_fsync_backend;
+ globalStats.bgwriterstats.buf_alloc += msg->m_buf_alloc;
}
/* ----------
@@ -4574,6 +4654,27 @@ pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict
*msg, int len)
}
/* ----------
+ * pgstat_recv_lwlockstat() -
+ *
+ * Process a LWLockstat message.
+ * ----------
+ */
+static void
+pgstat_recv_lwlockstat(PgStat_MsgLWLockstat *msg, int len)
+{
+ int i;
+
+ for (i=0 ; i<msg->m_nentries ; i++)
+ {
+ int32 lockid = msg->m_entry[i].lockid;
+
+ globalStats.lwlockstats.lwlock_stat[lockid].calls +=
msg->m_entry[i].calls;
+ globalStats.lwlockstats.lwlock_stat[lockid].waits +=
msg->m_entry[i].waits;
+ globalStats.lwlockstats.lwlock_stat[lockid].waited_time +=
msg->m_entry[i].waited_time;
+ }
+}
+
+/* ----------
* pgstat_recv_deadlock() -
*
* Process a DEADLOCK message.
diff --git a/src/backend/storage/lmgr/lwlock.c
b/src/backend/storage/lmgr/lwlock.c
index 5e1ce17..402799d 100644
--- a/src/backend/storage/lmgr/lwlock.c
+++ b/src/backend/storage/lmgr/lwlock.c
@@ -32,6 +32,7 @@
#include "storage/proc.h"
#include "storage/spin.h"
+#include <sys/time.h>
/* We use the ShmemLock spinlock to protect LWLockAssign */
extern slock_t *ShmemLock;
@@ -48,6 +49,25 @@ typedef struct LWLock
/* tail is undefined when head is NULL */
} LWLock;
+typedef struct LWLockCounter2
+{
+ /* statistics stuff */
+ uint64 calls;
+ uint64 waits;
+ uint64 time_ms;
+} LWLockCounter2;
+
+/*
+ * LWLockCounterLocal has <NumFixedLWLocks> counters
+ * and one additional counter for dynamic LWLocks
+ * to hold lwlock statistic in the local session.
+ */
+LWLockCounter2 LWLockCounterLocal[NumFixedLWLocks+1];
+
+LWLockCounter2 LWLockCounterGlobal[NumFixedLWLocks+1];
+
+#define LWLockCounterId(X) ((X) < (NumFixedLWLocks+1) ? (X) :
(NumFixedLWLocks+1))
+
/*
* All the LWLock structs are allocated as an array in shared memory.
* (LWLockIds are indexes into the array.) We force the array stride to
@@ -90,6 +110,8 @@ static LWLockId held_lwlocks[MAX_SIMUL_LWLOCKS];
static int lock_addin_request = 0;
static bool lock_addin_request_allowed = true;
+static void InitLWockCounter(void);
+
#ifdef LWLOCK_STATS
static int counts_for_pid = 0;
static int *sh_acquire_counts;
@@ -253,6 +275,26 @@ LWLockShmemSize(void)
return size;
}
+/*
+ * Initialize local and global counters for lwlock statistics.
+ */
+static void
+InitLWockCounter(void)
+{
+ int i;
+
+ for (i=0 ; i<NumFixedLWLocks+1 ; i++)
+ {
+ LWLockCounterLocal[i].calls = 0;
+ LWLockCounterLocal[i].waits = 0;
+ LWLockCounterLocal[i].time_ms = 0;
+
+ LWLockCounterGlobal[i].calls = 0;
+ LWLockCounterGlobal[i].waits = 0;
+ LWLockCounterGlobal[i].time_ms = 0;
+ }
+}
+
/*
* Allocate shmem space for LWLocks and initialize the locks.
@@ -298,6 +340,8 @@ CreateLWLocks(void)
LWLockCounter = (int *) ((char *) LWLockArray - 2 * sizeof(int));
LWLockCounter[0] = (int) NumFixedLWLocks;
LWLockCounter[1] = numLocks;
+
+ InitLWockCounter();
}
@@ -344,9 +388,13 @@ LWLockAcquire(LWLockId lockid, LWLockMode mode)
PGPROC *proc = MyProc;
bool retry = false;
int extraWaits = 0;
+ struct timeval wait_start,wait_done;
PRINT_LWDEBUG("LWLockAcquire", lockid, lock);
+ LWLockCounterLocal[ LWLockCounterId(lockid) ].calls++;
+ LWLockCounterGlobal[ LWLockCounterId(lockid) ].calls++;
+
#ifdef LWLOCK_STATS
/* Set up local count state first time through in a given process */
if (counts_for_pid != MyProcPid)
@@ -395,6 +443,7 @@ LWLockAcquire(LWLockId lockid, LWLockMode mode)
for (;;)
{
bool mustwait;
+ uint64 waited;
/* Acquire mutex. Time spent holding mutex should be short! */
#ifdef LWLOCK_STATS
@@ -473,6 +522,9 @@ LWLockAcquire(LWLockId lockid, LWLockMode mode)
#endif
TRACE_POSTGRESQL_LWLOCK_WAIT_START(lockid, mode);
+ LWLockCounterLocal[ LWLockCounterId(lockid) ].waits++;
+ LWLockCounterGlobal[ LWLockCounterId(lockid) ].waits++;
+ gettimeofday(&wait_start, NULL);
for (;;)
{
@@ -484,6 +536,20 @@ LWLockAcquire(LWLockId lockid, LWLockMode mode)
}
TRACE_POSTGRESQL_LWLOCK_WAIT_DONE(lockid, mode);
+ gettimeofday(&wait_done, NULL);
+
+ if ( wait_done.tv_usec >= wait_start.tv_usec )
+ {
+ waited = ( wait_done.tv_usec - wait_start.tv_usec ) /
1000 ;
+ waited += ( wait_done.tv_sec - wait_start.tv_sec ) *
1000 ;
+ }
+ else
+ {
+ waited = ( wait_done.tv_usec + 1000*1000 -
wait_start.tv_usec ) / 1000 ;
+ waited += ( wait_done.tv_sec - 1 - wait_start.tv_sec )
* 1000 ;
+ }
+ LWLockCounterLocal[ LWLockCounterId(lockid) ].time_ms += waited;
+ LWLockCounterGlobal[ LWLockCounterId(lockid) ].time_ms +=
waited;
LOG_LWDEBUG("LWLockAcquire", lockid, "awakened");
@@ -885,3 +951,55 @@ LWLockHeldByMe(LWLockId lockid)
}
return false;
}
+
+uint64
+lwlock_get_stat_calls_local(LWLockId lockid)
+{
+ return LWLockCounterLocal[ LWLockCounterId(lockid) ].calls;
+}
+
+uint64
+lwlock_get_stat_waits_local(LWLockId lockid)
+{
+ return LWLockCounterLocal[ LWLockCounterId(lockid) ].waits;
+}
+
+uint64
+lwlock_get_stat_time_ms_local(LWLockId lockid)
+{
+ return LWLockCounterLocal[ LWLockCounterId(lockid) ].time_ms;
+}
+
+void
+lwlock_reset_stat_local(LWLockId lockid)
+{
+ LWLockCounterLocal[ LWLockCounterId(lockid) ].calls = 0;
+ LWLockCounterLocal[ LWLockCounterId(lockid) ].waits = 0;
+ LWLockCounterLocal[ LWLockCounterId(lockid) ].time_ms = 0;
+}
+
+uint64
+lwlock_get_stat_calls_global(LWLockId lockid)
+{
+ return LWLockCounterGlobal[ LWLockCounterId(lockid) ].calls;
+}
+
+uint64
+lwlock_get_stat_waits_global(LWLockId lockid)
+{
+ return LWLockCounterGlobal[ LWLockCounterId(lockid) ].waits;
+}
+
+uint64
+lwlock_get_stat_time_ms_global(LWLockId lockid)
+{
+ return LWLockCounterGlobal[ LWLockCounterId(lockid) ].time_ms;
+}
+
+void
+lwlock_reset_stat_global(LWLockId lockid)
+{
+ LWLockCounterGlobal[ LWLockCounterId(lockid) ].calls = 0;
+ LWLockCounterGlobal[ LWLockCounterId(lockid) ].waits = 0;
+ LWLockCounterGlobal[ LWLockCounterId(lockid) ].time_ms = 0;
+}
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 585db1a..5ca2c6f 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -3919,6 +3919,8 @@ PostgresMain(int argc, char *argv[], const char *username)
pgstat_report_activity(STATE_IDLE, NULL);
}
+ pgstat_report_lwlockstat();
+
ReadyForQuery(whereToSendOutput);
send_ready_for_query = false;
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index 7d4059f..e74042d 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -118,6 +118,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_lwlocks(PG_FUNCTION_ARGS);
+
/* Global bgwriter statistics, from bgwriter.c */
extern PgStat_MsgBgWriter bgwriterStats;
@@ -1399,69 +1401,69 @@ pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS)
Datum
pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
{
- PG_RETURN_INT64(pgstat_fetch_global()->timed_checkpoints);
+ PG_RETURN_INT64(pgstat_fetch_global()->bgwriterstats.timed_checkpoints);
}
Datum
pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS)
{
- PG_RETURN_INT64(pgstat_fetch_global()->requested_checkpoints);
+
PG_RETURN_INT64(pgstat_fetch_global()->bgwriterstats.requested_checkpoints);
}
Datum
pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS)
{
- PG_RETURN_INT64(pgstat_fetch_global()->buf_written_checkpoints);
+
PG_RETURN_INT64(pgstat_fetch_global()->bgwriterstats.buf_written_checkpoints);
}
Datum
pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS)
{
- PG_RETURN_INT64(pgstat_fetch_global()->buf_written_clean);
+ PG_RETURN_INT64(pgstat_fetch_global()->bgwriterstats.buf_written_clean);
}
Datum
pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS)
{
- PG_RETURN_INT64(pgstat_fetch_global()->maxwritten_clean);
+ PG_RETURN_INT64(pgstat_fetch_global()->bgwriterstats.maxwritten_clean);
}
Datum
pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS)
{
/* time is already in msec, just convert to double for presentation */
- PG_RETURN_FLOAT8((double) pgstat_fetch_global()->checkpoint_write_time);
+ PG_RETURN_FLOAT8((double)
pgstat_fetch_global()->bgwriterstats.checkpoint_write_time);
}
Datum
pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS)
{
/* time is already in msec, just convert to double for presentation */
- PG_RETURN_FLOAT8((double) pgstat_fetch_global()->checkpoint_sync_time);
+ PG_RETURN_FLOAT8((double)
pgstat_fetch_global()->bgwriterstats.checkpoint_sync_time);
}
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()->bgwriterstats.reset_timestamp);
}
Datum
pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS)
{
- PG_RETURN_INT64(pgstat_fetch_global()->buf_written_backend);
+
PG_RETURN_INT64(pgstat_fetch_global()->bgwriterstats.buf_written_backend);
}
Datum
pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS)
{
- PG_RETURN_INT64(pgstat_fetch_global()->buf_fsync_backend);
+ PG_RETURN_INT64(pgstat_fetch_global()->bgwriterstats.buf_fsync_backend);
}
Datum
pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
{
- PG_RETURN_INT64(pgstat_fetch_global()->buf_alloc);
+ PG_RETURN_INT64(pgstat_fetch_global()->bgwriterstats.buf_alloc);
}
Datum
@@ -1701,3 +1703,162 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+
+Datum
+pg_stat_get_lwlocks(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ TupleDesc tupdesc;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ tupdesc = CreateTemplateTupleDesc(7, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "lockid",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "local_calls",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "local_waits",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "local_time_ms",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "shared_calls",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "shared_waits",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 7, "shared_time_ms",
+ INT8OID, -1, 0);
+
+ funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+ funcctx->max_calls = NumFixedLWLocks + 1;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ Datum values[7];
+ bool nulls[7];
+ HeapTuple tuple;
+ LWLockId lockid;
+ uint64 local_calls,local_waits,local_time_ms;
+ uint64 shared_calls,shared_waits,shared_time_ms;
+ int i;
+ PgStat_LWLockEntry *lwlock_stat =
pgstat_fetch_global()->lwlockstats.lwlock_stat;
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ lockid = funcctx->call_cntr;
+
+ local_calls = local_waits = local_time_ms = 0;
+ shared_calls = shared_waits = shared_time_ms = 0;
+
+ /*
+ * Partitioned locks need to be summed up by the lock group.
+ */
+ if ( FirstBufMappingLock <= lockid && lockid < FirstLockMgrLock
)
+ {
+ for (i=0 ; i<NUM_BUFFER_PARTITIONS ; i++)
+ {
+ /* local statistics */
+ local_calls =
lwlock_get_stat_calls_local(lockid);
+ local_waits =
lwlock_get_stat_waits_local(lockid);
+ local_time_ms =
lwlock_get_stat_time_ms_local(lockid);
+
+ /* global statistics */
+ shared_calls +=
lwlock_stat[FirstBufMappingLock+i].calls;
+ shared_waits +=
lwlock_stat[FirstBufMappingLock+i].waits;
+ shared_time_ms +=
lwlock_stat[FirstBufMappingLock+i].waited_time;
+ }
+
+ funcctx->call_cntr += NUM_BUFFER_PARTITIONS;
+ }
+ else if ( FirstLockMgrLock <= lockid && lockid <
FirstPredicateLockMgrLock )
+ {
+ for (i=0 ; i<NUM_LOCK_PARTITIONS ; i++)
+ {
+ /* local statistics */
+ local_calls =
lwlock_get_stat_calls_local(lockid);
+ local_waits =
lwlock_get_stat_waits_local(lockid);
+ local_time_ms =
lwlock_get_stat_time_ms_local(lockid);
+
+ /* global statistics */
+ shared_calls +=
lwlock_stat[FirstLockMgrLock+i].calls;
+ shared_waits +=
lwlock_stat[FirstLockMgrLock+i].waits;
+ shared_time_ms +=
lwlock_stat[FirstLockMgrLock+i].waited_time;
+ }
+
+ funcctx->call_cntr += NUM_LOCK_PARTITIONS;
+ }
+ else if ( FirstPredicateLockMgrLock <= lockid && lockid <
NumFixedLWLocks )
+ {
+ for (i=0 ; i<NUM_PREDICATELOCK_PARTITIONS ; i++)
+ {
+ /* local statistics */
+ local_calls =
lwlock_get_stat_calls_local(lockid);
+ local_waits =
lwlock_get_stat_waits_local(lockid);
+ local_time_ms =
lwlock_get_stat_time_ms_local(lockid);
+
+ /* global statistics */
+ shared_calls +=
lwlock_stat[FirstPredicateLockMgrLock+i].calls;
+ shared_waits +=
lwlock_stat[FirstPredicateLockMgrLock+i].waits;
+ shared_time_ms +=
lwlock_stat[FirstPredicateLockMgrLock+i].waited_time;
+ }
+
+ funcctx->call_cntr += NUM_PREDICATELOCK_PARTITIONS;
+ }
+ else
+ {
+ /* local statistics */
+ local_calls = lwlock_get_stat_calls_local(lockid);
+ local_waits = lwlock_get_stat_waits_local(lockid);
+ local_time_ms = lwlock_get_stat_time_ms_local(lockid);
+
+ /* global statistics */
+ shared_calls = lwlock_stat[lockid].calls;
+ shared_waits = lwlock_stat[lockid].waits;
+ shared_time_ms = lwlock_stat[lockid].waited_time;
+ }
+
+ values[0] = Int64GetDatum(lockid);
+ values[1] = Int64GetDatum(local_calls);
+ values[2] = Int64GetDatum(local_waits);
+ values[3] = Int64GetDatum(local_time_ms);
+ values[4] = Int64GetDatum(shared_calls);
+ values[5] = Int64GetDatum(shared_waits);
+ values[6] = Int64GetDatum(shared_time_ms);
+
+ tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+
+ SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+ }
+ else
+ {
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+Datum
+pg_stat_reset_lwlocks(PG_FUNCTION_ARGS)
+{
+ LWLockId lockid;
+
+ for (lockid=0 ; lockid<NumLWLocks() ; lockid++)
+ {
+ lwlock_reset_stat_local(lockid);
+ }
+
+ PG_RETURN_VOID();
+}
+
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f935eb1..4582b12 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2612,6 +2612,10 @@ DATA(insert OID = 1936 ( pg_stat_get_backend_idset
PGNSP PGUID 12 1 100 0 0 f
DESCR("statistics: currently active backend IDs");
DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12
1 100 0 0 f f f f f t s 1 0 2249 "23"
"{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23}"
"{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o}"
"{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port}"
_null_ pg_stat_get_activity _null_ _null_ _null_ ));
DESCR("statistics: information about currently active backends");
+DATA(insert OID = 3764 ( pg_stat_get_lwlocks PGNSP PGUID 12 1 100 0 0 f f f
f f t s 0 0 2249 "" "{20,20,20,20,20,20,20}" "{o,o,o,o,o,o,o}"
"{lwlockid,local_calls,local_waits,local_time_ms,shared_calls,shared_waits,shared_time_ms}"
_null_ pg_stat_get_lwlocks _null_ _null_ _null_ ));
+DESCR("statistics: light-weight lock statistics");
+DATA(insert OID = 3765 ( pg_stat_reset_lwlocks PGNSP PGUID 12 1 0 0 0 f f f
f f f v 0 0 2278 "" _null_ _null_ _null_ _null_ pg_stat_reset_lwlocks _null_
_null_ _null_ ));
+DESCR("statistics: reset light-weight lock statistics");
DATA(insert OID = 3099 ( pg_stat_get_wal_senders PGNSP PGUID 12 1 10 0 0
f f f f f t s 0 0 2249 "" "{23,25,25,25,25,25,23,25}" "{o,o,o,o,o,o,o,o}"
"{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}"
_null_ pg_stat_get_wal_senders _null_ _null_ _null_ ));
DESCR("statistics: information about currently active replication");
DATA(insert OID = 2026 ( pg_backend_pid PGNSP
PGUID 12 1 0 0 0 f f f f t f s 0 0 23 "" _null_ _null_ _null_ _null_
pg_backend_pid _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 613c1c2..0e52534 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -15,6 +15,7 @@
#include "fmgr.h"
#include "libpq/pqcomm.h"
#include "portability/instr_time.h"
+#include "storage/lwlock.h"
#include "utils/hsearch.h"
#include "utils/relcache.h"
@@ -49,6 +50,7 @@ typedef enum StatMsgType
PGSTAT_MTYPE_FUNCPURGE,
PGSTAT_MTYPE_RECOVERYCONFLICT,
PGSTAT_MTYPE_TEMPFILE,
+ PGSTAT_MTYPE_LWLOCKSTAT,
PGSTAT_MTYPE_DEADLOCK
} StatMsgType;
@@ -102,7 +104,8 @@ typedef struct PgStat_TableCounts
/* Possible targets for resetting cluster-wide shared values */
typedef enum PgStat_Shared_Reset_Target
{
- RESET_BGWRITER
+ RESET_BGWRITER,
+ RESET_LWLOCKSTAT
} PgStat_Shared_Reset_Target;
/* Possible object types for resetting single counters */
@@ -605,13 +608,31 @@ typedef struct PgStat_StatFuncEntry
PgStat_Counter f_self_time;
} PgStat_StatFuncEntry;
+#define MAX_LWLOCKSTAT_ENTRIES 20
+
+typedef struct PgStat_LWLockEntry
+{
+ LWLockId lockid;
+ PgStat_Counter calls;
+ PgStat_Counter waits;
+ PgStat_Counter waited_time; /* time in milliseconds */
+} PgStat_LWLockEntry;
+
+typedef struct PgStat_MsgLWLockstat
+{
+ PgStat_MsgHdr m_hdr;
+ int m_nentries;
+
+ /* Need to keep a msg smaller than PGSTAT_MSG_PAYLOAD */
+ PgStat_LWLockEntry m_entry[MAX_LWLOCKSTAT_ENTRIES];
+} PgStat_MsgLWLockstat;
+
/*
- * Global statistics kept in the stats collector
+ * Global statistics for BgWriter
*/
-typedef struct PgStat_GlobalStats
+typedef struct PgStat_BgWriterGlobalStats
{
- TimestampTz stats_timestamp; /* time of stats file update */
PgStat_Counter timed_checkpoints;
PgStat_Counter requested_checkpoints;
PgStat_Counter checkpoint_write_time; /* times in
milliseconds */
@@ -622,6 +643,28 @@ typedef struct PgStat_GlobalStats
PgStat_Counter buf_written_backend;
PgStat_Counter buf_fsync_backend;
PgStat_Counter buf_alloc;
+ TimestampTz reset_timestamp;
+} PgStat_BgWriterGlobalStats;
+
+/*
+ * Global statistics for LWLocks
+ */
+typedef struct PgStat_LWLockGlobalStats
+{
+ PgStat_LWLockEntry lwlock_stat[NumFixedLWLocks+1];
+ TimestampTz reset_timestamp;
+} PgStat_LWLockGlobalStats;
+
+/*
+ * Global statistics kept in the stats collector
+ */
+typedef struct PgStat_GlobalStats
+{
+ TimestampTz stats_timestamp; /* time of stats file update */
+
+ PgStat_BgWriterGlobalStats bgwriterstats;
+ PgStat_LWLockGlobalStats lwlockstats;
+
TimestampTz stat_reset_timestamp;
} PgStat_GlobalStats;
diff --git a/src/include/storage/lwlock.h b/src/include/storage/lwlock.h
index 82d8ec4..a101db5 100644
--- a/src/include/storage/lwlock.h
+++ b/src/include/storage/lwlock.h
@@ -119,4 +119,14 @@ extern void CreateLWLocks(void);
extern void RequestAddinLWLocks(int n);
+extern uint64 lwlock_get_stat_calls_local(LWLockId);
+extern uint64 lwlock_get_stat_waits_local(LWLockId);
+extern uint64 lwlock_get_stat_time_ms_local(LWLockId);
+extern void lwlock_reset_stat_local(LWLockId);
+
+extern uint64 lwlock_get_stat_calls_global(LWLockId);
+extern uint64 lwlock_get_stat_waits_global(LWLockId);
+extern uint64 lwlock_get_stat_time_ms_global(LWLockId);
+extern void lwlock_reset_stat_global(LWLockId);
+
#endif /* LWLOCK_H */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
