diff options
Diffstat (limited to 'collectors/python.d.plugin/postgres/postgres.chart.py')
| -rw-r--r-- | collectors/python.d.plugin/postgres/postgres.chart.py | 105 |
1 files changed, 102 insertions, 3 deletions
diff --git a/collectors/python.d.plugin/postgres/postgres.chart.py b/collectors/python.d.plugin/postgres/postgres.chart.py index 9e3020358..bd28dd9b7 100644 --- a/collectors/python.d.plugin/postgres/postgres.chart.py +++ b/collectors/python.d.plugin/postgres/postgres.chart.py @@ -39,6 +39,7 @@ CONN_PARAM_SSL_KEY = 'sslkey' QUERY_NAME_WAL = 'WAL' QUERY_NAME_ARCHIVE = 'ARCHIVE' QUERY_NAME_BACKENDS = 'BACKENDS' +QUERY_NAME_BACKEND_USAGE = 'BACKEND_USAGE' QUERY_NAME_TABLE_STATS = 'TABLE_STATS' QUERY_NAME_INDEX_STATS = 'INDEX_STATS' QUERY_NAME_DATABASE = 'DATABASE' @@ -76,6 +77,10 @@ METRICS = { 'backends_active', 'backends_idle' ], + QUERY_NAME_BACKEND_USAGE: [ + 'available', + 'used' + ], QUERY_NAME_INDEX_STATS: [ 'index_count', 'index_size' @@ -139,6 +144,10 @@ METRICS = { NO_VERSION = 0 DEFAULT = 'DEFAULT' +V72 = 'V72' +V82 = 'V82' +V91 = 'V91' +V92 = 'V92' V96 = 'V96' V10 = 'V10' V11 = 'V11' @@ -235,6 +244,76 @@ FROM pg_stat_activity; """, } +QUERY_BACKEND_USAGE = { + DEFAULT: """ +SELECT + COUNT(1) as used, + current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int + - COUNT(1) AS available +FROM pg_catalog.pg_stat_activity +WHERE backend_type IN ('client backend', 'background worker'); +""", + V10: """ +SELECT + SUM(s.conn) as used, + current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int + - SUM(s.conn) AS available +FROM ( + SELECT 's' as type, COUNT(1) as conn + FROM pg_catalog.pg_stat_activity + WHERE backend_type IN ('client backend', 'background worker') + UNION ALL + SELECT 'r', COUNT(1) + FROM pg_catalog.pg_stat_replication +) as s; +""", + V92: """ +SELECT + SUM(s.conn) as used, + current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int + - SUM(s.conn) AS available +FROM ( + SELECT 's' as type, COUNT(1) as conn + FROM pg_catalog.pg_stat_activity + WHERE query NOT LIKE 'autovacuum: %%' + UNION ALL + SELECT 'r', COUNT(1) + FROM pg_catalog.pg_stat_replication +) as s; +""", + V91: """ +SELECT + SUM(s.conn) as used, + current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int + - SUM(s.conn) AS available +FROM ( + SELECT 's' as type, COUNT(1) as conn + FROM pg_catalog.pg_stat_activity + WHERE current_query NOT LIKE 'autovacuum: %%' + UNION ALL + SELECT 'r', COUNT(1) + FROM pg_catalog.pg_stat_replication +) as s; +""", + V82: """ +SELECT + COUNT(1) as used, + current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int + - COUNT(1) AS available +FROM pg_catalog.pg_stat_activity +WHERE current_query NOT LIKE 'autovacuum: %%'; +""", + V72: """ +SELECT + COUNT(1) as used, + current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int + - COUNT(1) AS available +FROM pg_catalog.pg_stat_activity s +JOIN pg_catalog.pg_database d ON d.oid = s.datid +WHERE d.datallowconn; +""", +} + QUERY_TABLE_STATS = { DEFAULT: """ SELECT @@ -315,7 +394,7 @@ FROM pg_stat_database WHERE has_database_privilege( (SELECT current_user), datname, 'connect') - AND NOT datname ~* '^template\d '; + AND NOT datname ~* '^template\d'; """, } @@ -528,10 +607,21 @@ SELECT """, } - def query_factory(name, version=NO_VERSION): if name == QUERY_NAME_BACKENDS: return QUERY_BACKEND[DEFAULT] + elif name == QUERY_NAME_BACKEND_USAGE: + if version < 80200: + return QUERY_BACKEND_USAGE[V72] + if version < 90100: + return QUERY_BACKEND_USAGE[V82] + if version < 90200: + return QUERY_BACKEND_USAGE[V91] + if version < 100000: + return QUERY_BACKEND_USAGE[V92] + elif version < 120000: + return QUERY_BACKEND_USAGE[V10] + return QUERY_BACKEND_USAGE[DEFAULT] elif name == QUERY_NAME_TABLE_STATS: return QUERY_TABLE_STATS[DEFAULT] elif name == QUERY_NAME_INDEX_STATS: @@ -588,6 +678,7 @@ ORDER = [ 'db_stat_connections', 'database_size', 'backend_process', + 'backend_usage', 'index_count', 'index_size', 'table_count', @@ -674,6 +765,13 @@ CHARTS = { ['backends_idle', 'idle', 'absolute'] ] }, + 'backend_usage': { + 'options': [None, '% of Connections in use', 'percentage', 'backend processes', 'postgres.backend_usage', 'stacked'], + 'lines': [ + ['available', 'available', 'percentage-of-absolute-row'], + ['used', 'used', 'percentage-of-absolute-row'] + ] + }, 'index_count': { 'options': [None, 'Total indexes', 'index', 'indexes', 'postgres.index_count', 'line'], 'lines': [ @@ -970,6 +1068,7 @@ class Service(SimpleService): def populate_queries(self): self.queries[query_factory(QUERY_NAME_DATABASE)] = METRICS[QUERY_NAME_DATABASE] self.queries[query_factory(QUERY_NAME_BACKENDS)] = METRICS[QUERY_NAME_BACKENDS] + self.queries[query_factory(QUERY_NAME_BACKEND_USAGE, self.server_version)] = METRICS[QUERY_NAME_BACKEND_USAGE] self.queries[query_factory(QUERY_NAME_LOCKS)] = METRICS[QUERY_NAME_LOCKS] self.queries[query_factory(QUERY_NAME_BGWRITER)] = METRICS[QUERY_NAME_BGWRITER] self.queries[query_factory(QUERY_NAME_DIFF_LSN, self.server_version)] = METRICS[QUERY_NAME_WAL_WRITES] @@ -1063,7 +1162,7 @@ def zero_lock_types(databases): def hide_password(config): - return dict((k, v if k != 'password' else '*****') for k, v in config.items()) + return dict((k, v if k != 'password' or not v else '*****') for k, v in config.items()) def add_database_lock_chart(order, definitions, database_name): |