diff options
author | Vyacheslav Khaliev <vyacheslav.khaliev@zabbix.com> | 2022-03-01 12:52:24 +0300 |
---|---|---|
committer | Vyacheslav Khaliev <vyacheslav.khaliev@zabbix.com> | 2022-03-01 12:52:24 +0300 |
commit | c958b0cc53d5d03b4414467981b0bfaafe4b7e17 (patch) | |
tree | 4ef9c66ea8b6187d98f663ca3998d23156d8031c | |
parent | 380c2897e411347145fb601cf5bf31d512d5edeb (diff) |
...G.....T [ZBXNEXT-7065] updated postgres plugin readme
-rw-r--r-- | src/go/plugins/postgres/README.md | 50 |
1 files changed, 50 insertions, 0 deletions
diff --git a/src/go/plugins/postgres/README.md b/src/go/plugins/postgres/README.md index 8ec365bfe4c..0a688736d5a 100644 --- a/src/go/plugins/postgres/README.md +++ b/src/go/plugins/postgres/README.md @@ -455,6 +455,56 @@ FROM pg_catalog.pg_stat_activity" SQL query. - "1" if the connection is alive. - "0" if the connection is broken (returned if there was any error during the test, including AUTH and configuration issues). +**pgsql.queries[\<commonParams\>,TimePeriod]** - queries metrics by execution time. +*Parameters:* +TimePeriod (required) — execution time limit for count of slow queries. (must be an integer, must be greater than 0). + +*Returns:* Result of the +```sql +WITH T AS +(SELECT db.datname, +coalesce(T.query_time_max, 0) query_time_max, +coalesce(T.tx_time_max, 0) tx_time_max, +coalesce(T.mro_time_max, 0) mro_time_max, +coalesce(T.query_time_sum, 0) query_time_sum, +coalesce(T.tx_time_sum, 0) tx_time_sum, +coalesce(T.mro_time_sum, 0) mro_time_sum, +coalesce(T.query_slow_count, 0) query_slow_count, +coalesce(T.tx_slow_count, 0) tx_slow_count, +coalesce(T.mro_slow_count, 0) mro_slow_count +FROM pg_database db NATURAL +LEFT JOIN ( +SELECT datname, +extract(epoch FROM now())::integer ts, +coalesce(max(extract('epoch' FROM (clock_timestamp() - query_start))::integer * (state NOT IN ('idle', 'idle in transaction', 'idle in transaction (aborted)') AND query !~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)')::integer), 0) query_time_max, +coalesce(max(extract('epoch' FROM (clock_timestamp() - query_start))::integer * (state NOT IN ('idle') AND query !~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)')::integer), 0) tx_time_max, +coalesce(max(extract('epoch' FROM (clock_timestamp() - query_start))::integer * (state NOT IN ('idle') AND query ~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)')::integer), 0) mro_time_max, +coalesce(sum(extract('epoch' FROM (clock_timestamp() - query_start))::integer * (state NOT IN ('idle', 'idle in transaction', 'idle in transaction (aborted)') AND query !~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)')::integer), 0) query_time_sum, +coalesce(sum(extract('epoch' FROM (clock_timestamp() - query_start))::integer * (state NOT IN ('idle') AND query !~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)')::integer), 0) tx_time_sum, +coalesce(sum(extract('epoch' FROM (clock_timestamp() - query_start))::integer * (state NOT IN ('idle') AND query ~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)')::integer), 0) mro_time_sum, +coalesce(sum((extract('epoch' FROM (clock_timestamp() - query_start)) > %d)::integer * (state NOT IN ('idle', 'idle in transaction', 'idle in transaction (aborted)') AND query !~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)')::integer), 0) query_slow_count, +coalesce(sum((extract('epoch' FROM (clock_timestamp() - query_start)) > %d)::integer * (state NOT IN ('idle') AND query !~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)')::integer), 0) tx_slow_count, +coalesce(sum((extract('epoch' FROM (clock_timestamp() - query_start)) > %d)::integer * (state NOT IN ('idle') AND query ~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)')::integer), 0) mro_slow_count +FROM pg_stat_activity +WHERE pid <> pg_backend_pid() +GROUP BY 1) T +WHERE NOT db.datistemplate ) +SELECT json_object_agg(datname, row_to_json(T)) +FROM T +``` +> SQL query JSON format. + +Then JSON is proceeded by dependent items of: +- pgsql.queries.mro.time_max["{#DBNAME}"] - max maintenance query time. +- pgsql.queries.query.time_max["{#DBNAME}"] - max query time. +- pgsql.queries.tx.time_max["{#DBNAME}"] - max transaction query time. +- pgsql.queries.mro.slow_count["{#DBNAME}"] - slow maintenance query count. +- pgsql.queries.query.slow_count["{#DBNAME}"] - slow query count. +- pgsql.queries.tx.slow_count["{#DBNAME}"] - slow transaction query count. +- pgsql.queries.mro.time_sum["{#DBNAME}"] - sum maintenance query time. +- pgsql.queries.query.time_sum["{#DBNAME}"] - sum query time. +- pgsql.queries.tx.time_sum["{#DBNAME}"] - sum transaction query time. + **pgsql.replication.count[uri,username,password]** — number of standby servers. *Returns:* Result of the ```sql |