How to: Benchmark an Active Record query with a Ruby script

Copy# Run this script with e.g. `rails runner lib/scripts/benchmark.rb`

require 'open3'

# For debugging
# Rails.logger = Logger.new(STDOUT)
# ActiveRecord::Base.logger = Logger.new(STDOUT)

ITERATIONS = 100
PERCENTILE = 0.95

def system!(*)
  stdout_str, error_str, status = Open3.capture3(*)

  unless status.success?
    puts [status, stdout_str, error_str].inspect
    raise 'System execution failed'
  end
end

# https://stackoverflow.com/a/11785414
def calculate_percentile(values, percentile)
  values_sorted = values.sort
  k = (percentile * (values_sorted.length - 1) + 1).floor - 1
  f = (percentile * (values_sorted.length - 1) + 1).modulo(1)

  values_sorted[k] + (f * (values_sorted[k + 1] - values_sorted[k]))
end

benchmarks = [
  %(User.where('name ILIKE ?', '%test%').first),
  %(User.where('email ILIKE ?', '%test%').first),
]

measurements = benchmarks.index_with do |_benchmark|
  []
end

benchmarks.each do |benchmark|
  ITERATIONS.times do
    real_time = Benchmark.measure { eval(benchmark) }.real
    measurements[benchmark] << real_time
    puts [real_time.round(3), benchmark].inspect

    ActiveRecord::Base.connection.clear_query_cache
    ActiveRecord::Base.connection_pool.release_connection
    system!('sudo service postgresql stop')
    system!('sync')
    system!("sudo sh -c 'echo 3 > /proc/sys/vm/drop_caches'")
    system!('sudo service postgresql start')
  end
end

puts 'Summary:'
measurements.each do |key, value|
  puts "#{key}: #{calculate_percentile(value, PERCENTILE)}"
end