From 335feca441b338f796e205f0e227b2f3a43f130e Mon Sep 17 00:00:00 2001 From: Magnus Hagander Date: Fri, 30 Mar 2007 18:34:56 +0000 Subject: [PATCH] Add some instrumentation to the bgwriter, through the stats collector. New view pg_stat_bgwriter, and the functions required to build it. --- doc/src/sgml/monitoring.sgml | 77 +++++++++++++++++++++++- src/backend/catalog/system_views.sql | 12 +++- src/backend/postmaster/bgwriter.c | 25 +++++++- src/backend/postmaster/pgstat.c | 111 ++++++++++++++++++++++++++++++++++- src/backend/storage/buffer/bufmgr.c | 18 +++++- src/backend/utils/adt/pgstatfuncs.c | 54 ++++++++++++++++- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_proc.h | 16 ++++- src/include/pgstat.h | 41 ++++++++++++- src/test/regress/expected/rules.out | 3 +- 10 files changed, 349 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 6e57a83df8..58ec5d3fc4 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1,4 +1,4 @@ - + Monitoring Database Activity @@ -261,6 +261,16 @@ postgres: user database host + pg_stat_bgwriter + One row only, showing cluster-wide statistics from the + background writer: number of scheduled checkpoints, requested + checkpoints, buffers written by checkpoints, lru-scans and all-scans, + and the number of times the bgwriter aborted a round because it had + written too many buffers during lru-scans and all-scans. + + + + pg_stat_database One row per database, showing database OID, database name, number of active server processes connected to that database, @@ -752,6 +762,71 @@ postgres: user database host + pg_stat_get_bgwriter_timed_checkpoints() + bigint + + The number of times the bgwriter has started timed checkpoints + (because the checkpoint_timeout time has expired). + + + + + pg_stat_get_bgwriter_requested_checkpoints() + bigint + + The number of times the bgwriter has started checkpoints based on + requests from backends because the checkpoint_segments + has been exceeded or because the CHECKPOINT command has been issued. + + + + + pg_stat_get_bgwriter_buf_written_checkpoints() + bigint + + The number of buffers written by the bgwriter during checkpoints. + + + + + pg_stat_get_bgwriter_buf_written_lru() + bigint + + The number of buffers written by the bgwriter when performing a + LRU scan of the buffer cache. + + + + + pg_stat_get_bgwriter_buf_written_all() + bigint + + The number of buffers written by the bgwriter when performing a + scan of all the buffer cache. + + + + + pg_stat_get_bgwriter_maxwritten_lru() + bigint + + The number of times the bgwriter has stopped its LRU round because + it has written more buffers than specified in the + bgwriter_lru_maxpages parameter. + + + + + pg_stat_get_bgwriter_maxwritten_all() + bigint + + The number of times the bgwriter has stopped its all-buffer round + because it has written more buffers than specified in the + bgwriter_all_maxpages parameter. + + + + pg_stat_clear_snapshot() void diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 5948db162b..79a9d7bf40 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -3,7 +3,7 @@ * * Copyright (c) 1996-2007, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.36 2007/03/16 17:57:36 mha Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.37 2007/03/30 18:34:55 mha Exp $ */ CREATE VIEW pg_roles AS @@ -364,3 +364,13 @@ CREATE VIEW pg_stat_database AS pg_stat_get_db_tuples_updated(D.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted FROM pg_database D; + +CREATE VIEW pg_stat_bgwriter AS + 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_lru() AS buffers_lru, + pg_stat_get_bgwriter_buf_written_all() AS buffers_all, + pg_stat_get_bgwriter_maxwritten_lru() AS maxwritten_lru, + pg_stat_get_bgwriter_maxwritten_all() AS maxwritten_all; diff --git a/src/backend/postmaster/bgwriter.c b/src/backend/postmaster/bgwriter.c index 1224f556e8..273588424e 100644 --- a/src/backend/postmaster/bgwriter.c +++ b/src/backend/postmaster/bgwriter.c @@ -37,7 +37,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/postmaster/bgwriter.c,v 1.36 2007/01/17 16:25:01 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/postmaster/bgwriter.c,v 1.37 2007/03/30 18:34:55 mha Exp $ * *------------------------------------------------------------------------- */ @@ -50,6 +50,7 @@ #include "access/xlog_internal.h" #include "libpq/pqsignal.h" #include "miscadmin.h" +#include "pgstat.h" #include "postmaster/bgwriter.h" #include "storage/fd.h" #include "storage/freespace.h" @@ -125,6 +126,13 @@ typedef struct static BgWriterShmemStruct *BgWriterShmem; /* + * BgWriter statistics counters. + * Stored directly in a stats message structure so it can be sent + * without needing to copy things around. + */ +PgStat_MsgBgWriter BgWriterStats; + +/* * GUC parameters */ int BgWriterDelay = 200; @@ -243,6 +251,11 @@ BackgroundWriterMain(void) MemoryContextSwitchTo(bgwriter_context); /* + * Initialize statistics counters to zero + */ + memset(&BgWriterStats, 0, sizeof(BgWriterStats)); + + /* * If an exception is encountered, processing resumes here. * * See notes in postgres.c about the design of this coding. @@ -354,6 +367,7 @@ BackgroundWriterMain(void) checkpoint_requested = false; do_checkpoint = true; force_checkpoint = true; + BgWriterStats.m_requested_checkpoints++; } if (shutdown_requested) { @@ -376,7 +390,11 @@ BackgroundWriterMain(void) now = time(NULL); elapsed_secs = now - last_checkpoint_time; if (elapsed_secs >= CheckPointTimeout) + { do_checkpoint = true; + if (!force_checkpoint) + BgWriterStats.m_timed_checkpoints++; + } /* * Do a checkpoint if requested, otherwise do one cycle of @@ -474,6 +492,11 @@ BackgroundWriterMain(void) } /* + * Send off activity statistics to the stats collector + */ + pgstat_send_bgwriter(); + + /* * Nap for the configured time, or sleep for 10 seconds if there is no * bgwriter activity configured. * diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index fd19d5741c..e1699cd0bd 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -13,7 +13,7 @@ * * Copyright (c) 2001-2007, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.151 2007/03/28 22:17:12 alvherre Exp $ + * $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.152 2007/03/30 18:34:55 mha Exp $ * ---------- */ #include "postgres.h" @@ -135,6 +135,18 @@ static HTAB *pgStatDBHash = NULL; static PgBackendStatus *localBackendStatusTable = NULL; static int localNumBackends = 0; +/* + * BgWriter global statistics counters, from bgwriter.c + */ +extern PgStat_MsgBgWriter BgWriterStats; + +/* + * Cluster wide statistics, kept in the stats collector. + * Contains statistics that are not collected per database + * or per table. + */ +static PgStat_GlobalStats globalStats; + static volatile bool need_exit = false; static volatile bool need_statwrite = false; @@ -171,6 +183,7 @@ static void pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len); static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len); static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len); static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len); +static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len); /* ------------------------------------------------------------ @@ -1288,6 +1301,22 @@ pgstat_fetch_stat_numbackends(void) return localNumBackends; } +/* + * --------- + * pgstat_fetch_global() - + * + * Support function for the SQL-callable pgstat* functions. Returns + * a pointer to the global statistics struct. + * --------- + */ +PgStat_GlobalStats * +pgstat_fetch_global(void) +{ + backend_read_statsfile(); + + return &globalStats; +} + /* ------------------------------------------------------------ * Functions for management of the shared-memory PgBackendStatus array @@ -1646,6 +1675,42 @@ pgstat_send(void *msg, int len) #endif } +/* ---------- + * pgstat_send_bgwriter() - + * + * Send bgwriter statistics to the collector + * ---------- + */ +void +pgstat_send_bgwriter(void) +{ + /* + * This function can be called even if nothing at all has happened. + * In this case, avoid sending a completely empty message to + * the stats collector. + */ + if (BgWriterStats.m_timed_checkpoints == 0 && + BgWriterStats.m_requested_checkpoints == 0 && + BgWriterStats.m_buf_written_checkpoints == 0 && + BgWriterStats.m_buf_written_lru == 0 && + BgWriterStats.m_buf_written_all == 0 && + BgWriterStats.m_maxwritten_lru == 0 && + BgWriterStats.m_maxwritten_all == 0) + return; + + /* + * Prepare and send the message + */ + pgstat_setheader(&BgWriterStats.m_hdr, PGSTAT_MTYPE_BGWRITER); + pgstat_send(&BgWriterStats, sizeof(BgWriterStats)); + + /* + * Clear out the bgwriter statistics buffer, so it can be + * re-used. + */ + memset(&BgWriterStats, 0, sizeof(BgWriterStats)); +} + /* ---------- * PgstatCollectorMain() - @@ -1892,6 +1957,10 @@ PgstatCollectorMain(int argc, char *argv[]) pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len); break; + case PGSTAT_MTYPE_BGWRITER: + pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len); + break; + default: break; } @@ -2031,6 +2100,11 @@ pgstat_write_statsfile(void) fwrite(&format_id, sizeof(format_id), 1, fpout); /* + * Write global stats struct + */ + fwrite(&globalStats, sizeof(globalStats), 1, fpout); + + /* * Walk through the database table. */ hash_seq_init(&hstat, pgStatDBHash); @@ -2133,6 +2207,12 @@ pgstat_read_statsfile(Oid onlydb) HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT); /* + * Clear out global statistics so they start from zero in case we can't + * load an existing statsfile. + */ + memset(&globalStats, 0, sizeof(globalStats)); + + /* * Try to open the status file. If it doesn't exist, the backends simply * return zero for anything and the collector simply starts from scratch * with empty counters. @@ -2152,6 +2232,16 @@ pgstat_read_statsfile(Oid onlydb) } /* + * Read global stats struct + */ + if (fread(&globalStats, 1, sizeof(globalStats), fpin) != sizeof(globalStats)) + { + ereport(pgStatRunningInCollector ? LOG : WARNING, + (errmsg("corrupted pgstat.stat file"))); + goto done; + } + + /* * We found an existing collector stats file. Read it and put all the * hashtable entries into place. */ @@ -2656,3 +2746,22 @@ pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len) tabentry->n_dead_tuples = msg->m_dead_tuples; tabentry->last_anl_tuples = msg->m_live_tuples + msg->m_dead_tuples; } + + +/* ---------- + * pgstat_recv_bgwriter() - + * + * Process a BGWRITER message. + * ---------- + */ +static void +pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len) +{ + globalStats.timed_checkpoints += msg->m_timed_checkpoints; + globalStats.requested_checkpoints += msg->m_requested_checkpoints; + globalStats.buf_written_checkpoints += msg->m_buf_written_checkpoints; + globalStats.buf_written_lru += msg->m_buf_written_lru; + globalStats.buf_written_all += msg->m_buf_written_all; + globalStats.maxwritten_lru += msg->m_maxwritten_lru; + globalStats.maxwritten_all += msg->m_maxwritten_all; +} diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 867699b045..8ce7700c9c 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/storage/buffer/bufmgr.c,v 1.215 2007/02/01 19:10:27 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/storage/buffer/bufmgr.c,v 1.216 2007/03/30 18:34:55 mha Exp $ * *------------------------------------------------------------------------- */ @@ -80,6 +80,12 @@ static bool IsForInput; /* local state for LockBufferForCleanup */ static volatile BufferDesc *PinCountWaitBuf = NULL; +/* + * Global statistics for the bgwriter. The contents of this variable + * only makes sense in the bgwriter process. + */ +extern PgStat_MsgBgWriter BgWriterStats; + static bool PinBuffer(volatile BufferDesc *buf); static void PinBuffer_Locked(volatile BufferDesc *buf); @@ -964,6 +970,8 @@ BufferSync(void) { if (SyncOneBuffer(buf_id, false)) { + BgWriterStats.m_buf_written_checkpoints++; + /* * If in bgwriter, absorb pending fsync requests after each * WRITES_PER_ABSORB write operations, to prevent overflow of the @@ -1027,9 +1035,13 @@ BgBufferSync(void) if (SyncOneBuffer(buf_id1, false)) { if (++num_written >= bgwriter_all_maxpages) + { + BgWriterStats.m_maxwritten_all++; break; + } } } + BgWriterStats.m_buf_written_all += num_written; } /* @@ -1048,11 +1060,15 @@ BgBufferSync(void) if (SyncOneBuffer(buf_id2, true)) { if (++num_written >= bgwriter_lru_maxpages) + { + BgWriterStats.m_maxwritten_lru++; break; + } } if (++buf_id2 >= NBuffers) buf_id2 = 0; } + BgWriterStats.m_buf_written_lru += num_written; } } diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 831fe6b889..f30e57ec2c 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.40 2007/03/16 17:57:36 mha Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.41 2007/03/30 18:34:55 mha Exp $ * *------------------------------------------------------------------------- */ @@ -61,9 +61,19 @@ extern Datum pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_bgwriter_buf_written_lru(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_bgwriter_buf_written_all(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_bgwriter_maxwritten_lru(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_bgwriter_maxwritten_all(PG_FUNCTION_ARGS); + extern Datum pg_stat_clear_snapshot(PG_FUNCTION_ARGS); extern Datum pg_stat_reset(PG_FUNCTION_ARGS); +/* Global bgwriter statistics, from bgwriter.c */ +extern PgStat_MsgBgWriter bgwriterStats; Datum pg_stat_get_numscans(PG_FUNCTION_ARGS) @@ -756,6 +766,48 @@ pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS) PG_RETURN_INT64(result); } +Datum +pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_global()->timed_checkpoints); +} + +Datum +pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_global()->requested_checkpoints); +} + +Datum +pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_global()->buf_written_checkpoints); +} + +Datum +pg_stat_get_bgwriter_buf_written_lru(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_global()->buf_written_lru); +} + +Datum +pg_stat_get_bgwriter_buf_written_all(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_global()->buf_written_all); +} + +Datum +pg_stat_get_bgwriter_maxwritten_lru(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_global()->maxwritten_lru); +} + +Datum +pg_stat_get_bgwriter_maxwritten_all(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_fetch_global()->maxwritten_all); +} + /* Discard the active statistics snapshot */ Datum diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 270687b153..aa0e29a216 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.396 2007/03/27 23:21:11 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.397 2007/03/30 18:34:56 mha Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200703271 +#define CATALOG_VERSION_NO 200703301 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 44e585e9c4..713d76fbfa 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.451 2007/03/27 23:21:11 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.452 2007/03/30 18:34:56 mha Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -2987,6 +2987,20 @@ DATA(insert OID = 2761 ( pg_stat_get_db_tuples_updated PGNSP PGUID 12 1 0 f f t DESCR("Statistics: Tuples updated in database"); DATA(insert OID = 2762 ( pg_stat_get_db_tuples_deleted PGNSP PGUID 12 1 0 f f t f s 1 20 "26" _null_ _null_ _null_ pg_stat_get_db_tuples_deleted - _null_ )); DESCR("Statistics: Tuples deleted in database"); +DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints - _null_ )); +DESCR("Statistics: Number of timed checkpoints started by the bgwriter"); +DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints - _null_ )); +DESCR("Statistics: Number of backend requested checkpoints started by the bgwriter"); +DATA(insert OID = 2771 ( pg_stat_get_bgwriter_buf_written_checkpoints PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_buf_written_checkpoints - _null_ )); +DESCR("Statistics: Number of buffers written by the bgwriter during checkpoints"); +DATA(insert OID = 2772 ( pg_stat_get_bgwriter_buf_written_lru PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_buf_written_lru - _null_ )); +DESCR("Statistics: Number of buffers written by the bgwriter during LRU scans"); +DATA(insert OID = 2773 ( pg_stat_get_bgwriter_buf_written_all PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_buf_written_all - _null_ )); +DESCR("Statistics: Number of buffers written by the bgwriter during all-buffer scans"); +DATA(insert OID = 2774 ( pg_stat_get_bgwriter_maxwritten_lru PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_maxwritten_lru - _null_ )); +DESCR("Statistics: Number of times the bgwriter stopped processing when it had written too many buffers during LRU scans"); +DATA(insert OID = 2775 ( pg_stat_get_bgwriter_maxwritten_all PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_maxwritten_all - _null_ )); +DESCR("Statistics: Number of times the bgwriter stopped processing when it had written too many buffers during all-buffer scans"); DATA(insert OID = 2230 ( pg_stat_clear_snapshot PGNSP PGUID 12 1 0 f f f f v 0 2278 "" _null_ _null_ _null_ pg_stat_clear_snapshot - _null_ )); DESCR("Statistics: Discard current transaction's statistics snapshot"); DATA(insert OID = 2274 ( pg_stat_reset PGNSP PGUID 12 1 0 f f f f v 0 2278 "" _null_ _null_ _null_ pg_stat_reset - _null_ )); diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 8e08db4c17..3cfcea7e4b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -5,7 +5,7 @@ * * Copyright (c) 2001-2007, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/include/pgstat.h,v 1.56 2007/03/22 19:53:31 momjian Exp $ + * $PostgreSQL: pgsql/src/include/pgstat.h,v 1.57 2007/03/30 18:34:55 mha Exp $ * ---------- */ #ifndef PGSTAT_H @@ -30,7 +30,8 @@ typedef enum StatMsgType PGSTAT_MTYPE_RESETCOUNTER, PGSTAT_MTYPE_AUTOVAC_START, PGSTAT_MTYPE_VACUUM, - PGSTAT_MTYPE_ANALYZE + PGSTAT_MTYPE_ANALYZE, + PGSTAT_MTYPE_BGWRITER } StatMsgType; /* ---------- @@ -213,6 +214,24 @@ typedef struct PgStat_MsgAnalyze /* ---------- + * PgStat_MsgBgWriter Sent by the bgwriter to update statistics. + * ---------- + */ +typedef struct PgStat_MsgBgWriter +{ + PgStat_MsgHdr m_hdr; + + PgStat_Counter m_timed_checkpoints; + PgStat_Counter m_requested_checkpoints; + PgStat_Counter m_buf_written_checkpoints; + PgStat_Counter m_buf_written_lru; + PgStat_Counter m_buf_written_all; + PgStat_Counter m_maxwritten_lru; + PgStat_Counter m_maxwritten_all; +} PgStat_MsgBgWriter; + + +/* ---------- * PgStat_Msg Union over all possible messages. * ---------- */ @@ -227,6 +246,7 @@ typedef union PgStat_Msg PgStat_MsgAutovacStart msg_autovacuum; PgStat_MsgVacuum msg_vacuum; PgStat_MsgAnalyze msg_analyze; + PgStat_MsgBgWriter msg_bgwriter; } PgStat_Msg; @@ -297,6 +317,21 @@ typedef struct PgStat_StatTabEntry } PgStat_StatTabEntry; +/* + * Global statistics kept in the stats collector + */ +typedef struct PgStat_GlobalStats +{ + PgStat_Counter timed_checkpoints; + PgStat_Counter requested_checkpoints; + PgStat_Counter buf_written_checkpoints; + PgStat_Counter buf_written_lru; + PgStat_Counter buf_written_all; + PgStat_Counter maxwritten_lru; + PgStat_Counter maxwritten_all; +} PgStat_GlobalStats; + + /* ---------- * Shared-memory data structures * ---------- @@ -478,6 +513,7 @@ extern void pgstat_initstats(PgStat_Info *stats, Relation rel); extern void pgstat_count_xact_commit(void); extern void pgstat_count_xact_rollback(void); +extern void pgstat_send_bgwriter(void); /* ---------- * Support functions for the SQL-callable functions to @@ -488,5 +524,6 @@ extern PgStat_StatDBEntry *pgstat_fetch_stat_dbentry(Oid dbid); extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid); extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid); extern int pgstat_fetch_stat_numbackends(void); +extern PgStat_GlobalStats *pgstat_fetch_global(void); #endif /* PGSTAT_H */ diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 6ff781e6bd..112891bbb2 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1292,6 +1292,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_txn_start(s.backendid) AS txn_start, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid)); 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"])); 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_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 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; + 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_lru() AS buffers_lru, pg_stat_get_bgwriter_buf_written_all() AS buffers_all, pg_stat_get_bgwriter_maxwritten_lru() AS maxwritten_lru, pg_stat_get_bgwriter_maxwritten_all() AS maxwritten_all; 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 FROM pg_database d; 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", 'pg_toast'::"name", 'information_schema'::"name"])); pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"])); @@ -1326,7 +1327,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r."name", r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp."name", emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp; -(48 rows) +(49 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; -- 2.11.0