Skip to content

Instantly share code, notes, and snippets.

@sergey-alekseev

sergey-alekseev/mysql2-single-column-with-unique-index-reports.txt

Created October 30, 2017 14:54
Show Gist options
  • Save sergey-alekseev/d6bda8f304a907a366a866110971f610 to your computer and use it in GitHub Desktop.
Save sergey-alekseev/d6bda8f304a907a366a866110971f610 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
mysql2-single-column-with-unique-index-reports.txt
=========== [mysql2] Single column with unique index (1000 records) ============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.014k i/100ms
validate_only_if_changed_by_default = false
227.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
21.060k (± 3.1%) i/s - 106.742k in 5.073537s
validate_only_if_changed_by_default = false
2.277k (± 3.3%) i/s - 11.577k in 5.090601s
Comparison:
validate_only_if_changed_by_default = true: 21059.6 i/s
validate_only_if_changed_by_default = false: 2276.6 i/s - 9.25x slower
=========== [mysql2] Single column with unique index (10000 records) ===========
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.055k i/100ms
validate_only_if_changed_by_default = false
226.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
21.007k (± 3.5%) i/s - 106.860k in 5.093037s
validate_only_if_changed_by_default = false
2.248k (± 3.6%) i/s - 11.300k in 5.033465s
Comparison:
validate_only_if_changed_by_default = true: 21007.4 i/s
validate_only_if_changed_by_default = false: 2247.9 i/s - 9.35x slower
========== [mysql2] Single column with unique index (100000 records) ===========
Warming up --------------------------------------
validate_only_if_changed_by_default = true
1.983k i/100ms
validate_only_if_changed_by_default = false
220.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
20.731k (± 3.7%) i/s - 105.099k in 5.076577s
validate_only_if_changed_by_default = false
2.168k (± 4.8%) i/s - 11.000k in 5.086245s
Comparison:
validate_only_if_changed_by_default = true: 20731.0 i/s
validate_only_if_changed_by_default = false: 2168.0 i/s - 9.56x slower
========== [mysql2] Single column with unique index (1000000 records) ==========
/Users/sergey/.rvm/gems/ruby-2.4.2/gems/mysql2-0.4.9/lib/mysql2/client.rb:120:in `_query': Mysql2::Error: MySQL server has gone away: INSERT INTO posts (slug) VALUES ('post1'),('post2')...
Raw
mysql2-single-column-with-unique-index.rb
# frozen_string_literal: true
begin
require "bundler/inline"
rescue LoadError => e
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
raise e
end
gemfile(true) do
source "https://rubygems.org"
git_source(:github) { |repo| "https://github.com/#{repo}.git" }
gem "rails", github: "sergey-alekseev/rails", branch: "validate-only-changed-fields-for-benchmarking"
gem "arel", github: "rails/arel"
gem "benchmark-ips"
gem "mysql2"
end
require "active_record"
ActiveRecord::Base.establish_connection(adapter: "mysql2", database: "benchmark", username: "root")
ActiveRecord::Schema.define do
create_table :posts, force: true do |t|
t.string :slug, index: true, unique: true
end
end
class Post < ActiveRecord::Base
validates :slug, uniqueness: true
end
[1_000, 10_000, 100_000, 1_000_000].each do |number_of_seeded_records|
puts
puts " [mysql2] Single column with unique index (#{number_of_seeded_records} records) ".center(80, "=")
puts
values = (1..number_of_seeded_records).map { |i| "('post#{i}')" }.join(",")
ActiveRecord::Base.connection.execute("INSERT INTO posts (slug) VALUES #{values}")
post = Post.first
Benchmark.ips do |x|
x.report("validate_only_if_changed_by_default = true") do
Post.validate_only_if_changed_by_default = true
post.valid?
end
x.report("validate_only_if_changed_by_default = false") do
Post.validate_only_if_changed_by_default = false
post.valid?
end
x.compare!
end
Post.delete_all
end
Raw
mysql2-single-column-without-index-reports.txt
============= [mysql2] Single column without index (1000 records) ==============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
1.873k i/100ms
validate_only_if_changed_by_default = false
136.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
20.209k (± 8.6%) i/s - 101.142k in 5.050079s
validate_only_if_changed_by_default = false
1.510k (± 5.2%) i/s - 7.616k in 5.059313s
Comparison:
validate_only_if_changed_by_default = true: 20209.2 i/s
validate_only_if_changed_by_default = false: 1509.6 i/s - 13.39x slower
============= [mysql2] Single column without index (10000 records) =============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.024k i/100ms
validate_only_if_changed_by_default = false
35.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
21.211k (± 4.4%) i/s - 107.272k in 5.067078s
validate_only_if_changed_by_default = false
339.491 (± 9.7%) i/s - 1.680k in 5.012720s
Comparison:
validate_only_if_changed_by_default = true: 21211.2 i/s
validate_only_if_changed_by_default = false: 339.5 i/s - 62.48x slower
============ [mysql2] Single column without index (100000 records) =============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.012k i/100ms
validate_only_if_changed_by_default = false
4.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
21.058k (± 5.2%) i/s - 106.636k in 5.077659s
validate_only_if_changed_by_default = false
42.236 (± 4.7%) i/s - 212.000 in 5.026463s
Comparison:
validate_only_if_changed_by_default = true: 21058.1 i/s
validate_only_if_changed_by_default = false: 42.2 i/s - 498.59x slower
Raw
mysql2-single-column-without-index.rb
# frozen_string_literal: true
begin
require "bundler/inline"
rescue LoadError => e
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
raise e
end
gemfile(true) do
source "https://rubygems.org"
git_source(:github) { |repo| "https://github.com/#{repo}.git" }
gem "rails", github: "sergey-alekseev/rails", branch: "validate-only-changed-fields-for-benchmarking"
gem "arel", github: "rails/arel"
gem "benchmark-ips"
gem "mysql2"
end
require "active_record"
ActiveRecord::Base.establish_connection(adapter: "mysql2", database: "benchmark", username: "root")
ActiveRecord::Schema.define do
create_table :posts, force: true do |t|
t.string :slug
end
end
class Post < ActiveRecord::Base
validates :slug, uniqueness: true
end
[1_000, 10_000, 100_000].each do |number_of_seeded_records|
puts
puts " [mysql2] Single column without index (#{number_of_seeded_records} records) ".center(80, "=")
puts
values = (1..number_of_seeded_records).map { |i| "('post#{i}')" }.join(",")
ActiveRecord::Base.connection.execute("INSERT INTO posts (slug) VALUES #{values}")
post = Post.first
Benchmark.ips do |x|
x.report("validate_only_if_changed_by_default = true") do
Post.validate_only_if_changed_by_default = true
post.valid?
end
x.report("validate_only_if_changed_by_default = false") do
Post.validate_only_if_changed_by_default = false
post.valid?
end
x.compare!
end
Post.delete_all
end
Raw
postgresql-single-column-with-unique-index-reports.txt
========= [postgresql] Single column with unique index (1000 records) ==========
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.209k i/100ms
validate_only_if_changed_by_default = false
207.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
23.077k (± 3.6%) i/s - 117.077k in 5.079931s
validate_only_if_changed_by_default = false
2.053k (± 5.5%) i/s - 10.350k in 5.057698s
Comparison:
validate_only_if_changed_by_default = true: 23076.7 i/s
validate_only_if_changed_by_default = false: 2053.4 i/s - 11.24x slower
========= [postgresql] Single column with unique index (10000 records) =========
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.012k i/100ms
validate_only_if_changed_by_default = false
179.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
22.507k (± 6.1%) i/s - 112.672k in 5.026799s
validate_only_if_changed_by_default = false
2.048k (± 3.3%) i/s - 10.382k in 5.075816s
Comparison:
validate_only_if_changed_by_default = true: 22507.3 i/s
validate_only_if_changed_by_default = false: 2047.6 i/s - 10.99x slower
======== [postgresql] Single column with unique index (100000 records) =========
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.251k i/100ms
validate_only_if_changed_by_default = false
188.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
22.062k (± 6.8%) i/s - 110.299k in 5.023868s
validate_only_if_changed_by_default = false
1.988k (± 6.7%) i/s - 9.964k in 5.037798s
Comparison:
validate_only_if_changed_by_default = true: 22061.5 i/s
validate_only_if_changed_by_default = false: 1987.7 i/s - 11.10x slower
======== [postgresql] Single column with unique index (1000000 records) ========
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.249k i/100ms
validate_only_if_changed_by_default = false
200.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
23.771k (± 4.4%) i/s - 119.197k in 5.024050s
validate_only_if_changed_by_default = false
2.059k (± 2.7%) i/s - 10.400k in 5.053493s
Comparison:
validate_only_if_changed_by_default = true: 23770.8 i/s
validate_only_if_changed_by_default = false: 2059.5 i/s - 11.54x slower
Raw
postgresql-single-column-with-unique-index.rb
# frozen_string_literal: true
begin
require "bundler/inline"
rescue LoadError => e
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
raise e
end
gemfile(true) do
source "https://rubygems.org"
git_source(:github) { |repo| "https://github.com/#{repo}.git" }
gem "rails", github: "sergey-alekseev/rails", branch: "validate-only-changed-fields-for-benchmarking"
gem "arel", github: "rails/arel"
gem "benchmark-ips"
gem "pg"
end
require "active_record"
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "benchmark")
ActiveRecord::Schema.define do
create_table :posts, force: true do |t|
t.string :slug, index: true, unique: true
end
end
class Post < ActiveRecord::Base
validates :slug, uniqueness: true
end
[1_000, 10_000, 100_000, 1_000_000].each do |number_of_seeded_records|
puts
puts " [postgresql] Single column with unique index (#{number_of_seeded_records} records) ".center(80, "=")
puts
values = (1..number_of_seeded_records).map { |i| "('post#{i}')" }.join(",")
ActiveRecord::Base.connection.execute("INSERT INTO posts (slug) VALUES #{values}")
post = Post.first
Benchmark.ips do |x|
x.report("validate_only_if_changed_by_default = true") do
Post.validate_only_if_changed_by_default = true
post.valid?
end
x.report("validate_only_if_changed_by_default = false") do
Post.validate_only_if_changed_by_default = false
post.valid?
end
x.compare!
end
Post.delete_all
end
Raw
postgresql-single-column-without-index-reports.txt
=========== [postgresql] Single column without index (1000 records) ============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.121k i/100ms
validate_only_if_changed_by_default = false
128.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
20.073k (± 8.0%) i/s - 101.808k in 5.111480s
validate_only_if_changed_by_default = false
1.328k (± 8.4%) i/s - 6.656k in 5.055353s
Comparison:
validate_only_if_changed_by_default = true: 20072.9 i/s
validate_only_if_changed_by_default = false: 1328.1 i/s - 15.11x slower
=========== [postgresql] Single column without index (10000 records) ===========
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.092k i/100ms
validate_only_if_changed_by_default = false
35.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
21.237k (± 3.6%) i/s - 106.692k in 5.030332s
validate_only_if_changed_by_default = false
353.757 (± 2.3%) i/s - 1.785k in 5.048370s
Comparison:
validate_only_if_changed_by_default = true: 21236.9 i/s
validate_only_if_changed_by_default = false: 353.8 i/s - 60.03x slower
========== [postgresql] Single column without index (100000 records) ===========
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.031k i/100ms
validate_only_if_changed_by_default = false
3.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
21.014k (± 4.3%) i/s - 105.612k in 5.034997s
validate_only_if_changed_by_default = false
43.279 (± 2.3%) i/s - 219.000 in 5.064150s
Comparison:
validate_only_if_changed_by_default = true: 21014.5 i/s
validate_only_if_changed_by_default = false: 43.3 i/s - 485.56x slower
========== [postgresql] Single column without index (1000000 records) ==========
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.239k i/100ms
validate_only_if_changed_by_default = false
1.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
22.389k (± 3.6%) i/s - 111.950k in 5.006907s
validate_only_if_changed_by_default = false
4.232 (± 0.0%) i/s - 22.000 in 5.206567s
Comparison:
validate_only_if_changed_by_default = true: 22389.1 i/s
validate_only_if_changed_by_default = false: 4.2 i/s - 5289.98x slower
Raw
postgresql-single-column-without-index.rb
# frozen_string_literal: true
begin
require "bundler/inline"
rescue LoadError => e
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
raise e
end
gemfile(true) do
source "https://rubygems.org"
git_source(:github) { |repo| "https://github.com/#{repo}.git" }
gem "rails", github: "sergey-alekseev/rails", branch: "validate-only-changed-fields-for-benchmarking"
gem "arel", github: "rails/arel"
gem "benchmark-ips"
gem "pg"
end
require "active_record"
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "benchmark")
ActiveRecord::Schema.define do
create_table :posts, force: true do |t|
t.string :slug
end
end
class Post < ActiveRecord::Base
validates :slug, uniqueness: true
end
[1_000, 10_000, 100_000, 1_000_000].each do |number_of_seeded_records|
puts
puts " [postgresql] Single column without index (#{number_of_seeded_records} records) ".center(80, "=")
puts
values = (1..number_of_seeded_records).map { |i| "('post#{i}')" }.join(",")
ActiveRecord::Base.connection.execute("INSERT INTO posts (slug) VALUES #{values}")
post = Post.first
Benchmark.ips do |x|
x.report("validate_only_if_changed_by_default = true") do
Post.validate_only_if_changed_by_default = true
post.valid?
end
x.report("validate_only_if_changed_by_default = false") do
Post.validate_only_if_changed_by_default = false
post.valid?
end
x.compare!
end
Post.delete_all
end
Raw
sqlite3-single-column-with-unique-index-reports.txt
============== [sqlite3] Single column with index (1000 records) ===============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.321k i/100ms
validate_only_if_changed_by_default = false
382.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
24.029k (± 4.0%) i/s - 120.692k in 5.030793s
validate_only_if_changed_by_default = false
3.831k (± 4.3%) i/s - 19.482k in 5.094354s
Comparison:
validate_only_if_changed_by_default = true: 24029.1 i/s
validate_only_if_changed_by_default = false: 3831.4 i/s - 6.27x slower
============== [sqlite3] Single column with index (10000 records) ==============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.343k i/100ms
validate_only_if_changed_by_default = false
383.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
23.831k (± 5.2%) i/s - 119.493k in 5.028133s
validate_only_if_changed_by_default = false
3.870k (± 3.7%) i/s - 19.533k in 5.054852s
Comparison:
validate_only_if_changed_by_default = true: 23831.4 i/s
validate_only_if_changed_by_default = false: 3869.6 i/s - 6.16x slower
============= [sqlite3] Single column with index (100000 records) ==============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.330k i/100ms
validate_only_if_changed_by_default = false
389.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
23.789k (± 4.4%) i/s - 121.160k in 5.103225s
validate_only_if_changed_by_default = false
3.878k (± 3.8%) i/s - 19.450k in 5.022934s
Comparison:
validate_only_if_changed_by_default = true: 23788.8 i/s
validate_only_if_changed_by_default = false: 3877.7 i/s - 6.13x slower
============= [sqlite3] Single column with index (1000000 records) =============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.368k i/100ms
validate_only_if_changed_by_default = false
399.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
24.133k (± 4.5%) i/s - 120.768k in 5.015199s
validate_only_if_changed_by_default = false
3.820k (± 4.8%) i/s - 19.152k in 5.025385s
Comparison:
validate_only_if_changed_by_default = true: 24133.2 i/s
validate_only_if_changed_by_default = false: 3820.0 i/s - 6.32x slower
Raw
sqlite3-single-column-with-unique-index.rb
# frozen_string_literal: true
begin
require "bundler/inline"
rescue LoadError => e
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
raise e
end
gemfile(true) do
source "https://rubygems.org"
git_source(:github) { |repo| "https://github.com/#{repo}.git" }
gem "rails", github: "sergey-alekseev/rails", branch: "validate-only-changed-fields-for-benchmarking"
gem "arel", github: "rails/arel"
gem "benchmark-ips"
gem "sqlite3"
end
require "active_record"
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Schema.define do
create_table :posts, force: true do |t|
t.string :slug, index: true, unique: true
end
end
class Post < ActiveRecord::Base
validates :slug, uniqueness: true
end
[1_000, 10_000, 100_000, 1_000_000].each do |number_of_seeded_records|
puts
puts " [sqlite3] Single column with unique index (#{number_of_seeded_records} records) ".center(80, "=")
puts
values = (1..number_of_seeded_records).map { |i| "('post#{i}')" }.join(",")
ActiveRecord::Base.connection.execute("INSERT INTO posts (slug) VALUES #{values}")
post = Post.first
Benchmark.ips do |x|
x.report("validate_only_if_changed_by_default = true") do
Post.validate_only_if_changed_by_default = true
post.valid?
end
x.report("validate_only_if_changed_by_default = false") do
Post.validate_only_if_changed_by_default = false
post.valid?
end
x.compare!
end
Post.delete_all
end
Raw
sqlite3-single-column-without-index-reports.txt
============= [sqlite3] Single column without index (1000 records) =============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
1.844k i/100ms
validate_only_if_changed_by_default = false
291.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
20.898k (± 8.4%) i/s - 105.108k in 5.067557s
validate_only_if_changed_by_default = false
2.790k (± 6.8%) i/s - 13.968k in 5.030009s
Comparison:
validate_only_if_changed_by_default = true: 20897.8 i/s
validate_only_if_changed_by_default = false: 2790.1 i/s - 7.49x slower
============ [sqlite3] Single column without index (10000 records) =============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.015k i/100ms
validate_only_if_changed_by_default = false
114.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
21.625k (± 6.1%) i/s - 108.810k in 5.050617s
validate_only_if_changed_by_default = false
1.060k (±12.4%) i/s - 5.244k in 5.093769s
Comparison:
validate_only_if_changed_by_default = true: 21624.8 i/s
validate_only_if_changed_by_default = false: 1059.7 i/s - 20.41x slower
============ [sqlite3] Single column without index (100000 records) ============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.059k i/100ms
validate_only_if_changed_by_default = false
17.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
21.330k (± 6.4%) i/s - 107.068k in 5.040163s
validate_only_if_changed_by_default = false
162.166 (± 8.0%) i/s - 816.000 in 5.073967s
Comparison:
validate_only_if_changed_by_default = true: 21330.2 i/s
validate_only_if_changed_by_default = false: 162.2 i/s - 131.53x slower
=========== [sqlite3] Single column without index (1000000 records) ============
Warming up --------------------------------------
validate_only_if_changed_by_default = true
2.245k i/100ms
validate_only_if_changed_by_default = false
1.000 i/100ms
Calculating -------------------------------------
validate_only_if_changed_by_default = true
23.083k (± 3.1%) i/s - 116.740k in 5.062307s
validate_only_if_changed_by_default = false
17.690 (± 5.7%) i/s - 89.000 in 5.041516s
Comparison:
validate_only_if_changed_by_default = true: 23083.0 i/s
validate_only_if_changed_by_default = false: 17.7 i/s - 1304.84x slower
Raw
sqlite3-single-column-without-index.rb
# frozen_string_literal: true
begin
require "bundler/inline"
rescue LoadError => e
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler"
raise e
end
gemfile(true) do
source "https://rubygems.org"
git_source(:github) { |repo| "https://github.com/#{repo}.git" }
gem "rails", github: "sergey-alekseev/rails", branch: "validate-only-changed-fields-for-benchmarking"
gem "arel", github: "rails/arel"
gem "benchmark-ips"
gem "sqlite3"
end
require "active_record"
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Schema.define do
create_table :posts, force: true do |t|
t.string :slug
end
end
class Post < ActiveRecord::Base
validates :slug, uniqueness: true
end
[1_000, 10_000, 100_000, 1_000_000].each do |number_of_seeded_records|
puts
puts " [sqlite3] Single column without index (#{number_of_seeded_records} records) ".center(80, "=")
puts
values = (1..number_of_seeded_records).map { |i| "('post#{i}')" }.join(",")
ActiveRecord::Base.connection.execute("INSERT INTO posts (slug) VALUES #{values}")
post = Post.first
Benchmark.ips do |x|
x.report("validate_only_if_changed_by_default = true") do
Post.validate_only_if_changed_by_default = true
post.valid?
end
x.report("validate_only_if_changed_by_default = false") do
Post.validate_only_if_changed_by_default = false
post.valid?
end
x.compare!
end
Post.delete_all
end
@sergey-alekseev
Copy link
Author

oh, not unique indices actually, but it doesn't have any impact on those reports

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment