Skip to content

Instantly share code, notes, and snippets.

@nerzhul

nerzhul/collectd-postgresql.conf

Created June 16, 2017 09:19
Show Gist options
  • Save nerzhul/7d9bab80136f8dd5dde357c44043ef70 to your computer and use it in GitHub Desktop.
Save nerzhul/7d9bab80136f8dd5dde357c44043ef70 to your computer and use it in GitHub Desktop.
Download ZIP
collectd-postgresql.conf (Ansible template)
Raw
collectd-postgresql.conf
{{ ansible_managed | comment }}
{% if collectd_plugin_postgresql %}
LoadPlugin postgresql
<Plugin postgresql>
<Query query_plans>
Statement "SELECT sum(seq_scan) AS seq, \
sum(seq_tup_read) AS seq_tup_read, \
sum(idx_scan) AS idx, \
sum(idx_tup_fetch) AS idx_tup_fetch \
FROM pg_stat_user_tables;"
<Result>
Type "pg_scan"
InstancePrefix "seq"
ValuesFrom "seq"
</Result>
<Result>
Type "pg_scan"
InstancePrefix "seq_tup_read"
ValuesFrom "seq_tup_read"
</Result>
<Result>
Type "pg_scan"
InstancePrefix "idx"
ValuesFrom "idx"
</Result>
<Result>
Type "pg_scan"
InstancePrefix "idx_tup_fetch"
ValuesFrom "idx_tup_fetch"
</Result>
</Query>
<Query query_plans_by_table>
Statement "SELECT schemaname, relname, \
coalesce(seq_scan, 0) AS seq, \
coalesce(seq_tup_read, 0) AS seq_tup_read, \
coalesce(idx_scan, 0) AS idx, \
coalesce(idx_tup_fetch, 0) AS idx_tup_fetch \
FROM pg_stat_user_tables;"
<Result>
Type "pg_scan"
InstancePrefix "seq"
InstancesFrom "schemaname" "relname"
ValuesFrom "seq"
</Result>
<Result>
Type "pg_scan"
InstancePrefix "seq_tup_read"
InstancesFrom "schemaname" "relname"
ValuesFrom "seq_tup_read"
</Result>
<Result>
Type "pg_scan"
InstancePrefix "idx"
InstancesFrom "schemaname" "relname"
ValuesFrom "idx"
</Result>
<Result>
Type "pg_scan"
InstancePrefix "idx_tup_fetch"
InstancesFrom "schemaname" "relname"
ValuesFrom "idx_tup_fetch"
</Result>
</Query>
<Query table_states>
Statement "SELECT sum(n_live_tup) AS live, sum(n_dead_tup) AS dead \
FROM pg_stat_user_tables;"
<Result>
Type "pg_n_tup_g"
InstancePrefix "live"
ValuesFrom "live"
</Result>
<Result>
Type "pg_n_tup_g"
InstancePrefix "dead"
ValuesFrom "dead"
</Result>
MinVersion 80300
</Query>
<Query queries>
Statement "SELECT sum(n_tup_ins) AS ins, \
sum(n_tup_upd) AS upd, \
sum(n_tup_del) AS del, \
sum(n_tup_hot_upd) AS hot_upd \
FROM pg_stat_user_tables;"
<Result>
Type "pg_n_tup_c"
InstancePrefix "ins"
ValuesFrom "ins"
</Result>
<Result>
Type "pg_n_tup_c"
InstancePrefix "upd"
ValuesFrom "upd"
</Result>
<Result>
Type "pg_n_tup_c"
InstancePrefix "del"
ValuesFrom "del"
</Result>
<Result>
Type "pg_n_tup_c"
InstancePrefix "hot_upd"
ValuesFrom "hot_upd"
</Result>
MinVersion 80300
</Query>
<Query queries_by_table>
Statement "SELECT schemaname, relname, \
n_tup_ins AS ins, \
n_tup_upd AS upd, \
n_tup_del AS del, \
n_tup_hot_upd AS hot_upd \
FROM pg_stat_user_tables;"
<Result>
Type "pg_n_tup_c"
InstancePrefix "ins"
InstancesFrom "schemaname" "relname"
ValuesFrom "ins"
</Result>
<Result>
Type "pg_n_tup_c"
InstancePrefix "upd"
InstancesFrom "schemaname" "relname"
ValuesFrom "upd"
</Result>
<Result>
Type "pg_n_tup_c"
InstancePrefix "del"
InstancesFrom "schemaname" "relname"
ValuesFrom "del"
</Result>
<Result>
Type "pg_n_tup_c"
InstancePrefix "hot_upd"
InstancesFrom "schemaname" "relname"
ValuesFrom "hot_upd"
</Result>
MinVersion 80300
</Query>
<Query disk_usage>
Statement "SELECT pg_database_size($1) AS size;"
Param database
<Result>
Type pg_db_size
ValuesFrom "size"
</Result>
</Query>
<Query transactions>
Statement "SELECT xact_commit,xact_rollback FROM pg_stat_database WHERE datname=$1;"
Param database
<Result>
Type pg_xact
InstancePrefix "commit"
ValuesFrom "xact_commit"
</Result>
<Result>
Type pg_xact
InstancePrefix "rollback"
ValuesFrom "xact_rollback"
</Result>
</Query>
<Query pg_locks>
Statement "SELECT tmp.mode,COALESCE(count,0) as count FROM \
(VALUES ('accesssharelock'),('rowsharelock'),('rowexclusivelock'),('shareupdateexclusivelock'),('sharelock'),('sharerowexclusivelock'),('exclusivelock'),('accessexclusivelock')) AS tmp(mode) \
LEFT JOIN \
(SELECT lower(mode) AS mode,count(*) AS count \
FROM pg_locks WHERE database IS NOT NULL \
GROUP BY lower(mode) \
) AS tmp2 \
ON tmp.mode=tmp2.mode ORDER BY 1;"
<Result>
Type pg_locks
InstancesFrom "mode"
ValuesFrom "count"
</Result>
</Query>
<Query pg_locks_perdb>
Statement "SELECT tmp.mode,COALESCE(count,0) as count FROM \
(VALUES ('accesssharelock'),('rowsharelock'),('rowexclusivelock'),('shareupdateexclusivelock'),('sharelock'),('sharerowexclusivelock'),('exclusivelock'),('accessexclusivelock')) AS tmp(mode) \
LEFT JOIN \
(SELECT lower(mode) AS mode,count(*) AS count \
FROM pg_locks WHERE database IS NOT NULL AND database=(SELECT oid FROM pg_database WHERE datname=$1) \
GROUP BY lower(mode) \
) AS tmp2 \
ON tmp.mode=tmp2.mode ORDER BY 1;"
Param database
<Result>
Type pg_locks
InstancesFrom "mode"
ValuesFrom "count"
</Result>
</Query>
<Query pg_connections>
Statement "SELECT tmp.mstate AS state,COALESCE(count,0) as count FROM \
(VALUES ('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate) \
LEFT JOIN \
(SELECT CASE WHEN waiting THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END AS mstate, \
count(*) AS count \
FROM pg_stat_activity WHERE pid != pg_backend_pid() \
GROUP BY CASE WHEN waiting THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END \
) AS tmp2 \
ON tmp.mstate=tmp2.mstate \
ORDER BY 1;"
<Result>
Type pg_connections
InstancesFrom "state"
ValuesFrom "count"
</Result>
MaxVersion 90599
</Query>
<Query pg_connections>
Statement "SELECT tmp.mstate AS state,COALESCE(count,0) as count FROM \
(VALUES ('active'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate) \
LEFT JOIN \
(SELECT CASE WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END AS mstate, \
count(*) AS count \
FROM pg_stat_activity WHERE pid != pg_backend_pid() \
GROUP BY CASE WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END \
) AS tmp2 \
ON tmp.mstate=tmp2.mstate \
ORDER BY 1;"
<Result>
Type pg_connections
InstancesFrom "state"
ValuesFrom "count"
</Result>
MinVersion 90600
</Query>
<Query pg_connections_perdb>
Statement "SELECT tmp.mstate AS state,COALESCE(count,0) as count FROM \
(VALUES ('active'),('waiting'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate) \
LEFT JOIN \
(SELECT CASE WHEN waiting THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END AS mstate, \
count(*) AS count \
FROM pg_stat_activity WHERE pid != pg_backend_pid() AND datname=$1 \
GROUP BY CASE WHEN waiting THEN 'waiting' WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END \
) AS tmp2 \
ON tmp.mstate=tmp2.mstate \
ORDER BY 1;"
Param database
<Result>
Type pg_connections
InstancesFrom "state"
ValuesFrom "count"
</Result>
MaxVersion 90599
</Query>
<Query pg_connections_perdb>
Statement "SELECT tmp.mstate AS state,COALESCE(count,0) as count FROM \
(VALUES ('active'),('idle'),('idletransaction'),('unknown')) AS tmp(mstate) \
LEFT JOIN \
(SELECT CASE WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END AS mstate, \
count(*) AS count \
FROM pg_stat_activity WHERE pid != pg_backend_pid() AND datname=$1 \
GROUP BY CASE WHEN state='idle' THEN 'idle' WHEN state LIKE 'idle in transaction%' THEN 'idletransaction' WHEN state='disabled' THEN 'unknown' WHEN query='<insufficient privilege>' THEN 'unknown' ELSE 'active' END \
) AS tmp2 \
ON tmp.mstate=tmp2.mstate \
ORDER BY 1;"
Param database
<Result>
Type pg_connections
InstancesFrom "state"
ValuesFrom "count"
</Result>
MinVersion 90600
</Query>
<Query disk_io>
Statement "SELECT coalesce(sum(heap_blks_read), 0) AS heap_read, \
coalesce(sum(heap_blks_hit), 0) AS heap_hit, \
coalesce(sum(idx_blks_read), 0) AS idx_read, \
coalesce(sum(idx_blks_hit), 0) AS idx_hit, \
coalesce(sum(toast_blks_read), 0) AS toast_read, \
coalesce(sum(toast_blks_hit), 0) AS toast_hit, \
coalesce(sum(tidx_blks_read), 0) AS tidx_read, \
coalesce(sum(tidx_blks_hit), 0) AS tidx_hit \
FROM pg_statio_user_tables;"
<Result>
Type "pg_blks"
InstancePrefix "heap_read"
ValuesFrom "heap_read"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "heap_hit"
ValuesFrom "heap_hit"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "idx_read"
ValuesFrom "idx_read"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "idx_hit"
ValuesFrom "idx_hit"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "toast_read"
ValuesFrom "toast_read"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "toast_hit"
ValuesFrom "toast_hit"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "tidx_read"
ValuesFrom "tidx_read"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "tidx_hit"
ValuesFrom "tidx_hit"
</Result>
</Query>
<Query disk_io_by_table>
Statement "SELECT schemaname, relname, \
coalesce(heap_blks_read, 0) AS heap_read, \
coalesce(heap_blks_hit, 0) AS heap_hit, \
coalesce(idx_blks_read, 0) AS idx_read, \
coalesce(idx_blks_hit, 0) AS idx_hit, \
coalesce(toast_blks_read, 0) AS toast_read, \
coalesce(toast_blks_hit, 0) AS toast_hit, \
coalesce(tidx_blks_read, 0) AS tidx_read, \
coalesce(tidx_blks_hit, 0) AS tidx_hit \
FROM pg_statio_user_tables;"
<Result>
Type "pg_blks"
InstancePrefix "heap_read"
InstancesFrom "schemaname" "relname"
ValuesFrom "heap_read"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "heap_hit"
InstancesFrom "schemaname" "relname"
ValuesFrom "heap_hit"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "idx_read"
InstancesFrom "schemaname" "relname"
ValuesFrom "idx_read"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "idx_hit"
InstancesFrom "schemaname" "relname"
ValuesFrom "idx_hit"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "toast_read"
InstancesFrom "schemaname" "relname"
ValuesFrom "toast_read"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "toast_hit"
InstancesFrom "schemaname" "relname"
ValuesFrom "toast_hit"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "tidx_read"
InstancesFrom "schemaname" "relname"
ValuesFrom "tidx_read"
</Result>
<Result>
Type "pg_blks"
InstancePrefix "tidx_hit"
InstancesFrom "schemaname" "relname"
ValuesFrom "tidx_hit"
</Result>
</Query>
<Query pg_bgwriter>
Statement "SELECT buffers_checkpoint,buffers_clean,buffers_backend,buffers_alloc FROM pg_stat_bgwriter;"
<Result>
Type buffer_size
InstancePrefix "buffers_checkpoint"
ValuesFrom "buffers_checkpoint"
</Result>
<Result>
Type buffer_size
InstancePrefix "buffers_clean"
ValuesFrom "buffers_clean"
</Result>
<Result>
Type buffer_size
InstancePrefix "buffers_backend"
ValuesFrom "buffers_backend"
</Result>
<Result>
Type buffer_size
InstancePrefix "buffers_alloc"
ValuesFrom "buffers_alloc"
</Result>
</Query>
<Query pg_checkpoints>
Statement "SELECT checkpoints_timed,checkpoints_req FROM pg_stat_bgwriter;"
<Result>
Type checkpoint_blocks
InstancePrefix "checkpoints_timed"
ValuesFrom "checkpoints_timed"
</Result>
<Result>
Type checkpoint_blocks
InstancePrefix "checkpoints_req"
ValuesFrom "checkpoints_req"
</Result>
</Query>
<Query pg_autovacuum>
Statement "SELECT 'active' as autovacuum, count(*) as count FROM pg_stat_activity WHERE query LIKE 'autovacuum: %'"
<Result>
Type pg_autovacuum
InstancesFrom "autovacuum"
ValuesFrom "count"
</Result>
</Query>
<Query slow_queries>
Statement "SELECT count(*) AS count FROM pg_stat_activity WHERE state='active' \
AND now() - query_start > '1 seconds'::interval AND query ~* '^(insert|update|delete|select)';"
<Result>
Type pg_slowquery
ValuesFrom "count"
</Result>
</Query>
<Query slow_queries_perdb>
Statement "SELECT count(*) AS count FROM pg_stat_activity WHERE state='active' \
AND now() - query_start > '1 seconds'::interval AND query ~* '^(insert|update|delete|select)' \
AND datname=$1;"
Param database
<Result>
Type pg_slowquery
ValuesFrom "count"
</Result>
</Query>
<Query slave_lag>
Statement "SELECT CASE WHEN pg_is_in_recovery = 'false' THEN 0 \
ELSE COALESCE(ROUND(EXTRACT(epoch FROM now() - pg_last_xact_replay_timestamp())), 0) END AS seconds \
FROM pg_is_in_recovery()"
<Result>
Type counter
InstancePrefix "slave_lag"
ValuesFrom "seconds"
</Result>
</Query>
{% if postgresql_instances is defined %}
{% for k,db_instance in postgresql_instances.iteritems() %}
<Database "postgres">
Host "localhost"
User "postgres"
# Password ""
Port "{{ db_instance.port }}"
Query pg_locks
Query pg_connections
Query pg_bgwriter
Query pg_checkpoints
Query pg_autovacuum
Query disk_usage
Query disk_io
Query disk_io_by_table
Query slow_queries
Query slave_lag
</Database>
{% for db in db_instance.databases %}
<Database "{{ db.name }}">
Host "localhost"
User "postgres"
# Password ""
Port "{{ db_instance.port }}"
Query pg_locks_perdb
Query pg_connections_perdb
Query disk_usage
Query table_states
Query queries
Query queries_by_table
Query query_plans
Query query_plans_by_table
Query disk_io
Query disk_io_by_table
Query slow_queries_perdb
Query transactions
</Database>
{% endfor %}
{% endfor %}
{% endif %}
</Plugin>
{% endif %}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment