Monitor and Optimize database queries in Rails

It’s good to keep your code maintainable & optimized, especially while working with large enterprise applications.

Growing application codebase demands continuous refactoring in terms of making it more efficient.

Here is a quick guide on the usage of a couple of gems that can help to automate process of identifying instances in codebase where database queries can be optimized. It’s advisable to use them in development environment.

Gem: lol_dba

This gem can be used to find list of database columns that can be Indexed.

Add gem in Gemfile

group :development do  
  gem 'lol_dba'
end

Install bundle

$ bundle install

Run below rake tasks to get a list of fields that should be Indexed

$ rake db:find_indexes

Gem: bullet

This gem can be used to find instances where -

1. eager loading is required (to avoid N+1 query problem)
2. eager loading is used but not necessary to have it
3. counter cache should be used

Add gem in Gemfile

group :development do  
  gem 'bullet'
end

Install bundle

$ bundle install

Configure for development environment — add below code in config/environments/development.rb file

config.after_initialize do
  Bullet.enable = true
  Bullet.alert = true
  Bullet.bullet_logger = true
  Bullet.console = true
  Bullet.rails_logger = true
end

Above code enables bullet notifications in form of alert box on page, a separate bullet.log file, logging warnings in browser’s console.log and logging warnings in rails console log.

Restart your application & develop/ browse through it in development environment. Bullet simultaneously monitors queries & notifies you about query problems based on your configuration defined in environment file.

If you have used Bullet.bullet_logger = true option, you will see a new file created in logs directory — bullet.log

You can check log populated by bullet & work on refactoring required in codebase.

In bullet.log file, search for -

1. USE eager loading detected [ N+1 query notification ]
2. AVOID eager loading detected [ Unused eager loading notification ]
3. Need Counter Cache

Please visit gem’s homepage to check full list of notifiers supported by bullet & other configuration options (i.e, whitelist N+1 association)