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

Commit40d9e94

Browse files
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 parent1996b48 commit40d9e94

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+
CREATEVIEWpg_stat_database_conflictsAS
514+
SELECT
515+
D.oidAS datid,
516+
D.datnameAS 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
CREATEVIEWpg_stat_user_functionsAS

‎src/backend/postmaster/pgstat.c‎

Lines changed: 69 additions & 0 deletions
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
staticvoidpgstat_recv_bgwriter(PgStat_MsgBgWriter*msg,intlen);
279280
staticvoidpgstat_recv_funcstat(PgStat_MsgFuncstat*msg,intlen);
280281
staticvoidpgstat_recv_funcpurge(PgStat_MsgFuncpurge*msg,intlen);
282+
staticvoidpgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict*msg,intlen);
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(intreason)
1327+
{
1328+
PgStat_MsgRecoveryConflictmsg;
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[])
30533074
pgstat_recv_funcpurge((PgStat_MsgFuncpurge*)&msg,len);
30543075
break;
30553076

3077+
casePGSTAT_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+
staticvoid
4243+
pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict*msg,intlen)
4244+
{
4245+
PgStat_StatDBEntry*dbentry;
4246+
dbentry=pgstat_get_db_entry(msg->m_databaseid, true);
4247+
4248+
switch (msg->m_reason)
4249+
{
4250+
casePROCSIG_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+
casePROCSIG_RECOVERY_CONFLICT_TABLESPACE:
4258+
dbentry->n_conflict_tablespace++;
4259+
break;
4260+
casePROCSIG_RECOVERY_CONFLICT_LOCK:
4261+
dbentry->n_conflict_lock++;
4262+
break;
4263+
casePROCSIG_RECOVERY_CONFLICT_SNAPSHOT:
4264+
dbentry->n_conflict_snapshot++;
4265+
break;
4266+
casePROCSIG_RECOVERY_CONFLICT_BUFFERPIN:
4267+
dbentry->n_conflict_bufferpin++;
4268+
break;
4269+
casePROCSIG_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
elseif (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
elseif (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
externDatumpg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS);
7272
externDatumpg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS);
7373
externDatumpg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS);
74+
externDatumpg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS);
75+
externDatumpg_stat_get_db_conflict_lock(PG_FUNCTION_ARGS);
76+
externDatumpg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS);
77+
externDatumpg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
78+
externDatumpg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
79+
externDatumpg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
7480

7581
externDatumpg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
7682
externDatumpg_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+
Oiddbid=PG_GETARG_OID(0);
1142+
int64result;
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+
Oiddbid=PG_GETARG_OID(0);
1157+
int64result;
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+
Oiddbid=PG_GETARG_OID(0);
1172+
int64result;
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+
Oiddbid=PG_GETARG_OID(0);
1187+
int64result;
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+
Oiddbid=PG_GETARG_OID(0);
1202+
int64result;
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+
Oiddbid=PG_GETARG_OID(0);
1217+
int64result;
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(insertOID=2762 (pg_stat_get_db_tuples_deletedPGNSPPGUID12100ffftfs1020"26"_null__null__null__null_pg_stat_get_db_tuples_deleted_null__null__null_ ));
31193119
DESCR("statistics: tuples deleted in database");
3120+
DATA(insertOID=3065 (pg_stat_get_db_conflict_tablespacePGNSPPGUID12100ffftfs1020"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(insertOID=3066 (pg_stat_get_db_conflict_lockPGNSPPGUID12100ffftfs1020"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(insertOID=3067 (pg_stat_get_db_conflict_snapshotPGNSPPGUID12100ffftfs1020"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(insertOID=3068 (pg_stat_get_db_conflict_bufferpinPGNSPPGUID12100ffftfs1020"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(insertOID=3069 (pg_stat_get_db_conflict_startup_deadlockPGNSPPGUID12100ffftfs1020"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(insertOID=3070 (pg_stat_get_db_conflict_allPGNSPPGUID12100ffftfs1020"26"_null__null__null__null_pg_stat_get_db_conflict_all_null__null__null_ ));
3131+
DESCR("statistics: recovery conflicts in database");
31203132
DATA(insertOID=2769 (pg_stat_get_bgwriter_timed_checkpointsPGNSPPGUID12100ffftfs0020""_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(insertOID=2770 (pg_stat_get_bgwriter_requested_checkpointsPGNSPPGUID12100ffftfs0020""_null__null__null__null_pg_stat_get_bgwriter_requested_checkpoints_null__null__null_ ));

0 commit comments

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