Module: PgHero::Methods::QueryStats

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

Instance Method Summary collapse

Instance Method Details

#capture_query_stats(raise_errors: false) ⇒ Object 



138
139
140
141
142
143
144
 
# File 'lib/pghero/methods/query_stats.rb', line 138

def capture_query_stats(raise_errors: false)
  captured_at = Time.now
  db_query_stats = query_stats(limit: 100)
  if db_query_stats.any? && reset_query_stats(raise_errors: raise_errors)
    insert_query_stats(db_query_stats, captured_at)
  end
end

#clean_query_stats(before: nil) ⇒ Object 



146
147
148
149
 
# File 'lib/pghero/methods/query_stats.rb', line 146

def clean_query_stats(before: nil)
  before ||= 14.days.ago
  PgHero::QueryStats.where(database: id).where("captured_at < ?", before).delete_all
end

#disable_query_stats ⇒ Object 



88
89
90
91
 
# File 'lib/pghero/methods/query_stats.rb', line 88

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

#enable_query_stats ⇒ Object 



83
84
85
86
 
# File 'lib/pghero/methods/query_stats.rb', line 83

def enable_query_stats
  execute("CREATE EXTENSION IF NOT EXISTS pg_stat_statements")
  true
end

#historical_query_stats_enabled? ⇒ Boolean

https://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema

Returns:

  • (Boolean) 


124
125
126
127
128
 
# File 'lib/pghero/methods/query_stats.rb', line 124

def historical_query_stats_enabled?
  # TODO use schema from config
  # make sure primary database is PostgreSQL first
  queries_table_exists? && query_stats_table_exists? && capture_query_stats?
end

#queries_table_exists? ⇒ Boolean

Returns:

  • (Boolean) 


130
131
132
 
# File 'lib/pghero/methods/query_stats.rb', line 130

def queries_table_exists?
  table_exists?("pghero_queries")
end

#query_hash_stats(query_hash, user: nil, current: true) ⇒ Object 



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

def query_hash_stats(query_hash, user: nil, current: true)
  if !historical_query_stats_enabled?
    raise NotEnabled, "Query hash stats not enabled"
  end

  start_at = 24.hours.ago
  # specify pghero_queries.query in case pghero_query_stats.query exists
  sql = <<~SQL
    SELECT
      captured_at,
      total_time,
      calls,
      (SELECT regexp_matches(pghero_queries.query, '.*/\\*(.+?)\\*/'))[1] AS origin
    FROM
      pghero_query_stats
    INNER JOIN
      pghero_queries ON pghero_queries.id = pghero_query_stats.query_id
    WHERE
      database = :id
      AND captured_at >= :start_at
      AND query_hash = :query_hash
      #{"AND \"user\" = :user" if user}
    ORDER BY
      1 ASC
  SQL
  binds = {id: id, start_at: start_at, query_hash: query_hash}
  binds[:user] = user if user
  stats = select_all_stats(sql, binds)
  if current
    captured_at = Time.now
    current_stats, _ = current_query_stats(query_hash: query_hash, user: user, origin: true)
    current_stats.each do |r|
      stats << {
        captured_at: captured_at,
        total_time: r[:total_time],
        calls: r[:calls],
        origin: r[:origin]
      }
    end
  end
  stats.each do |query|
    query[:average_time] = query[:total_time] / query[:calls]
  end
  stats
end

#query_stats(current: true, historical: false, limit: nil, sort: nil, user: nil, query_hash: nil, start_at: nil, end_at: nil, min_average_time: nil, min_calls: nil) ⇒ 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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/pghero/methods/query_stats.rb', line 4

