Module: PgHero

Defined in:
lib/pghero.rb,
lib/pghero/engine.rb,
lib/pghero/version.rb,
app/controllers/pg_hero/home_controller.rb

Defined Under Namespace

Classes: Connection, Engine, HomeController

Constant Summary collapse

VERSION = 
"0.1.1"

Class Method Summary collapse

Class Method Details

.connection ⇒ Object 



312
313
314
 
# File 'lib/pghero.rb', line 312

def connection
  @connection ||= Connection.connection
end

.database_size ⇒ Object 



168
169
170
 
# File 'lib/pghero.rb', line 168

def database_size
  select_all("SELECT pg_size_pretty(pg_database_size(current_database()))").first["pg_size_pretty"]
end

.disable_query_stats ⇒ Object 



259
260
261
262
 
# File 'lib/pghero.rb', line 259

def disable_query_stats
  execute("DROP EXTENSION IF EXISTS pg_stat_statements")
  true
end

.enable_query_stats ⇒ Object 



255
256
257
 
# File 'lib/pghero.rb', line 255

def enable_query_stats
  execute("CREATE EXTENSION pg_stat_statements")
end

.execute(sql) ⇒ Object 



308
309
310
 
# File 'lib/pghero.rb', line 308

def execute(sql)
  connection.execute(sql)
end

.explain(sql) ⇒ Object 



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

def explain(sql)
  sql = squish(sql)
  explanation = nil
  explain_safe = explain_safe?

  # use transaction for safety
  Connection.transaction do
    if !explain_safe and (sql.sub(/;\z/, "").include?(";") or sql.upcase.include?("COMMIT"))
      raise ActiveRecord::StatementInvalid, "Unsafe statement"
    end
    explanation = select_all("EXPLAIN #{sql}").map{|v| v["QUERY PLAN"] }.join("\n")
    raise ActiveRecord::Rollback
  end

  explanation
end

.explain_safe? ⇒ Boolean

Returns:

  • (Boolean) 


294
295
296
297
298
299
300
301
 
# File 'lib/pghero.rb', line 294

def explain_safe?
  begin
    select_all("SELECT 1; SELECT 1")
    false
  rescue ActiveRecord::StatementInvalid
    true
  end
end

.index_hit_rate ⇒ Object 



56
57
58
59
60
61
62
63
 
# File 'lib/pghero.rb', line 56

