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



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

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?

  # use mapping, not query stats here
  # TODO add option for this, and make default in PgHero 3.0
  if false # mapping.size == 1 && server_version_num >= 120000
    query_stats.each do |db_id, db_query_stats|
      if reset_query_stats(database: mapping[db_id], raise_errors: raise_errors)
        insert_query_stats(db_id, db_query_stats, now)
      end
    end
  else
    if reset_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 ⇒ Object 



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

def clean_query_stats
  PgHero::QueryStats.where(database: id).where("captured_at < ?", 14.days.ago).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) 


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

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 



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

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

#query_hash_stats(query_hash, user: nil) ⇒ Object 



176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
 
# File 'lib/pghero/methods/query_stats.rb', line 176

def query_hash_stats(query_hash, user: nil)
  if historical_query_stats_enabled? && supports_query_hash?
    start_at = 24.hours.ago
    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
  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) 


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

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+



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

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

TODO scope by database in PgHero 3.0 (add database: database_name to options)



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

def reset_query_stats(**options)
  reset_instance_query_stats(**options)
end

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



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

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) 


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

def supports_query_hash?
  server_version_num >= 90400
end