So we have MySQL slow query logs from a suite of applications, mostly in Ruby, some using rails activerecord others using sequel. We'd love to be able to easily track a particular (MySQL) slow query back to the code which generated it. Is there a feature that can be enabled in these tools, or a patch that can be applied to them, which adds instrumentation embedded into SQL comments ... something like a FILE and FUNCTION identifier?
因此,我们有一组应用程序的MySQL慢速查询日志,大部分是Ruby,有些使用rails activerecord,有些使用sequel。我们希望能够轻松地跟踪一个特定的(MySQL)慢速查询返回到生成它的代码。在这些工具中是否有可以启用的特性,或者可以应用到这些工具上的补丁,将嵌入到SQL注释中的工具……文件和函数标识符之类的东西?
2 个解决方案
#1
2
Interesting question, here is how I would handle it...
有趣的问题是,我该如何处理……
I would use config.active_record.auto_explain_threshold_in_seconds
to automatically explain slow queries, as it sounds like you are doing.
我将用config.active_record。auto_explain_old_in_seconds自动解释慢速查询,因为它听起来像您在做的事情。
Then, I would override the logging_query_plan
method in ActiveRecord::Explain
to add any relevant data to your log. Here is an example that adds the current stacktrace:
然后,我将重写ActiveRecord:::Explain中的logging_query_plan方法,以便向日志添加任何相关数据。下面是一个添加当前堆栈跟踪的示例:
# /config/initializers/add_additional_instumentation_to_explain.rb
module ActiveRecord
module Explain
def logging_query_plan # :nodoc:
return yield unless logger
threshold = auto_explain_threshold_in_seconds
current = Thread.current
if threshold && current[:available_queries_for_explain].nil?
begin
queries = current[:available_queries_for_explain] = []
start = Time.now
result = yield
if Time.now - start > threshold
# START ADDING ADDITIONAL INFORMATION
begin
puts 'ADDING ADDITIONAL INFORMATION...'
raise 'foo'
rescue
puts 'DISPLAYING THE CURRENT STACKTRACE FOR THE FOLLOWING EXPLAIN'
puts $@
end
logger.warn(exec_explain(queries))
end
result
ensure
current[:available_queries_for_explain] = nil
end
else
yield
end
end
end
end
I would prefer a method that doesn't rely on an entire modified method from rails, but this is the only way that I could get it to work reliably.
我更喜欢不依赖于整个rails修改的方法的方法,但这是使它可靠工作的唯一方法。
For what it's worth, this would be pretty easy to spin off into a gem, with a new one for each rails version, and just include the relevant gem for your version, per app, as it sounds like you may possibly support multiple versions of rails. This would serve to minimize some of the brittleness of said approach. Anyway, hope this helps - good luck!
值得注意的是,这将很容易分解成一个gem,每个rails版本都有一个新的gem,并且每个应用都包含相应的gem,因为听起来您可能支持多个rails版本。这将有助于将上述方法的脆弱性降到最低。不管怎样,希望这能帮助你——祝你好运!
#2
0
I highly recommend rack-mini-profiler for helping to quickly understand how your Rails (or other rack based) app is spending it's time. It's especially good at showing which Ruby code generated which SQL statements. Visit all three links below to learn to use it well. Good luck.
我强烈推荐rack-mini-profiler,它可以帮助您快速了解Rails(或其他基于机架的)应用程序是如何花费时间的。它特别擅长显示哪些Ruby代码生成了哪些SQL语句。访问下面的三个链接,学习如何使用它。祝你好运。
http://miniprofiler.com/
http://railscasts.com/episodes/368-miniprofiler
http://railscasts.com/episodes/368-miniprofiler
http://samsaffron.com/archive/2012/07/12/miniprofiler-ruby-edition
http://samsaffron.com/archive/2012/07/12/miniprofiler-ruby-edition
#1
2
Interesting question, here is how I would handle it...
有趣的问题是,我该如何处理……
I would use config.active_record.auto_explain_threshold_in_seconds
to automatically explain slow queries, as it sounds like you are doing.
我将用config.active_record。auto_explain_old_in_seconds自动解释慢速查询,因为它听起来像您在做的事情。
Then, I would override the logging_query_plan
method in ActiveRecord::Explain
to add any relevant data to your log. Here is an example that adds the current stacktrace:
然后,我将重写ActiveRecord:::Explain中的logging_query_plan方法,以便向日志添加任何相关数据。下面是一个添加当前堆栈跟踪的示例:
# /config/initializers/add_additional_instumentation_to_explain.rb
module ActiveRecord
module Explain
def logging_query_plan # :nodoc:
return yield unless logger
threshold = auto_explain_threshold_in_seconds
current = Thread.current
if threshold && current[:available_queries_for_explain].nil?
begin
queries = current[:available_queries_for_explain] = []
start = Time.now
result = yield
if Time.now - start > threshold
# START ADDING ADDITIONAL INFORMATION
begin
puts 'ADDING ADDITIONAL INFORMATION...'
raise 'foo'
rescue
puts 'DISPLAYING THE CURRENT STACKTRACE FOR THE FOLLOWING EXPLAIN'
puts $@
end
logger.warn(exec_explain(queries))
end
result
ensure
current[:available_queries_for_explain] = nil
end
else
yield
end
end
end
end
I would prefer a method that doesn't rely on an entire modified method from rails, but this is the only way that I could get it to work reliably.
我更喜欢不依赖于整个rails修改的方法的方法,但这是使它可靠工作的唯一方法。
For what it's worth, this would be pretty easy to spin off into a gem, with a new one for each rails version, and just include the relevant gem for your version, per app, as it sounds like you may possibly support multiple versions of rails. This would serve to minimize some of the brittleness of said approach. Anyway, hope this helps - good luck!
值得注意的是,这将很容易分解成一个gem,每个rails版本都有一个新的gem,并且每个应用都包含相应的gem,因为听起来您可能支持多个rails版本。这将有助于将上述方法的脆弱性降到最低。不管怎样,希望这能帮助你——祝你好运!
#2
0
I highly recommend rack-mini-profiler for helping to quickly understand how your Rails (or other rack based) app is spending it's time. It's especially good at showing which Ruby code generated which SQL statements. Visit all three links below to learn to use it well. Good luck.
我强烈推荐rack-mini-profiler,它可以帮助您快速了解Rails(或其他基于机架的)应用程序是如何花费时间的。它特别擅长显示哪些Ruby代码生成了哪些SQL语句。访问下面的三个链接,学习如何使用它。祝你好运。
http://miniprofiler.com/
http://railscasts.com/episodes/368-miniprofiler
http://railscasts.com/episodes/368-miniprofiler
http://samsaffron.com/archive/2012/07/12/miniprofiler-ruby-edition
http://samsaffron.com/archive/2012/07/12/miniprofiler-ruby-edition