Queries used by jdbc_response profiles in MCS to collect PostgreSQL metrics

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• Unified Infrastructure Management for Mainframe
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

In DX UIM it is possible to monitor postgresql databases using the jdbc_response probe and MCS as described here: Monitor postgreSQL databases with MCS in DX UIM

What are the queries used to collect the metrics in the profiles?

 

 

Environment

DX UIM 20.4.* / 23.4.*

jdbc_response 1.24, 1.25

Cause

Guidance

Resolution

Below are the queries used in PostgreSQL MCS profile for monitoring the Postgres DB: 

• select *, round(cast(pg_database_size('{dbname}') as decimal) / 1024 / 1024, 2) as db_size, (select round(100.0 * sum(numbackends) / cast(current_setting('max_connections') as decimal), 2) as backends_used_pct from pg_stat_database), (SELECT count(*) as locks FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db ON db.oid = l.database WHERE (db.datname = '{dbname}' OR db.datname IS NULL) AND NOT pid = pg_backend_pid() and granted = 'f'), (SELECT round(100.0 * age(datfrozenxid) / cast(current_setting('autovacuum_freeze_max_age') as decimal), 2) as autovacuum_percent FROM pg_database where datname = '{dbname}') from pg_stat_database where datname = '{dbname}'
• SELECT count(*) as active, substr('State',1) as state, (SELECT count(*) as idle FROM pg_stat_activity where datname = '{dbname}' and state = 'idle'), (select count(*) as idle_in_transaction FROM pg_stat_activity where datname = '{dbname}' and state = 'idle in transaction'), (SELECT count(*) as idle_in_transaction_aborted FROM pg_stat_activity where datname = '{dbname}' and state = 'idle in transaction (aborted)') FROM pg_stat_activity where datname = '{dbname}' and state = 'active'
  
  
• SELECT cl.relname, Coalesce(seq_scan, 0) as seq_scan, Coalesce(seq_tup_read, 0) as sql_tup_read, Coalesce(idx_scan,0) as idx_scan, Coalesce(idx_tup_fetch,0) as idx_tup_fetch, Coalesce(n_tup_ins,0) as n_tup_ins, Coalesce(n_tup_upd,0) as n_tup_upd, Coalesce(n_tup_del,0) as n_tup_del, Coalesce(n_tup_hot_upd,0) as n_tup_hot_upd, Coalesce(n_live_tup,0) as n_live_tup, Coalesce(n_dead_tup,0) as n_dead_tup, Coalesce(heap_blks_read,0) as heap_blks_read, Coalesce(heap_blks_hit,0) as heap_blks_hit, Coalesce(idx_blks_read,0) as idx_blks_read, Coalesce(idx_blks_hit,0) as idx_blks_hit, Coalesce(toast_blks_read, 0) as toast_blks_read, Coalesce(toast_blks_hit, 0) as toast_blks_hit, Coalesce(tidx_blks_read, 0) as tidx_blks_read, Coalesce(tidx_blks_hit, 0) as tidx_blks_hit, Coalesce(vacuum_count,0) as vacuum_count, Coalesce(autovacuum_count,0) as autovacuum_count, Coalesce(analyze_count,0) as analyse_count, Coalesce(autoanalyze_count,0) as autoanalyse_count, pg_relation_size(stat.relid) / 1024 AS "table_size", CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE (pg_relation_size(cl.reltoastrelid)+ COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0)) / 1024 END AS toast_table_size_kb, COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=stat.relid)::int8, 0) / 1024 AS indexes_size_kb FROM pg_stat_all_tables stat JOIN pg_statio_all_tables statio ON stat.relid = statio.relid JOIN pg_class cl ON cl.oid=stat.relid WHERE statio.schemaname = 'public'
  
  
• SELECT cl.relname, Coalesce(seq_scan, 0) as seq_scan, Coalesce(seq_tup_read, 0) as sql_tup_read, Coalesce(idx_scan,0) as idx_scan, Coalesce(idx_tup_fetch,0) as idx_tup_fetch, Coalesce(n_tup_ins,0) as n_tup_ins, Coalesce(n_tup_upd,0) as n_tup_upd, Coalesce(n_tup_del,0) as n_tup_del, Coalesce(n_tup_hot_upd,0) as n_tup_hot_upd, Coalesce(n_live_tup,0) as n_live_tup, Coalesce(n_dead_tup,0) as n_dead_tup, Coalesce(heap_blks_read,0) as heap_blks_read, Coalesce(heap_blks_hit,0) as heap_blks_hit, Coalesce(idx_blks_read,0) as idx_blks_read, Coalesce(idx_blks_hit,0) as idx_blks_hit, Coalesce(toast_blks_read, 0) as toast_blks_read, Coalesce(toast_blks_hit, 0) as toast_blks_hit, Coalesce(tidx_blks_read, 0) as tidx_blks_read, Coalesce(tidx_blks_hit, 0) as tidx_blks_hit, Coalesce(vacuum_count,0) as vacuum_count, Coalesce(autovacuum_count,0) as autovacuum_count, Coalesce(analyze_count,0) as analyse_count, Coalesce(autoanalyze_count,0) as autoanalyse_count, pg_relation_size(stat.relid) / 1024 AS "table_size", CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE (pg_relation_size(cl.reltoastrelid)+ COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0)) / 1024 END AS toast_table_size_kb, COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=stat.relid)::int8, 0) / 1024 AS indexes_size_kb FROM pg_stat_all_tables stat JOIN pg_statio_all_tables statio ON stat.relid = statio.relid JOIN pg_class cl ON cl.oid=stat.relid WHERE statio.schemaname = 'public'
  
  
• SELECT cl.relname, Coalesce(seq_scan, 0) as seq_scan, Coalesce(seq_tup_read, 0) as sql_tup_read, Coalesce(idx_scan,0) as idx_scan, Coalesce(idx_tup_fetch,0) as idx_tup_fetch, Coalesce(n_tup_ins,0) as n_tup_ins, Coalesce(n_tup_upd,0) as n_tup_upd, Coalesce(n_tup_del,0) as n_tup_del, Coalesce(n_tup_hot_upd,0) as n_tup_hot_upd, Coalesce(n_live_tup,0) as n_live_tup, Coalesce(n_dead_tup,0) as n_dead_tup, Coalesce(heap_blks_read,0) as heap_blks_read, Coalesce(heap_blks_hit,0) as heap_blks_hit, Coalesce(idx_blks_read,0) as idx_blks_read, Coalesce(idx_blks_hit,0) as idx_blks_hit, Coalesce(toast_blks_read, 0) as toast_blks_read, Coalesce(toast_blks_hit, 0) as toast_blks_hit, Coalesce(tidx_blks_read, 0) as tidx_blks_read, Coalesce(tidx_blks_hit, 0) as tidx_blks_hit, Coalesce(vacuum_count,0) as vacuum_count, Coalesce(autovacuum_count,0) as autovacuum_count, Coalesce(analyze_count,0) as analyse_count, Coalesce(autoanalyze_count,0) as autoanalyse_count, pg_relation_size(stat.relid) / 1024 AS "table_size", CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE (pg_relation_size(cl.reltoastrelid)+ COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0)) / 1024 END AS toast_table_size_kb, COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=stat.relid)::int8, 0) / 1024 AS indexes_size_kb FROM pg_stat_all_tables stat JOIN pg_statio_all_tables statio ON stat.relid = statio.relid JOIN pg_class cl ON cl.oid=stat.relid WHERE statio.schemaname = 'pg_catalog'
  
  
• SELECT cl.relname, Coalesce(seq_scan, 0) as seq_scan, Coalesce(seq_tup_read, 0) as sql_tup_read, Coalesce(idx_scan,0) as idx_scan, Coalesce(idx_tup_fetch,0) as idx_tup_fetch, Coalesce(n_tup_ins,0) as n_tup_ins, Coalesce(n_tup_upd,0) as n_tup_upd, Coalesce(n_tup_del,0) as n_tup_del, Coalesce(n_tup_hot_upd,0) as n_tup_hot_upd, Coalesce(n_live_tup,0) as n_live_tup, Coalesce(n_dead_tup,0) as n_dead_tup, Coalesce(heap_blks_read,0) as heap_blks_read, Coalesce(heap_blks_hit,0) as heap_blks_hit, Coalesce(idx_blks_read,0) as idx_blks_read, Coalesce(idx_blks_hit,0) as idx_blks_hit, Coalesce(toast_blks_read, 0) as toast_blks_read, Coalesce(toast_blks_hit, 0) as toast_blks_hit, Coalesce(tidx_blks_read, 0) as tidx_blks_read, Coalesce(tidx_blks_hit, 0) as tidx_blks_hit, Coalesce(vacuum_count,0) as vacuum_count, Coalesce(autovacuum_count,0) as autovacuum_count, Coalesce(analyze_count,0) as analyse_count, Coalesce(autoanalyze_count,0) as autoanalyse_count, pg_relation_size(stat.relid) / 1024 AS "table_size", CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE (pg_relation_size(cl.reltoastrelid)+ COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0)) / 1024 END AS toast_table_size_kb, COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=stat.relid)::int8, 0) / 1024 AS indexes_size_kb FROM pg_stat_all_tables stat JOIN pg_statio_all_tables statio ON stat.relid = statio.relid JOIN pg_class cl ON cl.oid=stat.relid WHERE (statio.schemaname = 'public' OR statio.schemaname = 'pg_catalog')
  
  
• select (relname||'\'||indexrelname) as row_key, * from pg_stat_user_indexes
  
  
• select (relname||'\'||indexrelname) as row_key, * from pg_stat_sys_indexes
  
  
• select (relname||'\'||indexrelname) as row_key, * from pg_stat_all_indexes
  
  
• SELECT cl.relname, ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as disk_hits, (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0)) as total_hits, CASE WHEN coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0) > 0 THEN round((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0))::numeric / ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) + (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric)*100.0,2) ELSE 0 END as pct_disk_hits, CASE WHEN coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0) > 0 THEN round((coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric / ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) + (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric)*100.0,2) ELSE 0 END as pct_cache_hits FROM pg_stat_all_tables stat JOIN pg_statio_user_tables statio ON stat.relid = statio.relid JOIN pg_class cl ON cl.oid=stat.relid
  
  
• SELECT cl.relname, ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as disk_hits, (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0)) as total_hits, CASE WHEN coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0) > 0 THEN round((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0))::numeric / ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) + (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric)*100.0,2) ELSE 0 END as pct_disk_hits, CASE WHEN coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0) > 0 THEN round((coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric / ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) + (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric)*100.0,2) ELSE 0 END as pct_cache_hits FROM pg_stat_all_tables stat JOIN pg_statio_user_tables statio ON stat.relid = statio.relid JOIN pg_class cl ON cl.oid=stat.relid
  
  
• SELECT cl.relname, ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as disk_hits, (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0)) as total_hits, CASE WHEN coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0) > 0 THEN round((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0))::numeric / ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) + (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric)*100.0,2) ELSE 0 END as pct_disk_hits, CASE WHEN coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0) > 0 THEN round((coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric / ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) + (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric)*100.0,2) ELSE 0 END as pct_cache_hits FROM pg_stat_all_tables stat JOIN pg_statio_sys_tables statio ON stat.relid = statio.relid JOIN pg_class cl ON cl.oid=stat.relid WHERE statio.schemaname = 'pg_catalog'
  
  
• SELECT cl.relname, ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as disk_hits, (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0)) as total_hits, CASE WHEN coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0) > 0 THEN round((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0))::numeric / ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) + (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric)*100.0,2) ELSE 0 END as pct_disk_hits, CASE WHEN coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0) + coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0) > 0 THEN round((coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric / ((coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) + (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))::numeric)*100.0,2) ELSE 0 END as pct_cache_hits FROM pg_stat_all_tables stat JOIN pg_statio_all_tables statio ON stat.relid = statio.relid JOIN pg_class cl ON cl.oid=stat.relid WHERE (statio.schemaname = 'public' OR statio.schemaname = 'pg_catalog')
  
  
• select s.relname, (select count(*) from pg_locks where mode = 'AccessShareLock' and relation = s.relid and granted = 'f') as accessshare, (select count(*) from pg_locks where mode = 'RowExclusiveLock' and relation = s.relid and granted = 'f') as rowexclusive,(select count(*) from pg_locks where mode = 'ShareLock' and relation = s.relid and granted = 'f') as share,(select count(*) from pg_locks where mode = 'ExclusiveLock' and relation = s.relid and granted = 'f') as exclusive from pg_statio_user_tables s
  
  
• select s.relname, (select count(*) from pg_locks where mode = 'AccessShareLock' and relation = s.relid and granted = 'f') as accessshare, (select count(*) from pg_locks where mode = 'RowExclusiveLock' and relation = s.relid and granted = 'f') as rowexclusive,(select count(*) from pg_locks where mode = 'ShareLock' and relation = s.relid and granted = 'f') as share,(select count(*) from pg_locks where mode = 'ExclusiveLock' and relation = s.relid and granted = 'f') as exclusive from pg_statio_user_tables s
  
  
• select s.relname, (select count(*) from pg_locks where mode = 'AccessShareLock' and relation = s.relid and granted = 'f') as accessshare, (select count(*) from pg_locks where mode = 'RowExclusiveLock' and relation = s.relid and granted = 'f') as rowexclusive,(select count(*) from pg_locks where mode = 'ShareLock' and relation = s.relid and granted = 'f') as share,(select count(*) from pg_locks where mode = 'ExclusiveLock' and relation = s.relid and granted = 'f') as exclusive from pg_statio_sys_tables s
  
  
• select s.relname, (select count(*) from pg_locks where mode = 'AccessShareLock' and relation = s.relid and granted = 'f') as accessshare, (select count(*) from pg_locks where mode = 'RowExclusiveLock' and relation = s.relid and granted = 'f') as rowexclusive,(select count(*) from pg_locks where mode = 'ShareLock' and relation = s.relid and granted = 'f') as share,(select count(*) from pg_locks where mode = 'ExclusiveLock' and relation = s.relid and granted = 'f') as exclusive from pg_statio_all_tables s

Additional Information

Related KB: Monitor postgreSQL databases with MCS in DX UIM