Skip to content

Instantly share code, notes, and snippets.

@fractaledmind

fractaledmind/activerecord-benchmark.rb

Last active January 30, 2024 20:33
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save fractaledmind/fa7e975d59b093808334624ebe0b6f86 to your computer and use it in GitHub Desktop.
Save fractaledmind/fa7e975d59b093808334624ebe0b6f86 to your computer and use it in GitHub Desktop.
Download ZIP
A benchmarking script for ActiveRecord with the SQLite3 adapter
Raw
activerecord-benchmark.rb
require 'benchmark'
require 'active_record'
require 'pg'
# -----------------------------------------------------------------------------
INPUT_COLUMNS = {
name: "Name of benchmark",
iters: "Number of iterations the block is run",
usr_time: "Amount of user CPU time",
sys_time: "Amount of system CPU time",
ttl_time: "Amount of total CPU time",
clk_time: "Amount of actual/real/wallclock time",
txn: "Whether a transaction was used",
}
PRAGMAS = %w[
analysis_limit
application_id
auto_vacuum
automatic_index
busy_timeout
cache_size
cache_spill
case_sensitive_like
cell_size_check
checkpoint_fullfsync
data_version
defer_foreign_keys
encoding
foreign_keys
freelist_count
fullfsync
hard_heap_limit
ignore_check_constraints
integrity_check
journal_mode
journal_size_limit
legacy_alter_table
locking_mode
max_page_count
mmap_size
page_count
page_size
query_only
quick_check
read_uncommitted
recursive_triggers
reverse_unordered_selects
secure_delete
soft_heap_limit
synchronous
temp_store
threads
trusted_schema
user_version
wal_autocheckpoint
]
Result = Struct.new(*INPUT_COLUMNS.keys)
module Workbench
def measure(iterations, in_transaction = false, &block)
GC.start
label = caller_locations(1,1)[0].label.gsub("bench_", "")
measurement = Benchmark.measure(nil) do
if in_transaction
ActiveRecord::Base.transaction(&block)
else
block.call
end
end
output = measurement
.format('%u,%y,%t,%r')
.gsub(/[()]/, '')
.split(',')
.map { |s| s.strip.to_f.round(4) }
@results << Result.new(*[label, iterations, *output, in_transaction])
end
end
module Runner
def run!(log = true)
instance = new
instance.instance_variable_set(:@results, [])
instance_methods(_include_ancestors = false)
.select { |m| m.to_s.start_with?("bench_") }
.each do |m|
instance.method(m).call
TestRecord
.descendants
.each { |c| c.delete_all }
end
results = instance.instance_variable_get(:@results)
.sort_by { |result| result.clk_time }
.reverse
if log
$stdout << results
.map do |result|
sub = [
result.txn.to_s.ljust(6, ' '),
result.iters.to_s.ljust(5, ' '),
result.usr_time.to_s.ljust(7, ' '),
result.sys_time.to_s.ljust(7, ' '),
result.ttl_time.to_s.ljust(7, ' '),
].join(" ")
[
result.name.ljust(60, ' '),
result.clk_time.to_s.ljust(7, ' '),
"(#{sub})"
].join(" ")
end
.join("\n")
$stdout << "\n"
end
results
end
end
# Based off of: https://github.com/jeremyevans/simple_orm_benchmark
class BenchmarkTest
include Workbench
extend Runner
BASELINE = 64
def bench_model_object_creation
measure(BASELINE*50) do
Party.create(theme: "Halloween")
end
measure(BASELINE*50, in_transaction = true) do
Party.create(theme: "Halloween")
end
end
def bench_model_object_select
Party.insert_all((0..BASELINE*100).map { |n| {theme: "Theme: #{n}"} })
measure(BASELINE) do
Party.all
end
measure(BASELINE, in_transaction = true) do
Party.all
end
end
def bench_model_object_select_by_pk
Party.insert_all((0..BASELINE*10).map { |n| {theme: "Theme: #{n}"} })
party_ids = Party.pluck(:id)
measure(BASELINE) do
party_ids.each do |party_id|
Party.find(party_id)
end
end
measure(BASELINE, in_transaction = true) do
party_ids.each do |party_id|
Party.find(party_id)
end
end
end
def bench_model_object_select_by_attr
Party.insert_all((0..BASELINE*10).map { |n| {theme: "Theme: #{n}"} })
party_themes = Party.pluck(:theme)
measure(BASELINE) do
party_themes.each do |party_theme|
Party.find_by(theme: party_theme)
end
end
measure(BASELINE, in_transaction = true) do
party_themes.each do |party_theme|
Party.find_by(theme: party_theme)
end
end
end
def bench_model_object_select_and_save
Party.insert_all((0..BASELINE*50).map { |n| {theme: "Theme: #{n}"} })
measure(BASELINE) do
Party.all.each do |party|
party.theme += '.1'
party.save
end
end
measure(BASELINE, in_transaction = true) do
Party.all.each do |party|
party.theme += '.1'
party.save
end
end
end
def bench_model_object_destruction
Party.insert_all((0..BASELINE*100).map { |n| {theme: "Theme: #{n}"} })
measure(BASELINE) do
Party.all.each do |party|
party.destroy
end
end
measure(BASELINE, in_transaction = true) do
Party.all.each do |party|
party.destroy
end
end
end
def bench_model_object_and_associated_object_creation
measure(BASELINE*20) do
party = Party.create(theme: "X-mas")
Person.create(party: party, name: "Test_#{party.id}")
end
measure(BASELINE*20, in_transaction = true) do
party = Party.create(theme: "X-mas")
Person.create(party: party, name: "Test_#{party.id}")
end
end
def bench_model_object_and_associated_object_creation
Party.insert_all((0..BASELINE*25).map { |n| {theme: "Theme: #{n}"} })
party_ids = Party.pluck(:id)
Person.insert_all(party_ids.map { |party_id| {name: "Party: #{party_id}", party_id: party_id} })
measure(BASELINE) do
Party.all.each do |party|
party.people.each { |person| person.destroy }
party.destroy
end
end
measure(BASELINE, in_transaction = true) do
Party.all.each do |party|
party.people.each { |person| person.destroy }
party.destroy
end
end
end
def bench_eager_loading_query_per_association_with_1_to_1_records
(BASELINE*20).times do |n|
party = Party.create(theme: "Theme: #{n}")
Person.insert({name: "Name: #{n}", party_id: party.id})
end
measure(BASELINE*5/7) do
Party.preload(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
measure(BASELINE*5/7, in_transaction = true) do
Party.preload(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_eager_loading_single_query_with_1_to_1_records
(BASELINE*20).times do |n|
party = Party.create(theme: "Theme: #{n}")
Person.insert({name: "Name: #{n}", party_id: party.id})
end
measure(BASELINE*5/7) do
Party.eager_load(:people).to_a.each do |party|
party.people.each{|p| p.id}
end
end
measure(BASELINE*5/7, in_transaction = true) do
Party.eager_load(:people).to_a.each do |party|
party.people.each{|p| p.id}
end
end
end
def bench_eager_loading_query_per_association_with_1_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
end
end
measure(BASELINE*5/7) do
Party.preload(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
measure(BASELINE*5/7, in_transaction = true) do
Party.preload(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_eager_loading_single_query_with_1_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
end
end
measure(BASELINE*5/7) do
Party.eager_load(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
measure(BASELINE*5/7, in_transaction = true) do
Party.eager_load(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_eager_loading_query_per_association_with_1_to_n_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
Person.insert({name: "Name: #{nn}", other_party_id: party.id})
end
end
measure(BASELINE*2/7) do
Party.preload(:people, :other_people).to_a.each do |party|
party.people.map { |p| p.id }
party.other_people.map { |p| p.id }
end
end
measure(BASELINE*2/7, in_transaction = true) do
Party.preload(:people, :other_people).to_a.each do |party|
party.people.map { |p| p.id }
party.other_people.map { |p| p.id }
end
end
end
def bench_eager_loading_single_query_with_1_to_n_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
Person.insert({name: "Name: #{nn}", other_party_id: party.id})
end
end
measure(BASELINE*2/7) do
Party.eager_load(:people, :other_people).to_a.each do |party|
party.people.map { |p| p.id }
party.other_people.map { |p| p.id }
end
end
measure(BASELINE*2/7, in_transaction = true) do
Party.eager_load(:people, :other_people).to_a.each do |party|
party.people.map { |p| p.id }
party.other_people.map { |p| p.id }
end
end
end
def bench_lazy_loading_with_1_to_1_records
(BASELINE*20).times do |n|
party = Party.create(theme: "Theme: #{n}")
Person.insert({name: "Name: #{n}", party_id: party.id})
end
measure(1) do
Party.all.each do |party|
party.people.map { |p| p.id }
end
end
measure(1, in_transaction = true) do
Party.all.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_lazy_loading_with_1_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
end
end
measure(BASELINE/2) do
Party.all.each do |party|
party.people.map { |p| p.id }
end
end
measure(BASELINE/2, in_transaction = true) do
Party.all.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_light_threading
measure(1) do
threads = []
Math.sqrt(BASELINE).round.times do
threads << Thread.new do
Party.insert_all((0..BASELINE).map { |n| {theme: "Theme: #{n}"} })
BASELINE.times do
party = Party.first
party.update(theme: "Christmas")
party.destroy
end
ActiveRecord::Base.clear_active_connections!
end
threads.each { |t| t.join }
end
Party.create(theme: "Halloween")
end
end
def bench_heavy_threading
measure(1) do
threads = []
BASELINE.times do
threads << Thread.new do
Party.insert_all((0..BASELINE).map { |n| {theme: "Theme: #{n}"} })
BASELINE.times do
party = Party.first
party.update(theme: "Christmas")
party.destroy
end
ActiveRecord::Base.clear_active_connections!
end
threads.each { |t| t.join }
end
Party.create(theme: "Halloween")
end
end
def bench_model_object_select_json_nested
JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
party_ids = JsonParty.pluck(:id)
measure(BASELINE) do
party_ids.each do |party_id|
JsonParty.find_by("stuff->>'$.theme' = ?", "Theme")
end
end
measure(BASELINE, in_transaction = true) do
party_ids.each do |party_id|
JsonParty.find_by("stuff->>'$.theme' = ?", "Theme")
end
end
end
def bench_model_object_update_json_nested
JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
party_ids = JsonParty.pluck(:id)
measure(BASELINE) do
party_ids.each do |party_id|
JsonParty.where(id: party_id).update_all(["stuff = jsonb_set(stuff::jsonb, '{theme}', to_json(?::text)::jsonb)", 'New Theme'])
end
end
measure(BASELINE, in_transaction = true) do
party_ids.each do |party_id|
JsonParty.where(id: party_id).update_all(["stuff = jsonb_set(stuff::jsonb, '{theme}', to_json(?::text)::jsonb)", 'New Theme'])
end
end
end
def bench_model_object_update_json
JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
party_ids = JsonParty.pluck(:id)
measure(BASELINE) do
party_ids.each do |party_id|
JsonParty.where(id: party_id).update(:stuff=>{:pumpkin=>2, :candy=>1})
end
end
measure(BASELINE, in_transaction = true) do
party_ids.each do |party_id|
JsonParty.where(id: party_id).update(:stuff=>{:pumpkin=>2, :candy=>1})
end
end
end
end
# -----------------------------------------------------------------------------
class TestRecord < ActiveRecord::Base
primary_abstract_class
end
class Party < TestRecord
has_many :people
has_many :other_people, class_name: 'Person', foreign_key: :other_party_id
end
class Person < TestRecord
belongs_to :party
belongs_to :other_party, class_name: 'Party', foreign_key: 'other_party_id'
end
class JsonParty < TestRecord
end
def run_benchmark!(enhance: false, log: true)
# conn = PG.connect(dbname: 'postgres')
# conn.exec("CREATE DATABASE ar_test IF NOT EXISTS;")
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "ar_test")
ActiveRecord::Schema.define do
create_table :parties, force: true do |t|
t.string :theme
end
create_table :people, force: true do |t|
t.integer :party_id
t.integer :other_party_id
t.string :name
t.string :address
end
create_table :json_parties, force: true do |t|
t.json :stuff
end
end
if log
$stdout << {}.tap do |memo|
memo['pg-ruby version'] = PG::VERSION
memo['pg version'] = ActiveRecord::Base.connection.raw_connection.server_version
end
$stdout << "\n"
end
runs = []
10.times { runs << BenchmarkTest.run!(log = false) }
benches = runs.first.map { |result| [result.name, result.txn] }
results_per_bench = benches.map do |bench, txn|
[
bench + (txn ? " (txn)" : ""),
runs.sum do |results|
results.find { |result| result.name == bench && result.txn == txn }
.clk_time
end.fdiv(runs.size).round(4)
]
end.sort_by(&:last).reverse
avg_run_time = runs.sum do |results|
results.sum { |result| result.clk_time }
end.fdiv(runs.size).round(4)
[avg_run_time, results_per_bench]
end
Raw
sqlite-activerecord-benchmark.rb
require 'benchmark'
require 'active_record'
require 'sqlite3'
# -----------------------------------------------------------------------------
INPUT_COLUMNS = {
name: "Name of benchmark",
iters: "Number of iterations the block is run",
usr_time: "Amount of user CPU time",
sys_time: "Amount of system CPU time",
ttl_time: "Amount of total CPU time",
clk_time: "Amount of actual/real/wallclock time",
txn: "Whether a transaction was used",
}
PRAGMAS = %w[
analysis_limit
application_id
auto_vacuum
automatic_index
busy_timeout
cache_size
cache_spill
case_sensitive_like
cell_size_check
checkpoint_fullfsync
data_version
defer_foreign_keys
encoding
foreign_keys
freelist_count
fullfsync
hard_heap_limit
ignore_check_constraints
integrity_check
journal_mode
journal_size_limit
legacy_alter_table
locking_mode
max_page_count
mmap_size
page_count
page_size
query_only
quick_check
read_uncommitted
recursive_triggers
reverse_unordered_selects
secure_delete
soft_heap_limit
synchronous
temp_store
threads
trusted_schema
user_version
wal_autocheckpoint
]
Result = Struct.new(*INPUT_COLUMNS.keys)
module Workbench
def measure(iterations, in_transaction = false, &block)
GC.start
label = caller_locations(1,1)[0].label.gsub("bench_", "")
measurement = Benchmark.measure(nil) do
if in_transaction
ActiveRecord::Base.transaction(&block)
else
block.call
end
end
output = measurement
.format('%u,%y,%t,%r')
.gsub(/[()]/, '')
.split(',')
.map { |s| s.strip.to_f.round(4) }
@results << Result.new(*[label, iterations, *output, in_transaction])
end
end
module Runner
def run!(log = true)
instance = new
instance.instance_variable_set(:@results, [])
instance_methods(_include_ancestors = false)
.select { |m| m.to_s.start_with?("bench_") }
.each do |m|
instance.method(m).call
TestRecord
.descendants
.each { |c| c.delete_all }
end
results = instance.instance_variable_get(:@results)
.sort_by { |result| result.clk_time }
.reverse
if log
$stdout << results
.map do |result|
sub = [
result.txn.to_s.ljust(6, ' '),
result.iters.to_s.ljust(5, ' '),
result.usr_time.to_s.ljust(7, ' '),
result.sys_time.to_s.ljust(7, ' '),
result.ttl_time.to_s.ljust(7, ' '),
].join(" ")
[
result.name.ljust(60, ' '),
result.clk_time.to_s.ljust(7, ' '),
"(#{sub})"
].join(" ")
end
.join("\n")
$stdout << "\n"
end
results
end
end
# Based off of: https://github.com/jeremyevans/simple_orm_benchmark
class BenchmarkTest
include Workbench
extend Runner
BASELINE = 64
def bench_model_object_creation
measure(BASELINE*50) do
Party.create(theme: "Halloween")
end
measure(BASELINE*50, in_transaction = true) do
Party.create(theme: "Halloween")
end
end
def bench_model_object_select
Party.insert_all((0..BASELINE*100).map { |n| {theme: "Theme: #{n}"} })
measure(BASELINE) do
Party.all.entries
end
measure(BASELINE, in_transaction = true) do
Party.all.entries
end
end
def bench_model_object_select_by_pk
Party.insert_all((0..BASELINE*10).map { |n| {theme: "Theme: #{n}"} })
party_ids = Party.pluck(:id)
measure(BASELINE) do
party_ids.each do |party_id|
Party.find(party_id)
end
end
measure(BASELINE, in_transaction = true) do
party_ids.each do |party_id|
Party.find(party_id)
end
end
end
def bench_model_object_select_by_attr
Party.insert_all((0..BASELINE*10).map { |n| {theme: "Theme: #{n}"} })
party_themes = Party.pluck(:theme)
measure(BASELINE) do
party_themes.each do |party_theme|
Party.find_by(theme: party_theme)
end
end
measure(BASELINE, in_transaction = true) do
party_themes.each do |party_theme|
Party.find_by(theme: party_theme)
end
end
end
def bench_model_object_select_and_save
Party.insert_all((0..BASELINE*50).map { |n| {theme: "Theme: #{n}"} })
measure(BASELINE) do
Party.all.each do |party|
party.theme += '.1'
party.save
end
end
measure(BASELINE, in_transaction = true) do
Party.all.each do |party|
party.theme += '.1'
party.save
end
end
end
def bench_model_object_destruction
Party.insert_all((0..BASELINE*100).map { |n| {theme: "Theme: #{n}"} })
measure(BASELINE) do
Party.all.each do |party|
party.destroy
end
end
measure(BASELINE, in_transaction = true) do
Party.all.each do |party|
party.destroy
end
end
end
def bench_model_object_and_associated_object_creation
measure(BASELINE*20) do
party = Party.create(theme: "X-mas")
Person.create(party: party, name: "Test_#{party.id}")
end
measure(BASELINE*20, in_transaction = true) do
party = Party.create(theme: "X-mas")
Person.create(party: party, name: "Test_#{party.id}")
end
end
def bench_model_object_and_associated_object_creation
Party.insert_all((0..BASELINE*25).map { |n| {theme: "Theme: #{n}"} })
party_ids = Party.pluck(:id)
Person.insert_all(party_ids.map { |party_id| {name: "Party: #{party_id}", party_id: party_id} })
measure(BASELINE) do
Party.all.each do |party|
party.people.each { |person| person.destroy }
party.destroy
end
end
measure(BASELINE, in_transaction = true) do
Party.all.each do |party|
party.people.each { |person| person.destroy }
party.destroy
end
end
end
def bench_eager_loading_query_per_association_with_1_to_1_records
(BASELINE*20).times do |n|
party = Party.create(theme: "Theme: #{n}")
Person.insert({name: "Name: #{n}", party_id: party.id})
end
measure(BASELINE*5/7) do
Party.preload(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
measure(BASELINE*5/7, in_transaction = true) do
Party.preload(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_eager_loading_single_query_with_1_to_1_records
(BASELINE*20).times do |n|
party = Party.create(theme: "Theme: #{n}")
Person.insert({name: "Name: #{n}", party_id: party.id})
end
measure(BASELINE*5/7) do
Party.eager_load(:people).to_a.each do |party|
party.people.each{|p| p.id}
end
end
measure(BASELINE*5/7, in_transaction = true) do
Party.eager_load(:people).to_a.each do |party|
party.people.each{|p| p.id}
end
end
end
def bench_eager_loading_query_per_association_with_1_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
end
end
measure(BASELINE*5/7) do
Party.preload(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
measure(BASELINE*5/7, in_transaction = true) do
Party.preload(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_eager_loading_single_query_with_1_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
end
end
measure(BASELINE*5/7) do
Party.eager_load(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
measure(BASELINE*5/7, in_transaction = true) do
Party.eager_load(:people).to_a.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_eager_loading_query_per_association_with_1_to_n_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
Person.insert({name: "Name: #{nn}", other_party_id: party.id})
end
end
measure(BASELINE*2/7) do
Party.preload(:people, :other_people).to_a.each do |party|
party.people.map { |p| p.id }
party.other_people.map { |p| p.id }
end
end
measure(BASELINE*2/7, in_transaction = true) do
Party.preload(:people, :other_people).to_a.each do |party|
party.people.map { |p| p.id }
party.other_people.map { |p| p.id }
end
end
end
def bench_eager_loading_single_query_with_1_to_n_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
Person.insert({name: "Name: #{nn}", other_party_id: party.id})
end
end
measure(BASELINE*2/7) do
Party.eager_load(:people, :other_people).to_a.each do |party|
party.people.map { |p| p.id }
party.other_people.map { |p| p.id }
end
end
measure(BASELINE*2/7, in_transaction = true) do
Party.eager_load(:people, :other_people).to_a.each do |party|
party.people.map { |p| p.id }
party.other_people.map { |p| p.id }
end
end
end
def bench_lazy_loading_with_1_to_1_records
(BASELINE*20).times do |n|
party = Party.create(theme: "Theme: #{n}")
Person.insert({name: "Name: #{n}", party_id: party.id})
end
measure(1) do
Party.all.each do |party|
party.people.map { |p| p.id }
end
end
measure(1, in_transaction = true) do
Party.all.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_lazy_loading_with_1_to_n_records
BASELINE.times do |n|
party = Party.create(theme: "Theme: #{n}")
BASELINE.times do |nn|
Person.insert({name: "Name: #{nn}", party_id: party.id})
end
end
measure(BASELINE/2) do
Party.all.each do |party|
party.people.map { |p| p.id }
end
end
measure(BASELINE/2, in_transaction = true) do
Party.all.each do |party|
party.people.map { |p| p.id }
end
end
end
def bench_light_threading
measure(1) do
threads = []
Math.sqrt(BASELINE).round.times do
threads << Thread.new do
Party.insert_all((0..BASELINE).map { |n| {theme: "Theme: #{n}"} })
BASELINE.times do
party = Party.first
party.update(theme: "Christmas")
party.destroy
end
ActiveRecord::Base.clear_active_connections!
end
threads.each { |t| t.join }
end
Party.create(theme: "Halloween")
end
end
def bench_heavy_threading
measure(1) do
threads = []
BASELINE.times do
threads << Thread.new do
Party.insert_all((0..BASELINE).map { |n| {theme: "Theme: #{n}"} })
BASELINE.times do
party = Party.first
party.update(theme: "Christmas")
party.destroy
end
ActiveRecord::Base.clear_active_connections!
end
threads.each { |t| t.join }
end
Party.create(theme: "Halloween")
end
end
def bench_model_object_select_json_nested
JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
party_ids = JsonParty.pluck(:id)
measure(BASELINE) do
party_ids.each do |party_id|
JsonParty.find_by("json_extract(stuff, '$.theme') = ?", "Theme")
end
end
measure(BASELINE, in_transaction = true) do
party_ids.each do |party_id|
JsonParty.find_by("json_extract(stuff, '$.theme') = ?", "Theme")
end
end
end
def bench_model_object_update_json_nested
JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
party_ids = JsonParty.pluck(:id)
measure(BASELINE) do
party_ids.each do |party_id|
JsonParty.where(id: party_id).update_all(["stuff = JSON_SET(stuff, '$.theme', ?)", 'New Theme'])
end
end
measure(BASELINE, in_transaction = true) do
party_ids.each do |party_id|
JsonParty.where(id: party_id).update_all(["stuff = JSON_SET(stuff, '$.theme', ?)", 'New Theme'])
end
end
end
def bench_model_object_update_json
JsonParty.insert_all((0..BASELINE*10).map { |n| { stuff: { n: n, theme: "Theme" } } })
party_ids = JsonParty.pluck(:id)
measure(BASELINE) do
party_ids.each do |party_id|
JsonParty.where(id: party_id).update(:stuff=>{:pumpkin=>2, :candy=>1})
end
end
measure(BASELINE, in_transaction = true) do
party_ids.each do |party_id|
JsonParty.where(id: party_id).update(:stuff=>{:pumpkin=>2, :candy=>1})
end
end
end
end
# -----------------------------------------------------------------------------
module RailsExt
module SQLite3Adapter
PRAGMAS = {
# level of database durability, 2 = "FULL" (sync on every write), other values include 1 = "NORMAL" (sync every 1000 written pages) and 0 = "NONE"
# https://www.sqlite.org/pragma.html#pragma_synchronous
synchronous: "NORMAL",
# Journal mode WAL allows for greater concurrency (many readers + one writer)
# https://www.sqlite.org/pragma.html#pragma_journal_mode
journal_mode: "WAL",
# impose a limit on the WAL file to prevent unlimited growth (with a negative impact on read performance as well)
# https://www.sqlite.org/pragma.html#pragma_journal_size_limit
journal_size_limit: 67108864, # 64.megabytes
# set the global memory map so all processes can share data
# https://www.sqlite.org/pragma.html#pragma_mmap_size
# https://www.sqlite.org/mmap.html
mmap_size: 134217728, # 128.megabytes
# increase the local connection cache to 2000 pages
# https://www.sqlite.org/pragma.html#pragma_cache_size
cache_size: 2000,
}
# Perform any necessary initialization upon the newly-established
# @raw_connection -- this is the place to modify the adapter's
# connection settings, run queries to configure any application-global
# "session" variables, etc.
#
# Implementations may assume this method will only be called while
# holding @lock (or from #initialize).
# https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L691
def configure_connection
if @config[:timeout] && @config[:retries]
raise ArgumentError, "Cannot specify both timeout and retries arguments"
elsif @config[:retries]
raw_connection.busy_handler do |count|
count <= @config[:retries]
end
end
super
PRAGMAS.each do |key, value|
execute("PRAGMA #{key} = #{value}", "SCHEMA")
end
end
end
end
class TestRecord < ActiveRecord::Base
primary_abstract_class
end
class Party < TestRecord
has_many :people
has_many :other_people, class_name: 'Person', foreign_key: :other_party_id
end
class Person < TestRecord
belongs_to :party
belongs_to :other_party, class_name: 'Party', foreign_key: 'other_party_id'
end
class JsonParty < TestRecord
end
def run_benchmark!(enhance: false, log: true)
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: "ar_test.sqlite3")
# Enhance the SQLite3 ActiveRecord adapter with optimized defaults and extensions
ActiveSupport.on_load(:active_record_sqlite3adapter) do
# self refers to `SQLite3Adapter` here,
# so we can call .prepend
prepend(RailsExt::SQLite3Adapter) if enhance
end
ActiveRecord::Schema.define do
create_table :parties, force: true do |t|
t.string :theme
end
create_table :people, force: true do |t|
t.integer :party_id
t.integer :other_party_id
t.string :name
t.string :address
end
create_table :json_parties, force: true do |t|
t.json :stuff
end
end
if log
$stdout << {}.tap do |memo|
memo['sqlite3-ruby version'] = SQLite3::VERSION
memo['sqlite3 version'] = SQLite3::SQLITE_VERSION
memo['sqlcipher?'] = SQLite3.sqlcipher?
memo['threadsafe?'] = SQLite3.threadsafe?
compile_options = {
"compile_options" => ActiveRecord::Base.connection
.execute("PRAGMA compile_options;")
.map { _1["compile_options"] }
}
memo.merge!(compile_options)
pragmas = memo['pragmas'] = {}
PRAGMAS.each do |key, _value|
result = ActiveRecord::Base.connection.execute("PRAGMA #{key};").first
pragmas.merge!(result || {})
end
end
$stdout << "\n"
end
runs = []
10.times { runs << BenchmarkTest.run!(log = false) }
benches = runs.first.map { |result| [result.name, result.txn] }
results_per_bench = benches.map do |bench, txn|
[
bench + (txn ? " (txn)" : ""),
runs.sum do |results|
results.find { |result| result.name == bench && result.txn == txn }
.clk_time
end.fdiv(runs.size).round(4)
]
end.sort_by(&:last).reverse
avg_run_time = runs.sum do |results|
results.sum { |result| result.clk_time }
end.fdiv(runs.size).round(4)
[avg_run_time, results_per_bench]
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment