lib/pghero/methods/query_stats.rb in pghero-1.7.0 vs lib/pghero/methods/query_stats.rb in pghero-2.0.0
- old
+ new
@@ -1,104 +1,87 @@
module PgHero
module Methods
module QueryStats
- 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) : []
+ 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"]] })
+ 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 }
+ 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
+ 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] }
+ 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 options[:min_calls]
- query_stats.reject! { |q| q["calls"].to_i < options[:min_calls] }
+ if min_calls
+ query_stats.reject! { |q| q[:calls] < min_calls }
end
query_stats
end
def query_stats_available?
- select_all("SELECT COUNT(*) AS count FROM pg_available_extensions WHERE name = 'pg_stat_statements'").first["count"].to_i > 0
+ select_one("SELECT COUNT(*) AS count FROM pg_available_extensions WHERE name = 'pg_stat_statements'") > 0
end
+ # only cache if true
def query_stats_enabled?
- query_stats_extension_enabled? && query_stats_readable?
+ @query_stats_enabled ||= query_stats_readable?
end
def query_stats_extension_enabled?
- select_all("SELECT COUNT(*) AS count FROM pg_extension WHERE extname = 'pg_stat_statements'").first["count"].to_i > 0
+ select_one("SELECT COUNT(*) AS count FROM pg_extension WHERE extname = 'pg_stat_statements'") > 0
end
def query_stats_readable?
select_all("SELECT * FROM pg_stat_statements LIMIT 1")
true
rescue ActiveRecord::StatementInvalid
false
end
def enable_query_stats
- execute("CREATE EXTENSION pg_stat_statements")
+ execute("CREATE EXTENSION IF NOT EXISTS pg_stat_statements")
+ true
end
def disable_query_stats
execute("DROP EXTENSION IF EXISTS pg_stat_statements")
true
end
def reset_query_stats
- if query_stats_enabled?
- execute("SELECT pg_stat_statements_reset()")
- true
- else
- false
- end
+ execute("SELECT pg_stat_statements_reset()")
+ true
+ rescue ActiveRecord::StatementInvalid
+ false
end
# http://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema
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?
+ query_stats_table_exists? && capture_query_stats? && !missing_query_stats_columns.any?
end
- def supports_query_hash?
- @supports_query_hash ||= server_version_num >= 90400 && historical_query_stats_enabled? && PgHero::QueryStats.column_names.include?("query_hash")
+ def query_stats_table_exists?
+ table_exists?("pghero_query_stats")
end
- def supports_query_stats_user?
- @supports_query_stats_user ||= historical_query_stats_enabled? && PgHero::QueryStats.column_names.include?("user")
+ def missing_query_stats_columns
+ %w(query_hash user) - PgHero::QueryStats.column_names
end
- 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}")
+ def supports_query_hash?
+ server_version_num >= 90400
end
# resetting query stats will reset across the entire Postgres instance
# this is problematic if multiple PgHero databases use the same Postgres instance
#
@@ -121,71 +104,84 @@
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
+ qs[:query],
+ qs[:total_minutes] * 60 * 1000,
+ qs[:calls],
+ now,
+ qs[:query_hash],
+ qs[:user]
]
- 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
-
+ columns = %w[database query total_time calls captured_at query_hash user]
insert_stats("pghero_query_stats", columns, values)
end
end
end
end
- 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 }
+ 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
- private
-
- def stats_connection
- ::PgHero::QueryStats.connection
+ def query_hash_stats(query_hash)
+ 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)}
+ ORDER BY
+ 1 ASC
+ SQL
+ else
+ raise NotEnabled, "Query hash stats not enabled"
+ end
end
+ private
+
# http://www.craigkerstiens.com/2013/01/10/more-on-postgres-performance/
- def current_query_stats(options = {})
+ def current_query_stats(limit: nil, sort: nil, database: nil, query_hash: nil)
if query_stats_enabled?
- limit = options[:limit] || 100
- sort = options[:sort] || "total_minutes"
- database = options[:database] ? quote(options[:database]) : "current_database()"
+ limit ||= 100
+ sort ||= "total_minutes"
select_all <<-SQL
WITH query_stats AS (
SELECT
LEFT(query, 10000) AS query,
#{supports_query_hash? ? "queryid" : "md5(query)"} AS query_hash,
- #{supports_query_stats_user? ? "rolname" : "NULL::text"} AS user,
+ rolname AS user,
(total_time / 1000 / 60) AS total_minutes,
(total_time / calls) AS average_time,
calls
FROM
pg_stat_statements
INNER JOIN
pg_database ON pg_database.oid = pg_stat_statements.dbid
INNER JOIN
pg_roles ON pg_roles.oid = pg_stat_statements.userid
WHERE
- pg_database.datname = #{database}
+ pg_database.datname = #{database ? quote(database) : "current_database()"}
+ #{query_hash ? "AND queryid = #{quote(query_hash)}" : nil}
)
SELECT
query,
query_hash,
query_stats.user,
@@ -199,40 +195,42 @@
ORDER BY
#{quote_table_name(sort)} DESC
LIMIT #{limit.to_i}
SQL
else
- []
+ raise NotEnabled, "Query stats not enabled"
end
end
- def historical_query_stats(options = {})
+ def historical_query_stats(sort: nil, start_at: nil, end_at: nil, query_hash: nil)
if historical_query_stats_enabled?
- sort = options[:sort] || "total_minutes"
- stats_connection.select_all squish <<-SQL
+ sort ||= "total_minutes"
+ select_all_stats <<-SQL
WITH query_stats AS (
SELECT
#{supports_query_hash? ? "query_hash" : "md5(query)"} AS query_hash,
- #{supports_query_stats_user? ? "pghero_query_stats.user" : "NULL::text"} AS user,
- array_agg(LEFT(query, 10000)) AS query,
+ pghero_query_stats.user AS user,
+ array_agg(LEFT(query, 10000) ORDER BY REPLACE(LEFT(query, 1000), '?', '!') COLLATE "C" ASC) AS query,
(SUM(total_time) / 1000 / 60) AS total_minutes,
(SUM(total_time) / SUM(calls)) AS average_time,
SUM(calls) AS calls
FROM
pghero_query_stats
WHERE
database = #{quote(id)}
#{supports_query_hash? ? "AND query_hash IS NOT NULL" : ""}
- #{options[:start_at] ? "AND captured_at >= #{quote(options[:start_at])}" : ""}
- #{options[:end_at] ? "AND captured_at <= #{quote(options[:end_at])}" : ""}
+ #{start_at ? "AND captured_at >= #{quote(start_at)}" : ""}
+ #{end_at ? "AND captured_at <= #{quote(end_at)}" : ""}
+ #{query_hash ? "AND query_hash = #{quote(query_hash)}" : ""}
GROUP BY
1, 2
)
SELECT
query_hash,
query_stats.user,
- query[1],
+ query[1] AS query,
+ query[array_length(query, 1)] AS explainable_query,
total_minutes,
average_time,
calls,
total_minutes * 100.0 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent,
(SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes
@@ -241,32 +239,33 @@
ORDER BY
#{quote_table_name(sort)} DESC
LIMIT 100
SQL
else
- []
+ raise NotEnabled, "Historical query stats not enabled"
end
end
- def server_version_num
- @server_version ||= select_all("SHOW server_version_num").first["server_version_num"].to_i
- end
-
def combine_query_stats(grouped_stats)
query_stats = []
grouped_stats.each do |_, stats2|
value = {
- "query" => (stats2.find { |s| s["query"] } || {})["query"],
- "user" => (stats2.find { |s| s["user"] } || {})["user"],
- "query_hash" => (stats2.find { |s| s["query"] } || {})["query_hash"],
- "total_minutes" => stats2.sum { |s| s["total_minutes"].to_f },
- "calls" => stats2.sum { |s| s["calls"].to_i },
- "all_queries_total_minutes" => stats2.sum { |s| s["all_queries_total_minutes"].to_f }
+ query: (stats2.find { |s| s[:query] } || {})[:query],
+ user: (stats2.find { |s| s[:user] } || {})[:user],
+ query_hash: (stats2.find { |s| s[:query_hash] } || {})[:query_hash],
+ total_minutes: stats2.sum { |s| s[:total_minutes] },
+ calls: stats2.sum { |s| s[:calls] }.to_i,
+ all_queries_total_minutes: stats2.sum { |s| s[:all_queries_total_minutes] }
}
- value["total_percent"] = value["total_minutes"] * 100.0 / value["all_queries_total_minutes"]
+ value[:total_percent] = value[:total_minutes] * 100.0 / value[:all_queries_total_minutes]
+ value[:explainable_query] = stats2.map { |s| s[:explainable_query] }.select { |q| q && explainable?(q) }.first
query_stats << value
end
query_stats
+ end
+
+ def explainable?(query)
+ query =~ /select/i && !query.include?("?)") && !query.include?("= ?") && !query.include?("$1") && query !~ /limit \?/i
end
# removes comments
# combines ?, ?, ? => ?
def normalize_query(query)