SQL Query performance logging

Usage Stories
1

I would like to know which parts of my application are slow. Is there a way to configure ROM or is there a separate tool, that would log the long queries?
I am using Hanami 1.3, but I substituted its DB layer with ROM configured via dry-system.

2

@apohllo everything that works with Sequel will work with rom-sql. How exactly are you planning to monitor this?

3

Actually I have identified the primary reason for very bad application performance.

For an unknown reason hanami-reloader and shotgun sneaked into production env. causing e.g. ROM container to be instantiated for each request (even though the entry was memoized…). As a result at each request all DB-based metadata were queried for yielding a huge DB traffic.
The performance gains of removing these two gems were dramatic.

But in future I would like to have a similar feature as in Rails, i.e. a number showing how much time did the request spend performing DB queries. One aggregate value for the full request would be enough. If there is an easy way to do so, I would like to know it. If there’s not, this is not a big issue at the moment.

BTW I know about logging queries to STDOUT or file, but it is not easy to parse and aggregate. It’s also a performance hit. I use it only for debugging purposes during development.

4

@apohllo I’m deeply passionate about logging and monitoring, so you can be sure this will be 1st-class stuff in rom 6.0 and hanami 2.0. I already started working on a dedicated logger, we also have dry-monitor and a notification system in place. I just need to wire all of this together :slightly_smiling_face:You can do it already but it needs some manual setup:


    rom_config = ROM::Configuration.new(:sql, container[:settings].database_url)

    rom_config.plugin(:sql, relations: :instrumentation) do |plugin_config|D
      plugin_config.notifications = notifications
    end

Where notifications is an instance of Dry::Monitor::Notifications, so you can subscribe to its events and you’ll get info about queries, time spent, context etc. For example, here’s a built-in Dry::Monitor::SQL::Logger that subscribes to such notifications.

This is actually “old stuff” but I never got around to wrapping it all up and polishing it. It’s one of my priorities for H2 now so the time has come finally :slightly_smiling_face:

5

Well, it looks really simple, so I will definitely try it out.

I am very thrilled about Hanami 2.0. I’m not asking about the timeline, but I hope it will be available soon :wink:

1 Like
6

Give 2.0.0.alpha2 a try and report any issues/suggestions!