A benchmarking script to compare different kinds of `busy_handler` implementations for a SQLite3::Database connection under concurrent load

sqlite-busy-handler-benchmarks.rb

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  gem "sqlite3"
  gem "enumerable-statistics"
end

require "benchmark"
require "yaml"
require "sqlite3"
require "enumerable/statistics"

retry_interval_60_us = 6e-5 # 60 microseconds
retry_interval_1_ms = 1e-3 # 1 millisecond

def setup_busy_handler(retry_interval:, timeout_seconds:, modulo:, precise:)
  case [modulo, precise]
  when [true, true]
    Proc.new do |count|
      @start_time = Process.clock_gettime(Process::CLOCK_MONOTONIC) if count == 0
      timed_out = if (count % 100).zero?
        elapsed_time = Process.clock_gettime(Process::CLOCK_MONOTONIC) - @start_time
        timed_out = elapsed_time > timeout_seconds
      else
        false
      end

      return false if timed_out

      sleep(retry_interval)
    end
  when [true, false]
    Proc.new do |count|
      timed_out = (count % 100).zero? ? ((count * retry_interval) > timeout_seconds) : false

      return false if timed_out

      sleep(retry_interval)
    end
  when [false, true]
    Proc.new do |count|
      @start_time = Process.clock_gettime(Process::CLOCK_MONOTONIC) if count == 0
      elapsed_time = Process.clock_gettime(Process::CLOCK_MONOTONIC) - @start_time

      return false if elapsed_time > timeout_seconds

      sleep(retry_interval)
    end
  when [false, false]
    Proc.new do |count|
      return false if (count * retry_interval) > timeout_seconds

      sleep(retry_interval)
    end
  end
end

def setup_variation(retry_interval:, modulo:, precise:)
  Proc.new do
    [1, 2].map do |i|
      Thread.new do
        db = SQLite3::Database.new("test.sqlite")
        db.busy_handler(setup_busy_handler(retry_interval:, timeout_seconds: 5, modulo:, precise:))
        db.transaction(:immediate) do
          db.execute("INSERT INTO t (data) VALUES (?)", rand(1000).to_s)
          sleep 1
          db.execute("INSERT INTO t (data) VALUES (?)", rand(1000).to_s)
        end
      end
    end.each(&:join)
  end
end

variations = {
  "^01ms%" => setup_variation(retry_interval: retry_interval_1_ms, modulo: true, precise: true),
  "~01ms%" => setup_variation(retry_interval: retry_interval_1_ms, modulo: true, precise: false),
  "^01ms-" => setup_variation(retry_interval: retry_interval_1_ms, modulo: false, precise: true),
  "~01ms-" => setup_variation(retry_interval: retry_interval_1_ms, modulo: false, precise: false),
  "^60us%" => setup_variation(retry_interval: retry_interval_60_us, modulo: true, precise: true),
  "~60us%" => setup_variation(retry_interval: retry_interval_60_us, modulo: true, precise: false),
  "^60us-" => setup_variation(retry_interval: retry_interval_60_us, modulo: false, precise: true),
  "~60us-" => setup_variation(retry_interval: retry_interval_60_us, modulo: false, precise: false),
}

results = {}
50.times do
  File.delete("test.sqlite") if File.exist?("test.sqlite")
  @db = SQLite3::Database.new("test.sqlite")
  @db.transaction do
    @db.execute "create table t ( id integer primary key, data text )"
    @db.execute "insert into t ( data ) values ( 'init' )"
  end

  output = Benchmark.bmbm do |x|
    variations.to_a.shuffle.each do |name, variation|
      x.report(name, &variation)
    end
  end

  output.map { |r| [r.label, r.real] }.each do |label, real|
    results[label] ||= {}
    results[label]["results"] ||= []
    results[label]["results"] << real
  end

  @db.close
  File.delete("test.sqlite")
end

results.transform_values! do |v|
  v["mean"] = ('%.6f' % v["results"].mean).to_f
  v["median"] = ('%.6f' % v["results"].median).to_f
  v["variance"] = ('%.9f' % v["results"].variance).to_f
  v["stdev"] = ('%.6f' % v["results"].stdev).to_f
  v
end

puts YAML.dump(results)

p results.sort_by { |_, h| h["median"] }.map { |label, h| [label, h["median"]] }

p results.sort_by { |_, h| h["mean"] }.map { |label, h| [label, h["mean"]] }