Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit960869d

Browse files
committed
Add pg_stat_database counters for sessions and session time
This add counters for number of sessions, the different kind of sessiontermination types, and timers for how much time is spent in active vsidle in a database to pg_stat_database.Internally this also renames the parameter "force" to disconnect. Thiswas the only use-case for the parameter before, so repurposing it tothis mroe narrow usecase makes things cleaner than inventing somethingnew.Author: Laurenz AlbeReviewed-By: Magnus Hagander, Soumyadeep Chakraborty, Masahiro IkedaDiscussion:https://postgr.es/m/b07e1f9953701b90c66ed368656f2aef40cac4fb.camel@cybertec.at
1 parent891a1d0 commit960869d

File tree

10 files changed

+405
-6
lines changed

10 files changed

+405
-6
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3737,6 +3737,83 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
37373737
</para></entry>
37383738
</row>
37393739

3740+
<row>
3741+
<entry role="catalog_table_entry"><para role="column_definition">
3742+
<structfield>session_time</structfield> <type>double precision</type>
3743+
</para>
3744+
<para>
3745+
Time spent by database sessions in this database, in milliseconds
3746+
(note that statistics are only updated when the state of a session
3747+
changes, so if sessions have been idle for a long time, this idle time
3748+
won't be included)
3749+
</para></entry>
3750+
</row>
3751+
3752+
<row>
3753+
<entry role="catalog_table_entry"><para role="column_definition">
3754+
<structfield>active_time</structfield> <type>double precision</type>
3755+
</para>
3756+
<para>
3757+
Time spent executing SQL statements in this database, in milliseconds
3758+
(this corresponds to the states <literal>active</literal> and
3759+
<literal>fastpath function call</literal> in
3760+
<link linkend="monitoring-pg-stat-activity-view">
3761+
<structname>pg_stat_activity</structname></link>)
3762+
</para></entry>
3763+
</row>
3764+
3765+
<row>
3766+
<entry role="catalog_table_entry"><para role="column_definition">
3767+
<structfield>idle_in_transaction_time</structfield> <type>double precision</type>
3768+
</para>
3769+
<para>
3770+
Time spent idling while in a transaction in this database, in milliseconds
3771+
(this corresponds to the states <literal>idle in transaction</literal> and
3772+
<literal>idle in transaction (aborted)</literal> in
3773+
<link linkend="monitoring-pg-stat-activity-view">
3774+
<structname>pg_stat_activity</structname></link>)
3775+
</para></entry>
3776+
</row>
3777+
3778+
<row>
3779+
<entry role="catalog_table_entry"><para role="column_definition">
3780+
<structfield>sessions</structfield> <type>bigint</type>
3781+
</para>
3782+
<para>
3783+
Total number of sessions established to this database
3784+
</para></entry>
3785+
</row>
3786+
3787+
<row>
3788+
<entry role="catalog_table_entry"><para role="column_definition">
3789+
<structfield>sessions_abandoned</structfield> <type>bigint</type>
3790+
</para>
3791+
<para>
3792+
Number of database sessions to this database that were terminated
3793+
because connection to the client was lost
3794+
</para></entry>
3795+
</row>
3796+
3797+
<row>
3798+
<entry role="catalog_table_entry"><para role="column_definition">
3799+
<structfield>sessions_fatal</structfield> <type>bigint</type>
3800+
</para>
3801+
<para>
3802+
Number of database sessions to this database that were terminated
3803+
by fatal errors
3804+
</para></entry>
3805+
</row>
3806+
3807+
<row>
3808+
<entry role="catalog_table_entry"><para role="column_definition">
3809+
<structfield>sessions_killed</structfield> <type>bigint</type>
3810+
</para>
3811+
<para>
3812+
Number of database sessions to this database that were terminated
3813+
by operator intervention
3814+
</para></entry>
3815+
</row>
3816+
37403817
<row>
37413818
<entry role="catalog_table_entry"><para role="column_definition">
37423819
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>

‎src/backend/catalog/system_views.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -924,6 +924,13 @@ CREATE VIEW pg_stat_database AS
924924
pg_stat_get_db_checksum_last_failure(D.oid)AS checksum_last_failure,
925925
pg_stat_get_db_blk_read_time(D.oid)AS blk_read_time,
926926
pg_stat_get_db_blk_write_time(D.oid)AS blk_write_time,
927+
pg_stat_get_db_session_time(D.oid)AS session_time,
928+
pg_stat_get_db_active_time(D.oid)AS active_time,
929+
pg_stat_get_db_idle_in_transaction_time(D.oid)AS idle_in_transaction_time,
930+
pg_stat_get_db_sessions(D.oid)AS sessions,
931+
pg_stat_get_db_sessions_abandoned(D.oid)AS sessions_abandoned,
932+
pg_stat_get_db_sessions_fatal(D.oid)AS sessions_fatal,
933+
pg_stat_get_db_sessions_killed(D.oid)AS sessions_killed,
927934
pg_stat_get_db_stat_reset_time(D.oid)AS stats_reset
928935
FROM (
929936
SELECT0ASoid,NULL::nameAS datname

‎src/backend/postmaster/pgstat.c

Lines changed: 130 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -258,6 +258,9 @@ static intpgStatXactCommit = 0;
258258
staticintpgStatXactRollback=0;
259259
PgStat_CounterpgStatBlockReadTime=0;
260260
PgStat_CounterpgStatBlockWriteTime=0;
261+
staticPgStat_CounterpgStatActiveTime=0;
262+
staticPgStat_CounterpgStatTransactionIdleTime=0;
263+
SessionEndTypepgStatSessionEndCause=DISCONNECT_NORMAL;
261264

262265
/* Record that's written to 2PC state file when pgstat state is persisted */
263266
typedefstructTwoPhasePgStatRecord
@@ -343,6 +346,7 @@ static void pgstat_send_tabstat(PgStat_MsgTabstat *tsmsg);
343346
staticvoidpgstat_send_funcstats(void);
344347
staticvoidpgstat_send_slru(void);
345348
staticHTAB*pgstat_collect_oids(Oidcatalogid,AttrNumberanum_oid);
349+
staticvoidpgstat_send_connstats(booldisconnect,TimestampTzlast_report);
346350

347351
staticPgStat_TableStatus*get_tabstat_entry(Oidrel_id,boolisshared);
348352

@@ -378,6 +382,7 @@ static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
378382
staticvoidpgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict*msg,intlen);
379383
staticvoidpgstat_recv_deadlock(PgStat_MsgDeadlock*msg,intlen);
380384
staticvoidpgstat_recv_checksum_failure(PgStat_MsgChecksumFailure*msg,intlen);
385+
staticvoidpgstat_recv_connstat(PgStat_MsgConn*msg,intlen);
381386
staticvoidpgstat_recv_replslot(PgStat_MsgReplSlot*msg,intlen);
382387
staticvoidpgstat_recv_tempfile(PgStat_MsgTempFile*msg,intlen);
383388

@@ -855,10 +860,14 @@ allow_immediate_pgstat_restart(void)
855860
*per-table and function usage statistics to the collector. Note that this
856861
*is called only when not within a transaction, so it is fair to use
857862
*transaction stop time as an approximation of current time.
863+
*
864+
*"disconnect" is "true" only for the last call before the backend
865+
*exits. This makes sure that no data is lost and that interrupted
866+
*sessions are reported correctly.
858867
* ----------
859868
*/
860869
void
861-
pgstat_report_stat(boolforce)
870+
pgstat_report_stat(booldisconnect)
862871
{
863872
/* we assume this inits to all zeroes: */
864873
staticconstPgStat_TableCountsall_zeroes;
@@ -873,17 +882,22 @@ pgstat_report_stat(bool force)
873882
/* Don't expend a clock check if nothing to do */
874883
if ((pgStatTabList==NULL||pgStatTabList->tsa_used==0)&&
875884
pgStatXactCommit==0&&pgStatXactRollback==0&&
876-
!have_function_stats)
885+
!have_function_stats&& !disconnect)
877886
return;
878887

879888
/*
880889
* Don't send a message unless it's been at least PGSTAT_STAT_INTERVAL
881-
* msec since we last sent one, or thecaller wantstoforce stats out.
890+
* msec since we last sent one, or thebackend is abouttoexit.
882891
*/
883892
now=GetCurrentTransactionStopTimestamp();
884-
if (!force&&
893+
if (!disconnect&&
885894
!TimestampDifferenceExceeds(last_report,now,PGSTAT_STAT_INTERVAL))
886895
return;
896+
897+
/* for backends, send connection statistics */
898+
if (MyBackendType==B_BACKEND)
899+
pgstat_send_connstats(disconnect,last_report);
900+
887901
last_report=now;
888902

889903
/*
@@ -1351,6 +1365,48 @@ pgstat_drop_relation(Oid relid)
13511365
#endif/* NOT_USED */
13521366

13531367

1368+
/* ----------
1369+
* pgstat_send_connstats() -
1370+
*
1371+
*Tell the collector about session statistics.
1372+
*The parameter "disconnect" will be true when the backend exits.
1373+
*"last_report" is the last time we were called (0 if never).
1374+
* ----------
1375+
*/
1376+
staticvoid
1377+
pgstat_send_connstats(booldisconnect,TimestampTzlast_report)
1378+
{
1379+
PgStat_MsgConnmsg;
1380+
longsecs;
1381+
intusecs;
1382+
1383+
if (pgStatSock==PGINVALID_SOCKET|| !pgstat_track_counts)
1384+
return;
1385+
1386+
pgstat_setheader(&msg.m_hdr,PGSTAT_MTYPE_CONNECTION);
1387+
msg.m_databaseid=MyDatabaseId;
1388+
1389+
/* session time since the last report */
1390+
TimestampDifference(((last_report==0) ?MyStartTimestamp :last_report),
1391+
GetCurrentTimestamp(),
1392+
&secs,&usecs);
1393+
msg.m_session_time=secs*1000000+usecs;
1394+
1395+
msg.m_disconnect=disconnect ?pgStatSessionEndCause :DISCONNECT_NOT_YET;
1396+
1397+
msg.m_active_time=pgStatActiveTime;
1398+
pgStatActiveTime=0;
1399+
1400+
msg.m_idle_in_xact_time=pgStatTransactionIdleTime;
1401+
pgStatTransactionIdleTime=0;
1402+
1403+
/* report a new session only the first time */
1404+
msg.m_count= (last_report==0) ?1 :0;
1405+
1406+
pgstat_send(&msg,sizeof(PgStat_MsgConn));
1407+
}
1408+
1409+
13541410
/* ----------
13551411
* pgstat_reset_counters() -
13561412
*
@@ -3348,6 +3404,30 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
33483404
}
33493405
current_timestamp=GetCurrentTimestamp();
33503406

3407+
/*
3408+
* If the state has changed from "active" or "idle in transaction",
3409+
* calculate the duration.
3410+
*/
3411+
if ((beentry->st_state==STATE_RUNNING||
3412+
beentry->st_state==STATE_FASTPATH||
3413+
beentry->st_state==STATE_IDLEINTRANSACTION||
3414+
beentry->st_state==STATE_IDLEINTRANSACTION_ABORTED)&&
3415+
state!=beentry->st_state)
3416+
{
3417+
longsecs;
3418+
intusecs;
3419+
3420+
TimestampDifference(beentry->st_state_start_timestamp,
3421+
current_timestamp,
3422+
&secs,&usecs);
3423+
3424+
if (beentry->st_state==STATE_RUNNING||
3425+
beentry->st_state==STATE_FASTPATH)
3426+
pgStatActiveTime+=secs*1000000+usecs;
3427+
else
3428+
pgStatTransactionIdleTime+=secs*1000000+usecs;
3429+
}
3430+
33513431
/*
33523432
* Now update the status entry
33533433
*/
@@ -4919,6 +4999,10 @@ PgstatCollectorMain(int argc, char *argv[])
49194999
pgstat_recv_replslot(&msg.msg_replslot,len);
49205000
break;
49215001

5002+
casePGSTAT_MTYPE_CONNECTION:
5003+
pgstat_recv_connstat(&msg.msg_conn,len);
5004+
break;
5005+
49225006
default:
49235007
break;
49245008
}
@@ -4993,6 +5077,13 @@ reset_dbentry_counters(PgStat_StatDBEntry *dbentry)
49935077
dbentry->last_checksum_failure=0;
49945078
dbentry->n_block_read_time=0;
49955079
dbentry->n_block_write_time=0;
5080+
dbentry->n_sessions=0;
5081+
dbentry->total_session_time=0;
5082+
dbentry->total_active_time=0;
5083+
dbentry->total_idle_in_xact_time=0;
5084+
dbentry->n_sessions_abandoned=0;
5085+
dbentry->n_sessions_fatal=0;
5086+
dbentry->n_sessions_killed=0;
49965087

49975088
dbentry->stat_reset_timestamp=GetCurrentTimestamp();
49985089
dbentry->stats_timestamp=0;
@@ -6944,6 +7035,41 @@ pgstat_recv_replslot(PgStat_MsgReplSlot *msg, int len)
69447035
}
69457036
}
69467037

