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
 
# 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]

  @connection = self.connect
end

Instance Method Details

#backend_query ⇒ Object

returns Active and Idle connections from DB



54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
# File 'lib/postgres_monitor/monitor.rb', line 54

def backend_query
  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



25
26
27
 
# File 'lib/postgres_monitor/monitor.rb', line 25

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



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

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)



79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
# File 'lib/postgres_monitor/monitor.rb', line 79

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



469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
 
# File 'lib/postgres_monitor/monitor.rb', line 469

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 



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

def close_connection
  @connection.close
end

#connect ⇒ Object

GENERAL METHODS



488
489
490
 
# File 'lib/postgres_monitor/monitor.rb', line 488

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

#database_bloat ⇒ Object

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



98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
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
 
# File 'lib/postgres_monitor/monitor.rb', line 98

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



20
21
22
 
# File 'lib/postgres_monitor/monitor.rb', line 20

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

#extension_loaded?(extname) ⇒ Boolean

Returns:

  • (Boolean) 


509
510
511
512
513
 
# File 'lib/postgres_monitor/monitor.rb', line 509

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

#get_database_version ⇒ Object

returns database version in SQL form



15
16
17
 
# File 'lib/postgres_monitor/monitor.rb', line 15

def get_database_version
  execute_sql 'SELECT version();'
end

#index_count_query ⇒ Object

count of indexes in the database



30
31
32
 
# File 'lib/postgres_monitor/monitor.rb', line 30

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



249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
 
# File 'lib/postgres_monitor/monitor.rb', line 249

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 



34
35
36
 
# File 'lib/postgres_monitor/monitor.rb', line 34

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



213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
 
# File 'lib/postgres_monitor/monitor.rb', line 213

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



49
50
51
 
# File 'lib/postgres_monitor/monitor.rb', line 49

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

#locks ⇒ Object

display queries with active locks



388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
 
# File 'lib/postgres_monitor/monitor.rb', line 388

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



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

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 '5 minutes'
    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) 


497
498
499
 
# File 'lib/postgres_monitor/monitor.rb', line 497

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



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 448

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 



505
506
507
 
# File 'lib/postgres_monitor/monitor.rb', line 505

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

#port ⇒ Object 



492
493
494
 
# File 'lib/postgres_monitor/monitor.rb', line 492

def port
  @port || 5432
end

#query_column ⇒ Object 



501
502
503
 
# File 'lib/postgres_monitor/monitor.rb', line 501

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



44
45
46
 
# File 'lib/postgres_monitor/monitor.rb', line 44

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



442
443
444
445
 
# File 'lib/postgres_monitor/monitor.rb', line 442

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



39
40
41
 
# File 'lib/postgres_monitor/monitor.rb', line 39

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

#table_indexes_size ⇒ Object

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



318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
 
# File 'lib/postgres_monitor/monitor.rb', line 318

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



298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
 
# File 'lib/postgres_monitor/monitor.rb', line 298

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



232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
 
# File 'lib/postgres_monitor/monitor.rb', line 232

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';
  )

  self.execute(sql)
end

#total_table_size ⇒ Object

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



338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/postgres_monitor/monitor.rb', line 338

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.



273
274
275
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 273

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



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

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