From 77c07842b1111576f521e8ee23fe0494202f31d6 Mon Sep 17 00:00:00 2001 From: gizzardqueen Date: Mon, 21 Jun 2021 09:32:01 -0600 Subject: =?UTF-8?q?Added=20new=20postgres=20charts=20and=20updated=20stand?= =?UTF-8?q?by=20charts=20to=20include=20slot=5F=E2=80=A6=20(#11267)?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- .../python.d.plugin/postgres/postgres.chart.py | 99 +++++++++++++++++++--- web/gui/dashboard_info.js | 19 +++++ 2 files changed, 107 insertions(+), 11 deletions(-) diff --git a/collectors/python.d.plugin/postgres/postgres.chart.py b/collectors/python.d.plugin/postgres/postgres.chart.py index 09c30f7956..cab8f6161f 100644 --- a/collectors/python.d.plugin/postgres/postgres.chart.py +++ b/collectors/python.d.plugin/postgres/postgres.chart.py @@ -55,6 +55,8 @@ QUERY_NAME_REPSLOT_FILES = 'REPSLOT_FILES' QUERY_NAME_IF_SUPERUSER = 'IF_SUPERUSER' QUERY_NAME_SERVER_VERSION = 'SERVER_VERSION' QUERY_NAME_AUTOVACUUM = 'AUTOVACUUM' +QUERY_NAME_FORCED_AUTOVACUUM = 'FORCED_AUTOVACUUM' +QUERY_NAME_TX_WRAPAROUND = 'TX_WRAPAROUND' QUERY_NAME_DIFF_LSN = 'DIFF_LSN' QUERY_NAME_WAL_WRITES = 'WAL_WRITES' @@ -135,6 +137,13 @@ METRICS = { 'vacuum_freeze', 'brin_summarize' ], + QUERY_NAME_FORCED_AUTOVACUUM: [ + 'percent_towards_forced_vacuum' + ], + QUERY_NAME_TX_WRAPAROUND: [ + 'oldest_current_xid', + 'percent_towards_wraparound' + ], QUERY_NAME_STANDBY_DELTA: [ 'sent_delta', 'write_delta', @@ -453,10 +462,10 @@ WHERE QUERY_STANDBY = { DEFAULT: """ SELECT - application_name -FROM pg_stat_replication -WHERE application_name IS NOT NULL -GROUP BY application_name; + COALESCE(prs.slot_name, psr.application_name) application_name +FROM pg_stat_replication psr +LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid +WHERE application_name IS NOT NULL; """, } @@ -470,7 +479,7 @@ FROM pg_replication_slots; QUERY_STANDBY_DELTA = { DEFAULT: """ SELECT - application_name, + COALESCE(prs.slot_name, psr.application_name) application_name, pg_wal_lsn_diff( CASE pg_is_in_recovery() WHEN true THEN pg_last_wal_receive_lsn() @@ -495,12 +504,13 @@ SELECT ELSE pg_current_wal_lsn() END, replay_lsn) AS replay_delta -FROM pg_stat_replication +FROM pg_stat_replication psr +LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid WHERE application_name IS NOT NULL; """, V96: """ SELECT - application_name, + COALESCE(prs.slot_name, psr.application_name) application_name, pg_xlog_location_diff( CASE pg_is_in_recovery() WHEN true THEN pg_last_xlog_receive_location() @@ -525,7 +535,8 @@ SELECT ELSE pg_current_xlog_location() END, replay_location) AS replay_delta -FROM pg_stat_replication +FROM pg_stat_replication psr +LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid WHERE application_name IS NOT NULL; """, } @@ -533,11 +544,12 @@ WHERE application_name IS NOT NULL; QUERY_STANDBY_LAG = { DEFAULT: """ SELECT - application_name, + COALESCE(prs.slot_name, psr.application_name) application_name, COALESCE(EXTRACT(EPOCH FROM write_lag)::bigint, 0) AS write_lag, COALESCE(EXTRACT(EPOCH FROM flush_lag)::bigint, 0) AS flush_lag, COALESCE(EXTRACT(EPOCH FROM replay_lag)::bigint, 0) AS replay_lag -FROM pg_stat_replication +FROM pg_stat_replication psr +LEFT OUTER JOIN pg_replication_slots prs on psr.pid = prs.active_pid WHERE application_name IS NOT NULL; """ } @@ -650,6 +662,43 @@ WHERE query NOT LIKE '%%pg_stat_activity%%'; """, } +QUERY_FORCED_AUTOVACUUM = { + DEFAULT: """ +WITH max_age AS ( + SELECT setting AS autovacuum_freeze_max_age + FROM pg_catalog.pg_settings + WHERE name = 'autovacuum_freeze_max_age' ) +, per_database_stats AS ( + SELECT datname + , m.autovacuum_freeze_max_age::int + , age(d.datfrozenxid) AS oldest_current_xid + FROM pg_catalog.pg_database d + JOIN max_age m ON (true) + WHERE d.datallowconn ) +SELECT max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_forced_autovacuum +FROM per_database_stats; +""", +} + +QUERY_TX_WRAPAROUND = { + DEFAULT: """ +WITH max_age AS ( + SELECT 2000000000 as max_old_xid + FROM pg_catalog.pg_settings + WHERE name = 'autovacuum_freeze_max_age' ) +, per_database_stats AS ( + SELECT datname + , m.max_old_xid::int + , age(d.datfrozenxid) AS oldest_current_xid + FROM pg_catalog.pg_database d + JOIN max_age m ON (true) + WHERE d.datallowconn ) +SELECT max(oldest_current_xid) AS oldest_current_xid + , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound +FROM per_database_stats; +""", +} + QUERY_DIFF_LSN = { DEFAULT: """ SELECT @@ -712,6 +761,10 @@ def query_factory(name, version=NO_VERSION): return QUERY_SHOW_VERSION[DEFAULT] elif name == QUERY_NAME_AUTOVACUUM: return QUERY_AUTOVACUUM[DEFAULT] + elif name == QUERY_NAME_FORCED_AUTOVACUUM: + return QUERY_FORCED_AUTOVACUUM[DEFAULT] + elif name == QUERY_NAME_TX_WRAPAROUND: + return QUERY_TX_WRAPAROUND[DEFAULT] elif name == QUERY_NAME_WAL: if version < 100000: return QUERY_WAL[V96] @@ -767,7 +820,10 @@ ORDER = [ 'replication_slot', 'standby_delta', 'standby_lag', - 'autovacuum' + 'autovacuum', + 'forced_autovacuum', + 'tx_wraparound_oldest_current_xid', + 'tx_wraparound_percent_towards_wraparound' ] CHARTS = { @@ -954,6 +1010,24 @@ CHARTS = { ['brin_summarize', 'brin summarize', 'absolute'] ] }, + 'forced_autovacuum': { + 'options': [None, 'Percent towards forced autovacuum', 'percent', 'autovacuum', 'postgres.forced_autovacuum', 'line'], + 'lines': [ + ['percent_towards_forced_autovacuum', 'percent', 'absolute'] + ] + }, + 'tx_wraparound_oldest_current_xid': { + 'options': [None, 'Oldest current XID', 'xid', 'tx_wraparound', 'postgres.tx_wraparound_oldest_current_xid', 'line'], + 'lines': [ + ['oldest_current_xid', 'xid', 'absolute'] + ] + }, + 'tx_wraparound_percent_towards_wraparound': { + 'options': [None, 'Percent towards wraparound', 'percent', 'tx_wraparound', 'postgres.percent_towards_wraparound', 'line'], + 'lines': [ + ['percent_towards_wraparound', 'percent', 'absolute'] + ] + }, 'standby_delta': { 'options': [None, 'Standby delta', 'KiB', 'replication delta', 'postgres.standby_delta', 'line'], 'lines': [ @@ -1180,6 +1254,9 @@ class Service(SimpleService): if self.server_version >= 90400: self.queries[query_factory(QUERY_NAME_AUTOVACUUM)] = METRICS[QUERY_NAME_AUTOVACUUM] + self.queries[query_factory(QUERY_NAME_FORCED_AUTOVACUUM)] = METRICS[QUERY_NAME_FORCED_AUTOVACUUM] + self.queries[query_factory(QUERY_NAME_TX_WRAPAROUND)] = METRICS[QUERY_NAME_TX_WRAPAROUND] + if self.server_version >= 100000: self.queries[query_factory(QUERY_NAME_STANDBY_LAG)] = METRICS[QUERY_NAME_STANDBY_LAG] diff --git a/web/gui/dashboard_info.js b/web/gui/dashboard_info.js index 8807548bd4..a1188e332d 100644 --- a/web/gui/dashboard_info.js +++ b/web/gui/dashboard_info.js @@ -1684,6 +1684,25 @@ netdataDashboard.context = { 'Assuming non-superuser accounts are being used to connect to Postgres (so superuser_reserved_connections are subtracted from max_connections).
' + 'For more information see Connections and Authentication.' }, + 'postgres.forced_autovacuum': { + info: 'Percent towards forced autovacuum for one or more tables.' + + 'For more information see Preventing Transaction ID Wraparound Failures.' + }, + 'postgres.tx_wraparound_oldest_current_xid': { + info: 'The oldest current transaction id (xid).' + + 'If for some reason autovacuum fails to clear old XIDs from a table, the system will begin to emit warning messages when the database\'s oldest XIDs reach eleven million transactions from the wraparound point.
' + + 'For more information see Preventing Transaction ID Wraparound Failures.' + }, + 'postgres.percent_towards_wraparound': { + info: 'Percent towards transaction wraparound.' + + 'For more information see Preventing Transaction ID Wraparound Failures.' + }, // ------------------------------------------------------------------------ -- cgit v1.2.3