diff options
Diffstat (limited to '')
| -rw-r--r-- | collectors/python.d.plugin/postgres/postgres.chart.py (renamed from python.d/postgres.chart.py) | 593 |
1 files changed, 375 insertions, 218 deletions
diff --git a/python.d/postgres.chart.py b/collectors/python.d.plugin/postgres/postgres.chart.py index 0522b1938..7f43877c3 100644 --- a/python.d/postgres.chart.py +++ b/collectors/python.d.plugin/postgres/postgres.chart.py @@ -1,6 +1,7 @@ # -*- coding: utf-8 -*- # Description: example netdata python.d module # Authors: facetoe, dangtranhoang +# SPDX-License-Identifier: GPL-3.0-or-later from copy import deepcopy @@ -20,221 +21,310 @@ update_every = 1 priority = 60000 retries = 60 -METRICS = dict( - DATABASE=['connections', - 'xact_commit', - 'xact_rollback', - 'blks_read', - 'blks_hit', - 'tup_returned', - 'tup_fetched', - 'tup_inserted', - 'tup_updated', - 'tup_deleted', - 'conflicts', - 'temp_files', - 'temp_bytes', - 'size'], - BACKENDS=['backends_active', - 'backends_idle'], - INDEX_STATS=['index_count', - 'index_size'], - TABLE_STATS=['table_size', - 'table_count'], - WAL=['written_wal', - 'recycled_wal', - 'total_wal'], - WAL_WRITES=['wal_writes'], - ARCHIVE=['ready_count', - 'done_count', - 'file_count'], - BGWRITER=['checkpoint_scheduled', - 'checkpoint_requested', - 'buffers_checkpoint', - 'buffers_clean', - 'maxwritten_clean', - 'buffers_backend', - 'buffers_alloc', - 'buffers_backend_fsync'], - LOCKS=['ExclusiveLock', - 'RowShareLock', - 'SIReadLock', - 'ShareUpdateExclusiveLock', - 'AccessExclusiveLock', - 'AccessShareLock', - 'ShareRowExclusiveLock', - 'ShareLock', - 'RowExclusiveLock'], - AUTOVACUUM=['analyze', - 'vacuum_analyze', - 'vacuum', - 'vacuum_freeze', - 'brin_summarize'], - STANDBY_DELTA=['sent_delta', - 'write_delta', - 'flush_delta', - 'replay_delta'], - REPSLOT_FILES=['replslot_wal_keep', - 'replslot_files'] - -) - -QUERIES = dict( - WAL=""" +METRICS = { + 'DATABASE': [ + 'connections', + 'xact_commit', + 'xact_rollback', + 'blks_read', + 'blks_hit', + 'tup_returned', + 'tup_fetched', + 'tup_inserted', + 'tup_updated', + 'tup_deleted', + 'conflicts', + 'temp_files', + 'temp_bytes', + 'size' + ], + 'BACKENDS': [ + 'backends_active', + 'backends_idle' + ], + 'INDEX_STATS': [ + 'index_count', + 'index_size' + ], + 'TABLE_STATS': [ + 'table_size', + 'table_count' + ], + 'WAL': [ + 'written_wal', + 'recycled_wal', + 'total_wal' + ], + 'WAL_WRITES': [ + 'wal_writes' + ], + 'ARCHIVE': [ + 'ready_count', + 'done_count', + 'file_count' + ], + 'BGWRITER': [ + 'checkpoint_scheduled', + 'checkpoint_requested', + 'buffers_checkpoint', + 'buffers_clean', + 'maxwritten_clean', + 'buffers_backend', + 'buffers_alloc', + 'buffers_backend_fsync' + ], + 'LOCKS': [ + 'ExclusiveLock', + 'RowShareLock', + 'SIReadLock', + 'ShareUpdateExclusiveLock', + 'AccessExclusiveLock', + 'AccessShareLock', + 'ShareRowExclusiveLock', + 'ShareLock', + 'RowExclusiveLock' + ], + 'AUTOVACUUM': [ + 'analyze', + 'vacuum_analyze', + 'vacuum', + 'vacuum_freeze', + 'brin_summarize' + ], + 'STANDBY_DELTA': [ + 'sent_delta', + 'write_delta', + 'flush_delta', + 'replay_delta' + ], + 'REPSLOT_FILES': [ + 'replslot_wal_keep', + 'replslot_files' + ] +} + +QUERIES = { + 'WAL': """ SELECT - count(*) as total_wal, - count(*) FILTER (WHERE type = 'recycled') AS recycled_wal, - count(*) FILTER (WHERE type = 'written') AS written_wal + count(*) as total_wal, + count(*) FILTER (WHERE type = 'recycled') AS recycled_wal, + count(*) FILTER (WHERE type = 'written') AS written_wal FROM - (SELECT wal.name, - pg_{0}file_name(CASE pg_is_in_recovery() WHEN true THEN NULL ELSE pg_current_{0}_{1}() END ), - CASE WHEN wal.name > pg_{0}file_name(CASE pg_is_in_recovery() WHEN true THEN NULL ELSE pg_current_{0}_{1}() END ) THEN 'recycled' - ELSE 'written' - END AS type - FROM pg_catalog.pg_ls_dir('pg_{0}') AS wal(name) - WHERE name ~ '^[0-9A-F]{{24}}$' - ORDER BY (pg_stat_file('pg_{0}/'||name)).modification, wal.name DESC) sub; + (SELECT + wal.name, + pg_{0}file_name( + CASE pg_is_in_recovery() + WHEN true THEN NULL + ELSE pg_current_{0}_{1}() + END ), + CASE + WHEN wal.name > pg_{0}file_name( + CASE pg_is_in_recovery() + WHEN true THEN NULL + ELSE pg_current_{0}_{1}() + END ) THEN 'recycled' + ELSE 'written' + END AS type + FROM pg_catalog.pg_ls_dir('pg_{0}') AS wal(name) + WHERE name ~ '^[0-9A-F]{{24}}$' + ORDER BY + (pg_stat_file('pg_{0}/'||name)).modification, + wal.name DESC) sub; """, - ARCHIVE=""" + 'ARCHIVE': """ SELECT CAST(COUNT(*) AS INT) AS file_count, - CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.ready$$r$ as INT)), 0) AS INT) AS ready_count, - CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.done$$r$ AS INT)), 0) AS INT) AS done_count + CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.ready$$r$ as INT)),0) AS INT) AS ready_count, + CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.done$$r$ AS INT)),0) AS INT) AS done_count FROM pg_catalog.pg_ls_dir('pg_{0}/archive_status') AS archive_files (archive_file); """, - BACKENDS=""" + 'BACKENDS': """ SELECT - count(*) - (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS backends_active, - (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle' ) AS backends_idle -FROM pg_stat_activity; + count(*) - (SELECT count(*) + FROM pg_stat_activity + WHERE state = 'idle') + AS backends_active, + (SELECT count(*) + FROM pg_stat_activity + WHERE state = 'idle') + AS backends_idle +FROM pg_stat_activity; """, - TABLE_STATS=""" + 'TABLE_STATS': """ SELECT - ((sum(relpages) * 8) * 1024) AS table_size, - count(1) AS table_count + ((sum(relpages) * 8) * 1024) AS table_size, + count(1) AS table_count FROM pg_class WHERE relkind IN ('r', 't'); """, - INDEX_STATS=""" + 'INDEX_STATS': """ SELECT - ((sum(relpages) * 8) * 1024) AS index_size, - count(1) AS index_count + ((sum(relpages) * 8) * 1024) AS index_size, + count(1) AS index_count FROM pg_class -WHERE relkind = 'i';""", - DATABASE=""" +WHERE relkind = 'i'; +""", + 'DATABASE': """ SELECT - datname AS database_name, - numbackends AS connections, - xact_commit AS xact_commit, - xact_rollback AS xact_rollback, - blks_read AS blks_read, - blks_hit AS blks_hit, - tup_returned AS tup_returned, - tup_fetched AS tup_fetched, - tup_inserted AS tup_inserted, - tup_updated AS tup_updated, - tup_deleted AS tup_deleted, - conflicts AS conflicts, - pg_database_size(datname) AS size, - temp_files AS temp_files, - temp_bytes AS temp_bytes + datname AS database_name, + numbackends AS connections, + xact_commit AS xact_commit, + xact_rollback AS xact_rollback, + blks_read AS blks_read, + blks_hit AS blks_hit, + tup_returned AS tup_returned, + tup_fetched AS tup_fetched, + tup_inserted AS tup_inserted, + tup_updated AS tup_updated, + tup_deleted AS tup_deleted, + conflicts AS conflicts, + pg_database_size(datname) AS size, + temp_files AS temp_files, + temp_bytes AS temp_bytes FROM pg_stat_database -WHERE datname IN %(databases)s -; +WHERE datname IN %(databases)s ; """, - BGWRITER=""" + 'BGWRITER': """ SELECT - checkpoints_timed AS checkpoint_scheduled, - checkpoints_req AS checkpoint_requested, - buffers_checkpoint * current_setting('block_size')::numeric buffers_checkpoint, - buffers_clean * current_setting('block_size')::numeric buffers_clean, - maxwritten_clean, - buffers_backend * current_setting('block_size')::numeric buffers_backend, - buffers_alloc * current_setting('block_size')::numeric buffers_alloc, - buffers_backend_fsync + checkpoints_timed AS checkpoint_scheduled, + checkpoints_req AS checkpoint_requested, + buffers_checkpoint * current_setting('block_size')::numeric buffers_checkpoint, + buffers_clean * current_setting('block_size')::numeric buffers_clean, + maxwritten_clean, + buffers_backend * current_setting('block_size')::numeric buffers_backend, + buffers_alloc * current_setting('block_size')::numeric buffers_alloc, + buffers_backend_fsync FROM pg_stat_bgwriter; """, - LOCKS=""" + 'LOCKS': """ SELECT - pg_database.datname as database_name, - mode, - count(mode) AS locks_count + pg_database.datname as database_name, + mode, + count(mode) AS locks_count FROM pg_locks - INNER JOIN pg_database ON pg_database.oid = pg_locks.database +INNER JOIN pg_database + ON pg_database.oid = pg_locks.database GROUP BY datname, mode ORDER BY datname, mode; """, - FIND_DATABASES=""" -SELECT datname + 'FIND_DATABASES': """ +SELECT + datname FROM pg_stat_database -WHERE has_database_privilege((SELECT current_user), datname, 'connect') -AND NOT datname ~* '^template\d+'; +WHERE + has_database_privilege( + (SELECT current_user), datname, 'connect') + AND NOT datname ~* '^template\d '; """, - FIND_STANDBY=""" -SELECT application_name + 'FIND_STANDBY': """ +SELECT + application_name FROM pg_stat_replication WHERE application_name IS NOT NULL GROUP BY application_name; """, - FIND_REPLICATION_SLOT=""" + 'FIND_REPLICATION_SLOT': """ SELECT slot_name FROM pg_replication_slots; """, - STANDBY_DELTA=""" -SELECT application_name, - pg_{0}_{1}_diff(CASE pg_is_in_recovery() WHEN true THEN pg_last_{0}_receive_{1}() ELSE pg_current_{0}_{1}() END , sent_{1}) AS sent_delta, - pg_{0}_{1}_diff(CASE pg_is_in_recovery() WHEN true THEN pg_last_{0}_receive_{1}() ELSE pg_current_{0}_{1}() END , write_{1}) AS write_delta, - pg_{0}_{1}_diff(CASE pg_is_in_recovery() WHEN true THEN pg_last_{0}_receive_{1}() ELSE pg_current_{0}_{1}() END , flush_{1}) AS flush_delta, - pg_{0}_{1}_diff(CASE pg_is_in_recovery() WHEN true THEN pg_last_{0}_receive_{1}() ELSE pg_current_{0}_{1}() END , replay_{1}) AS replay_delta + 'STANDBY_DELTA': """ +SELECT + application_name, + pg_{0}_{1}_diff( + CASE pg_is_in_recovery() + WHEN true THEN pg_last_{0}_receive_{1}() + ELSE pg_current_{0}_{1}() + END, + sent_{1}) AS sent_delta, + pg_{0}_{1}_diff( + CASE pg_is_in_recovery() + WHEN true THEN pg_last_{0}_receive_{1}() + ELSE pg_current_{0}_{1}() + END, + write_{1}) AS write_delta, + pg_{0}_{1}_diff( + CASE pg_is_in_recovery() + WHEN true THEN pg_last_{0}_receive_{1}() + ELSE pg_current_{0}_{1}() + END, + flush_{1}) AS flush_delta, + pg_{0}_{1}_diff( + CASE pg_is_in_recovery() + WHEN true THEN pg_last_{0}_receive_{1}() + ELSE pg_current_{0}_{1}() + END, + replay_{1}) AS replay_delta FROM pg_stat_replication WHERE application_name IS NOT NULL; """, - REPSLOT_FILES=""" + 'REPSLOT_FILES': """ WITH wal_size AS ( - SELECT current_setting('wal_block_size')::INT * setting::INT AS val - FROM pg_settings - WHERE name = 'wal_segment_size' -) -SELECT slot_name, slot_type, replslot_wal_keep, count(slot_file) AS replslot_files -FROM ( - SELECT slot.slot_name, CASE WHEN slot_file <> 'state' THEN 1 END AS slot_file , slot_type, - COALESCE (floor((pg_wal_lsn_diff (pg_current_wal_lsn (), - slot.restart_lsn) - (pg_walfile_name_offset (restart_lsn)).file_offset) / (s.val)), - 0) AS replslot_wal_keep - FROM pg_replication_slots slot - LEFT JOIN ( - SELECT slot2.slot_name, - pg_ls_dir('pg_replslot/' || slot2.slot_name) AS slot_file - FROM pg_replication_slots slot2 - ) files (slot_name, slot_file) - ON slot.slot_name = files.slot_name - CROSS JOIN wal_size s) AS d -GROUP BY slot_name, slot_type, replslot_wal_keep; + SELECT + current_setting('wal_block_size')::INT * setting::INT AS val + FROM pg_settings + WHERE name = 'wal_segment_size' + ) +SELECT + slot_name, + slot_type, + replslot_wal_keep, + count(slot_file) AS replslot_files +FROM + (SELECT + slot.slot_name, + CASE + WHEN slot_file <> 'state' THEN 1 + END AS slot_file , + slot_type, + COALESCE ( + floor( + (pg_wal_lsn_diff(pg_current_wal_lsn (),slot.restart_lsn) + - (pg_walfile_name_offset (restart_lsn)).file_offset) / (s.val) + ),0) AS replslot_wal_keep + FROM pg_replication_slots slot + LEFT JOIN ( + SELECT + slot2.slot_name, + pg_ls_dir('pg_replslot/' || slot2.slot_name) AS slot_file + FROM pg_replication_slots slot2 + ) files (slot_name, slot_file) + ON slot.slot_name = files.slot_name + CROSS JOIN wal_size s + ) AS d +GROUP BY + slot_name, + slot_type, + replslot_wal_keep; """, - IF_SUPERUSER=""" + 'IF_SUPERUSER': """ SELECT current_setting('is_superuser') = 'on' AS is_superuser; """, - DETECT_SERVER_VERSION=""" + 'DETECT_SERVER_VERSION': """ SHOW server_version_num; """, - AUTOVACUUM=""" + 'AUTOVACUUM': """ SELECT - count(*) FILTER (WHERE query LIKE 'autovacuum: ANALYZE%%') AS analyze, - count(*) FILTER (WHERE query LIKE 'autovacuum: VACUUM ANALYZE%%') AS vacuum_analyze, - count(*) FILTER (WHERE query LIKE 'autovacuum: VACUUM%%' - AND query NOT LIKE 'autovacuum: VACUUM ANALYZE%%' - AND query NOT LIKE '%%to prevent wraparound%%') AS vacuum, - count(*) FILTER (WHERE query LIKE '%%to prevent wraparound%%') AS vacuum_freeze, - count(*) FILTER (WHERE query LIKE 'autovacuum: BRIN summarize%%') AS brin_summarize + count(*) FILTER (WHERE query LIKE 'autovacuum: ANALYZE%%') AS analyze, + count(*) FILTER (WHERE query LIKE 'autovacuum: VACUUM ANALYZE%%') AS vacuum_analyze, + count(*) FILTER (WHERE query LIKE 'autovacuum: VACUUM%%' + AND query NOT LIKE 'autovacuum: VACUUM ANALYZE%%' + AND query NOT LIKE '%%to prevent wraparound%%') AS vacuum, + count(*) FILTER (WHERE query LIKE '%%to prevent wraparound%%') AS vacuum_freeze, + count(*) FILTER (WHERE query LIKE 'autovacuum: BRIN summarize%%') AS brin_summarize FROM pg_stat_activity WHERE query NOT LIKE '%%pg_stat_activity%%'; """, - DIFF_LSN=""" -SELECT pg_{0}_{1}_diff(CASE pg_is_in_recovery() WHEN true THEN pg_last_{0}_receive_{1}() ELSE pg_current_{0}_{1}() END, '0/0') as wal_writes ; + 'DIFF_LSN': """ +SELECT + pg_{0}_{1}_diff( + CASE pg_is_in_recovery() + WHEN true THEN pg_last_{0}_receive_{1}() + ELSE pg_current_{0}_{1}() + END, + '0/0') as wal_writes ; """ - -) +} QUERY_STATS = { @@ -243,11 +333,34 @@ QUERY_STATS = { QUERIES['LOCKS']: METRICS['LOCKS'] } -ORDER = ['db_stat_temp_files', 'db_stat_temp_bytes', 'db_stat_blks', 'db_stat_tuple_returned', 'db_stat_tuple_write', - 'db_stat_transactions','db_stat_connections', 'database_size', 'backend_process', 'index_count', 'index_size', - 'table_count', 'table_size', 'wal', 'wal_writes', 'archive_wal', 'checkpointer', 'stat_bgwriter_alloc', 'stat_bgwriter_checkpoint', - 'stat_bgwriter_backend', 'stat_bgwriter_backend_fsync' , 'stat_bgwriter_bgwriter', 'stat_bgwriter_maxwritten', - 'replication_slot', 'standby_delta', 'autovacuum'] +ORDER = [ + 'db_stat_temp_files', + 'db_stat_temp_bytes', + 'db_stat_blks', + 'db_stat_tuple_returned', + 'db_stat_tuple_write', + 'db_stat_transactions', + 'db_stat_connections', + 'database_size', + 'backend_process', + 'index_count', + 'index_size', + 'table_count', + 'table_size', + 'wal', + 'wal_writes', + 'archive_wal', + 'checkpointer', + 'stat_bgwriter_alloc', + 'stat_bgwriter_checkpoint', + 'stat_bgwriter_backend', + 'stat_bgwriter_backend_fsync', + 'stat_bgwriter_bgwriter', + 'stat_bgwriter_maxwritten', + 'replication_slot', + 'standby_delta', + 'autovacuum' +] CHARTS = { 'db_stat_transactions': { @@ -256,26 +369,30 @@ CHARTS = { 'lines': [ ['xact_commit', 'committed', 'incremental'], ['xact_rollback', 'rolled back', 'incremental'] - ]}, + ] + }, 'db_stat_connections': { 'options': [None, 'Current connections to db', 'count', 'db statistics', 'postgres.db_stat_connections', 'line'], 'lines': [ ['connections', 'connections', 'absolute'] - ]}, + ] + }, 'db_stat_blks': { 'options': [None, 'Disk blocks reads from db', 'reads/s', 'db statistics', 'postgres.db_stat_blks', 'line'], 'lines': [ ['blks_read', 'disk', 'incremental'], ['blks_hit', 'cache', 'incremental'] - ]}, + ] + }, 'db_stat_tuple_returned': { 'options': [None, 'Tuples returned from db', 'tuples/s', 'db statistics', 'postgres.db_stat_tuple_returned', 'line'], 'lines': [ ['tup_returned', 'sequential', 'incremental'], ['tup_fetched', 'bitmap', 'incremental'] - ]}, + ] + }, 'db_stat_tuple_write': { 'options': [None, 'Tuples written to db', 'writes/s', 'db statistics', 'postgres.db_stat_tuple_write', 'line'], 'lines': [ @@ -283,103 +400,128 @@ CHARTS = { ['tup_updated', 'updated', 'incremental'], ['tup_deleted', 'deleted', 'incremental'], ['conflicts', 'conflicts', 'incremental'] - ]}, + ] + }, 'db_stat_temp_bytes': { - 'options': [None, 'Temp files written to disk', 'KB/s', 'db statistics', 'postgres.db_stat_temp_bytes', 'line'], + 'options': [None, 'Temp files written to disk', 'KB/s', 'db statistics', 'postgres.db_stat_temp_bytes', + 'line'], 'lines': [ ['temp_bytes', 'size', 'incremental', 1, 1024] - ]}, + ] + }, 'db_stat_temp_files': { - 'options': [None, 'Temp files written to disk', 'files', 'db statistics', 'postgres.db_stat_temp_files', 'line'], + 'options': [None, 'Temp files written to disk', 'files', 'db statistics', 'postgres.db_stat_temp_files', + 'line'], 'lines': [ ['temp_files', 'files', 'incremental'] - ]}, + ] + }, 'database_size': { 'options': [None, 'Database size', 'MB', 'database size', 'postgres.db_size', 'stacked'], 'lines': [ - ]}, + ] + }, 'backend_process': { 'options': [None, 'Current Backend Processes', 'processes', 'backend processes', 'postgres.backend_process', 'line'], 'lines': [ ['backends_active', 'active', 'absolute'], ['backends_idle', 'idle', 'absolute'] - ]}, + ] + }, 'index_count': { 'options': [None, 'Total indexes', 'index', 'indexes', 'postgres.index_count', 'line'], 'lines': [ ['index_count', 'total', 'absolute'] - ]}, + ] + }, 'index_size': { 'options': [None, 'Indexes size', 'MB', 'indexes', 'postgres.index_size', 'line'], 'lines': [ ['index_size', 'size', 'absolute', 1, 1024 * 1024] - ]}, + ] + }, 'table_count': { 'options': [None, 'Total Tables', 'tables', 'tables', 'postgres.table_count', 'line'], 'lines': [ ['table_count', 'total', 'absolute'] - ]}, + ] + }, 'table_size': { 'options': [None, 'Tables size', 'MB', 'tables', 'postgres.table_size', 'line'], 'lines': [ ['table_size', 'size', 'absolute', 1, 1024 * 1024] - ]}, + ] + }, 'wal': { 'options': [None, 'Write-Ahead Logs', 'files', 'wal', 'postgres.wal', 'line'], 'lines': [ ['written_wal', 'written', 'absolute'], ['recycled_wal', 'recycled', 'absolute'], ['total_wal', 'total', 'absolute'] - ]}, + ] + }, 'wal_writes': { 'options': [None, 'Write-Ahead Logs', 'kilobytes/s', 'wal_writes', 'postgres.wal_writes', 'line'], 'lines': [ ['wal_writes', 'writes', 'incremental', 1, 1024] - ]}, + ] + }, 'archive_wal': { 'options': [None, 'Archive Write-Ahead Logs', 'files/s', 'archive wal', 'postgres.archive_wal', 'line'], 'lines': [ ['file_count', 'total', 'incremental'], ['ready_count', 'ready', 'incremental'], ['done_count', 'done', 'incremental'] - ]}, + ] + }, 'checkpointer': { 'options': [None, 'Checkpoints', 'writes', 'checkpointer', 'postgres.checkpointer', 'line'], 'lines': [ ['checkpoint_scheduled', 'scheduled', 'incremental'], ['checkpoint_requested', 'requested', 'incremental'] - ]}, + ] + }, 'stat_bgwriter_alloc': { 'options': [None, 'Buffers allocated', 'kilobytes/s', 'bgwriter', 'postgres.stat_bgwriter_alloc', 'line'], 'lines': [ ['buffers_alloc', 'alloc', 'incremental', 1, 1024] - ]}, + ] + }, 'stat_bgwriter_checkpoint': { - 'options': [None, 'Buffers written during checkpoints', 'kilobytes/s', 'bgwriter', 'postgres.stat_bgwriter_checkpoint', 'line'], + 'options': [None, 'Buffers written during checkpoints', 'kilobytes/s', 'bgwriter', + 'postgres.stat_bgwriter_checkpoint', 'line'], 'lines': [ ['buffers_checkpoint', 'checkpoint', 'incremental', 1, 1024] - ]}, + ] + }, 'stat_bgwriter_backend': { - 'options': [None, 'Buffers written directly by a backend', 'kilobytes/s', 'bgwriter', 'postgres.stat_bgwriter_backend', 'line'], + 'options': [None, 'Buffers written directly by a backend', 'kilobytes/s', 'bgwriter', + 'postgres.stat_bgwriter_backend', 'line'], 'lines': [ ['buffers_backend', 'backend', 'incremental', 1, 1024] - ]}, + ] + }, 'stat_bgwriter_backend_fsync': { 'options': [None, 'Fsync by backend', 'times', 'bgwriter', 'postgres.stat_bgwriter_backend_fsync', 'line'], 'lines': [ ['buffers_backend_fsync', 'backend fsync', 'incremental'] - ]}, + ] + }, 'stat_bgwriter_bgwriter': { - 'options': [None, 'Buffers written by the background writer', 'kilobytes/s', 'bgwriter', 'postgres.bgwriter_bgwriter', 'line'], + 'options': [None, 'Buffers written by the background writer', 'kilobytes/s', 'bgwriter', + 'postgres.bgwriter_bgwriter', 'line'], 'lines': [ ['buffers_clean', 'clean', 'incremental', 1, 1024] - ]}, + ] + }, 'stat_bgwriter_maxwritten': { - 'options': [None, 'Too many buffers written', 'times', 'bgwriter', 'postgres.stat_bgwriter_maxwritten', 'line'], + 'options': [None, 'Too many buffers written', 'times', 'bgwriter', 'postgres.stat_bgwriter_maxwritten', + 'line'], 'lines': [ ['maxwritten_clean', 'maxwritten', 'incremental'] - ]}, + ] + }, 'autovacuum': { 'options': [None, 'Autovacuum workers', 'workers', 'autovacuum', 'postgres.autovacuum', 'line'], 'lines': [ @@ -388,7 +530,8 @@ CHARTS = { ['vacuum_analyze', 'vacuum analyze', 'absolute'], ['vacuum_freeze', 'vacuum freeze', 'absolute'], ['brin_summarize', 'brin summarize', 'absolute'] - ]}, + ] + }, 'standby_delta': { 'options': [None, 'Standby delta', 'kilobytes', 'replication delta', 'postgres.standby_delta', 'line'], 'lines': [ @@ -396,13 +539,15 @@ CHARTS = { ['write_delta', 'write delta', 'absolute', 1, 1024], ['flush_delta', 'flush delta', 'absolute', 1, 1024], ['replay_delta', 'replay delta', 'absolute', 1, 1024] - ]}, - 'replication_slot': { + ] + }, + 'replication_slot': { 'options': [None, 'Replication slot files', 'files', 'replication slot', 'postgres.replication_slot', 'line'], 'lines': [ ['replslot_wal_keep', 'wal keeped', 'absolute'], ['replslot_files', 'pg_replslot files', 'absolute'] - ]} + ] + } } @@ -462,7 +607,7 @@ class Service(SimpleService): cursor.close() if self.database_poll and isinstance(self.database_poll, str): - self.databases = [dbase for dbase in self.databases if dbase in self.database_poll.split()]\ + self.databases = [dbase for dbase in self.databases if dbase in self.database_poll.split()] \ or self.databases self.locks_zeroed = populate_lock_types(self.databases) @@ -482,8 +627,8 @@ class Service(SimpleService): wal = 'xlog' lsn = 'location' self.queries[QUERIES['BGWRITER']] = METRICS['BGWRITER'] - self.queries[QUERIES['DIFF_LSN'].format(wal,lsn)] = METRICS['WAL_WRITES'] - self.queries[QUERIES['STANDBY_DELTA'].format(wal,lsn)] = METRICS['STANDBY_DELTA'] + self.queries[QUERIES['DIFF_LSN'].format(wal, lsn)] = METRICS['WAL_WRITES'] + self.queries[QUERIES['STANDBY_DELTA'].format(wal, lsn)] = METRICS['STANDBY_DELTA'] if self.index_stats: self.queries[QUERIES['INDEX_STATS']] = METRICS['INDEX_STATS'] @@ -492,7 +637,7 @@ class Service(SimpleService): if is_superuser: self.queries[QUERIES['ARCHIVE'].format(wal)] = METRICS['ARCHIVE'] if self.server_version >= 90400: - self.queries[QUERIES['WAL'].format(wal,lsn)] = METRICS['WAL'] + self.queries[QUERIES['WAL'].format(wal, lsn)] = METRICS['WAL'] if self.server_version >= 100000: self.queries[QUERIES['REPSLOT_FILES']] = METRICS['REPSLOT_FILES'] if self.server_version >= 90400: @@ -501,8 +646,8 @@ class Service(SimpleService): def create_dynamic_charts_(self): for database_name in self.databases[::-1]: - self.definitions['database_size']['lines'].append([database_name + '_size', - database_name, 'absolute', 1, 1024 * 1024]) + self.definitions['database_size']['lines'].append( + [database_name + '_size', database_name, 'absolute', 1, 1024 * 1024]) for chart_name in [name for name in self.order if name.startswith('db_stat')]: add_database_stat_chart_(order=self.order, definitions=self.definitions, name=chart_name, database_name=database_name) @@ -510,17 +655,21 @@ class Service(SimpleService): add_database_lock_chart_(order=self.order, definitions=self.definitions, database_name=database_name) for application_name in self.secondaries[::-1]: - add_replication_delta_chart_(order=self.order, definitions=self.definitions, - name='standby_delta', application_name=application_name) + add_replication_delta_chart_( + order=self.order, + definitions=self.definitions, + name='standby_delta', + application_name=application_name) for slot_name in self.replication_slots[::-1]: - add_replication_slot_chart_(order=self.order, definitions=self.definitions, - name='replication_slot', slot_name=slot_name) - - + add_replication_slot_chart_( + order=self.order, + definitions=self.definitions, + name='replication_slot', + slot_name=slot_name) def _get_data(self): - result, error = self._connect() + result, _ = self._connect() if result: cursor = self.connection.cursor(cursor_factory=DictCursor) try: @@ -551,7 +700,8 @@ class Service(SimpleService): else: dimension_id = metric if metric in row: - self.data[dimension_id] = int(row[metric]) + if row[metric] is not None: + self.data[dimension_id] = int(row[metric]) elif 'locks_count' in row: self.data[dimension_id] = row['locks_count'] if metric == row['mode'] else 0 @@ -564,6 +714,7 @@ def discover_databases_(cursor, query): result.append(db) return result + def discover_secondaries_(cursor, query): cursor.execute(query) result = list() @@ -572,6 +723,7 @@ def discover_secondaries_(cursor, query): result.append(sc) return result + def discover_replication_slots_(cursor, query): cursor.execute(query) result = list() @@ -580,14 +732,17 @@ def discover_replication_slots_(cursor, query): result.append(slot) return result + def check_if_superuser_(cursor, query): cursor.execute(query) return cursor.fetchone()[0] + def detect_server_version(cursor, query): cursor.execute(query) return int(cursor.fetchone()[0]) + def populate_lock_types(databases): result = dict() for database in databases: @@ -626,11 +781,12 @@ def add_database_stat_chart_(order, definitions, name, database_name): chart_template = CHARTS[name] chart_name = '_'.join([database_name, name]) order.insert(0, chart_name) - name, title, units, family, context, chart_type = chart_template['options'] + name, title, units, _, context, chart_type = chart_template['options'] definitions[chart_name] = { 'options': [name, title + ': ' + database_name, units, 'db ' + database_name, context, chart_type], 'lines': create_lines(database_name, chart_template['lines'])} + def add_replication_delta_chart_(order, definitions, name, application_name): def create_lines(standby, lines): result = list() @@ -648,6 +804,7 @@ def add_replication_delta_chart_(order, definitions, name, application_name): 'options': [name, title + ': ' + application_name, units, 'replication delta', context, chart_type], 'lines': create_lines(application_name, chart_template['lines'])} + def add_replication_slot_chart_(order, definitions, name, slot_name): def create_lines(slot, lines): result = list() |