Ruby Sequel的工具或补丁，或者ORM的SQL查询工具，用于慢速查询日志分析

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

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

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://samsaffron.com/archive/2012/07/12/miniprofiler-ruby-edition

#1