lib/pghero/methods/query_stats.rb in pghero-1.2.4 vs lib/pghero/methods/query_stats.rb in pghero-1.3.0
- old
+ new
@@ -1,23 +1,24 @@
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)).index_by { |q| q["query"] }
- historical_query_stats = (options[:historical] ? historical_query_stats(options) : []).index_by { |q| q["query"] }
+ current_query_stats = (options[:historical] && options[:end_at] && options[:end_at] < Time.now ? [] : current_query_stats(options)).index_by { |q| q["query_hash"] }
+ historical_query_stats = (options[:historical] ? historical_query_stats(options) : []).index_by { |q| q["query_hash"] }
current_query_stats.default = {}
historical_query_stats.default = {}
query_stats = []
- (current_query_stats.keys + historical_query_stats.keys).uniq.each do |query|
+ (current_query_stats.keys + historical_query_stats.keys).uniq.each do |query_hash|
value = {
- "query" => query,
- "total_minutes" => current_query_stats[query]["total_minutes"].to_f + historical_query_stats[query]["total_minutes"].to_f,
- "calls" => current_query_stats[query]["calls"].to_i + historical_query_stats[query]["calls"].to_i
+ "query" => current_query_stats[query_hash]["query"] || historical_query_stats[query_hash]["query"],
+ "query_hash" => query_hash,
+ "total_minutes" => current_query_stats[query_hash]["total_minutes"].to_f + historical_query_stats[query_hash]["total_minutes"].to_f,
+ "calls" => current_query_stats[query_hash]["calls"].to_i + historical_query_stats[query_hash]["calls"].to_i
}
value["average_time"] = value["total_minutes"] * 1000 * 60 / value["calls"]
- value["total_percent"] = value["total_minutes"] * 100.0 / (current_query_stats[query]["all_queries_total_minutes"].to_f + historical_query_stats[query]["all_queries_total_minutes"].to_f)
+ value["total_percent"] = value["total_minutes"] * 100.0 / (current_query_stats[query_hash]["all_queries_total_minutes"].to_f + historical_query_stats[query_hash]["all_queries_total_minutes"].to_f)
query_stats << value
end
sort = options[:sort] || "total_minutes"
query_stats = query_stats.sort_by { |q| -q[sort] }.first(100)
if options[:min_average_time]
@@ -59,28 +60,60 @@
else
false
end
end
+ # 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
def capture_query_stats
- config["databases"].keys.each do |database|
+ # get database names
+ pg_databases = {}
+ supports_query_hash = {}
+ config["databases"].each do |k, _|
+ with(k) do
+ pg_databases[k] = execute("SELECT current_database()").first["current_database"]
+ supports_query_hash[k] = supports_query_hash?
+ end
+ end
+
+ config["databases"].reject { |_, v| v["capture_query_stats"] && v["capture_query_stats"] != true }.each do |database, _|
with(database) do
+ mapping = {database => pg_databases[database]}
+ config["databases"].select { |_, v| v["capture_query_stats"] == database }.each do |k, _|
+ mapping[k] = pg_databases[k]
+ end
+
now = Time.now
- query_stats = self.query_stats(limit: 1000000)
- if query_stats.any? && reset_query_stats
- values =
- query_stats.map do |qs|
- [
- database,
- qs["query"],
- qs["total_minutes"].to_f * 60 * 1000,
- qs["calls"],
- now
- ].map { |v| quote(v) }.join(",")
- end.map { |v| "(#{v})" }.join(",")
+ query_stats = {}
+ mapping.each do |db, pg_database|
+ query_stats[db] = self.query_stats(limit: 1000000, database: pg_database)
+ end
- stats_connection.execute("INSERT INTO pghero_query_stats (database, query, total_time, calls, captured_at) VALUES #{values}")
+ if query_stats.any? { |_, v| v.any? } && reset_query_stats
+ query_stats.each do |db, db_query_stats|
+ if db_query_stats.any?
+ values =
+ db_query_stats.map do |qs|
+ values = [
+ db,
+ qs["query"],
+ qs["total_minutes"].to_f * 60 * 1000,
+ qs["calls"],
+ now
+ ]
+ values << qs["query_hash"] if supports_query_hash[db]
+ values.map { |v| quote(v) }.join(",")
+ end.map { |v| "(#{v})" }.join(",")
+
+ columns = %w[database query total_time calls captured_at]
+ columns << "query_hash" if supports_query_hash[db]
+ stats_connection.execute("INSERT INTO pghero_query_stats (#{columns.join(", ")}) VALUES #{values}")
+ end
+ end
end
end
end
end
@@ -113,26 +146,29 @@
# http://www.craigkerstiens.com/2013/01/10/more-on-postgres-performance/
def current_query_stats(options = {})
if query_stats_enabled?
limit = options[:limit] || 100
sort = options[:sort] || "total_minutes"
+ database = options[:database] ? quote(options[:database]) : "current_database()"
select_all <<-SQL
WITH query_stats AS (
SELECT
query,
- (total_time / 1000 / 60) as total_minutes,
- (total_time / calls) as average_time,
+ #{supports_query_hash? ? "queryid" : "md5(query)"} AS query_hash,
+ (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
WHERE
- pg_database.datname = current_database()
+ pg_database.datname = #{database}
)
SELECT
query,
+ query_hash,
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
@@ -151,24 +187,27 @@
if historical_query_stats_enabled?
sort = options[:sort] || "total_minutes"
stats_connection.select_all squish <<-SQL
WITH query_stats AS (
SELECT
- query,
- (SUM(total_time) / 1000 / 60) as total_minutes,
- (SUM(total_time) / SUM(calls)) as average_time,
- SUM(calls) as calls
+ #{supports_query_hash? ? "query_hash" : "md5(query)"} AS query_hash,
+ MAX(query) 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(current_database)}
+ #{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])}" : ""}
GROUP BY
- query
+ 1
)
SELECT
+ query_hash,
query,
total_minutes,
average_time,
calls,
total_minutes * 100.0 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent,
@@ -180,9 +219,22 @@
LIMIT 100
SQL
else
[]
end
+ end
+
+ def supports_query_hash?
+ @supports_query_hash ||= {}
+ if @supports_query_hash[current_database].nil?
+ @supports_query_hash[current_database] = server_version >= 90400 && historical_query_stats_enabled? && PgHero::QueryStats.column_names.include?("query_hash")
+ end
+ @supports_query_hash[current_database]
+ end
+
+ def server_version
+ @server_version ||= {}
+ @server_version[current_database] ||= select_all("SHOW server_version_num").first["server_version_num"].to_i
end
end
end
end