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



107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
 
# File 'lib/pghero/methods/query_stats.rb', line 107

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

  if query_stats.any? { |_, v| v.any? } && reset_query_stats
    query_stats.each do |db_id, db_query_stats|
      if db_query_stats.any?
        supports_query_hash = PgHero.databases[db_id].supports_query_hash?
        supports_query_stats_user = PgHero.databases[db_id].supports_query_stats_user?

        values =
          db_query_stats.map do |qs|
            values = [
              db_id,
              qs["query"],
              qs["total_minutes"].to_f * 60 * 1000,
              qs["calls"],
              now
            ]
            values << qs["query_hash"] if supports_query_hash
            values << qs["user"] if supports_query_stats_user
            values
          end

        columns = %w[database query total_time calls captured_at]
        columns << "query_hash" if supports_query_hash
        columns << "user" if supports_query_stats_user

        insert_stats("pghero_query_stats", columns, values)
      end
    end
  end
end

#disable_query_stats ⇒ Object 



52
53
54
55
 
# File 'lib/pghero/methods/query_stats.rb', line 52

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

#enable_query_stats ⇒ Object 



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

def enable_query_stats
  execute("CREATE EXTENSION pg_stat_statements")
end

#historical_query_stats_enabled? ⇒ Boolean

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

Returns:



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/pghero/methods/query_stats.rb', line 67

def historical_query_stats_enabled?
  # TODO use schema from config
  # make sure primary database is PostgreSQL first
  ["PostgreSQL", "PostGIS"].include?(stats_connection.adapter_name) &&
  PgHero.truthy?(stats_connection.select_all(squish <<-SQL
    SELECT EXISTS (
      SELECT
        1
      FROM
        pg_catalog.pg_class c
      INNER JOIN
        pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      WHERE
        n.nspname = 'public'
        AND c.relname = 'pghero_query_stats'
        AND c.relkind = 'r'
    )
  SQL
  ).to_a.first["exists"]) && capture_query_stats?
end

#insert_stats(table, columns, values) ⇒ Object 



96
97
98
99
100
 
# File 'lib/pghero/methods/query_stats.rb', line 96

def insert_stats(table, columns, values)
  values = values.map { |v| "(#{v.map { |v2| quote(v2) }.join(",")})" }.join(",")
  columns = columns.map { |v| quote_table_name(v) }.join(",")
  stats_connection.execute("INSERT INTO #{quote_table_name(table)} (#{columns}) VALUES #{values}")
end

#query_stats(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(options = {})
  current_query_stats = options[:historical] && options[:end_at] && options[:end_at] < Time.now ? [] : current_query_stats(options)
  historical_query_stats = options[:historical] ? historical_query_stats(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"].to_f }
  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] }.first(100)
  if options[:min_average_time]
    query_stats.reject! { |q| q["average_time"].to_f < options[:min_average_time] }
  end
  if options[:min_calls]
    query_stats.reject! { |q| q["calls"].to_i < options[:min_calls] }
  end
  query_stats
end

#query_stats_available? ⇒ Boolean

Returns:



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

def query_stats_available?
  select_all("SELECT COUNT(*) AS count FROM pg_available_extensions WHERE name = 'pg_stat_statements'").first["count"].to_i > 0
end

#query_stats_enabled? ⇒ Boolean

Returns:



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

def query_stats_enabled?
  query_stats_extension_enabled? && query_stats_readable?
end

#query_stats_extension_enabled? ⇒ Boolean

Returns:



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

def query_stats_extension_enabled?
  select_all("SELECT COUNT(*) AS count FROM pg_extension WHERE extname = 'pg_stat_statements'").first["count"].to_i > 0
end

#query_stats_readable? ⇒ Boolean

Returns:



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

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

#reset_query_stats ⇒ Object 



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

def reset_query_stats
  if query_stats_enabled?
    execute("SELECT pg_stat_statements_reset()")
    true
  else
    false
  end
end

#slow_queries(options = {}) ⇒ Object 



153
154
155
156
 
# File 'lib/pghero/methods/query_stats.rb', line 153

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

#supports_query_hash? ⇒ Boolean

Returns:



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

def supports_query_hash?
  @supports_query_hash ||= server_version_num >= 90400 && historical_query_stats_enabled? && PgHero::QueryStats.column_names.include?("query_hash")
end

#supports_query_stats_user? ⇒ Boolean

Returns:



92
93
94
 
# File 'lib/pghero/methods/query_stats.rb', line 92

def supports_query_stats_user?
  @supports_query_stats_user ||= historical_query_stats_enabled? && PgHero::QueryStats.column_names.include?("user")
end