diff options
Diffstat (limited to 'collectors/python.d.plugin/postgres')
| -rw-r--r-- | collectors/python.d.plugin/postgres/README.md | 49 | ||||
| -rw-r--r-- | collectors/python.d.plugin/postgres/postgres.chart.py | 105 | ||||
| -rw-r--r-- | collectors/python.d.plugin/postgres/postgres.conf | 9 |
3 files changed, 143 insertions, 20 deletions
diff --git a/collectors/python.d.plugin/postgres/README.md b/collectors/python.d.plugin/postgres/README.md index 29dd85a5c..dc9b18467 100644 --- a/collectors/python.d.plugin/postgres/README.md +++ b/collectors/python.d.plugin/postgres/README.md @@ -1,10 +1,16 @@ -# postgres +<!-- +title: "PostgreSQL monitoring with Netdata" +custom_edit_url: https://github.com/netdata/netdata/edit/master/collectors/python.d.plugin/postgres/README.md +sidebar_label: "PostgreSQL" +--> -Module monitors one or more postgres servers. +# PostgreSQL monitoring with Netdata -**Requirements:** +Collects database health and performance metrics. -- `python-psycopg2` package. You have to install it manually. +## Requirements + +- `python-psycopg2` package. You have to install it manually and make sure that it is available to the `netdata` user, either using `pip`, the package manager of your Linux distribution, or any other method you prefer. Following charts are drawn: @@ -16,50 +22,63 @@ Following charts are drawn: - active -3. **Write-Ahead Logging Statistics** files/s +3. **Current Backend Process 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 -## configuration +## Configuration -For all available options please see module [configuration file](postgres.conf). +Edit the `python.d/postgres.conf` configuration file using `edit-config` from the Netdata [config +directory](/docs/configure/nodes.md), which is typically at `/etc/netdata`. + +```bash +cd /etc/netdata # Replace this path with your Netdata config directory, if different +sudo ./edit-config python.d/postgres.conf +``` + +When no configuration file is found, the module tries to connect to TCP/IP socket: `localhost:5432`. ```yaml socket: @@ -75,8 +94,6 @@ tcp: port : 5432 ``` -When no configuration file is found, module tries to connect to TCP/IP socket: `localhost:5432`. - --- [](<>) 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): diff --git a/collectors/python.d.plugin/postgres/postgres.conf b/collectors/python.d.plugin/postgres/postgres.conf index 3dd461408..1970a7a27 100644 --- a/collectors/python.d.plugin/postgres/postgres.conf +++ b/collectors/python.d.plugin/postgres/postgres.conf @@ -81,7 +81,7 @@ # sslkey : path/to/key # the location of the client key file # # SSL connection parameters description: https://www.postgresql.org/docs/current/libpq-ssl.html -# +# # Additionally, the following options allow selective disabling of charts # # table_stats : false @@ -93,6 +93,10 @@ # a postgres user for netdata and add its password below to allow # netdata connect. # +# Please note that when running Postgres from inside the container, +# the client (Netdata) is not considered local, unless it runs from inside +# the same container. +# # Postgres supported versions are : # - 9.3 (without autovacuum) # - 9.4 @@ -116,6 +120,7 @@ tcp: name : 'local' database : 'postgres' user : 'postgres' + password : 'postgres' host : 'localhost' port : 5432 @@ -123,6 +128,7 @@ tcpipv4: name : 'local' database : 'postgres' user : 'postgres' + password : 'postgres' host : '127.0.0.1' port : 5432 @@ -130,5 +136,6 @@ tcpipv6: name : 'local' database : 'postgres' user : 'postgres' + password : 'postgres' host : '::1' port : 5432 |