Class: PostgresMonitor::Monitor

Inherits:
Object
  • Object
show all
Defined in:
lib/postgres_monitor/monitor.rb

Instance Method Summary collapse

Constructor Details

#initialize(connection_params) ⇒ Monitor

Returns a new instance of Monitor.



3
4
5
6
7
8
9
10
11
12
13
14
 
# File 'lib/postgres_monitor/monitor.rb', line 3

def initialize(connection_params)
  @host     = connection_params[:host]
  @port     = connection_params[:port] ? connection_params[:port] : self.port
  @user     = connection_params[:user]
  @password = connection_params[:password]
  @sslmode  = connection_params[:sslmode] ? connection_params[:sslmode] : 'require'
  @dbname   = connection_params[:dbname]

  @long_query_threshold = connection_params[:long_query_threshold] ? connection_params[:long_query_threshold] : '5 seconds'

  @connection = self.connect
end

Instance Method Details

#backend_query ⇒ Object

DEPRECATION WARNING This seems to have an issue with returning multiple duplicate results; Deprecating in favor of connection_counts returns Active and Idle connections from DB



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
 
# File 'lib/postgres_monitor/monitor.rb', line 64

def backend_query
  warn 'DEPRECATED. Please use connection_counts instead'

  sql = %Q(
    SELECT ( SELECT count(*) FROM pg_stat_activity WHERE
      #{
        if nine_two?
          "state <> 'idle'"
        else
          "current_query <> '<IDLE>'"
        end
      }
    ) AS backends_active, ( SELECT count(*) FROM pg_stat_activity WHERE
      #{
        if nine_two?
          "state = 'idle'"
        else
          "current_query = '<IDLE>'"
        end
      }
    ) AS backends_idle FROM pg_stat_activity;
  )

  execute_sql(sql)
end

#bgwriter_query ⇒ Object

returns Scheduled and Requested Checkpoints



27
28
29
 
# File 'lib/postgres_monitor/monitor.rb', line 27

def bgwriter_query
  execute_sql 'SELECT * FROM pg_stat_bgwriter;'
end

#blocking ⇒ Object

QUERY QUERIES [that feels wrong…] display queries holding locks other queries are waiting to be released



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
 
# File 'lib/postgres_monitor/monitor.rb', line 386

def blocking
  query_column = self.query_column
  pid_column = self.pid_column
  sql = %Q(
    SELECT
        bl.pid AS blocked_pid,
        ka.#{query_column} AS blocking_statement,
        now() - ka.query_start AS blocking_duration,
        kl.pid AS blocking_pid,
        a.#{query_column} AS blocked_statement,
        now() - a.query_start AS blocked_duration
    FROM
        pg_catalog.pg_locks bl
    JOIN
        pg_catalog.pg_stat_activity a ON bl.pid = a.#{pid_column}
    JOIN
        pg_catalog.pg_locks kl
    JOIN
        pg_catalog.pg_stat_activity ka
        ON kl.pid = ka.#{pid_column}
        ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
    WHERE NOT
      bl.granted
  )

  execute_sql(sql)
end

#cache_hit ⇒ Object

calculates your cache hit rate (effective databases are at 99% and up)



106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'lib/postgres_monitor/monitor.rb', line 106

def cache_hit
  sql = %q(
    SELECT
      'index hit rate' AS name,
      (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio
    FROM
      pg_statio_user_indexes
    UNION ALL
    SELECT
      'table hit rate' AS name,
      sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio
    FROM
      pg_statio_user_tables;
  )

  execute_sql(sql)
end

#calls ⇒ Object

show 10 most frequently called queries This is dependent on the pg_stat_statements being loaded



496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
 
# File 'lib/postgres_monitor/monitor.rb', line 496

def calls
  return unless extension_loaded? 'pg_stat_statements'
  sql = %Q(
    SELECT
      query AS qry,
      interval '1 millisecond' * total_time AS exec_time,
      to_char((total_time/sum(total_time) OVER()) * 100, 'FM90D0') || '%'  AS prop_exec_time,
      to_char(calls, 'FM999G999G990') AS ncalls,
      interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time
  FROM
    pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)
  ORDER BY
    calls DESC LIMIT 10
  )

  execute_sql(sql)
