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



91
92
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
122
123
124
125
126
127
128
129
130
 
# File 'lib/pghero/methods/query_stats.rb', line 91

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

  supports_query_hash = supports_query_hash?

  if query_stats.any? { |_, v| v.any? } && reset_query_stats(raise_errors: raise_errors)
    query_stats.each do |db_id, db_query_stats|
      if db_query_stats.any?
        values =
          db_query_stats.map do |qs|
            [
              db_id,
              qs[:query],
              qs[:total_minutes] * 60 * 1000,
              qs[:calls],
              now,
              supports_query_hash ? qs[:query_hash] : nil,
              qs[:user]
            ]
          end

        columns = %w[database query total_time calls captured_at query_hash user]
        insert_stats("pghero_query_stats", columns, values)
      end
    end
  end
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) 


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

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 



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

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

#query_hash_stats(query_hash, user: nil) ⇒ Object 



137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/pghero/methods/query_stats.rb', line 137

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) 


74
75
76
 
# File 'lib/pghero/methods/query_stats.rb', line 74

def query_stats_table_exists?
  table_exists?("pghero_query_stats")
end

#reset_query_stats(raise_errors: false) ⇒ Object 



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

def reset_query_stats(raise_errors: false)
  execute("SELECT pg_stat_statements_reset()")
  true
rescue ActiveRecord::StatementInvalid => e
  raise e if raise_errors
  false
end

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



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

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) 


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

def supports_query_hash?
  server_version_num >= 90400
end