debugging rails internal query execution

while we were working with somewhere in… ads project we came up with some debugging and performance mesuring tool, here in my post i will describe how you can use it for yourself.

query debugging –
picture-16
query debugging tool logs every executed query from active record and keep them in memory and using assisting template code it display all executed query from the active page.

also it executes query with mysql “explain” keyword. so on the same window you can see mysql query execution plan.
it helped us to track down queries which were not hitting the right index.
this is very simple trick – go through the code below –

module DebugUtil
class QueryDebug
@@QUERIES = {}
def self.add(p_query, p_report)
@@QUERIES[p_query] = p_report
end

def self.queries
q = @@QUERIES
clean
return q
end

def self.clean
@@QUERIES = {}
end
end
end

QueryDebug class keeps all executed query and their explained resultset in to the static array. so later in template QueryDebug::queries is invoked to get all executed query for the current page.

here is how we trap the query execution from active record –

if defined?(QUERY_DEBUG_ENABLED) && QUERY_DEBUG_ENABLED
ActiveRecord::ConnectionAdapters::MysqlAdapter.class_eval do
alias __existing_execute_method execute

def execute(sql, name = nil)
if sql.match(/^SELECT/i)
report = []
@connection.query(“explain #{sql}”).each do |row|
report < < row
end
DebugUtil::QueryDebug.add(sql, report)
end
__existing_execute_method(sql, name)
end
end

Object.class_eval do
def raise_during_query_debug
raise DebugUtil::QueryDebug::queries.inspect
end
end
end

you can see we have used “QUERY_DEBUG_ENABLED” constant to ensure whether this is enabled by intention.
now see how we are rendering on our template.

query debug

  1. checked
    < %= row.join(“ “) % >

we put this code in common layout. so it renders on every page. thats all 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

my tweets

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

September 2008
S S M T W T F
« Jun   Oct »
 12345
6789101112
13141516171819
20212223242526
27282930  

Flickr Photos

RSS my shared links on google reader

  • An error has occurred; the feed is probably down. Try again later.
%d bloggers like this: