Log your slow queries in slack using Rails

This post is inspired by Monitoring Slow SQL Queries via Slack. After reading that, I thought, how we can implement this in Rails. Then after a thorough search, I found that ActiveSupport:: Notifications could help us in achieving this. ActiveSupport:: Notifications provides an instrumentation API to track not only SQL queries but also view rendering information. Prior monitoring SQL queries, one should subscribe eventsql.active_record along with callback as shown below.

callback = lambda {|*args| ... }

ActiveSupport::Notifications
  .subscribed(callback,"sql.active_record") do
  # your code is here
end

For every SQL that fires by an active record, callback will get executed.

callback = lambda do |_, start_time, end_time, *args|
  puts args.last[:sql]
  puts "#{(end_time - start_time) * 1000} ms"
end

ActiveSupport::Notifications.subscribed(callback, "sql.active_record") do

   Farmer.accessible_by(current_ability).first(10)

end

When you run above code you would following output:

Now let’s notify these slow queries on to slack. First, you have to register slack webhook URL. After that please follow steps below:

# Gemfile
gem 'slack-notifier'

in your codebase, please add following code:

if duration > 2.0
  url = <slack webhook URL>
  notifier = Slack::Notifier.new url, 
             channel: "#ror", 
             username: "SQL-bot"

  attachment = {
     fallback: '',
     text: "#{sql} took #{duration}",
     color: "warning"
  }

  notifier.post text: "Slow SQL Queries", attachments: [attachment]
end

Final code will appear as below once you merged instrumentation and notifications:

callback = lambda do |_, start_time, end_time, *args|
  sql = args.last[:sql]
  duration = (end_time - start_time) * 1000

  if duration > 2.0
    url = <slack webhook URL>
    notifier = Slack::Notifier.new url, channel: "#ror", 
                                        username: "SQL-bot"

  attachment = {
      fallback: '',
      text: "#{sql} took #{duration}",
      color: "warning"
  }

  notifier.post text: "Slow SQL Queries", attachments: [attachment]
end

ActiveSupport::Notifications.subscribed(callback, "sql.active_record") do
     Farmer.accessible_by(current_ability).first(10)
   end
end

And on your slack

This is a basic version of instrumenting your code base. For detailed metrics, it’s better to use some tools like new-relic or skylight.

Thanks for reading. Happy query logging!!!.