DEV Community

Ahmad Ya'kob Ubaidullah
Ahmad Ya'kob Ubaidullah

Posted on

Tracking long queries into csv

If you have tons of queries executed in a Job or in a method, and you want to monitor which queries are slow and need attention, how do you track them ?

I was wondering what really dragging my cache job running time. The job supposed to be done in under 4 minutes suddenly spiked to 22 minutes.

I found query_tracker gem which displays all the info I needed. It also allows slack integration. In my case, I wanted it to store query informations into CSV. Fortunately the gem allows custom handlers.

Below is how I did my custom handlers :

QueryTrack::Settings.configure do |config|
  config.duration = 0.5
  config.notifications.custom_handler = -> (sql, duration, trace) {
    CSV.open("log/query_tracker.csv", "a+") do |csv|
      csv << [Time.now, duration, sql, trace]
    end
  }
end
Enter fullscreen mode Exit fullscreen mode

notice the a+ open type, that will prepend the file.

Now all the queries information are visible for your next action :
Alt Text

You can always use the sort function to get more insights :
Alt Text

In my case, this is my queries that needs attention :
Alt Text

Thank you :)

Discussion (0)