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

resetting query stats will reset across the entire Postgres instance in Postgres < 12 this is problematic if multiple PgHero databases use the same Postgres instance

to get around this, we capture queries for every Postgres database before we reset query stats for the Postgres instance with the ‘capture_query_stats` option



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/pghero/methods/query_stats.rb', line 128

def capture_query_stats(raise_errors: false)
  return if config["capture_query_stats"] && config["capture_query_stats"] != true

  # get all databases that use same query stats and build mapping
  mapping = {id => database_name}
  PgHero.databases.select { |_, d| d.config["capture_query_stats"] == id }.each do |_, d|
    mapping[d.id] = d.database_name
  end

  now = Time.now

  query_stats = {}
  mapping.each do |database_id, database_name|
    query_stats[database_id] = query_stats(limit: 1000000, database: database_name)
  end

  query_stats = query_stats.select { |_, v| v.any? }

  # nothing to do
  return if query_stats.empty?

  # reset individual databases for Postgres 12+ instance
  if server_version_num >= 120000
    query_stats.each do |db_id, db_query_stats|
      if reset_instance_query_stats(database: mapping[db_id], raise_errors: raise_errors)
        insert_query_stats(db_id, db_query_stats, now)
      end
    end
  else
    if reset_instance_query_stats(raise_errors: raise_errors)
      query_stats.each do |db_id, db_query_stats|
        insert_query_stats(db_id, db_query_stats, now)
      end
    end
  end
end

#clean_query_stats(before: nil) ⇒ Object 



165
166
167
168
 
# File 'lib/pghero/methods/query_stats.rb', line 165

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 



54
55
56
57
 
# File 'lib/pghero/methods/query_stats.rb', line 54

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

#enable_query_stats ⇒ Object 



49
50
51
52
 
# File 'lib/pghero/methods/query_stats.rb', line 49

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

#historical_query_stats_enabled? ⇒ Boolean

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

Returns:

  • (Boolean) 


105
106
107
108
109
 
# File 'lib/pghero/methods/query_stats.rb', line 105

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

#missing_query_stats_columns ⇒ Object 



115
116
117
 
# File 'lib/pghero/methods/query_stats.rb', line 115

def missing_query_stats_columns
  %w(query_hash user) - PgHero::QueryStats.column_names
end

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



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

def query_hash_stats(query_hash, user: nil, current: false)
  if historical_query_stats_enabled? && supports_query_hash?
    start_at = 24.hours.ago
    stats = select_all_stats <<~SQL
      SELECT
        captured_at,
        total_time / 1000 / 60 AS total_minutes,
        (total_time / calls) AS average_time,
        calls,
        (SELECT regexp_matches(query, '.*/\\*(.+?)\\*/'))[1] AS origin
      FROM
        pghero_query_stats
      WHERE
        database = #{quote(id)}
        AND captured_at >= #{quote(start_at)}
        AND query_hash = #{quote(query_hash)}
        #{user ? "AND \"user\" = #{quote(user)}" : ""}
      ORDER BY
        1 ASC
    SQL
    if current
      captured_at = Time.current
      current_stats = current_query_stats(query_hash: query_hash, user: user, origin: true)
      current_stats.each do |r|
        r[:captured_at] = captured_at
      end
      stats += current_stats
    end
    stats
  else
    raise NotEnabled, "Query hash stats not enabled"
  end
end

#query_stats(historical: false, start_at: nil, end_at: nil, min_average_time: nil, min_calls: nil, **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/query_stats.rb', line 4

def query_stats(historical: false, start_at: nil, end_at: nil, min_average_time: nil, min_calls: nil, **options)
  current_query_stats = historical && end_at && end_at < Time.now ? [] : current_query_stats(**options)
  historical_query_stats = historical && historical_query_stats_enabled? ? historical_query_stats(start_at: start_at, end_at: end_at, **options) : []

  query_stats = combine_query_stats((current_query_stats + historical_query_stats).group_by { |q| [q[:query_hash], q[:user]] })
  query_stats = combine_query_stats(query_stats.group_by { |q| [normalize_query(q[:query]), q[:user]] })

  # add percentages
  all_queries_total_minutes = [current_query_stats, historical_query_stats].sum { |s| (s.first || {})[:all_queries_total_minutes] || 0 }
  query_stats.each do |query|
    query[:average_time] = query[:total_minutes] * 1000 * 60 / query[:calls]
    query[:total_percent] = query[:total_minutes] * 100.0 / all_queries_total_minutes
  end

  sort = options[:sort] || "total_minutes"
  query_stats = query_stats.sort_by { |q| -q[sort.to_sym] }.first(100)
  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) 


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

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) 


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

def query_stats_enabled?
  @query_stats_enabled ||= query_stats_readable?
end

#query_stats_extension_enabled? ⇒ Boolean

Returns:

  • (Boolean) 


38
39
40
 
# File 'lib/pghero/methods/query_stats.rb', line 38

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) 


42
43
44
45
46
47
 
# File 'lib/pghero/methods/query_stats.rb', line 42

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) 


111
112
113
 
# File 'lib/pghero/methods/query_stats.rb', line 111

def query_stats_table_exists?
  table_exists?("pghero_query_stats")
end

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

resets query stats for the entire instance it’s possible to reset stats for a specific database, user or query hash in Postgres 12+



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/pghero/methods/query_stats.rb', line 67

def reset_instance_query_stats(database: nil, user: nil, query_hash: nil, raise_errors: false)
  if database || user || query_hash
    raise PgHero::Error, "Requires PostgreSQL 12+" if server_version_num < 120000

    if database
      database_id = execute("SELECT oid FROM pg_database WHERE datname = #{quote(database)}").first.try(:[], "oid")
      raise PgHero::Error, "Database not found: #{database}" unless database_id
    else
      database_id = 0
    end

    if user
      user_id = execute("SELECT usesysid FROM pg_user WHERE usename = #{quote(user)}").first.try(:[], "usesysid")
      raise PgHero::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 PgHero::Error, "Invalid query hash: #{query_hash}" if query_id == 0
    else
      query_id = 0
    end

    execute("SELECT pg_stat_statements_reset(#{quote(user_id.to_i)}, #{quote(database_id.to_i)}, #{quote(query_id.to_i)})")
  else
    execute("SELECT pg_stat_statements_reset()")
  end
  true
rescue ActiveRecord::StatementInvalid => e
  raise e if raise_errors
  false
end

#reset_query_stats(**options) ⇒ Object

Raises:



59
60
61
62
 
# File 'lib/pghero/methods/query_stats.rb', line 59

def reset_query_stats(**options)
  raise PgHero::Error, "Use reset_instance_query_stats to pass database" if options.delete(:database)
  reset_instance_query_stats(**options, database: database_name)
end

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



170
171
172
173
 
# File 'lib/pghero/methods/query_stats.rb', line 170

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

#supports_query_hash? ⇒ Boolean

Returns:

  • (Boolean) 


119
120
121
 
# File 'lib/pghero/methods/query_stats.rb', line 119

def supports_query_hash?
  server_version_num >= 90400
end