diff options
author | Manuel Pombo <39411194+ManuelPombo@users.noreply.github.com> | 2020-02-28 16:21:46 +0000 |
---|---|---|
committer | GitHub <noreply@github.com> | 2020-02-28 16:21:46 +0000 |
commit | 822381f5e7fe9fa48a88a164b44edad8fa67ed6f (patch) | |
tree | 7e401f5ae32231a710dc01794600b05a08ab2344 /collectors | |
parent | 6863447f894f0c9e928cc0fa97eac8cab3d80418 (diff) |
Added connections (backend) usage to postgres monitoring (#8126)
* Added connections (backend) usage to postgres monitoring
* Adjusted PostgreSQL connection usage against server version
* Changed PostgreSQL connection usage against server version
* Fixed chart PG Backend Usage family and improved documentation
Diffstat (limited to 'collectors')
-rw-r--r-- | collectors/python.d.plugin/postgres/README.md | 21 | ||||
-rw-r--r-- | collectors/python.d.plugin/postgres/postgres.chart.py | 101 |
2 files changed, 113 insertions, 9 deletions
diff --git a/collectors/python.d.plugin/postgres/README.md b/collectors/python.d.plugin/postgres/README.md index ce0c3ac5e2..6ab1211ee1 100644 --- a/collectors/python.d.plugin/postgres/README.md +++ b/collectors/python.d.plugin/postgres/README.md @@ -16,44 +16,49 @@ Following charts are drawn: - active -3. **Write-Ahead Logging Statistics** files/s +3. **Current Backend Processe Usage** percentage + + - used + - available + +4. **Write-Ahead Logging Statistics** files/s - total - ready - done -4. **Checkpoints** writes/s +5. **Checkpoints** writes/s - scheduled - requested -5. **Current connections to db** count +6. **Current connections to db** count - connections -6. **Tuples returned from db** tuples/s +7. **Tuples returned from db** tuples/s - sequential - bitmap -7. **Tuple reads from db** reads/s +8. **Tuple reads from db** reads/s - disk - cache -8. **Transactions on db** transactions/s +9. **Transactions on db** transactions/s - committed - rolled back -9. **Tuples written to db** writes/s +10. **Tuples written to db** writes/s - inserted - updated - deleted - conflicts -10. **Locks on db** count per type +11. **Locks on db** count per type - locks diff --git a/collectors/python.d.plugin/postgres/postgres.chart.py b/collectors/python.d.plugin/postgres/postgres.chart.py index 9e30203589..ed2e2de583 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 @@ -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] |