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

Commit61cb8c5

Browse files
committed
Add deadlock counter to pg_stat_database
Adds a counter that tracks number of deadlocks that occurred ineach database to pg_stat_database.Magnus Hagander, reviewed by Jaime Casanova
1 parent0e54969 commit61cb8c5

File tree

9 files changed

+91
-8
lines changed

9 files changed

+91
-8
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -283,8 +283,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
283283
read requests avoided by finding the block already in buffer cache),
284284
number of rows returned, fetched, inserted, updated and deleted, the
285285
total number of queries canceled due to conflict with recovery (on
286-
standby servers), number and size of temporary files used,and time
287-
of last statistics reset.
286+
standby servers), number and size of temporary files used,total
287+
number of deadlocks detected, and timeof last statistics reset.
288288
</entry>
289289
</row>
290290

@@ -909,6 +909,14 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
909909
</entry>
910910
</row>
911911

912+
<row>
913+
<entry><literal><function>pg_stat_get_db_deadlocks</function>(<type>oid</type>)</literal></entry>
914+
<entry><type>bigint</type></entry>
915+
<entry>
916+
Number of deadlocks detected in the database
917+
</entry>
918+
</row>
919+
912920
<row>
913921
<entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
914922
<entry><type>bigint</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -578,6 +578,7 @@ CREATE VIEW pg_stat_database AS
578578
pg_stat_get_db_conflict_all(D.oid)AS conflicts,
579579
pg_stat_get_db_temp_files(D.oid)AS temp_files,
580580
pg_stat_get_db_temp_bytes(D.oid)AS temp_bytes,
581+
pg_stat_get_db_deadlocks(D.oid)AS deadlocks,
581582
pg_stat_get_db_stat_reset_time(D.oid)AS stats_reset
582583
FROM pg_database D;
583584

‎src/backend/postmaster/pgstat.c

Lines changed: 41 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -286,6 +286,7 @@ static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
286286
staticvoidpgstat_recv_funcstat(PgStat_MsgFuncstat*msg,intlen);
287287
staticvoidpgstat_recv_funcpurge(PgStat_MsgFuncpurge*msg,intlen);
288288
staticvoidpgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict*msg,intlen);
289+
staticvoidpgstat_recv_deadlock(PgStat_MsgDeadlock*msg,intlen);
289290
staticvoidpgstat_recv_tempfile(PgStat_MsgTempFile*msg,intlen);
290291

291292

@@ -1340,6 +1341,24 @@ pgstat_report_recovery_conflict(int reason)
13401341
pgstat_send(&msg,sizeof(msg));
13411342
}
13421343

1344+
/* --------
1345+
* pgstat_report_deadlock() -
1346+
*
1347+
*Tell the collector about a deadlock detected.
1348+
* --------
1349+
*/
1350+
void
1351+
pgstat_report_deadlock(void)
1352+
{
1353+
PgStat_MsgDeadlockmsg;
1354+
1355+
if (pgStatSock==PGINVALID_SOCKET|| !pgstat_track_counts)
1356+
return;
1357+
1358+
pgstat_setheader(&msg.m_hdr,PGSTAT_MTYPE_DEADLOCK);
1359+
msg.m_databaseid=MyDatabaseId;
1360+
pgstat_send(&msg,sizeof(msg));
1361+
}
13431362