end

#close_connection ⇒ Object 



546
547
548
 
# File 'lib/postgres_monitor/monitor.rb', line 546

def close_connection
  @connection.close
end

#connect ⇒ Object

GENERAL METHODS



515
516
517
 
# File 'lib/postgres_monitor/monitor.rb', line 515

def connect
  PG::Connection.new(:host => @host, :port => @port, :user => @user, :password => @password, :sslmode => @sslmode, :dbname => @dbname)
end

#connection_counts ⇒ Object

list connection states and count



56
57
58
 
# File 'lib/postgres_monitor/monitor.rb', line 56

def connection_counts
  execute_sql "SELECT #{state_column}, COUNT(*) FROM pg_stat_activity GROUP BY #{state_column};"
end

#database_bloat ⇒ Object

show table and index bloat in your database ordered by most wasteful



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
 
# File 'lib/postgres_monitor/monitor.rb', line 125

def database_bloat
   sql = %q(
      WITH constants AS (
              SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma
            ), bloat_info AS (
              SELECT
                ma,bs,schemaname,tablename,
                (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
                (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
              FROM (
                SELECT
                  schemaname, tablename, hdr, ma, bs,
                  SUM((1-null_frac)*avg_width) AS datawidth,
                  MAX(null_frac) AS maxfracsum,
                  hdr+(
                    SELECT 1+count(*)/8
                    FROM pg_stats s2
                    WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
                  ) AS nullhdr
                FROM pg_stats s, constants
                GROUP BY 1,2,3,4,5
              ) AS foo
            ), table_bloat AS (
              SELECT
                schemaname, tablename, cc.relpages, bs,
                CEIL((cc.reltuples*((datahdr+ma-
                  (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
              FROM bloat_info
                JOIN pg_class cc ON cc.relname = bloat_info.tablename
                JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
            ), index_bloat AS (
              SELECT
                schemaname, tablename, bs,
                COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
                COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
              FROM bloat_info
                JOIN pg_class cc ON cc.relname = bloat_info.tablename
                JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
                JOIN pg_index i ON indrelid = cc.oid
                JOIN pg_class c2 ON c2.oid = i.indexrelid
            )
            SELECT
              type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste
            FROM
            (SELECT
              'table' as type,
              schemaname,
              tablename as object_name,
              ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat,
              CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste
            FROM
              table_bloat
                UNION
            SELECT
              'index' as type,
              schemaname,
              tablename || '::' || iname as object_name,
              ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat,
              CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste
            FROM
              index_bloat) bloat_summary
            ORDER BY raw_waste DESC, bloat DESC
      )

    execute_sql(sql)
end

#database_query ⇒ Object

returns database tranasction and row activity for the DB



22
23
24
 
# File 'lib/postgres_monitor/monitor.rb', line 22

def database_query
  execute_sql "SELECT * FROM pg_stat_database WHERE datname='#{@dbname}';"
end

#extension_loaded?(extname) ⇒ Boolean

Returns:

  • (Boolean) 


540
541
542
543
544
 
# File 'lib/postgres_monitor/monitor.rb', line 540

def extension_loaded?(extname)
  @connection.exec("SELECT count(*) FROM pg_extension WHERE extname = '#{extname}'") do |result|
    result[0]['count'] == '1'
  end
end

#get_database_sizes ⇒ Object

get database sizes



91
92
93
94
95
96
97
98
99
100
101
102
103
 
# File 'lib/postgres_monitor/monitor.rb', line 91

def get_database_sizes
  sql = %q(
    SELECT
      t1.datname AS db_name,
      pg_size_pretty(pg_database_size(t1.datname)) as db_size
    FROM
      pg_database t1
    ORDER BY
      pg_database_size(t1.datname) desc;
  )

  execute_sql(sql)
end

#get_database_version ⇒ Object

returns database version in SQL form



17
18
19
 
# File 'lib/postgres_monitor/monitor.rb', line 17

def get_database_version
  execute_sql 'SELECT version();'
end

#index_count_query ⇒ Object

count of indexes in the database



32
33
34
 
# File 'lib/postgres_monitor/monitor.rb', line 32

def index_count_query
  execute_sql "SELECT count(1) as indexes FROM pg_class WHERE relkind = 'i';"
end

#index_size ⇒ Object

show the size of indexes, descending by size



276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/postgres_monitor/monitor.rb', line 276

def index_size
   sql = %q(
     SELECT
       c.relname AS name,
       pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size
     FROM
       pg_class c
       LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
     WHERE
       n.nspname NOT IN ('pg_catalog', 'information_schema')
       AND n.nspname !~ '^pg_toast'
       AND c.relkind='i'
     GROUP BY
       c.relname
     ORDER BY
       sum(c.relpages) DESC;
   )
   execute_sql(sql)
end

#index_size_query ⇒ Object 



36
37
38
 
# File 'lib/postgres_monitor/monitor.rb', line 36

def index_size_query
  execute_sql 'SELECT sum(relpages::bigint*8192) AS size FROM pg_class WHERE reltype = 0;'
end

#index_usage ⇒ Object

INDEX queries calculates your index hit rate



240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
 
# File 'lib/postgres_monitor/monitor.rb', line 240

def index_usage
  sql = %q(
     SELECT
       relname,
        CASE idx_scan
          WHEN 0 THEN 'Insufficient data'
          ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
        END percent_of_times_index_used,
        n_live_tup rows_in_table
      FROM
        pg_stat_user_tables
      ORDER BY
        n_live_tup DESC;
   )

  execute_sql(sql)
end

#list_databases ⇒ Object

list all non-template DBs known



51
52
53
 
# File 'lib/postgres_monitor/monitor.rb', line 51

def list_databases
  execute_sql 'SELECT datname FROM pg_database WHERE datistemplate = false;'
end

#locks ⇒ Object

display queries with active locks



415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
 
# File 'lib/postgres_monitor/monitor.rb', line 415

def locks
  query_column = self.query_column
  pid_column = self.pid_column
  sql = %Q(
   SELECT
      pg_stat_activity.#{pid_column},
      pg_class.relname,
      pg_locks.transactionid,
      pg_locks.granted,
      pg_stat_activity.#{query_column},
      age(now(),pg_stat_activity.query_start) AS "age"
   FROM
      pg_stat_activity,pg_locks left
      OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
   WHERE
      pg_stat_activity.#{query_column} <> '<insufficient privilege>'
      AND pg_locks.pid = pg_stat_activity.#{pid_column}
      AND pg_locks.mode = 'ExclusiveLock'
      AND pg_stat_activity.#{pid_column} <> pg_backend_pid() order by query_start;
  )

  execute_sql(sql)
end

#long_running_queries ⇒ Object

show all queries longer than five minutes by descending duration



440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
 
# File 'lib/postgres_monitor/monitor.rb', line 440

def long_running_queries
  query_column = self.query_column
  pid_column = self.pid_column
  sql = %Q(
    SELECT
      #{pid_column},
      now() - pg_stat_activity.query_start AS duration,
      #{query_column} AS query
    FROM
      pg_stat_activity
    WHERE
      pg_stat_activity.#{query_column} <> ''::text
      #{
        if nine_two?
          "AND state <> 'idle'"
        else
          "AND current_query <> '<IDLE>'"
        end
      }
      AND now() - pg_stat_activity.query_start > interval '#{@long_query_threshold}'
    ORDER BY
      now() - pg_stat_activity.query_start DESC;
  )

  execute_sql(sql)
end

#nine_two? ⇒ Boolean

Certain queries are dependent on the Postgres version

Returns:

  • (Boolean) 


524
525
526
 
# File 'lib/postgres_monitor/monitor.rb', line 524

def nine_two?
  @connection.server_version >= 90200
end

#outliers ⇒ Object

show 10 queries that have longest execution time in aggregate. needs pg_stat_statements extension



475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
 
# File 'lib/postgres_monitor/monitor.rb', line 475

def outliers
  return unless extension_loaded? 'pg_stat_statements'

  sql = %q(
      SELECT
        query AS qry,
        interval '1 millisecond' * total_time AS total_exec_time,
        to_char((total_time/sum(total_time) OVER()) * 100, 'FM90D0') || '%'  AS prop_exec_time,
        to_char(calls, 'FM999G999G999G990') AS ncalls,
        interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time
      FROM
        pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)
      ORDER BY
      total_time DESC LIMIT 10
  )

  execute_sql(sql)
end

#pid_column ⇒ Object 



532
533
534
 
# File 'lib/postgres_monitor/monitor.rb', line 532

def pid_column
  nine_two? ? 'pid' : 'procpid'
end

#port ⇒ Object 



519
520
521
 
# File 'lib/postgres_monitor/monitor.rb', line 519

def port
  @port || 5432
end

#query_column ⇒ Object 



528
529
530
 
# File 'lib/postgres_monitor/monitor.rb', line 528

def query_column
  nine_two? ? 'query' : 'current_query'
end

#records_rank ⇒ Object

show all tables and the number of rows in each ordered by number of rows descending



46
47
48
 
# File 'lib/postgres_monitor/monitor.rb', line 46

def records_rank
  execute_sql 'SELECT relname AS name, n_live_tup AS estimated_count FROM pg_stat_user_tables ORDER BY n_live_tup DESC;'
end

#reset_pg_stats_statements ⇒ Object

PG_STATS_STATEMENTS QUERIES reset pg_stats



469
470
471
472
 
# File 'lib/postgres_monitor/monitor.rb', line 469

def reset_pg_stats_statements
  return unless extension_loaded? 'pg_stat_statements'
  execute_sql 'SELECT pg_stat_statements_reset();'
end

#seq_scans ⇒ Object

show the count of sequential scans by table descending by order



41
42
43
 
# File 'lib/postgres_monitor/monitor.rb', line 41

def seq_scans
  execute_sql 'SELECT relname AS name, seq_scan as count FROM pg_stat_user_tables ORDER BY seq_scan DESC;'
end

#state_column ⇒ Object 



536
537
538
 
# File 'lib/postgres_monitor/monitor.rb', line 536

def state_column
  nine_two? ? 'state' : 'current_query'
end

#table_indexes_size ⇒ Object

show the total size of all the indexes on each table, descending by size



345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
 
# File 'lib/postgres_monitor/monitor.rb', line 345

def table_indexes_size
   sql = %q(
     SELECT
       c.relname AS table,
       pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
     FROM
       pg_class c
       LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
     WHERE
       n.nspname NOT IN ('pg_catalog', 'information_schema')
       AND n.nspname !~ '^pg_toast'
       AND c.relkind='r'
     ORDER BY
       pg_indexes_size(c.oid) DESC;
   )

   execute_sql(sql)
end

#table_size ⇒ Object

TABLE QUERIES show the size of the tables (excluding indexes), descending by size



325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
 
# File 'lib/postgres_monitor/monitor.rb', line 325

def table_size
  sql = %q(
    SELECT
      c.relname AS name,
      pg_size_pretty(pg_table_size(c.oid)) AS size
    FROM
      pg_class c
      LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
    WHERE
      n.nspname NOT IN ('pg_catalog', 'information_schema')
      AND n.nspname !~ '^pg_toast'
      AND c.relkind='r'
    ORDER BY
    pg_table_size(c.oid) DESC;
  )

  execute_sql(sql)
end

#total_index_size ⇒ Object

show the total size of all indexes in MB



259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
 
# File 'lib/postgres_monitor/monitor.rb', line 259

def total_index_size
  sql = %q(
     SELECT
       pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size
     FROM
       pg_class c
       LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
     WHERE
       n.nspname NOT IN ('pg_catalog', 'information_schema')
       AND n.nspname !~ '^pg_toast'
       AND c.relkind='i';
  )

  execute_sql(sql)
end

#total_table_size ⇒ Object

show the size of the tables (including indexes), descending by size



365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
 
# File 'lib/postgres_monitor/monitor.rb', line 365

def total_table_size
  sql = %q(
    SELECT
      c.relname AS name,
      pg_size_pretty(pg_total_relation_size(c.oid)) AS size
    FROM
      pg_class c
      LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
    WHERE
      n.nspname NOT IN ('pg_catalog', 'information_schema')
      AND n.nspname !~ '^pg_toast'
      AND c.relkind='r'
    ORDER BY
      pg_total_relation_size(c.oid) DESC;
  )

  execute_sql(sql)
end

#unused_indexes ⇒ Object

show unused and almost unused indexes, ordered by their size relative to the number of index scans. Exclude indexes of very small tables (less than 5 pages), where the planner will almost invariably select a sequential scan, but may not in the future as the table grows.



300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
 
# File 'lib/postgres_monitor/monitor.rb', line 300

def unused_indexes
   sql = %q(
     SELECT
       schemaname || '.' || relname AS table,
       indexrelname AS index,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
       idx_scan as index_scans
     FROM
       pg_stat_user_indexes ui
     JOIN
       pg_index i ON ui.indexrelid = i.indexrelid
     WHERE NOT
       indisunique
       AND idx_scan < 50
       AND pg_relation_size(relid) > 5 * 8192
     ORDER BY
       pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
       pg_relation_size(i.indexrelid) DESC;
    )

   execute_sql(sql)
end

#vacuum_stats ⇒ Object

show dead rows and whether an automatic vacuum is expected to be triggered



193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
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
 
# File 'lib/postgres_monitor/monitor.rb', line 193

def vacuum_stats
   sql = %q(
    WITH table_opts AS (
      SELECT
        pg_class.oid, relname, nspname, array_to_string(reloptions, '') AS relopts
      FROM
         pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid
    ), vacuum_settings AS (
      SELECT
        oid, relname, nspname,
        CASE
          WHEN relopts LIKE '%autovacuum_vacuum_threshold%'
            THEN regexp_replace(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*', E'\\\\\\1')::integer
            ELSE current_setting('autovacuum_vacuum_threshold')::integer
          END AS autovacuum_vacuum_threshold,
        CASE
          WHEN relopts LIKE '%autovacuum_vacuum_scale_factor%'
            THEN regexp_replace(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*', E'\\\\\\1')::real
            ELSE current_setting('autovacuum_vacuum_scale_factor')::real
          END AS autovacuum_vacuum_scale_factor
      FROM
        table_opts
    )
    SELECT
      vacuum_settings.nspname AS schema,
      vacuum_settings.relname AS table,
      to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') AS last_vacuum,
      to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
      to_char(pg_class.reltuples, '9G999G999G999') AS rowcount,
      to_char(psut.n_dead_tup, '9G999G999G999') AS dead_rowcount,
      to_char(autovacuum_vacuum_threshold
           + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), '9G999G999G999') AS autovacuum_threshold,
      CASE
        WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup
        THEN 'yes'
      END AS expect_autovacuum
    FROM
      pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid
        INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid
    ORDER BY 1
 )

  execute_sql(sql)
end