def index_hit_rate
  select_all(%Q{
    SELECT
      (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS rate
    FROM
      pg_statio_user_indexes
  }).first["rate"].to_f
end

.index_usage ⇒ Object 



74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
# File 'lib/pghero.rb', line 74

def index_usage
  select_all %Q{
    SELECT
      relname AS table,
      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,
      relname ASC
   }
end

.kill(pid) ⇒ Object 



172
173
174
 
# File 'lib/pghero.rb', line 172

def kill(pid)
  execute("SELECT pg_cancel_backend(#{pid.to_i})").first["pg_cancel_backend"] == "t"
end

.kill_all ⇒ Object 



176
177
178
179
180
181
182
183
184
185
186
187
 
# File 'lib/pghero.rb', line 176

def kill_all
  select_all %Q{
    SELECT
      pg_terminate_backend(pid)
    FROM
      pg_stat_activity
    WHERE
      pid <> pg_backend_pid()
      AND query <> '<insufficient privilege>'
  }
  true
end

.long_running_queries ⇒ Object 



34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/pghero.rb', line 34

def long_running_queries
  select_all %Q{
    SELECT
      pid,
      state,
      application_name AS source,
      age(now(), xact_start) AS duration,
      waiting,
      query,
      xact_start AS started_at
    FROM
      pg_stat_activity
    WHERE
      query <> '<insufficient privilege>'
      AND state <> 'idle'
      AND pid <> pg_backend_pid()
      AND now() - query_start > interval '5 minutes'
    ORDER BY
      query_start DESC
  }
end

.missing_indexes ⇒ Object 



91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/pghero.rb', line 91

def missing_indexes
  select_all %Q{
    SELECT
      relname AS table,
      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
    WHERE
      idx_scan > 0
      AND (100 * idx_scan / (seq_scan + idx_scan)) < 95
      AND n_live_tup >= 10000
    ORDER BY
      n_live_tup DESC,
      relname ASC
   }
end

.query_stats ⇒ Object

www.craigkerstiens.com/2013/01/10/more-on-postgres-performance/



190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/pghero.rb', line 190

def query_stats
  if query_stats_enabled?
    select_all %Q{
      SELECT
        query,
        (total_time / 1000 / 60) as total_minutes,
        (total_time / calls) as average_time,
        calls
      FROM
        pg_stat_statements
      INNER JOIN
        pg_database ON pg_database.oid = pg_stat_statements.dbid
      WHERE
        pg_database.datname = current_database()
      ORDER BY
        total_minutes DESC
      LIMIT 100
    }
  else
    []
  end
end

.query_stats_available? ⇒ Boolean

Returns:

  • (Boolean) 


238
239
240
 
# File 'lib/pghero.rb', line 238

def query_stats_available?
  select_all("SELECT COUNT(*) AS count FROM pg_available_extensions WHERE name = 'pg_stat_statements'").first["count"].to_i > 0
end

.query_stats_enabled? ⇒ Boolean

Returns:

  • (Boolean) 


242
243
244
 
# File 'lib/pghero.rb', line 242

def query_stats_enabled?
  select_all("SELECT COUNT(*) AS count FROM pg_extension WHERE extname = 'pg_stat_statements'").first["count"].to_i > 0 && query_stats_readable?
end

.query_stats_readable? ⇒ Boolean

Returns:

  • (Boolean) 


246
247
248
249
250
251
252
253
 
# File 'lib/pghero.rb', line 246

def query_stats_readable?
  begin
    # ensure the user has access to the table
    select_all("SELECT has_table_privilege(current_user, 'pg_stat_statements', 'SELECT')").first["has_table_privilege"] == "t"
  rescue ActiveRecord::StatementInvalid
    false
  end
end

.rds? ⇒ Boolean

Returns:

  • (Boolean) 


273
274
275
 
# File 'lib/pghero.rb', line 273

def rds?
  !!(Connection.connection_config[:host].to_s =~ /rds\.amazonaws\.com\z/)
end

.relation_sizes ⇒ Object 



148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
 
# File 'lib/pghero.rb', line 148

def relation_sizes
  select_all %Q{
    SELECT
      c.relname AS name,
      CASE WHEN c.relkind = 'r' THEN 'table' ELSE 'index' END AS type,
      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 IN ('r', 'i')
    ORDER BY
      pg_table_size(c.oid) DESC,
      name ASC
  }
end

.reset_query_stats ⇒ Object 



264
265
266
267
268
269
270
271
 
# File 'lib/pghero.rb', line 264

def reset_query_stats
  if query_stats_enabled?
    execute("SELECT pg_stat_statements_reset()")
    true
  else
    false
  end
end

.running_queries ⇒ Object 



13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
# File 'lib/pghero.rb', line 13

def running_queries
  select_all %Q{
    SELECT
      pid,
      state,
      application_name AS source,
      age(now(), xact_start) AS duration,
      waiting,
      query,
      xact_start AS started_at
    FROM
      pg_stat_activity
    WHERE
      query <> '<insufficient privilege>'
      AND state <> 'idle'
      AND pid <> pg_backend_pid()
    ORDER BY
      query_start DESC
  }
end

.select_all(sql) ⇒ Object 



303
304
305
306
 
# File 'lib/pghero.rb', line 303

def select_all(sql)
  # squish for logs
  connection.select_all(squish(sql)).to_a
end

.slow_queries ⇒ Object 



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/pghero.rb', line 213

def slow_queries
  if query_stats_enabled?
    select_all %Q{
      SELECT
        query,
        (total_time / 1000 / 60) as total_minutes,
        (total_time / calls) as average_time,
        calls
      FROM
        pg_stat_statements
      INNER JOIN
        pg_database ON pg_database.oid = pg_stat_statements.dbid
      WHERE
        pg_database.datname = current_database()
        AND calls >= 100
        AND (total_time / calls) >= 20
      ORDER BY
        total_minutes DESC
      LIMIT 100
    }
  else
    []
  end
end

.squish(str) ⇒ Object

from ActiveSupport



317
318
319
 
# File 'lib/pghero.rb', line 317

def squish(str)
  str.to_s.gsub(/\A[[:space:]]+/, '').gsub(/[[:space:]]+\z/, '').gsub(/[[:space:]]+/, ' ')
end

.table_hit_rate ⇒ Object 



65
66
67
68
69
70
71
72
 
# File 'lib/pghero.rb', line 65

def table_hit_rate
  select_all(%Q{
    SELECT
      sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS rate
    FROM
      pg_statio_user_tables
  }).first["rate"].to_f
end

.unused_indexes ⇒ Object 



127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/pghero.rb', line 127

def unused_indexes
  select_all %Q{
    SELECT
      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
    INNER 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,
      relname ASC
  }
end

.unused_tables ⇒ Object 



112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/pghero.rb', line 112

def unused_tables
  select_all %Q{
    SELECT
      relname AS table,
      n_live_tup rows_in_table
    FROM
      pg_stat_user_tables
    WHERE
      idx_scan = 0
    ORDER BY
      n_live_tup DESC,
      relname ASC
   }
end