13441363
/* --------
13451364
* pgstat_report_tempfile() -
@@ -1361,7 +1380,6 @@ pgstat_report_tempfile(size_t filesize)
13611380
pgstat_send(&msg,sizeof(msg));
13621381
}
13631382

1364-
;
13651383

13661384
/* ----------
13671385
* pgstat_ping() -
@@ -3242,6 +3260,10 @@ PgstatCollectorMain(int argc, char *argv[])
32423260
pgstat_recv_recoveryconflict((PgStat_MsgRecoveryConflict*)&msg,len);
32433261
break;
32443262

3263+
casePGSTAT_MTYPE_DEADLOCK:
3264+
pgstat_recv_deadlock((PgStat_MsgDeadlock*)&msg,len);
3265+
break;
3266+
32453267
casePGSTAT_MTYPE_TEMPFILE:
32463268
pgstat_recv_tempfile((PgStat_MsgTempFile*)&msg,len);
32473269
break;
@@ -3329,6 +3351,7 @@ pgstat_get_db_entry(Oid databaseid, bool create)
33293351
result->n_conflict_startup_deadlock=0;
33303352
result->n_temp_files=0;
33313353
result->n_temp_bytes=0;
3354+
result->n_deadlocks=0;
33323355

33333356
result->stat_reset_timestamp=GetCurrentTimestamp();
33343357

@@ -4242,6 +4265,7 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len)
42424265
dbentry->last_autovac_time=0;
42434266
dbentry->n_temp_bytes=0;
42444267
dbentry->n_temp_files=0;
4268+
dbentry->n_deadlocks=0;
42454269

42464270
dbentry->stat_reset_timestamp=GetCurrentTimestamp();
42474271

@@ -4467,6 +4491,22 @@ pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len)
44674491
}
44684492
}
44694493

4494+
/* ----------
4495+
* pgstat_recv_deadlock() -
4496+
*
4497+
*Process as DEADLOCK message.
4498+
* ----------
4499+
*/
4500+
staticvoid
4501+
pgstat_recv_deadlock(PgStat_MsgDeadlock*msg,intlen)
4502+
{
4503+
PgStat_StatDBEntry*dbentry;
4504+
4505+
dbentry=pgstat_get_db_entry(msg->m_databaseid, true);
4506+
4507+
dbentry->n_deadlocks++;
4508+
}
4509+
44704510
/* ----------
44714511
* pgstat_recv_tempfile() -
44724512
*
@@ -4482,7 +4522,6 @@ pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len)
44824522

44834523
dbentry->n_temp_bytes+=msg->m_filesize;
44844524
dbentry->n_temp_files+=1;
4485-
44864525
}
44874526

44884527
/* ----------

‎src/backend/storage/lmgr/deadlock.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -938,6 +938,8 @@ DeadLockReport(void)
938938
pgstat_get_backend_current_activity(info->pid, false));
939939
}
940940

941+
pgstat_report_deadlock();
942+
941943
ereport(ERROR,
942944
(errcode(ERRCODE_T_R_DEADLOCK_DETECTED),
943945
errmsg("deadlock detected"),

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,7 @@ extern Datum pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS);
7878
externDatumpg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
7979
externDatumpg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
8080
externDatumpg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
81+
externDatumpg_stat_get_db_deadlocks(PG_FUNCTION_ARGS);
8182
externDatumpg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS);
8283
externDatumpg_stat_get_db_temp_files(PG_FUNCTION_ARGS);
8384
externDatumpg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
@@ -1341,6 +1342,21 @@ pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS)
13411342
PG_RETURN_INT64(result);
13421343
}
13431344

1345+
Datum
1346+
pg_stat_get_db_deadlocks(PG_FUNCTION_ARGS)
1347+
{
1348+
Oiddbid=PG_GETARG_OID(0);
1349+
int64result;
1350+
PgStat_StatDBEntry*dbentry;
1351+
1352+
if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)
1353+
result=0;
1354+
else
1355+
result= (int64) (dbentry->n_deadlocks);
1356+
1357+
PG_RETURN_INT64(result);
1358+
}
1359+
13441360
Datum
13451361
pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
13461362
{

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201201261
56+
#defineCATALOG_VERSION_NO201201262
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2634,6 +2634,8 @@ DATA(insert OID = 3069 ( pg_stat_get_db_conflict_startup_deadlock PGNSP PGUID 1
26342634
DESCR("statistics: recovery conflicts in database caused by buffer deadlock");
26352635
DATA(insertOID=3070 (pg_stat_get_db_conflict_allPGNSPPGUID121000ffftfs1020"26"_null__null__null__null_pg_stat_get_db_conflict_all_null__null__null_ ));
26362636
DESCR("statistics: recovery conflicts in database");
2637+
DATA(insertOID=3152 (pg_stat_get_db_deadlocksPGNSPPGUID121000ffftfs1020"26"_null__null__null__null_pg_stat_get_db_deadlocks_null__null__null_ ));
2638+
DESCR("statistics: deadlocks detected in database");
26372639
DATA(insertOID=3074 (pg_stat_get_db_stat_reset_timePGNSPPGUID121000ffftfs101184"26"_null__null__null__null_pg_stat_get_db_stat_reset_time_null__null__null_ ));
26382640
DESCR("statistics: last reset for a database");
26392641
DATA(insertOID=3150 (pg_stat_get_db_temp_filesPGNSPPGUID121000ffftfs1020"26"_null__null__null__null_pg_stat_get_db_temp_files_null__null__null_ ));

‎src/include/pgstat.h

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,8 @@ typedef enum StatMsgType
4848
PGSTAT_MTYPE_FUNCSTAT,
4949
PGSTAT_MTYPE_FUNCPURGE,
5050
PGSTAT_MTYPE_RECOVERYCONFLICT,
51-
PGSTAT_MTYPE_TEMPFILE
51+
PGSTAT_MTYPE_TEMPFILE,
52+
PGSTAT_MTYPE_DEADLOCK
5253
}StatMsgType;
5354

5455
/* ----------
@@ -462,6 +463,17 @@ typedef struct PgStat_MsgFuncpurge
462463
Oidm_functionid[PGSTAT_NUM_FUNCPURGE];
463464
}PgStat_MsgFuncpurge;
464465

466+
/* ----------
467+
* PgStat_MsgDeadlockSent by the backend to tell the collector
468+
*about a deadlock that occurred.
469+
* ----------
470+
*/
471+
typedefstructPgStat_MsgDeadlock
472+
{
473+
PgStat_MsgHdrm_hdr;
474+
Oidm_databaseid;
475+
}PgStat_MsgDeadlock;
476+
465477

