Attached patch adds a counter for number of deadlocks in a database to
pg_stat_database.
While not enough to diagnose a problem on it's own, this is an easy
way to get an indicator when for when you need to go look in the logs
for details. Overhead should be very small - one counter per database
is not enough to bloat the statsfile,and if you have enough deadlocks
that the sendinf of the messages actually cause a performance
overhead, you have a bigger problem...
Comments?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
***
*** 283,289 postgres: replaceableuser/ replaceabledatabase/ replaceablehost/ re
read requests avoided by finding the block already in buffer cache),
number of rows returned, fetched, inserted, updated and deleted, the
total number of queries canceled due to conflict with recovery (on
! standby servers), and time of last statistics reset.
/entry
/row
--- 283,290
read requests avoided by finding the block already in buffer cache),
number of rows returned, fetched, inserted, updated and deleted, the
total number of queries canceled due to conflict with recovery (on
! standby servers), total number of deadlocks detected, and time of
! last statistics reset.
/entry
/row
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***
*** 574,579 CREATE VIEW pg_stat_database AS
--- 574,580
pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
pg_stat_get_db_conflict_all(D.oid) AS conflicts,
+ pg_stat_get_db_deadlocks(D.oid) AS deadlocks,
pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
FROM pg_database D;
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***
*** 286,291 static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
--- 286,292
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
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);
/*
***
*** 1339,1344 pgstat_report_recovery_conflict(int reason)
--- 1340,1364
pgstat_send(msg, sizeof(msg));
}
+ /*
+ * pgstat_report_deadlock() -
+ *
+ * Tell the collector about a deadlock detected.
+ *
+ */
+ void
+ pgstat_report_deadlock(void)
+ {
+ PgStat_MsgDeadlock msg;
+
+ if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+ return;
+
+ pgstat_setheader(msg.m_hdr, PGSTAT_MTYPE_DEADLOCK);
+ msg.m_databaseid = MyDatabaseId;
+ pgstat_send(msg, sizeof(msg));
+ }
+
/* --
* pgstat_ping() -
*
***
*** 3185,3190 PgstatCollectorMain(int argc, char *argv[])
--- 3205,3214
pgstat_recv_recoveryconflict((PgStat_MsgRecoveryConflict *) msg, len);
break;
+ case PGSTAT_MTYPE_DEADLOCK:
+ pgstat_recv_deadlock((PgStat_MsgDeadlock *) msg, len);
+ break;
+
default:
break;
}
***
*** 3266,3271 pgstat_get_db_entry(Oid databaseid, bool create)
--- 3290,3296
result-n_conflict_snapshot = 0;
result-n_conflict_bufferpin = 0;
result-n_conflict_startup_deadlock = 0;
+ result-n_deadlocks = 0;
result-stat_reset_timestamp = GetCurrentTimestamp();
***
*** 4403,4408 pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len)
--- 4428,4449
}
/* --
+ * pgstat_recv_deadlock() -
+ *
+ * Process as DEADLOCK message.
+ * --
+ */
+ static void
+ pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len)
+ {
+ PgStat_StatDBEntry *dbentry;
+
+ dbentry = pgstat_get_db_entry(msg-m_databaseid, true);
+
+ dbentry-n_deadlocks++;
+ }
+
+ /* --
* pgstat_recv_funcstat() -
*
* Count what the backend has done.
*** a/src/backend/storage/lmgr/deadlock.c
--- b/src/backend/storage/lmgr/deadlock.c
***
*** 938,943 DeadLockReport(void)
--- 938,945
pgstat_get_backend_current_activity(info-pid, false));
}
+ pgstat_report_deadlock();
+
ereport(ERROR,
(errcode(ERRCODE_T_R_DEADLOCK_DETECTED),
errmsg(deadlock detected),
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***
*** 78,83 extern Datum pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS);
--- 78,84
extern Datum pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
extern