def query_stats(
  current: true,
  historical: false,
  limit: nil,
  sort: nil,
  user: nil,
  query_hash: nil,
  start_at: nil,
  end_at: nil,
  min_average_time: nil,
  min_calls: nil
)
  limit ||= 100

  sort ||= "total_time"
  unless ["total_time", "average_time", "calls"].include?(sort)
    raise ArgumentError, "Invalid sort"
  end

  current_query_stats, current_total_time =
    if !current || (historical && end_at && end_at < Time.now)
      [[], 0]
    else
      current_query_stats(limit: limit, sort: sort, user: user, query_hash: query_hash)
    end

  historical_query_stats, historical_total_time =
    if historical && historical_query_stats_enabled?
      historical_query_stats(limit: limit, sort: sort, user: user, query_hash: query_hash, start_at: start_at, end_at: end_at)
    else
      [[], 0]
    end

  query_stats = current_query_stats + historical_query_stats
  query_stats = combine_query_stats(query_stats.group_by { |q| [q[:query_hash], q[:user]] })
  query_stats.each do |query|
    query[:average_time] = query[:total_time] / query[:calls]
  end

  # add total percent when not filtering by user or query hash
  # could make accurate for these by changing location of filters in queries
  # but not needed at the moment
  if user.nil? && query_hash.nil?
    all_queries_total_time = current_total_time + historical_total_time
    query_stats.each do |query|
      query[:total_percent] = query[:total_time] * 100.0 / all_queries_total_time
    end
  end

  query_stats = query_stats.sort_by { |q| -q[sort.to_sym] }.first(limit)
  if min_average_time
    query_stats.reject! { |q| q[:average_time] < min_average_time }
  end
  if min_calls
    query_stats.reject! { |q| q[:calls] < min_calls }
  end
  query_stats
end

#query_stats_available? ⇒ Boolean

Returns:

  • (Boolean) 


63
64
65
 
# File 'lib/pghero/methods/query_stats.rb', line 63

def query_stats_available?
  select_one("SELECT COUNT(*) AS count FROM pg_available_extensions WHERE name = 'pg_stat_statements'") > 0
end

#query_stats_enabled? ⇒ Boolean

only cache if true

Returns:

  • (Boolean) 


68
69
70
 
# File 'lib/pghero/methods/query_stats.rb', line 68

def query_stats_enabled?
  @query_stats_enabled ||= query_stats_readable?
end

#query_stats_extension_enabled? ⇒ Boolean

Returns:

  • (Boolean) 


72
73
74
 
# File 'lib/pghero/methods/query_stats.rb', line 72

def query_stats_extension_enabled?
  select_one("SELECT COUNT(*) AS count FROM pg_extension WHERE extname = 'pg_stat_statements'") > 0
end

#query_stats_readable? ⇒ Boolean

Returns:

  • (Boolean) 


76
77
78
79
80
81
 
# File 'lib/pghero/methods/query_stats.rb', line 76

def query_stats_readable?
  select_all("SELECT * FROM pg_stat_statements LIMIT 1")
  true
rescue ActiveRecord::StatementInvalid
  false
end

#query_stats_table_exists? ⇒ Boolean

Returns:

  • (Boolean) 


134
135
136
 
# File 'lib/pghero/methods/query_stats.rb', line 134

def query_stats_table_exists?
  table_exists?("pghero_query_stats")
end

#reset_query_stats(user: nil, query_hash: nil, raise_errors: false) ⇒ Object 



93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
# File 'lib/pghero/methods/query_stats.rb', line 93

def reset_query_stats(user: nil, query_hash: nil, raise_errors: false)
  database = database_name
  database_id = select_one("SELECT oid FROM pg_database WHERE datname = :database", {database: database})
  raise Error, "Database not found: #{database}" unless database_id

  if user
    user_id = select_one("SELECT usesysid FROM pg_user WHERE usename = :user", {user: user})
    raise Error, "User not found: #{user}" unless user_id
  else
    user_id = 0
  end

  if query_hash
    query_id = query_hash.to_i
    # may not be needed
    # but not intuitive that all query hashes are reset with 0
    raise Error, "Invalid query hash: #{query_hash}" if query_id == 0
  else
    query_id = 0
  end

  binds = {user_id: user_id, database_id: database_id, query_id: query_id}
  # use execute to prevent "unknown OID 2278" warning
  execute("SELECT pg_stat_statements_reset(:user_id, :database_id, :query_id)", binds)
  true
rescue ActiveRecord::StatementInvalid => e
  raise e if raise_errors
  false
end

#slow_queries(query_stats: nil, **options) ⇒ Object 



151
152
153
154
 
# File 'lib/pghero/methods/query_stats.rb', line 151

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