466478
/* ----------
467479
* PgStat_MsgUnion over all possible messages.
@@ -485,6 +497,7 @@ typedef union PgStat_Msg
485497
PgStat_MsgFuncstatmsg_funcstat;
486498
PgStat_MsgFuncpurgemsg_funcpurge;
487499
PgStat_MsgRecoveryConflictmsg_recoveryconflict;
500+
PgStat_MsgDeadlockmsg_deadlock;
488501
}PgStat_Msg;
489502

490503

@@ -496,7 +509,7 @@ typedef union PgStat_Msg
496509
* ------------------------------------------------------------
497510
*/
498511

499-
#definePGSTAT_FILE_FORMAT_ID0x01A5BC99
512+
#definePGSTAT_FILE_FORMAT_ID0x01A5BC9A
500513

501514
/* ----------
502515
* PgStat_StatDBEntryThe collector's data per database
@@ -522,6 +535,7 @@ typedef struct PgStat_StatDBEntry
522535
PgStat_Countern_conflict_startup_deadlock;
523536
PgStat_Countern_temp_files;
524537
PgStat_Countern_temp_bytes;
538+
PgStat_Countern_deadlocks;
525539

526540
TimestampTzstat_reset_timestamp;
527541

@@ -746,6 +760,7 @@ extern void pgstat_report_analyze(Relation rel,
746760
PgStat_Counterlivetuples,PgStat_Counterdeadtuples);
747761

748762
externvoidpgstat_report_recovery_conflict(intreason);
763+
externvoidpgstat_report_deadlock(void);
749764

750765
externvoidpgstat_initialize(void);
751766
externvoidpgstat_bestart(void);

‎src/test/regress/expected/rules.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1296,7 +1296,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
12961296
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
12971297
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
12981298
pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
1299-
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, 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_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
1299+
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, 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_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes,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;
13001300
pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d;
13011301
pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
13021302
pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text));

0 commit comments

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