Module: PgHero::Methods::Queries

Included in:
Database
Defined in:
lib/pghero/methods/queries.rb

Instance Method Summary collapse

Instance Method Details

#blocked_queries ⇒ Object

from wiki.postgresql.org/wiki/Lock_Monitoring



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
 
# File 'lib/pghero/methods/queries.rb', line 60

def blocked_queries
  select_all "    SELECT\n      bl.pid AS blocked_pid,\n      a.usename AS blocked_user,\n      ka.query AS current_or_recent_query_in_blocking_process,\n      ka.state AS state_of_blocking_process,\n      age(now(), ka.query_start) AS blocking_duration,\n      kl.pid AS blocking_pid,\n      ka.usename AS blocking_user,\n      a.query AS blocked_query,\n      age(now(), a.query_start) AS blocked_duration\n    FROM\n      pg_catalog.pg_locks bl\n    JOIN\n      pg_catalog.pg_stat_activity a ON a.pid = bl.pid\n    JOIN\n      pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid\n    JOIN\n      pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid\n    WHERE\n      NOT bl.GRANTED\n    ORDER BY\n      blocked_duration DESC\n  SQL\nend\n"

#locks ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
# File 'lib/pghero/methods/queries.rb', line 38

def locks
  select_all "    SELECT DISTINCT ON (pid)\n      pg_stat_activity.pid,\n      pg_stat_activity.query,\n      age(now(), pg_stat_activity.query_start) AS age\n    FROM\n      pg_stat_activity\n    INNER JOIN\n      pg_locks ON pg_locks.pid = pg_stat_activity.pid\n    WHERE\n      pg_stat_activity.query <> '<insufficient privilege>'\n      AND pg_locks.mode = 'ExclusiveLock'\n      AND pg_stat_activity.pid <> pg_backend_pid()\n      AND pg_stat_activity.datname = current_database()\n    ORDER BY\n      pid,\n      query_start\n  SQL\nend\n"

#long_running_queries ⇒ Object 



29
30
31
 
# File 'lib/pghero/methods/queries.rb', line 29

def long_running_queries
  running_queries(min_duration: long_running_query_sec)
end

#running_queries(options = {}) ⇒ Object 



4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
# File 'lib/pghero/methods/queries.rb', line 4

def running_queries(options = {})
  min_duration = options[:min_duration]
  select_all "    SELECT\n      pid,\n      state,\n      application_name AS source,\n      age(NOW(), COALESCE(query_start, xact_start)) AS duration,\n      \#{server_version_num >= 90600 ? \"(wait_event IS NOT NULL) AS waiting\" : \"waiting\"},\n      query,\n      COALESCE(query_start, xact_start) AS started_at,\n      usename AS user\n    FROM\n      pg_stat_activity\n    WHERE\n      query <> '<insufficient privilege>'\n      AND state <> 'idle'\n      AND pid <> pg_backend_pid()\n      AND datname = current_database()\n      \#{min_duration ? \"AND NOW() - COALESCE(query_start, xact_start) > interval '\#{min_duration.to_i} seconds'\" : nil}\n    ORDER BY\n      COALESCE(query_start, xact_start) DESC\n  SQL\nend\n"

#slow_queries(options = {}) ⇒ Object 



33
34
35
36
 
# File 'lib/pghero/methods/queries.rb', line 33

def slow_queries(options = {})
  query_stats = options[:query_stats] || self.query_stats(options.except(:query_stats))
  query_stats.select { |q| q["calls"].to_i >= slow_query_calls.to_i && q["average_time"].to_i >= slow_query_ms.to_i }
end