Module: PgMetrics::Metrics

Defined in:
lib/pg_metrics/metrics.rb

Constant Summary collapse

Functions = 
:functions
Locks = 
:locks
TableSizes = 
:table_size
IndexSizes = 
:index_size
TableStatio = 
:table_statio
TableStats = 
:table_stats
IndexStatio = 
:index_statio
IndexStats = 
:index_stats
TableFreeSpace = 
:table_free_space
IndexIdealSizes = 
:index_ideal_size

Class Method Summary collapse

Class Method Details

.conn_str(conn_info, dbname = "postgres") ⇒ Object 



54
55
56
 
# 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) ⇒ Object 



209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
 
# 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) ⇒ Object 



109
110
111
112
113
114
115
 
# 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) ⇒ Object 



29
30
31
32
33
34
35
36
37
38
39
 
# 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) ⇒ Object 



18
19
20
21
22
23
24
25
26
27
 
# 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) ⇒ Object 



58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
 
# 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) ⇒ Object 



48
49
50
51
52
 
# 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) ⇒ Object 



103
104
105
106
107
 
# 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) ⇒ Object 



117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
 
# 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) ⇒ Object 



41
42
43
44
45
46
 
# 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