Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ruby mysql vs sqlite
#!/usr/bin/env ruby
#
require 'benchmark'
require 'thread'
require 'rubygems'
require 'sqlite3'
require 'mysql2'
SQLITE3_DB = 'test.db'
def new_sqlite_connection
SQLite3::Database.new SQLITE3_DB
end
def sqlite_query
new_sqlite_connection.execute 'select * from dummies'
end
# seed sqlite
File.delete SQLITE3_DB if File.exists? SQLITE3_DB
new_sqlite_connection.tap do |db|
db.execute "create table dummies ( a integer );"
db.execute "insert into dummies values ( 1000 );"
(1..20).each do |i| # 2 ^ 20
db.execute "insert into dummies (a) select d.a from dummies as d;"
end
end
def mysql_query
db = Mysql2::Client.new
db.query("SELECT SLEEP(3)")
end
def sleep_some_time
50.times { sleep 0.1 }
end
Benchmark.bm do |b|
b.report('sleep ') { sleep_some_time }
b.report('mysql ') { mysql_query }
b.report('mysql + sleep ') do
threads = []
threads << Thread.new { sleep_some_time }
threads << Thread.new { mysql_query }
threads.each &:join
end
b.report('sqlite ') { sqlite_query }
b.report('sqlite + sleep') do
threads = []
threads << Thread.new { sleep_some_time }
threads << Thread.new { sqlite_query }
threads.each &:join
end
end
# ruby -v
# 1.9.3-p194
#
# gem list sqlite
# *** LOCAL GEMS ***
# sqlite3 (1.3.6)
# sqlite3-ruby (1.3.3)
#
# sqlite3 --version
# 3.7.7 2011-06-25 16:35:41 8f8b373eed7052e6e93c1805fc1effcf1db09366
#
# gem list mysql2
# *** LOCAL GEMS ***
# mysql (2.8.1)
# mysql2 (0.3.11)
#
# mysql --version
# mysql Ver 14.14 Distrib 5.5.25a, for osx10.7 (i386) using readline 5.1
#
# ./ruby_gil_test.rb
# user system total real
# sleep 0.000000 0.000000 0.000000 ( 5.047439)
# mysql 0.010000 0.000000 0.010000 ( 3.008452)
# mysql + sleep 0.000000 0.000000 0.000000 ( 5.049623)
# sqlite 1.360000 0.020000 1.380000 ( 1.384636)
# sqlite + sleep 1.340000 0.030000 1.370000 ( 5.912172)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment