8000 Add views and functions to monitor hot standby query conflicts · postgrespro/postgres@40d9e94 · GitHub
[go: up one dir, main page]
Skip to content

Commit 40d9e94

Browse files
mhagandermhagander
committed
Add views and functions to monitor hot standby query conflicts
Add the view pg_stat_database_conflicts and a column to pg_stat_database, and the underlying functions to provide the information.
1 parent 1996b48 commit 40d9e94

File tree

9 files changed

+287
-5
lines changed

9 files changed

+287
-5
lines changed

‎doc/src/sgml/high-availability.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1554,6 +1554,13 @@ if (!triggered)
15541554
approach, since <varname>vacuum_defer_cleanup_age</> is measured in
15551555
transactions executed on the primary server.
15561556
</para>
1557+
1558+
<para>
1559+
The number of query cancels and the reason for them can be viewed using
1560+
the <structname>pg_stat_database_conflicts</> system view on the standby
1561+
server. The <structname>pg_stat_database</> system view also contains
1562+
summary information.
1563+
</para>
15571564
</sect2>
15581565

15591566
<sect2 id="hot-standby-admin">

‎doc/src/sgml/monitoring.sgml

Lines changed: 53 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -278,7 +278,19 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
278278
number of transactions committed and rolled back in that database,
279279
total disk blocks read, total buffer hits (i.e., block
280280
read requests avoided by finding the block already in buffer cache),
281-
number of rows returned, fetched, inserted, updated and deleted.
281+
number of rows returned, fetched, inserted, updated and deleted, and
282+
total number of queries cancelled due to conflict with recovery (on
283+
standby servers).
284+
</entry>
285+
</row>
286+
287+
<row>
288+
<entry><structname>pg_stat_database_conflicts</><indexterm><primary>pg_stat_database_conflicts</primary></indexterm></entry>
289+
<entry>One row per database, showing database OID, database name and
290+
the number of queries that have been cancelled in this database due to
291+
dropped tablespaces, lock timeouts, old snapshots, pinned buffers and
292+
deadlocks. Will only contain information on standby servers, since
293+
conflicts do not occur on master servers.
282294
</entry>
283295
</row>
284296

@@ -599,6 +611,46 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
599611
</entry>
600612
</row>
601613

614+
<row>
615+
<entry><literal><function>pg_stat_get_db_conflict_tablespace</function>(<type>oid</type>)</literal></entry>
616+
<entry><type>bigint</type></entry>
617+
<entry>
618+
Number of queries cancelled because of recovery conflict with dropped tablespaces in database
619+
</entry>
620+
</row>
621+
622+
<row>
623+
<entry><literal><function>pg_stat_get_db_conflict_lock</function>(<type>oid</type>)</literal></entry>
624+
<entry><type>bigint</type></entry>
625+
<entry>
626+
Number of queries cancelled because of recovery conflict with locks in database
627+
</entry>
628+
</row>
629+
630+
<row>
631+
<entry><literal><function>pg_stat_get_db_conflict_snapshot</function>(<type>oid</type>)</literal></entry>
632+
<entry><type>bigint</type></entry>
633+
<entry>
634+
Number of queries cancelled because of recovery conflict with old snapshots in database
635+
</entry>
636+
</row>
637+
638+
<row>
639+
<entry><literal><function>pg_stat_get_db_conflict_bufferpin</function>(<type>oid</type>)</literal></entry>
640+
<entry><type>bigint</type></entry>
641+
<entry>
642+
Number of queries cancelled because of recovery conflict with pinned buffers in database
643+
</entry>
644+
</row>
645+
646+
<row>
647+
<entry><literal><function>pg_stat_get_db_conflict_startup_deadlock</function>(<type>oid</type>)</literal></entry>
648+
<entry><type>bigint</type></entry>
649+
<entry>
650+
Number of queries cancelled because of recovery conflict with deadlocks in database
651+
</entry>
652+
</row>
653+
602654
<row>
603655
<entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
604656
<entry><type>bigint</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -506,7 +506,19 @@ CREATE VIEW pg_stat_database AS
506506
pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
507507
pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
508508
pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
509-
pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted
509+
pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
510+
pg_stat_get_db_conflict_all(D.oid) AS conflicts
511+
FROM pg_database D;
512+
513+
CREATE VIEW pg_stat_database_conflicts AS
514+
SELECT
515+
D.oid AS datid,
516+
D.datname AS datname,
517+
pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace,
518+
pg_stat_get_db_conflict_lock(D.oid) AS confl_lock,
519+
pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot,
520+
pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin,
521+
pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock
510522
FROM pg_database D;
511523

512524
CREATE VIEW pg_stat_user_functions AS

‎src/backend/postmaster/pgstat.c

Lines changed: 69 additions & 0 deletions
< 10000 table aria-label="Diff for: src/backend/postmaster/pgstat.c" class="tab-size width-full DiffLines-module__tableLayoutFixed--ZmaVx" data-diff-anchor="diff-6161d3e2403ae3e7c0e8434321221ec00f45b6e924eb0825651cb3352a882eaa" data-tab-size="8" data-paste-markdown-skip="true" role="grid" style="--line-number-cell-width:52px;--line-number-cell-width-unified:104px">Original file line numberDiff line numberDiff line change
@@ -57,6 +57,7 @@
5757
#include "storage/ipc.h"
5858
#include "storage/pg_shmem.h"
5959
#include "storage/pmsignal.h"
60+
#include "storage/procsignal.h"
6061
#include "utils/guc.h"
6162
#include "utils/memutils.h"
6263
#include "utils/ps_status.h"
@@ -278,6 +279,7 @@ static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
278279
static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
279280
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
280281
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
282+
static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len);
281283

282284

283285
/* ------------------------------------------------------------
@@ -1314,6 +1316,25 @@ pgstat_report_analyze(Relation rel, bool adopt_counts,
13141316
pgstat_send(&msg, sizeof(msg));
13151317
}
13161318

1319+
/* --------
1320+
* pgstat_report_recovery_conflict() -
1321+
*
1322+
* Tell the collector about a Hot Standby recovery conflict.
1323+
* --------
1324+
*/
1325+
void
1326+
pgstat_report_recovery_conflict(int reason)
1327+
{
1328+
PgStat_MsgRecoveryConflict msg;
1329+
1330+
if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
1331+
return;
1332+
1333+
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RECOVERYCONFLICT);
1334+
msg.m_databaseid = MyDatabaseId;
1335+
msg.m_reason = reason;
1336+
pgstat_send(&msg, sizeof(msg));
1337+
}
13171338

13181339
/* ----------
13191340
* pgstat_ping() -
@@ -3053,6 +3074,10 @@ PgstatCollectorMain(int argc, char *argv[])
30 E377 533074
pgstat_recv_funcpurge((PgStat_MsgFuncpurge *) &msg, len);
30543075
break;
30553076

3077+
case PGSTAT_MTYPE_RECOVERYCONFLICT:
3078+
pgstat_recv_recoveryconflict((PgStat_MsgRecoveryConflict *) &msg, len);
3079+
break;
3080+
30563081
default:
30573082
break;
30583083
}
@@ -3129,6 +3154,11 @@ pgstat_get_db_entry(Oid databaseid, bool create)
31293154
result->n_tuples_updated = 0;
31303155
result->n_tuples_deleted = 0;
31313156
result->last_autovac_time = 0;
3157+
result->n_conflict_tablespace = 0;
3158+
result->n_conflict_lock = 0;
3159+
result->n_conflict_snapshot = 0;
3160+
result->n_conflict_bufferpin = 0;
3161+
result->n_conflict_startup_deadlock = 0;
31323162

31333163
memset(&hash_ctl, 0, sizeof(hash_ctl));
31343164
hash_ctl.keysize = sizeof(Oid);
@@ -4203,6 +4233,45 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
42034233
globalStats.buf_alloc += msg->m_buf_alloc;
42044234
}
42054235

4236+
/* ----------
4237+
* pgstat_recv_recoveryconflict() -
4238+
*
4239+
* Process as RECOVERYCONFLICT message.
4240+
* ----------
4241+
*/
4242+
static void
4243+
pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len)
4244+
{
4245+
PgStat_StatDBEntry *dbentry;
4246+
dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
4247+
4248+
switch (msg->m_reason)
4249+
{
4250+
case PROCSIG_RECOVERY_CONFLICT_DATABASE:
4251+
/*
4252+
* Since we drop the information about the database as soon
4253+
* as it replicates, there is no point in counting these
4254+
* conflicts.
4255+
*/
4256+
break;
4257+
case PROCSIG_RECOVERY_CONFLICT_TABLESPACE:
4258+
dbentry->n_conflict_tablespace++;
4259+
break;
4260+
case PROCSIG_RECOVERY_CONFLICT_LOCK:
4261+
dbentry->n_conflict_lock++;
4262+
break;
4263+
case PROCSIG_RECOVERY_CONFLICT_SNAPSHOT:
4264+
dbentry->n_conflict_snapshot++;
4265+
break;
4266+
case PROCSIG_RECOVERY_CONFLICT_BUFFERPIN:
4267+
dbentry->n_conflict_bufferpin++;
4268+
break;
4269+
case PROCSIG_RECOVERY_CONFLICT_STARTUP_DEADLOCK:
4270+
dbentry->n_conflict_startup_deadlock++;
4271+
break;
4272+
}
4273+
}
4274+
42064275
/* ----------
42074276
* pgstat_recv_funcstat() -
42084277
*

‎src/backend/tcop/postgres.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2903,15 +2903,21 @@ ProcessInterrupts(void)
29032903
(errcode(ERRCODE_ADMIN_SHUTDOWN),
29042904
errmsg("terminating autovacuum process due to administrator command")));
29052905
else if (RecoveryConflictPending && RecoveryConflictRetryable)
2906+
{
2907+
pgstat_report_recovery_conflict(RecoveryConflictReason);
29062908
ereport(FATAL,
29072909
(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
29082910
errmsg("terminating connection due to conflict with recovery"),
29092911
errdetail_recovery_conflict()));
2912+
}
29102913
else if (RecoveryConflictPending)
2914+
{
2915+
pgstat_report_recovery_conflict(RecoveryConflictReason);
29112916
ereport(FATAL,
29122917
(errcode(ERRCODE_ADMIN_SHUTDOWN),
29132918
errmsg("terminating connection due to conflict with recovery"),
29142919
errdetail_recovery_conflict()));
2920+
}
29152921
else
29162922
ereport(FATAL,
29172923
(errcode(ERRCODE_ADMIN_SHUTDOWN),
@@ -2956,6 +2962,7 @@ ProcessInterrupts(void)
29562962
RecoveryConflictPending = false;
29572963
DisableNotifyInterrupt();
29582964
DisableCatchupInterrupt();
2965+
pgstat_report_recovery_conflict(RecoveryConflictReason);
29592966
if (DoingCommandRead)
29602967
ereport(FATAL,
29612968
(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,12 @@ extern Datum pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS);
7171
extern Datum pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS);
7272
extern Datum pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS);
7373
extern Datum pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS);
74+
extern Datum pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS);
75+
extern Datum pg_stat_get_db_conflict_lock(PG_FUNCTION_ARGS);
76+
extern Datum pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS);
77+
extern Datum pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
78+
extern Datum pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
79+
extern Datum pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
7480

7581
extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
7682
extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
@@ -1129,6 +1135,101 @@ pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS)
11291135
PG_RETURN_INT64(result);
11301136
}
11311137

1138+
Datum
1139+
pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS)
1140+
{
1141+
Oid dbid = PG_GETARG_OID(0);
1142+
int64 result;
1143+
PgStat_StatDBEntry *dbentry;
1144+
1145+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1146+
result = 0;
1147+
else
1148+
result = (int64) (dbentry->n_conflict_tablespace);
1149+
1150+
PG_RETURN_INT64(result);
1151+
}
1152+
1153+
Datum
1154+
pg_stat_get_db_conflict_lock(PG_FUNCTION_ARGS)
1155+
{
1156+
Oid dbid = PG_GETARG_OID(0);
1157+
int64 result;
1158+
PgStat_StatDBEntry *dbentry;
1159+
1160+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1161+
result = 0;
1162+
else
1163+
result = (int64) (dbentry->n_conflict_lock);
1164+
1165+
PG_RETURN_INT64(result);
1166+
}
1167+
1168+
Datum
1169+
pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS)
1170+
{
1171+
Oid dbid = PG_GETARG_OID(0);
1172+
int64 result;
1173+
PgStat_StatDBEntry *dbentry;
1174+
1175+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1176+
result = 0;
1177+
else
1178+
result = (int64) (dbentry->n_conflict_snapshot);
1179+
1180+
PG_RETURN_INT64(result);
1181+
}
1182+
1183+
Datum
1184+
pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS)
1185+
{
1186+
Oid dbid = PG_GETARG_OID(0);
1187+
int64 result;
1188+
PgStat_StatDBEntry *dbentry;
1189+
1190+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1191+
result = 0;
1192+
else
1193+
result = (int64) (dbentry->n_conflict_bufferpin);
1194+
1195+
PG_RETURN_INT64(result);
1196+
}
1197+
1198+
Datum
1199+
pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS)
1200+
{
1201+
Oid dbid = PG_GETARG_OID(0);
1202+
int64 result;
1203+
PgStat_StatDBEntry *dbentry;
1204+
1205+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1206+
result = 0;
1207+
else
1208+
result = (int64) (dbentry->n_conflict_startup_deadlock);
1209+
1210+
PG_RETURN_INT64(result);
1211+
}
1212+
1213+
Datum
1214+
pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS)
1215+
{
1216+
Oid dbid = PG_GETARG_OID(0);
1217+
int64 result;
1218+
PgStat_StatDBEntry *dbentry;
1219+
1220+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1221+
result = 0;
1222+
else
1223+
result = (int64) (
1224+
dbentry->n_conflict_tablespace +
1225+
dbentry->n_conflict_lock +
1226+
dbentry->n_conflict_snapshot +
1227+
dbentry->n_conflict_bufferpin +
1228+
dbentry->n_conflict_startup_deadlock);
1229+
1230+
PG_RETURN_INT64(result);
1231+
}
1232+
11321233
Datum
11331234
pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
11341235
{

‎src/include/catalog/pg_proc.h

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3117,6 +3117,18 @@ DATA(insert OID = 2761 ( pg_stat_get_db_tuples_updated PGNSP PGUID 12 1 0 0 f f
31173117
DESCR("statistics: tuples updated in database");
31183118
DATA(insert OID = 2762 ( pg_stat_get_db_tuples_deleted PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_tuples_deleted _null_ _null_ _null_ ));
31193119
DESCR("statistics: tuples deleted in database");
3120+
DATA(insert OID = 3065 ( pg_stat_get_db_conflict_tablespace PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_conflict_tablespace _null_ _null_ _null_ ));
3121+
DESCR("statistics: recovery conflicts in database caused by drop tablespace");
3122+
DATA(insert OID = 3066 ( pg_stat_get_db_conflict_lock PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_conflict_lock _null_ _null_ _null_ ));
3123+
DESCR("statistics: recovery conflicts in database caused by relation lock");
3124+
DATA(insert OID = 3067 ( pg_stat_get_db_conflict_snapshot PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_conflict_snapshot _null_ _null_ _null_ ));
3125+
DESCR("statistics: recovery conflicts in database caused by snapshot expiry");
3126+
DATA(insert OID = 3068 ( pg_stat_get_db_conflict_bufferpin PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_conflict_bufferpin _null_ _null_ _null_ ));
3127+
DESCR("statistics: recovery conflicts in database caused by shared buffer pin");
3128+
DATA(insert OID = 3069 ( pg_stat_get_db_conflict_startup_deadlock PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_conflict_startup_deadlock _null_ _null_ _null_ ));
3129+
DESCR("statistics: recovery conflicts in database caused by buffer deadlock");
3130+
DATA(insert OID = 3070 ( pg_stat_get_db_conflict_all PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_conflict_all _null_ _null_ _null_ ));
3131+
DESCR("statistics: recovery conflicts in database");
31203132
DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
31213133
DESCR("statistics: number of timed checkpoints started by the bgwriter");
31223134
DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ ));

0 commit comments

Comments
 (0)
0