7038+
/* ----------
7039+
* pgstat_recv_connstat() -
7040+
*
7041+
* Process connection information.
7042+
* ----------
7043+
*/
7044+
staticvoid
7045+
pgstat_recv_connstat(PgStat_MsgConn*msg,intlen)
7046+
{
7047+
PgStat_StatDBEntry*dbentry;
7048+
7049+
dbentry=pgstat_get_db_entry(msg->m_databaseid, true);
7050+
7051+
dbentry->n_sessions+=msg->m_count;
7052+
dbentry->total_session_time+=msg->m_session_time;
7053+
dbentry->total_active_time+=msg->m_active_time;
7054+
dbentry->total_idle_in_xact_time+=msg->m_idle_in_xact_time;
7055+
switch (msg->m_disconnect)
7056+
{
7057+
caseDISCONNECT_NOT_YET:
7058+
caseDISCONNECT_NORMAL:
7059+
/* we don't collect these */
7060+
break;
7061+
caseDISCONNECT_CLIENT_EOF:
7062+
dbentry->n_sessions_abandoned++;
7063+
break;
7064+
caseDISCONNECT_FATAL:
7065+
dbentry->n_sessions_fatal++;
7066+
break;
7067+
caseDISCONNECT_KILLED:
7068+
dbentry->n_sessions_killed++;
7069+
break;
7070+
}
7071+
}
7072+
69477073
/* ----------
69487074
* pgstat_recv_tempfile() -
69497075
*

‎src/backend/tcop/postgres.c

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2865,6 +2865,9 @@ die(SIGNAL_ARGS)
28652865
ProcDiePending= true;
28662866
}
28672867

2868+
/* for the statistics collector */
2869+
pgStatSessionEndCause=DISCONNECT_KILLED;
2870+
28682871
/* If we're still here, waken anything waiting on the process latch */
28692872
SetLatch(MyLatch);
28702873

@@ -4579,9 +4582,15 @@ PostgresMain(int argc, char *argv[],
45794582
* means unexpected loss of frontend connection. Either way,
45804583
* perform normal shutdown.
45814584
*/
4582-
case'X':
45834585
caseEOF:
45844586

4587+
/* for the statistics collector */
4588+
pgStatSessionEndCause=DISCONNECT_CLIENT_EOF;
4589+
4590+
/* FALLTHROUGH */
4591+
4592+
case'X':
4593+
45854594
/*
45864595
* Reset whereToSendOutput to prevent ereport from attempting
45874596
* to send any more messages to client.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp