Last active
May 23, 2019 04:54
-
-
Save plockaby/b4d818e545c98b0700ecc59ef84d02da to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# this configuration file was originally provided to me by Lloyd Albin | |
# from Fred Hutch. he runs the SEAPUG Seattle PostgreSQL Users Group. | |
# thanks Lloyd! | |
LoadPlugin postgresql | |
<Plugin postgresql> | |
<Query connections> | |
Statement "SELECT count(*) AS connections FROM pg_stat_activity;" | |
<Result> | |
Type pg_conns | |
ValuesFrom connections | |
</Result> | |
</Query> | |
<Query connection_states> | |
Statement " | |
SELECT a.state, TRUE AS waiting, COALESCE(b.connections,0) AS connections FROM ( | |
SELECT 'active' AS state | |
UNION ALL | |
SELECT 'idle' | |
UNION ALL | |
SELECT 'idle in transaction' | |
UNION ALL | |
SELECT 'idle in transaction (aborted)' | |
UNION ALL | |
SELECT 'fastpath' | |
UNION ALL | |
SELECT 'disabled' | |
) a | |
LEFT JOIN ( | |
SELECT state, COALESCE(wait_event_type = 'Lock', FALSE) AS waiting, count(*) as connections | |
FROM pg_stat_activity | |
GROUP BY pg_stat_activity.state, pg_stat_activity.wait_event_type | |
) b ON a.state = b.state AND b.waiting = TRUE | |
UNION ALL | |
SELECT a.state, FALSE AS waiting, COALESCE(b.connections,0) FROM ( | |
SELECT 'active' AS state | |
UNION ALL | |
SELECT 'idle' | |
UNION ALL | |
SELECT 'idle in transaction' | |
UNION ALL | |
SELECT 'idle in transaction (aborted)' | |
UNION ALL | |
SELECT 'fastpath' | |
UNION ALL | |
SELECT 'disabled' | |
) a | |
LEFT JOIN ( | |
SELECT state, COALESCE(wait_event_type = 'Lock', FALSE) AS waiting, count(*) as connections | |
FROM pg_stat_activity | |
GROUP BY pg_stat_activity.state, pg_stat_activity.wait_event_type | |
) b ON a.state = b.state AND b.waiting = FALSE; | |
" | |
<Result> | |
Type pg_connection_state | |
InstancesFrom "state" "waiting" | |
ValuesFrom connections | |
</Result> | |
</Query> | |
<Query concurrent_txns> | |
Statement " | |
SELECT a.datname, FALSE AS waiting, COALESCE(b.txns,0) AS txns | |
FROM pg_database a | |
LEFT JOIN ( | |
SELECT datname, COALESCE(wait_event_type = 'Lock', FALSE) AS waiting, count(*) AS txns | |
FROM pg_stat_activity | |
WHERE state != 'idle' | |
GROUP BY datname, wait_event_type | |
) b ON a.datname = b.datname AND b.waiting = FALSE | |
WHERE a.datname NOT LIKE 'test%' | |
UNION ALL | |
SELECT a.datname, TRUE AS waiting, COALESCE(b.txns,0) | |
FROM pg_database a | |
LEFT JOIN ( | |
SELECT datname, COALESCE(wait_event_type = 'Lock', FALSE) AS waiting, count(*) AS txns | |
FROM pg_stat_activity | |
WHERE state != 'idle' | |
GROUP BY datname, wait_event_type | |
) b ON a.datname = b.datname AND b.waiting = TRUE | |
WHERE a.datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_concurrent_txns | |
InstancesFrom "datname" "waiting" | |
ValuesFrom txns | |
</Result> | |
</Query> | |
<Query connection_state> | |
Statement " | |
SELECT | |
a.count as locked, | |
b.count as active, | |
c.count as idle, | |
d.count as idle_transaction, | |
e.count AS idle_trans_abort, | |
f.count AS fastpath, | |
g.count AS disabled | |
FROM ( | |
SELECT count(*) | |
FROM pg_stat_activity | |
WHERE COALESCE(wait_event_type = 'Lock', FALSE) | |
) AS a, ( | |
SELECT count(*) | |
FROM pg_stat_activity | |
WHERE state = 'active' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) | |
) AS b, ( | |
SELECT count(*) | |
FROM pg_stat_activity | |
WHERE state = 'idle' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) | |
) AS c, ( | |
SELECT count(*) | |
FROM pg_stat_activity | |
WHERE state = 'idle in transaction' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) | |
) AS d, ( | |
SELECT count(*) | |
FROM pg_stat_activity | |
WHERE state = 'idle in transaction (aborted)' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) | |
) AS e, ( | |
SELECT count(*) | |
FROM pg_stat_activity | |
WHERE state = 'fastpath' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) | |
) AS f, ( | |
SELECT count(*) | |
FROM pg_stat_activity | |
WHERE state = 'disabled' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) | |
) AS g; | |
" | |
<Result> | |
Type pg_connections | |
ValuesFrom locked active idle idle_transaction idle_trans_abort fastpath disabled | |
</Result> | |
</Query> | |
<Query connection_state_by_database> | |
Statement " | |
SELECT | |
f.datname, | |
COALESCE(a.count,0) as locked, | |
COALESCE(b.count,0) as active, | |
COALESCE(c.count,0) as idle, | |
COALESCE(d.count,0) as idle_transaction, | |
COALESCE(e.count,0) AS idle_trans_abort, | |
COALESCE(g.count,0) AS fastpath, | |
COALESCE(h.count,0) AS disabled | |
FROM ( | |
SELECT datname | |
FROM pg_stat_activity | |
WHERE datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS f | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS a ON f.datname = a.datname | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'active' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS b ON f.datname = b.datname | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'idle' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS c ON f.datname = c.datname | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'idle in transaction' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS d ON f.datname = d.datname | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'idle in transaction (aborted)' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS e ON f.datname = e.datname | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'fastpath' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS g ON f.datname = g.datname | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'disabled' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS h ON f.datname = h.datname; | |
" | |
<Result> | |
Type pg_connections | |
InstancesFrom "datname" | |
ValuesFrom locked active idle idle_transaction idle_trans_abort fastpath disabled | |
</Result> | |
</Query> | |
<Query database_size> | |
Statement " | |
SELECT pg_database.datname AS database, pg_database_size(pg_database.oid) AS size | |
FROM pg_database | |
WHERE datname NOT LIKE 'test%' | |
ORDER BY pg_database.datname; | |
" | |
<Result> | |
Type pg_databases | |
InstancesFrom "database" | |
ValuesFrom size | |
</Result> | |
</Query> | |
<Query database_transactions> | |
Statement " | |
SELECT datname AS database, txns | |
FROM ( | |
SELECT foo.freez::int, age(datfrozenxid) AS txns, datname | |
FROM pg_database d | |
JOIN ( | |
SELECT setting AS freez | |
FROM pg_settings | |
WHERE name = 'autovacuum_freeze_max_age' | |
) AS foo ON (true) | |
WHERE d.datallowconn AND d.datname NOT LIKE 'test%' | |
) AS foo2; | |
" | |
<Result> | |
Type pg_txns | |
InstancesFrom "database" | |
ValuesFrom txns | |
</Result> | |
</Query> | |
<Query transactions> | |
Statement " | |
SELECT sum(xact_commit + xact_rollback) | |
FROM pg_stat_database; | |
" | |
<Result> | |
Type pg_db_txns | |
ValuesFrom sum | |
</Result> | |
</Query> | |
<Query buffercache> | |
Statement " | |
SELECT | |
CASE pg_buffercache.usagecount | |
WHEN 0 THEN 'Just Read - Least Used' | |
WHEN 1 THEN 'Low Usage' | |
WHEN 2 THEN 'Medium Low Usage' | |
WHEN 3 THEN 'Medium High Usage' | |
WHEN 4 THEN 'High Usage' | |
WHEN 5 THEN 'Heavily Used' | |
ELSE 'Unused' | |
END AS usage, | |
CASE pg_buffercache.isdirty | |
WHEN TRUE THEN 'WAL Only' | |
ELSE 'Normal' | |
END AS written, | |
count(*) * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) AS size | |
FROM pg_buffercache | |
GROUP BY pg_buffercache.usagecount, pg_buffercache.isdirty | |
ORDER BY pg_buffercache.isdirty, pg_buffercache.usagecount; | |
" | |
<Result> | |
Type pg_buffercache | |
InstancesFrom "usage", "written" | |
ValuesFrom size | |
</Result> | |
</Query> | |
<Query buffercache_databases> | |
Statement " | |
SELECT datname, COALESCE(count(*) * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
))) AS shared_buffers | |
FROM pg_database a | |
LEFT JOIN pg_buffercache b ON a.oid = b.reldatabase | |
WHERE datname NOT LIKE 'test%' | |
GROUP BY datname; | |
" | |
<Result> | |
Type pg_buffercache_databases | |
InstancesFrom "datname" | |
ValuesFrom shared_buffers | |
</Result> | |
</Query> | |
<Query database_commit_ratio_by_database> | |
Statement " | |
SELECT round(100.*sd.xact_commit/(sd.xact_commit+sd.xact_rollback), 2) AS dcommitratio, d.datname, u.usename | |
FROM pg_stat_database sd | |
JOIN pg_database d ON (d.oid=sd.datid) | |
JOIN pg_user u ON (u.usesysid=d.datdba) | |
WHERE sd.xact_commit+sd.xact_rollback<>0 AND d.datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_commit_ratio | |
InstancesFrom "datname" | |
ValuesFrom dcommitratio | |
</Result> | |
</Query> | |
<Query database_commit_ratio> | |
Statement " | |
SELECT round(100.*sd.xact_commit/(sd.xact_commit+sd.xact_rollback), 2) AS dcommitratio, d.datname, u.usename | |
FROM pg_stat_database sd | |
JOIN pg_database d ON (d.oid=sd.datid) | |
JOIN pg_user u ON (u.usesysid=d.datdba) | |
WHERE sd.xact_commit+sd.xact_rollback<>0 AND d.datname = $1; | |
" | |
Param database | |
<Result> | |
Type pg_commit_ratio | |
InstancesFrom "datname" | |
ValuesFrom dcommitratio | |
</Result> | |
</Query> | |
<Query database_stats> | |
Statement " | |
SELECT | |
datname, | |
numbackends AS backends, | |
xact_commit AS commits, | |
xact_rollback AS rollbacks, | |
blks_read * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) AS read, | |
blks_hit * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) AS hit, | |
COALESCE((SELECT SUM(idx_scan) FROM pg_stat_user_indexes),0) AS idxscan, | |
COALESCE((SELECT SUM(idx_tup_read) FROM pg_stat_user_indexes),0) AS idxtupread, | |
COALESCE((SELECT SUM(idx_tup_fetch) FROM pg_stat_user_indexes),0) AS idxtupfetch, | |
COALESCE((SELECT SUM(idx_blks_read) * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) FROM pg_statio_user_indexes),0) AS idxblksread, | |
COALESCE((SELECT SUM(idx_blks_hit) * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) FROM pg_statio_user_indexes),0) AS idxblkshit, | |
COALESCE((SELECT SUM(seq_scan) FROM pg_stat_user_tables),0) AS seqscan, | |
COALESCE((SELECT SUM(seq_tup_read) FROM pg_stat_user_tables),0) AS seqtupread, | |
tup_returned AS ret, | |
tup_fetched AS fetch, | |
tup_inserted AS ins, | |
tup_updated AS upd, | |
tup_deleted AS del, | |
deadlocks, | |
blk_read_time, | |
blk_write_time | |
FROM pg_stat_database | |
WHERE datname = $1; | |
" | |
Param database | |
<Result> | |
Type pg_bkends | |
InstancesFrom "datname" | |
ValuesFrom backends | |
</Result> | |
<Result> | |
Type pg_xact_commit | |
InstancesFrom "datname" | |
ValuesFrom commits | |
</Result> | |
<Result> | |
Type pg_xact_rollback | |
InstancesFrom "datname" | |
ValuesFrom rollbacks | |
</Result> | |
<Result> | |
Type pg_blks_read | |
InstancesFrom "datname" | |
ValuesFrom read | |
</Result> | |
<Result> | |
Type pg_blks_hit | |
InstancesFrom "datname" | |
ValuesFrom hit | |
</Result> | |
<Result> | |
Type pg_idx_scan | |
InstancesFrom "datname" | |
ValuesFrom idxscan | |
</Result> | |
<Result> | |
Type pg_idx_tup_read | |
InstancesFrom "datname" | |
ValuesFrom idxtupread | |
</Result> | |
<Result> | |
Type pg_idx_tup_fetch | |
InstancesFrom "datname" | |
ValuesFrom idxtupfetch | |
</Result> | |
<Result> | |
Type pg_idx_blks_read | |
InstancesFrom "datname" | |
ValuesFrom idxblksread | |
</Result> | |
<Result> | |
Type pg_idx_blks_hit | |
InstancesFrom "datname" | |
ValuesFrom idxblkshit | |
</Result> | |
<Result> | |
Type pg_seq_scan | |
InstancesFrom "datname" | |
ValuesFrom seqscan | |
</Result> | |
<Result> | |
Type pg_seq_tup_read | |
InstancesFrom "datname" | |
ValuesFrom seqtupread | |
</Result> | |
<Result> | |
Type pg_tup_returned | |
InstancesFrom "datname" | |
ValuesFrom ret | |
</Result> | |
<Result> | |
Type pg_tup_fetched | |
InstancesFrom "datname" | |
ValuesFrom fetch | |
</Result> | |
<Result> | |
Type pg_tup_inserted | |
InstancesFrom "datname" | |
ValuesFrom ins | |
</Result> | |
<Result> | |
Type pg_tup_updated | |
InstancesFrom "datname" | |
ValuesFrom upd | |
</Result> | |
<Result> | |
Type pg_tup_deleted | |
InstancesFrom "datname" | |
ValuesFrom del | |
</Result> | |
<Result> | |
Type pg_deadlocks | |
InstancesFrom "datname" | |
ValuesFrom deadlocks | |
</Result> | |
<Result> | |
# if track_io_timing is enabled, otherwise zero | |
Type pg_blk_read_time | |
InstancesFrom "datname" | |
ValuesFrom blk_read_time | |
</Result> | |
<Result> | |
# if track_io_timing is enabled, otherwise zero | |
Type pg_blk_write_time | |
InstancesFrom "datname" | |
ValuesFrom blk_write_time | |
</Result> | |
</Query> | |
<Query database_stats_by_database> | |
Statement " | |
SELECT | |
datname, | |
numbackends AS backends, | |
xact_commit AS commits, | |
xact_rollback AS rollbacks, | |
blks_read * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) AS read, | |
blks_hit * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) AS hit, | |
COALESCE((SELECT SUM(idx_scan) FROM pg_stat_user_indexes),0) AS idxscan, | |
COALESCE((SELECT SUM(idx_tup_read) FROM pg_stat_user_indexes),0) AS idxtupread, | |
COALESCE((SELECT SUM(idx_tup_fetch) FROM pg_stat_user_indexes),0) AS idxtupfetch, | |
COALESCE((SELECT SUM(idx_blks_read) * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) FROM pg_statio_user_indexes),0) AS idxblksread, | |
COALESCE((SELECT SUM(idx_blks_hit) * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) FROM pg_statio_user_indexes),0) AS idxblkshit, | |
COALESCE((SELECT SUM(seq_scan) FROM pg_stat_user_tables),0) AS seqscan, | |
COALESCE((SELECT SUM(seq_tup_read) FROM pg_stat_user_tables),0) AS seqtupread, | |
tup_returned AS ret, | |
tup_fetched AS fetch, | |
tup_inserted AS ins, | |
tup_updated AS upd, | |
tup_deleted AS del, | |
deadlocks, | |
blk_read_time, | |
blk_write_time | |
FROM pg_stat_database | |
WHERE datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_bkends | |
InstancesFrom "datname" | |
ValuesFrom backends | |
</Result> | |
<Result> | |
Type pg_xact_commit | |
InstancesFrom "datname" | |
ValuesFrom commits | |
</Result> | |
<Result> | |
Type pg_xact_rollback | |
InstancesFrom "datname" | |
ValuesFrom rollbacks | |
</Result> | |
<Result> | |
Type pg_blks_read | |
InstancesFrom "datname" | |
ValuesFrom read | |
</Result> | |
<Result> | |
Type pg_blks_hit | |
InstancesFrom "datname" | |
ValuesFrom hit | |
</Result> | |
<Result> | |
Type pg_idx_scan | |
InstancesFrom "datname" | |
ValuesFrom idxscan | |
</Result> | |
<Result> | |
Type pg_idx_tup_read | |
InstancesFrom "datname" | |
ValuesFrom idxtupread | |
</Result> | |
<Result> | |
Type pg_idx_tup_fetch | |
InstancesFrom "datname" | |
ValuesFrom idxtupfetch | |
</Result> | |
<Result> | |
Type pg_idx_blks_read | |
InstancesFrom "datname" | |
ValuesFrom idxblksread | |
</Result> | |
<Result> | |
Type pg_idx_blks_hit | |
InstancesFrom "datname" | |
ValuesFrom idxblkshit | |
</Result> | |
<Result> | |
Type pg_seq_scan | |
InstancesFrom "datname" | |
ValuesFrom seqscan | |
</Result> | |
<Result> | |
Type pg_seq_tup_read | |
InstancesFrom "datname" | |
ValuesFrom seqtupread | |
</Result> | |
<Result> | |
Type pg_tup_returned | |
InstancesFrom "datname" | |
ValuesFrom ret | |
</Result> | |
<Result> | |
Type pg_tup_fetched | |
InstancesFrom "datname" | |
ValuesFrom fetch | |
</Result> | |
<Result> | |
Type pg_tup_inserted | |
InstancesFrom "datname" | |
ValuesFrom ins | |
</Result> | |
<Result> | |
Type pg_tup_updated | |
InstancesFrom "datname" | |
ValuesFrom upd | |
</Result> | |
<Result> | |
Type pg_tup_deleted | |
InstancesFrom "datname" | |
ValuesFrom del | |
</Result> | |
<Result> | |
Type pg_deadlocks | |
InstancesFrom "datname" | |
ValuesFrom deadlocks | |
</Result> | |
<Result> | |
# if track_io_timing is enabled, otherwise zero | |
Type pg_blk_read_time | |
InstancesFrom "datname" | |
ValuesFrom blk_read_time | |
</Result> | |
<Result> | |
# if track_io_timing is enabled, otherwise zero | |
Type pg_blk_write_time | |
InstancesFrom "datname" | |
ValuesFrom blk_write_time | |
</Result> | |
</Query> | |
<Query pg_xlog> | |
Statement " | |
SELECT count(*) AS xlog_count | |
FROM pg_ls_dir('pg_wal') | |
WHERE pg_ls_dir ~ E'^[0-9A-F]{24}\$'; | |
" | |
<Result> | |
Type pg_xlog | |
ValuesFrom xlog_count | |
</Result> | |
</Query> | |
<Query pg_trig_disabled> | |
Statement " | |
SELECT count(*) AS pg_trig_disabled | |
FROM pg_trigger | |
WHERE tgenabled = 'D'; | |
" | |
<Result> | |
Type pg_trig_disabled | |
ValuesFrom pg_trig_disabled | |
</Result> | |
</Query> | |
<Query pg_hit_ratio> | |
Statement " | |
SELECT | |
round(100.*sd.blks_hit/(sd.blks_read+sd.blks_hit), 2) AS dhitratio, | |
d.datname | |
FROM pg_stat_database sd | |
JOIN pg_database d ON (d.oid=sd.datid) | |
WHERE sd.blks_read+sd.blks_hit<>0 AND d.datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_hit_ratio | |
InstancesFrom "datname" | |
ValuesFrom dhitratio | |
</Result> | |
</Query> | |
<Query pg_conflicts> | |
# only contains data for slaves because masters don't have conflicts | |
Statement " | |
SELECT datname, confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock | |
FROM pg_stat_database_conflicts | |
WHERE datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_conflicts | |
InstancesFrom "datname" | |
ValuesFrom confl_tablespace confl_lock confl_snapshot confl_bufferpin confl_deadlock | |
</Result> | |
</Query> | |
<Query a_server_location> | |
# For A Servers Only | |
Statement " | |
SELECT | |
CASE | |
WHEN pg_is_in_recovery() THEN 0 | |
ELSE ( | |
SELECT | |
('x00000000FF000000'::bit(64)::bigint * | |
('x' || lpad(split_part(pg_current_wal_lsn::text, '/', 1),16,'0'))::bit(64)::bigint) + | |
('x' || lpad(split_part(pg_current_wal_lsn::text, '/', 2),16,'0'))::bit(64)::bigint AS location | |
FROM pg_current_wal_lsn() | |
) | |
END AS location; | |
" | |
<Result> | |
Type pg_a_location | |
ValuesFrom location | |
</Result> | |
</Query> | |
<Query b_server_location> | |
# For B Servers Only | |
Statement " | |
SELECT | |
COALESCE(('x00000000FF000000'::bit(64)::bigint * | |
('x' || lpad(split_part(pg_last_wal_receive_lsn::text, '/', 1),16,'0'))::bit(64)::bigint) + | |
('x' || lpad(split_part(pg_last_wal_receive_lsn::text, '/', 2),16,'0'))::bit(64)::bigint,0) AS receive, | |
COALESCE(('x00000000FF000000'::bit(64)::bigint * | |
('x' || lpad(split_part(pg_last_wal_replay_lsn::text, '/', 1),16,'0'))::bit(64)::bigint) + | |
('x' || lpad(split_part(pg_last_wal_replay_lsn::text, '/', 2),16,'0'))::bit(64)::bigint,0) AS replay | |
FROM pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(); | |
" | |
<Result> | |
Type pg_b_location | |
ValuesFrom receive replay | |
</Result> | |
</Query> | |
<Query query_length> | |
Statement " | |
SELECT a.datname, COALESCE(b.query_length,0) AS seconds, COALESCE(ROUND(b.query_length)/60,0) AS minutes, COALESCE(ROUND(b.query_length)/60/60,0) AS hours | |
FROM pg_database a | |
LEFT JOIN ( | |
SELECT datname, EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)) AS query_length | |
FROM pg_stat_activity | |
WHERE state = 'active' AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) b ON a.datname = b.datname | |
WHERE a.datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_query_length | |
InstancesFrom "datname" | |
ValuesFrom seconds | |
</Result> | |
<Result> | |
Type pg_query_length_min | |
InstancesFrom "datname" | |
ValuesFrom minutes | |
</Result> | |
<Result> | |
Type pg_query_length_hour | |
InstancesFrom "datname" | |
ValuesFrom hours | |
</Result> | |
</Query> | |
<Query query_length_server> | |
Statement " | |
SELECT COALESCE(b.query_length,0) AS seconds, COALESCE(ROUND(b.query_length)/60,0) AS minutes, COALESCE(ROUND(b.query_length)/60/60,0) AS hours | |
FROM ( | |
SELECT EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)) AS query_length | |
FROM pg_stat_activity | |
WHERE state = 'active' AND datname NOT LIKE 'test%' | |
) b; | |
" | |
<Result> | |
Type pg_query_length | |
ValuesFrom seconds | |
</Result> | |
<Result> | |
Type pg_query_length_min | |
ValuesFrom minutes | |
</Result> | |
<Result> | |
Type pg_query_length_hour | |
ValuesFrom hours | |
</Result> | |
</Query> | |
<Query wait_length> | |
Statement " | |
SELECT a.datname, COALESCE(b.query_length,0) AS seconds | |
FROM pg_database a | |
LEFT JOIN ( | |
SELECT datname, EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)) AS query_length | |
FROM pg_stat_activity | |
WHERE COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' GROUP BY datname | |
) b ON a.datname = b.datname | |
WHERE a.datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_wait_length | |
InstancesFrom "datname" | |
ValuesFrom seconds | |
</Result> | |
</Query> | |
<Query wait_length_server> | |
Statement " | |
SELECT COALESCE(EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)),0) AS seconds | |
FROM pg_stat_activity | |
WHERE COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_wait_length | |
ValuesFrom seconds | |
</Result> | |
</Query> | |
<Query transaction_length> | |
Statement " | |
SELECT a.datname, COALESCE(b.xact_length,0) AS seconds | |
FROM pg_database a | |
LEFT JOIN ( | |
SELECT datname, EXTRACT(EPOCH FROM max(clock_timestamp()-xact_start)) AS xact_length | |
FROM pg_stat_activity | |
WHERE xact_start IS NOT NULL AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) b ON a.datname = b.datname | |
WHERE a.datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_xact_length | |
InstancesFrom "datname" | |
ValuesFrom seconds | |
</Result> | |
</Query> | |
<Query transaction_length_server> | |
Statement " | |
SELECT COALESCE(EXTRACT(EPOCH FROM max(clock_timestamp()-xact_start)),0) AS seconds | |
FROM pg_stat_activity | |
WHERE xact_start IS NOT NULL AND datname NOT LIKE 'test%'; | |
" | |
<Result> | |
Type pg_xact_length | |
ValuesFrom seconds | |
</Result> | |
</Query> | |
<Query query_database_xxx> | |
Statement " | |
SELECT | |
pg_database.datname AS database, | |
pg_database_size(pg_database.oid) AS size, | |
COALESCE(a.count,0) AS locked, | |
COALESCE(b.count,0) AS active, | |
COALESCE(c.count,0) AS idle, | |
COALESCE(d.count,0) AS idle_transaction, | |
COALESCE(e.count,0) AS fastpath, | |
COALESCE(f.count,0) AS disabled, | |
pg_stat_database.numbackends AS backends, | |
pg_stat_database.xact_commit AS commits, | |
pg_stat_database.xact_rollback AS rollbacks, | |
pg_stat_database.blks_read, | |
pg_stat_database.blks_hit, | |
pg_stat_database.tup_returned AS ret, | |
pg_stat_database.tup_fetched AS fetch, | |
pg_stat_database.tup_inserted AS ins, | |
pg_stat_database.tup_updated AS upd, | |
pg_stat_database.tup_deleted AS del, | |
pg_stat_database.deadlocks, | |
pg_stat_database.blk_read_time, | |
pg_stat_database.blk_write_time, | |
COALESCE(g.query_length,0) AS query_length_seconds, | |
COALESCE(ROUND(g.query_length)/60,0) AS query_length_minutes, | |
COALESCE(ROUND(g.query_length)/60/60,0) AS query_length_hours, | |
COALESCE(h.shared_buffers,0) AS shared_buffers | |
FROM pg_database | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS a USING (datname) | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'active' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS b USING (datname) | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'idle' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS c USING (datname) | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'idle in transaction' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS d USING (datname) | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'fastpath function call' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS e USING (datname) | |
LEFT JOIN ( | |
SELECT datname, count(*) | |
FROM pg_stat_activity | |
WHERE state = 'disabled' AND NOT COALESCE(wait_event_type = 'Lock', FALSE) AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) AS f USING (datname) | |
LEFT JOIN pg_stat_database USING (datname) | |
LEFT JOIN ( | |
SELECT datname, EXTRACT(EPOCH FROM max(clock_timestamp()-query_start)) AS query_length | |
FROM pg_stat_activity | |
WHERE state = 'active' AND datname NOT LIKE 'test%' | |
GROUP BY datname | |
) g USING (datname) | |
LEFT JOIN ( | |
SELECT count(*) * (( | |
SELECT pg_settings.setting::bigint AS setting | |
FROM pg_settings | |
WHERE pg_settings.name = 'block_size'::text | |
)) AS shared_buffers, | |
CASE | |
WHEN pg_database.datname IS NULL THEN 'Free Space'::name | |
ELSE pg_database.datname | |
END AS datname | |
FROM pg_buffercache | |
LEFT JOIN pg_database ON pg_buffercache.reldatabase = pg_database.oid | |
GROUP BY pg_database.datname | |
) h USING (datname) | |
WHERE AND datname NOT LIKE 'test%' | |
ORDER BY datname; | |
" | |
<Result> | |
Type pg_database_xxx | |
InstancesFrom "database" | |
ValuesFrom backends shared_buffers deadlocks commits rollbacks | |
</Result> | |
<Result> | |
Type pg_database_conns_xxx | |
InstancesFrom "database" | |
ValuesFrom size locked active idle idle_transaction fastpath disabled | |
</Result> | |
<Result> | |
Type pg_database_blocks_xxx | |
InstancesFrom "database" | |
ValuesFrom blks_read blks_hit blk_read_time blk_write_time | |
</Result> | |
<Result> | |
Type pg_database_queries_xxx | |
InstancesFrom "database" | |
ValuesFrom query_length_seconds query_length_minutes query_length_hours | |
</Result> | |
<Result> | |
Type pg_database_results_xxx | |
InstancesFrom "database" | |
ValuesFrom ret fetch ins upd del | |
</Result> | |
</Query> | |
<Database postgres> | |
Instance "localhost" | |
Host "localhost" | |
Port "5432" | |
User "postgres" | |
Query connections | |
Query connection_states | |
Query connection_state | |
Query database_size | |
Query buffercache | |
Query buffercache_databases | |
Query database_transactions | |
Query transactions | |
Query concurrent_txns | |
Query pg_hit_ratio | |
Query pg_xlog | |
Query connection_state_by_database | |
Query database_commit_ratio_by_database | |
Query database_stats_by_database | |
Query a_server_location | |
Query b_server_location | |
Query query_length | |
Query query_length_server | |
Query wait_length | |
Query wait_length_server | |
Query transaction_length | |
Query transaction_length_server | |
Query pg_conflicts | |
Query query_database_xxx | |
</Database> | |
</Plugin> | |
# vim: set ft=config : |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
You will need to add this to your collectd types.db.local file.