On Tue, 2020-08-11 at 13:53 +0200, I wrote:
> On Thu, 2020-07-23 at 18:16 +0500, Ahsan Hadi wrote:
>
> > On Wed, Jul 8, 2020 at 4:17 PM Laurenz Albe <laurenz.a...@cybertec.at>
> > wrote:
> > > Here is a patch that adds the following to pg_stat_database:
> > > - number of connections
> >
> > Is it expected behaviour to not count idle connections? The connection is
> > included after it is aborted but not while it was idle.
>
> Currently, the patch counts connections when they close.
>
> I could change the behavior that they are counted immediately.
I have changed the code so that connections are counted immediately.
Attached is a new version.
Yours,
Laurenz Albe
From 6d9bfbd682a9f4723f030fdc461f731175f55f44 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.a...@cybertec.at>
Date: Fri, 4 Sep 2020 17:30:24 +0200
Subject: [PATCH] Add session statistics to pg_stat_database
If "track_counts" is active, track the following per database:
- number of connections
- number of sessions that were not disconnected regularly
- total time spent in database sessions
- total time spent executing queries
- total idle in transaction time
This is useful to check if connection pooling is working.
It also helps to estimate the size of the connection pool
required to keep the database busy, which depends on the
percentage of the transaction time that is spent idling.
---
doc/src/sgml/monitoring.sgml | 46 +++++++++
src/backend/catalog/system_views.sql | 5 +
src/backend/postmaster/pgstat.c | 146 ++++++++++++++++++++++++++-
src/backend/tcop/postgres.c | 5 +
src/backend/utils/adt/pgstatfuncs.c | 78 ++++++++++++++
src/include/catalog/pg_proc.dat | 20 ++++
src/include/pgstat.h | 29 +++++-
src/test/regress/expected/rules.out | 5 +
8 files changed, 332 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 673a0e73e4..aa5e22d213 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3514,6 +3514,52 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>session_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent in database sessions in this database, in milliseconds.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>active_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent executing SQL statements in this database, in milliseconds.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>idle_in_transaction_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent idling while in a transaction in this database, in milliseconds.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>connections</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of connections established to this database.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>aborted_sessions</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of database sessions to this database that did not end
+ with a regular client disconnection.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index ed4f3f142d..d8b28c7600 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -912,6 +912,11 @@ CREATE VIEW pg_stat_database AS
pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure,
pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time,
pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time,
+ pg_stat_get_db_session_time(D.oid) AS session_time,
+ pg_stat_get_db_active_time(D.oid) AS active_time,
+ pg_stat_get_db_idle_in_transaction_time(D.oid) AS idle_in_transaction_time,
+ pg_stat_get_db_connections(D.oid) AS connections,
+ pg_stat_get_db_aborted_sessions(D.oid) AS aborted_sessions,
pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
FROM (
SELECT 0 AS oid, NULL::name AS datname
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 5f4b168fd1..12a7543554 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -247,6 +247,12 @@ static int pgStatXactCommit = 0;
static int pgStatXactRollback = 0;
PgStat_Counter pgStatBlockReadTime = 0;
PgStat_Counter pgStatBlockWriteTime = 0;
+static TimestampTz pgStatActiveStart = DT_NOBEGIN;
+static PgStat_Counter pgStatActiveTime = 0;
+static TimestampTz pgStatTransactionIdleStart = DT_NOBEGIN;
+static PgStat_Counter pgStatTransactionIdleTime = 0;
+static bool pgStatSessionReported = false;
+bool pgStatSessionDisconnected = false;
/* Record that's written to 2PC state file when pgstat state is persisted */
typedef struct TwoPhasePgStatRecord
@@ -326,6 +332,7 @@ static void pgstat_send_tabstat(PgStat_MsgTabstat *tsmsg);
static void pgstat_send_funcstats(void);
static void pgstat_send_slru(void);
static HTAB *pgstat_collect_oids(Oid catalogid, AttrNumber anum_oid);
+static void pgstat_send_connstats(bool force);
static PgStat_TableStatus *get_tabstat_entry(Oid rel_id, bool isshared);
@@ -359,6 +366,7 @@ static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len);
static void pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len);
static void pgstat_recv_checksum_failure(PgStat_MsgChecksumFailure *msg, int len);
+static void pgstat_recv_connection(PgStat_MsgConn *msg, int len);
static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
/* ------------------------------------------------------------
@@ -851,7 +859,7 @@ pgstat_report_stat(bool force)
/* Don't expend a clock check if nothing to do */
if ((pgStatTabList == NULL || pgStatTabList->tsa_used == 0) &&
pgStatXactCommit == 0 && pgStatXactRollback == 0 &&
- !have_function_stats)
+ !have_function_stats && !force)
return;
/*
@@ -938,6 +946,10 @@ pgstat_report_stat(bool force)
/* Now, send function statistics */
pgstat_send_funcstats();
+ /* for backends, send connection statistics */
+ if (MyBackendType == B_BACKEND)
+ pgstat_send_connstats(force);
+
/* Finally send SLRU statistics */
pgstat_send_slru();
}
@@ -1327,6 +1339,61 @@ pgstat_drop_relation(Oid relid)
#endif /* NOT_USED */
+/* ----------
+ * pgstat_send_connstats() -
+ *
+ * Tell the collector about session statistics.
+ * The parameter "force" will be true when the session ends,
+ * so we report total session time only if it is true.
+ * ----------
+ */
+static void
+pgstat_send_connstats(bool force)
+{
+ PgStat_MsgConn msg;
+
+ if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+ return;
+
+ pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_CONNECTION);
+ msg.m_databaseid = MyDatabaseId;
+
+ if (force)
+ {
+ long secs;
+ int usecs;
+
+ TimestampDifference(MyStartTimestamp,
+ GetCurrentTimestamp(),
+ &secs, &usecs);
+
+ msg.m_session_time = secs * 1000000 + usecs;
+ msg.m_aborted = pgStatSessionDisconnected ? 0 : 1;
+ }
+ else
+ {
+ msg.m_session_time = 0;
+ msg.m_aborted = 0;
+ }
+
+ msg.m_active_time = pgStatActiveTime;
+ pgStatActiveTime = 0;
+ msg.m_idle_in_xact_time = pgStatTransactionIdleTime;
+ pgStatTransactionIdleTime = 0;
+
+ /* report a new session only the first time */
+ if (pgStatSessionReported)
+ msg.m_count = 0;
+ else
+ {
+ msg.m_count = 1;
+ pgStatSessionReported = true;
+ }
+
+ pgstat_send(&msg, sizeof(PgStat_MsgConn));
+}
+
+
/* ----------
* pgstat_reset_counters() -
*
@@ -3197,6 +3264,54 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
}
PGSTAT_END_WRITE_ACTIVITY(beentry);
+
+ /*
+ * If the state has changed to "active" or "idle in transaction",
+ * record the start time.
+ * If the state has changed away from these, calculate the duration.
+ */
+ if (state == STATE_RUNNING)
+ {
+ if (pgStatActiveStart == DT_NOBEGIN)
+ pgStatActiveStart = current_timestamp;
+ }
+ else
+ {
+ if (pgStatActiveStart != DT_NOBEGIN)
+ {
+ long secs;
+ int usecs;
+
+ TimestampDifference(pgStatActiveStart,
+ GetCurrentTimestamp(),
+ &secs, &usecs);
+
+ pgStatActiveTime += secs * 1000000 + usecs;
+ pgStatActiveStart = DT_NOBEGIN;
+ }
+ }
+
+ if (state == STATE_IDLEINTRANSACTION ||
+ state == STATE_IDLEINTRANSACTION_ABORTED)
+ {
+ if (pgStatTransactionIdleStart == DT_NOBEGIN)
+ pgStatTransactionIdleStart = current_timestamp;
+ }
+ else
+ {
+ if (pgStatTransactionIdleStart != DT_NOBEGIN)
+ {
+ long secs;
+ int usecs;
+
+ TimestampDifference(pgStatTransactionIdleStart,
+ GetCurrentTimestamp(),
+ &secs, &usecs);
+
+ pgStatTransactionIdleTime += secs * 1000000 + usecs;
+ pgStatTransactionIdleStart = DT_NOBEGIN;
+ }
+ }
}
/*-----------
@@ -4688,6 +4803,10 @@ PgstatCollectorMain(int argc, char *argv[])
len);
break;
+ case PGSTAT_MTYPE_CONNECTION:
+ pgstat_recv_connection(&msg.msg_conn, len);
+ break;
+
default:
break;
}
@@ -4762,6 +4881,11 @@ reset_dbentry_counters(PgStat_StatDBEntry *dbentry)
dbentry->last_checksum_failure = 0;
dbentry->n_block_read_time = 0;
dbentry->n_block_write_time = 0;
+ dbentry->n_connections = 0;
+ dbentry->n_session_time = 0;
+ dbentry->n_active_time = 0;
+ dbentry->n_idle_in_xact_time = 0;
+ dbentry->n_aborted = 0;
dbentry->stat_reset_timestamp = GetCurrentTimestamp();
dbentry->stats_timestamp = 0;
@@ -6513,6 +6637,26 @@ pgstat_recv_checksum_failure(PgStat_MsgChecksumFailure *msg, int len)
dbentry->last_checksum_failure = msg->m_failure_time;
}
+/* ----------
+ * pgstat_recv_connection() -
+ *
+ * Process connection information.
+ * ----------
+ */
+static void
+pgstat_recv_connection(PgStat_MsgConn *msg, int len)
+{
+ PgStat_StatDBEntry *dbentry;
+
+ dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+ dbentry->n_connections += msg->m_count;
+ dbentry->n_session_time += msg->m_session_time;
+ dbentry->n_active_time += msg->m_active_time;
+ dbentry->n_idle_in_xact_time += msg->m_idle_in_xact_time;
+ dbentry->n_aborted += msg->m_aborted;
+}
+
/* ----------
* pgstat_recv_tempfile() -
*
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index c9424f167c..79d3d236d7 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -4504,6 +4504,11 @@ PostgresMain(int argc, char *argv[],
* perform normal shutdown.
*/
case 'X':
+ /* report as normal client disconnection */
+ pgStatSessionDisconnected = true;
+
+ /* FALLTHROUGH */
+
case EOF:
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 95738a4e34..02c659d196 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1629,6 +1629,84 @@ pg_stat_get_db_blk_write_time(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8(result);
}
+Datum
+pg_stat_get_db_session_time(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ double result;
+ PgStat_StatDBEntry *dbentry;
+
+ /* convert counter from microsec to millisec for display */
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = ((double) dbentry->n_session_time) / 1000.0;
+
+ PG_RETURN_FLOAT8(result);
+}
+
+Datum
+pg_stat_get_db_active_time(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ double result;
+ PgStat_StatDBEntry *dbentry;
+
+ /* convert counter from microsec to millisec for display */
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = ((double) dbentry->n_active_time) / 1000.0;
+
+ PG_RETURN_FLOAT8(result);
+}
+
+Datum
+pg_stat_get_db_idle_in_transaction_time(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ double result;
+ PgStat_StatDBEntry *dbentry;
+
+ /* convert counter from microsec to millisec for display */
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = ((double) dbentry->n_idle_in_xact_time) / 1000.0;
+
+ PG_RETURN_FLOAT8(result);
+}
+
+Datum
+pg_stat_get_db_connections(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (dbentry->n_connections);
+
+ PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_aborted_sessions(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatDBEntry *dbentry;
+
+ if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (dbentry->n_aborted);
+
+ PG_RETURN_INT64(result);
+}
+
Datum
pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 687509ba92..4724755dd0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5425,6 +5425,26 @@
proname => 'pg_stat_get_db_blk_write_time', provolatile => 's',
proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
prosrc => 'pg_stat_get_db_blk_write_time' },
+{ oid => '9575', descr => 'statistics: session time, in seconds',
+ proname => 'pg_stat_get_db_session_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_session_time' },
+{ oid => '9576', descr => 'statistics: session active time, in seconds',
+ proname => 'pg_stat_get_db_active_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_active_time' },
+{ oid => '9577', descr => 'statistics: session idle in transaction time, in seconds',
+ proname => 'pg_stat_get_db_idle_in_transaction_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_idle_in_transaction_time' },
+{ oid => '9578', descr => 'statistics: number of connections',
+ proname => 'pg_stat_get_db_connections', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_connections' },
+{ oid => '9579', descr => 'statistics: number of aborted sessions',
+ proname => 'pg_stat_get_db_aborted_sessions', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_db_aborted_sessions' },
{ oid => '3195', descr => 'statistics: information about WAL archiver',
proname => 'pg_stat_get_archiver', proisstrict => 'f', provolatile => 's',
proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 0dfbac46b4..91b32ced48 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -67,7 +67,8 @@ typedef enum StatMsgType
PGSTAT_MTYPE_RECOVERYCONFLICT,
PGSTAT_MTYPE_TEMPFILE,
PGSTAT_MTYPE_DEADLOCK,
- PGSTAT_MTYPE_CHECKSUMFAILURE
+ PGSTAT_MTYPE_CHECKSUMFAILURE,
+ PGSTAT_MTYPE_CONNECTION
} StatMsgType;
/* ----------
@@ -574,6 +575,21 @@ typedef struct PgStat_MsgChecksumFailure
TimestampTz m_failure_time;
} PgStat_MsgChecksumFailure;
+/* ----------
+ * PgStat_MsgConn Sent by pgstat_connection to update connection statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgConn
+{
+ PgStat_MsgHdr m_hdr;
+ Oid m_databaseid;
+ PgStat_Counter m_count;
+ PgStat_Counter m_session_time;
+ PgStat_Counter m_active_time;
+ PgStat_Counter m_idle_in_xact_time;
+ PgStat_Counter m_aborted;
+} PgStat_MsgConn;
+
/* ----------
* PgStat_Msg Union over all possible messages.
@@ -603,6 +619,7 @@ typedef union PgStat_Msg
PgStat_MsgDeadlock msg_deadlock;
PgStat_MsgTempFile msg_tempfile;
PgStat_MsgChecksumFailure msg_checksumfailure;
+ PgStat_MsgConn msg_conn;
} PgStat_Msg;
@@ -645,6 +662,11 @@ typedef struct PgStat_StatDBEntry
TimestampTz last_checksum_failure;
PgStat_Counter n_block_read_time; /* times in microseconds */
PgStat_Counter n_block_write_time;
+ PgStat_Counter n_connections;
+ PgStat_Counter n_session_time;
+ PgStat_Counter n_active_time;
+ PgStat_Counter n_idle_in_xact_time;
+ PgStat_Counter n_aborted;
TimestampTz stat_reset_timestamp;
TimestampTz stats_timestamp; /* time of db stats file update */
@@ -1271,6 +1293,11 @@ extern PgStat_MsgBgWriter BgWriterStats;
extern PgStat_Counter pgStatBlockReadTime;
extern PgStat_Counter pgStatBlockWriteTime;
+/*
+ * Updated in PostgresMain upon disconnect.
+ */
+extern bool pgStatSessionDisconnected;
+
/* ----------
* Functions called from postmaster
* ----------
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2a18dc423e..7b07442d30 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1848,6 +1848,11 @@ pg_stat_database| SELECT d.oid AS datid,
pg_stat_get_db_checksum_last_failure(d.oid) AS checksum_last_failure,
pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time,
pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time,
+ pg_stat_get_db_session_time(d.oid) AS session_time,
+ pg_stat_get_db_active_time(d.oid) AS active_time,
+ pg_stat_get_db_idle_in_transaction_time(d.oid) AS idle_in_transaction_time,
+ pg_stat_get_db_connections(d.oid) AS connections,
+ pg_stat_get_db_aborted_sessions(d.oid) AS aborted_sessions,
pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset
FROM ( SELECT 0 AS oid,
NULL::name AS datname
--
2.26.2
