From 8a9aa7ba753f8ae77651861629266b86b276bf11 Mon Sep 17 00:00:00 2001 From: Dilip Kumar Date: Sun, 12 Dec 2021 17:10:55 +0530 Subject: [PATCH v2] Add functions to show subtransaction count and overflow status If there are some backends having a lot of nested subtransaction or the subtransaction cache is overflowed there is a no way to detect that. So this patch is making that easy by providing functions to get that information. --- doc/src/sgml/monitoring.sgml | 27 +++++++++++++++++++++++++++ src/backend/storage/ipc/sinvaladt.c | 13 +++++++++---- src/backend/utils/activity/backend_status.c | 4 +++- src/backend/utils/adt/pgstatfuncs.c | 23 +++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 8 ++++++++ src/include/storage/sinvaladt.h | 4 +++- src/include/utils/backend_status.h | 11 +++++++++++ 7 files changed, 84 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 62f2a33..77c6cfc 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -5482,6 +5482,33 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid, + pg_stat_get_backend_subxact_count + + pg_stat_get_backend_subxact_count ( integer ) + integer + + + Returns the number of cached subtransactions of this backend. + + + + + + + pg_stat_get_backend_subxact_overflow + + pg_stat_get_backend_subxact_overflow ( integer ) + boolean + + + Returns true if the number of active subtransactions crossed per + backend subtransaction cache limit, false otherwise. + + + + + + pg_stat_get_backend_userid pg_stat_get_backend_userid ( integer ) diff --git a/src/backend/storage/ipc/sinvaladt.c b/src/backend/storage/ipc/sinvaladt.c index 946bd8e..876d7fe 100644 --- a/src/backend/storage/ipc/sinvaladt.c +++ b/src/backend/storage/ipc/sinvaladt.c @@ -395,17 +395,20 @@ BackendIdGetProc(int backendID) /* * BackendIdGetTransactionIds - * Get the xid and xmin of the backend. The result may be out of date - * arbitrarily quickly, so the caller must be careful about how this - * information is used. + * Get the xid and xmin, nsubxid and overflow status of the backend. The + * result may be out of date arbitrarily quickly, so the caller must be + * careful about how this information is used. */ void -BackendIdGetTransactionIds(int backendID, TransactionId *xid, TransactionId *xmin) +BackendIdGetTransactionIds(int backendID, TransactionId *xid, + TransactionId *xmin, int *nsubxid, bool *overflowed) { SISeg *segP = shmInvalBuffer; *xid = InvalidTransactionId; *xmin = InvalidTransactionId; + *nsubxid = 0; + *overflowed = false; /* Need to lock out additions/removals of backends */ LWLockAcquire(SInvalWriteLock, LW_SHARED); @@ -419,6 +422,8 @@ BackendIdGetTransactionIds(int backendID, TransactionId *xid, TransactionId *xmi { *xid = proc->xid; *xmin = proc->xmin; + *nsubxid = proc->subxidStatus.count; + *overflowed = proc->subxidStatus.overflowed; } } diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c index 7229598..9c904be 100644 --- a/src/backend/utils/activity/backend_status.c +++ b/src/backend/utils/activity/backend_status.c @@ -848,7 +848,9 @@ pgstat_read_current_status(void) { BackendIdGetTransactionIds(i, &localentry->backend_xid, - &localentry->backend_xmin); + &localentry->backend_xmin, + &localentry->subxact_count, + &localentry->subxact_overflowed); localentry++; localappname += NAMEDATALEN; diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index f529c15..0c8bf4a 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1005,6 +1005,29 @@ pg_stat_get_backend_userid(PG_FUNCTION_ARGS) PG_RETURN_OID(beentry->st_userid); } +Datum +pg_stat_get_backend_subxact_count(PG_FUNCTION_ARGS) +{ + int32 beid = PG_GETARG_INT32(0); + LocalPgBackendStatus *local_beentry; + + if ((local_beentry = pgstat_fetch_stat_local_beentry(beid)) == NULL) + PG_RETURN_NULL(); + + PG_RETURN_INT32(local_beentry->subxact_count); +} + +Datum +pg_stat_get_backend_subxact_overflow(PG_FUNCTION_ARGS) +{ + int32 beid = PG_GETARG_INT32(0); + LocalPgBackendStatus *local_beentry; + + if ((local_beentry = pgstat_fetch_stat_local_beentry(beid)) == NULL) + PG_RETURN_NULL(); + + PG_RETURN_INT32(local_beentry->subxact_overflowed); +} Datum pg_stat_get_backend_activity(PG_FUNCTION_ARGS) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 79d787c..4da1bca 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5416,6 +5416,14 @@ proname => 'pg_stat_get_backend_dbid', provolatile => 's', proparallel => 'r', prorettype => 'oid', proargtypes => 'int4', prosrc => 'pg_stat_get_backend_dbid' }, +{ oid => '6107', descr => 'statistics: cached subtransaction count of backend', + proname => 'pg_stat_get_backend_subxact_count', provolatile => 's', proparallel => 'r', + prorettype => 'int4', proargtypes => 'int4', + prosrc => 'pg_stat_get_backend_subxact_count' }, +{ oid => '6108', descr => 'statistics: subtransaction cache of backend overflowed', + proname => 'pg_stat_get_backend_subxact_overflow', provolatile => 's', proparallel => 'r', + prorettype => 'bool', proargtypes => 'int4', + prosrc => 'pg_stat_get_backend_subxact_overflow' }, { oid => '1939', descr => 'statistics: user ID of backend', proname => 'pg_stat_get_backend_userid', provolatile => 's', proparallel => 'r', prorettype => 'oid', proargtypes => 'int4', diff --git a/src/include/storage/sinvaladt.h b/src/include/storage/sinvaladt.h index 14148bf..a342da8 100644 --- a/src/include/storage/sinvaladt.h +++ b/src/include/storage/sinvaladt.h @@ -32,7 +32,9 @@ extern Size SInvalShmemSize(void); extern void CreateSharedInvalidationState(void); extern void SharedInvalBackendInit(bool sendOnly); extern PGPROC *BackendIdGetProc(int backendID); -extern void BackendIdGetTransactionIds(int backendID, TransactionId *xid, TransactionId *xmin); +extern void BackendIdGetTransactionIds(int backendID, TransactionId *xid, + TransactionId *xmin, int *nsubxid, + bool *overflowed); extern void SIInsertDataEntries(const SharedInvalidationMessage *data, int n); extern int SIGetDataEntries(SharedInvalidationMessage *data, int datasize); diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h index 8042b81..36ecf33 100644 --- a/src/include/utils/backend_status.h +++ b/src/include/utils/backend_status.h @@ -258,6 +258,17 @@ typedef struct LocalPgBackendStatus * not. */ TransactionId backend_xmin; + + /* + * Number of cached subtransactions in the current session. + */ + int subxact_count; + + /* + * The number of subtransactions in the current session exceeded the cached + * subtransaction limit. + */ + bool subxact_overflowed; } LocalPgBackendStatus; -- 1.8.3.1