module PgMetrics::Metrics
Constants
- Functions
- IndexIdealSizes
- IndexSizes
- IndexStatio
- IndexStats
- Locks
- TableFreeSpace
- TableSizes
- TableStatio
- TableStats
Public Class Methods
conn_str(conn_info, dbname = "postgres")
click to toggle source
# File lib/pg_metrics/metrics.rb, line 54 def self.conn_str(conn_info, dbname = "postgres") [conn_info, %(dbname=#{dbname})].join(" ") end
database_metrics(server_version)
click to toggle source
# File lib/pg_metrics/metrics.rb, line 209 def self.database_metrics(server_version) { Functions => { prefix: %w(function), query: Gem::Version.new(server_version) >= Gem::Version.new('8.4') \ ? %q{SELECT schemaname AS key, array_to_string(ARRAY[funcname, '-', pronargs::TEXT, CASE WHEN pronargs = 0 THEN '' ELSE '-' || array_to_string(CASE WHEN pronargs > 16 THEN ARRAY(SELECT args[i] FROM generate_series(1, 8) AS _(i)) || '-'::TEXT || ARRAY(SELECT args[i] FROM generate_series(pronargs - 7, pronargs) AS _ (i)) || funcid::TEXT ELSE args END, '-') END], '') AS key2, calls, total_time, self_time FROM (SELECT funcid, schemaname, funcname::TEXT, pronargs, ARRAY(SELECT typname::TEXT FROM pg_type JOIN (SELECT args.i, proargtypes[args.i] AS typid FROM pg_catalog.generate_series(0, array_upper(proargtypes, 1)) AS args (i)) AS args (i, typid) ON typid = pg_type.oid ORDER BY i) AS args, calls, total_time, self_time FROM pg_stat_user_functions JOIN pg_proc ON pg_proc.oid = funcid WHERE schemaname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')) AS funcs} : nil }, Locks => { prefix: %w(table), query: %q{SELECT nspname AS key, CASE rel.relkind WHEN 'r' THEN rel.relname ELSE crel.relname END AS key2, CASE rel.relkind WHEN 'r' THEN 'locks' ELSE 'index' END AS key3, CASE rel.relkind WHEN 'r' THEN mode ELSE rel.relname END AS key4, CASE rel.relkind WHEN 'r' THEN NULL ELSE 'locks' END AS key5, CASE rel.relkind WHEN 'r' THEN NULL ELSE mode END AS key6, count(*) AS value FROM pg_locks JOIN pg_database dat ON dat.oid = database JOIN pg_class rel ON rel.oid = relation LEFT JOIN pg_index ON indexrelid = rel.oid LEFT JOIN pg_class crel ON indrelid = crel.oid JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace WHERE locktype = 'relation' AND nspname <> 'pg_catalog' AND rel.relkind in ('r', 'i') GROUP BY 1, 2, 3, 4, 5, 6} }, TableSizes => { prefix: %w(table), query: %q{SELECT n.nspname AS key, r.relname AS key2, pg_relation_size(r.oid) AS size, pg_total_relation_size(r.oid) AS total_size FROM pg_class r JOIN pg_namespace n ON r.relnamespace = n.oid WHERE r.relkind = 'r' AND n.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')} }, TableFreeSpace => { prefix: %w(table), query: Gem::Version.new(server_version) >= Gem::Version.new('8.4') \ ? %{SELECT n.nspname AS key, t.relname AS key2, COALESCE((SELECT sum(pg_freespace.avail) AS sum FROM pg_freespace(t.oid::regclass) AS pg_freespace(blkno, avail)), 0::bigint) AS free_space FROM pg_class t JOIN pg_namespace n ON t.relnamespace = n.oid WHERE t.relkind = 'r'::"char" AND n.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')} : %{SELECT n.nspname AS key, t.relname AS key2, fsm.bytes AS free_space FROM pg_class t JOIN pg_namespace n ON t.relnamespace = n.oid LEFT JOIN (SELECT fsm.relfilenode, sum(fsm.bytes) AS bytes FROM pg_freespacemap_pages fsm JOIN pg_database db ON db.oid = fsm.reldatabase AND db.datname = current_database() GROUP BY fsm.relfilenode) fsm ON t.relfilenode = fsm.relfilenode WHERE t.relkind = 'r'::"char" AND n.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')} }, IndexSizes => { prefix: %w(table), query: %q{SELECT n.nspname AS key, cr.relname AS key2, 'index' AS key3, ci.relname AS key4, pg_relation_size(ci.oid) AS size FROM pg_class ci JOIN pg_index i ON ci.oid = i.indexrelid JOIN pg_class cr ON cr.oid = i.indrelid JOIN pg_namespace n on ci.relnamespace = n.oid WHERE ci.relkind = 'i' AND cr.relkind = 'r' AND n.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')} }, IndexIdealSizes => { prefix: %w(table), query: %{SELECT pg_namespace.nspname AS key, rel.relname AS key2, 'index' AS key3, idx.relname AS key4, ((ceil(idx.reltuples * ((constants.index_tuple_header_size + constants.item_id_data_size + CASE WHEN (COALESCE(sum(CASE WHEN statts.staattnotnull THEN 0 ELSE 1 END), 0::bigint) + ((SELECT COALESCE(sum(CASE WHEN atts.attnotnull THEN 0 ELSE 1 END), 0::bigint) AS "coalesce" FROM pg_attribute atts JOIN (SELECT pg_index.indkey[the.i] AS attnum FROM generate_series(0, pg_index.indnatts - 1) the(i)) cols ON atts.attnum = cols.attnum WHERE atts.attrelid = pg_index.indrelid))) > 0 THEN (SELECT the.null_bitmap_size + constants.max_align - CASE WHEN (the.null_bitmap_size % constants.max_align) = 0 THEN constants.max_align ELSE the.null_bitmap_size % constants.max_align END FROM (VALUES (ceil(pg_index.indnatts::real / 8)::int)) the (null_bitmap_size)) ELSE 0 END)::double precision + COALESCE(sum(statts.stawidth::double precision * (1::double precision - statts.stanullfrac)), 0::double precision) + COALESCE((SELECT sum(atts.stawidth::double precision * (1::double precision - atts.stanullfrac)) AS sum FROM pg_statistic atts JOIN (SELECT pg_index.indkey[the.i] AS attnum FROM generate_series(0, pg_index.indnatts - 1) the(i)) cols ON atts.staattnum = cols.attnum WHERE atts.starelid = pg_index.indrelid), 0::double precision)) / (constants.block_size - constants.page_header_data_size::numeric - constants.special_space::numeric)::double precision) + constants.index_metadata_pages::double precision) * constants.block_size::double precision)::bigint AS ideal_size FROM pg_index JOIN pg_class idx ON pg_index.indexrelid = idx.oid JOIN pg_class rel ON pg_index.indrelid = rel.oid JOIN pg_namespace ON idx.relnamespace = pg_namespace.oid LEFT JOIN (SELECT pg_statistic.starelid, pg_statistic.staattnum, pg_statistic.stanullfrac, pg_statistic.stawidth, pg_attribute.attnotnull AS staattnotnull FROM pg_statistic JOIN pg_attribute ON (pg_statistic.starelid,pg_statistic.staattnum) = (pg_attribute.attrelid, pg_attribute.attnum)) statts ON statts.starelid = idx.oid CROSS JOIN (SELECT current_setting('block_size'::text)::numeric AS block_size, CASE WHEN "substring"(version(), 12, 3) = ANY (ARRAY['8.0'::text, '8.1'::text, '8.2'::text]) THEN 27 ELSE 23 END AS tuple_header_size, CASE WHEN version() ~ 'mingw32'::text THEN 8 ELSE 4 END AS max_align, 8 AS index_tuple_header_size, 4 AS item_id_data_size, 24 AS page_header_data_size, 0 AS special_space, 1 AS index_metadata_pages) AS constants WHERE nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast') GROUP BY pg_namespace.nspname, rel.relname, rel.oid, idx.relname, idx.reltuples, idx.relpages, pg_index.indexrelid, pg_index.indrelid, pg_index.indkey, pg_index.indnatts, constants.block_size, constants.tuple_header_size, constants.max_align, constants.index_tuple_header_size, constants.item_id_data_size, constants.page_header_data_size, constants.index_metadata_pages, constants.special_space;} }, TableStatio => { prefix: %w(table), query: %q{SELECT schemaname AS key, relname AS key2, 'statio' AS key3, nullif(heap_blks_read, 0) AS heap_blks_read, nullif(heap_blks_hit, 0) AS heap_blks_hit, nullif(idx_blks_read, 0) AS idx_blks_read, nullif(idx_blks_hit, 0) AS idx_blks_hit, nullif(toast_blks_read, 0) AS toast_blks_read, nullif(toast_blks_hit, 0) AS toast_blks_hit, nullif(tidx_blks_read, 0) AS tidx_blks_read, nullif(tidx_blks_hit, 0) AS tidx_blks_hit FROM pg_statio_user_tables} }, TableStats => { prefix: %w(table), query: Gem::Version.new(server_version) >= Gem::Version.new('9.1') \ ? %q{SELECT schemaname AS key, relname AS key2, 'stat' AS key3, nullif(seq_scan, 0) AS seq_scan, nullif(seq_tup_read, 0) AS seq_tup_read, nullif(idx_scan, 0) AS idx_scan, nullif(idx_tup_fetch, 0) AS idx_tup_fetch, nullif(n_tup_ins, 0) AS n_tup_ins, nullif(n_tup_upd, 0) AS n_tup_upd, nullif(n_tup_del, 0) AS n_tup_del, nullif(n_tup_hot_upd, 0) AS n_tup_hot_upd, nullif(n_live_tup, 0) AS n_live_tup, nullif(n_dead_tup, 0) AS n_dead_tup, nullif(vacuum_count, 0) AS vacuum_count, nullif(autovacuum_count, 0) AS autovacuum_count, nullif(analyze_count, 0) AS analyze_count, nullif(autoanalyze_count, 0) AS autoanalyze_count FROM pg_stat_user_tables} \ : %q{SELECT schemaname AS key, relname AS key2, 'stat' AS key3, nullif(seq_scan, 0) AS seq_scan, nullif(seq_tup_read, 0) AS seq_tup_read, nullif(idx_scan, 0) AS idx_scan, nullif(idx_tup_fetch, 0) AS idx_tup_fetch, nullif(n_tup_ins, 0) AS n_tup_ins, nullif(n_tup_upd, 0) AS n_tup_upd, nullif(n_tup_del, 0) AS n_tup_del, nullif(n_tup_hot_upd, 0) AS n_tup_hot_upd, nullif(n_live_tup, 0) AS n_live_tup, nullif(n_dead_tup, 0) AS n_dead_tup FROM pg_stat_user_tables}, }, IndexStatio => { prefix: %w(table), query: %q{SELECT schemaname AS key, relname AS key2, 'index' AS key3, indexrelname AS key4, 'statio' AS key5, nullif(idx_blks_read, 0) AS idx_blks_read, nullif(idx_blks_hit, 0) AS idx_blks_hit FROM pg_statio_user_indexes}, }, IndexStats => { prefix: %w(table), query: %q{SELECT schemaname AS key, relname AS key2, 'index' AS key3, indexrelname AS key4, 'stat' AS key5, nullif(idx_scan, 0) AS idx_scan, nullif(idx_tup_read, 0) AS idx_tup_read, nullif(idx_tup_fetch, 0) AS idx_tup_fetch FROM pg_stat_user_indexes} } } end
decode_xlog_location(val)
click to toggle source
# File lib/pg_metrics/metrics.rb, line 109 def self.decode_xlog_location(val) return val if val.nil? if (m = val.match(%r{([A-Fa-f0-9]+)/([A-Fa-f0-9]+)})) return (m[1].hex << 32) + m[2].hex end val end
fetch_database_metrics(app_name, conn_info, dbname, select_names, regexp = nil)
click to toggle source
# File lib/pg_metrics/metrics.rb, line 29 def self.fetch_database_metrics(app_name, conn_info, dbname, select_names, regexp = nil) metrics = [] conn = make_conn(conn_str(conn_info, dbname), app_name) server_version = conn.parameter_status("server_version") select_metrics = database_metrics(server_version).select { |k, _v| select_names.include? k } select_metrics.values.each do |m| metrics += fetch_metrics(conn, ["database", dbname] + m[:prefix], m[:query]) end conn.finish filter_metrics(metrics, regexp) end
fetch_instance_metrics(app_name, conn_info, regexp = nil)
click to toggle source
# File lib/pg_metrics/metrics.rb, line 18 def self.fetch_instance_metrics(app_name, conn_info, regexp = nil) metrics = [] conn = make_conn(conn_str(conn_info), app_name) server_version = conn.parameter_status("server_version") instance_metrics(server_version).values.each do |m| metrics += fetch_metrics(conn, m[:prefix], m[:query]) end conn.finish filter_metrics(metrics, regexp) end
fetch_metrics(conn, keys, query)
click to toggle source
# File lib/pg_metrics/metrics.rb, line 58 def self.fetch_metrics(conn, keys, query) metrics = [] return metrics if query.nil? timestamp = Time.now.to_i conn.exec(query) do |result| if result.nfields == 1 && result.ntuples == 1 # Typically result of SHOW command metrics << format_metric(keys, result.getvalue(0, 0), timestamp) elsif result.nfields >= 2 && result.fields.first == "key" if result.fields.last == "value" # Omit "value" column from metric name nkeys = result.nfields - 1 result.each_row do |row| mkeys = row.first(nkeys) value = row.last metrics << format_metric(keys + mkeys, value, timestamp) end else # Use any column named key* as part of the metric name. # Any other columns are named values. nkeys = result.fields.take_while { |f| f =~ /^key/ }.count keycols = result.fields.first(nkeys) nvals = result.nfields - nkeys valcols = result.fields.last(nvals) result.each do |tup| mkeys = keycols.map { |col| tup[col] } valcols.each do |key| value = tup[key] metrics << format_metric(keys + mkeys + [key], value, timestamp) end end end else # We've got a single-row result where columns are named values result[0].each do |key, value| metrics << format_metric(keys + [key], value, timestamp) end end end metrics end
filter_metrics(metrics, regexp = nil)
click to toggle source
# File lib/pg_metrics/metrics.rb, line 48 def self.filter_metrics(metrics, regexp = nil) metrics.reject! { |m| m[1].nil? } metrics.reject! { |m| m[0].any? { |k| k =~ regexp } } if regexp metrics end
format_metric(keys, value, timestamp)
click to toggle source
# File lib/pg_metrics/metrics.rb, line 103 def self.format_metric(keys, value, timestamp) segs = keys.reject { |k| k.nil? }.map { |x| x.gsub(/[\s.]/, "_") } value = decode_xlog_location(value) [segs, value, timestamp] end
instance_metrics(server_version)
click to toggle source
# File lib/pg_metrics/metrics.rb, line 117 def self.instance_metrics(server_version) { max_connections: { prefix: %w(config instance max_connections), query: %q{SHOW max_connections} }, superuser_connections: { prefix: %w(config instance superuser_reserved_connections), query: %q{SHOW superuser_reserved_connections} }, archive_files: { prefix: %w(archive_files), query: %q{SELECT CAST(COALESCE(SUM(CAST(archive_file ~ E'\\.ready$' AS int)), 0) AS INT) AS ready, CAST(COALESCE(SUM(CAST(archive_file ~ E'\\.done$' AS int)), 0) AS INT) AS done FROM pg_catalog.pg_ls_dir('pg_xlog/archive_status') AS archive_files (archive_file)} }, bgwriter: { prefix: %w(bgwriter), query: %q{SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_alloc FROM pg_stat_bgwriter} }, sessions: { prefix: %w(sessions), query: Gem::Version.new(server_version) >= Gem::Version.new('9.2') \ ? %{SELECT datname AS key, usename AS key2, CASE WHEN waiting THEN 'waiting' ELSE state END AS key3, count(*) AS value FROM pg_stat_activity WHERE pid <> pg_backend_pid() GROUP BY datname, usename, 3} : %{SELECT datname AS key, usename AS key2, CASE WHEN waiting THEN 'waiting' ELSE CASE current_query WHEN NULL THEN 'disabled' WHEN '<IDLE>' THEN 'idle' WHEN '<IDLE> in transaction' THEN 'idle in transaction' ELSE 'active' END END AS key3, count(*) AS value FROM pg_stat_activity WHERE procpid <> pg_backend_pid() GROUP BY datname, usename, 3} }, database_connection_limits: { prefix: %w(config database), query: %q{SELECT datname AS key, CASE WHEN datconnlimit <> -1 THEN datconnlimit ELSE current_setting('max_connections')::int END AS connection_limit FROM pg_database WHERE datallowconn AND NOT datistemplate} }, user_connection_limits: { prefix: %w(config user), query: %q{SELECT rolname AS key, CASE WHEN rolconnlimit <> -1 THEN rolconnlimit ELSE current_setting('max_connections')::INT - CASE WHEN rolsuper THEN 0 ELSE current_setting('superuser_reserved_connections')::INT END END AS connection_limit FROM pg_roles WHERE rolcanlogin} }, database_size: { prefix: %w(database), query: %q{SELECT datname AS key, pg_database_size(oid) AS size FROM pg_database WHERE NOT datistemplate} }, streaming_state: { prefix: %w(streaming_state), query: Gem::Version.new(server_version) >= Gem::Version.new('9.1') \ ? %q{SELECT CASE WHEN client_hostname IS NULL THEN 'socket' ELSE host(client_addr) END AS key, CASE state WHEN 'catchup' THEN 1 WHEN 'streaming' THEN 2 ELSE 0 END as value FROM pg_stat_replication} : nil }, transactions: { prefix: %w(database), query: %q{SELECT dat.datname AS key, 'transactions' AS key2, xact_commit AS commit, xact_rollback AS rollback FROM pg_stat_database JOIN pg_database dat ON dat.oid = datid WHERE datallowconn AND NOT datistemplate} }, xlog: { prefix: %w(xlog), query: Gem::Version.new(server_version) >= Gem::Version.new('9.0') \ ? %q{SELECT CASE WHEN pg_is_in_recovery() THEN NULL ELSE pg_current_xlog_location() END AS location, pg_last_xlog_receive_location() AS receive_location, pg_last_xlog_replay_location() AS replay_location} : %q{SELECT pg_current_xlog_location() AS location} } } end
make_conn(conn_str, app_name)
click to toggle source
# File lib/pg_metrics/metrics.rb, line 41 def self.make_conn(conn_str, app_name) conn = PG::Connection.new(conn_str) server_version = conn.parameter_status("server_version") conn.exec(%(SET application_name = "#{app_name}")) if Gem::Version.new(server_version) >= Gem::Version.new("9.0") conn end