Slide 1
Slide 1 text
Stephen Margheim · 13 April 2024
SQLite on Rails
The how and why of optimal performance
Slide 2
Slide 2 text
Who am I?
Stephen Margheim · @fractaledmind
🇺🇸 🛫 🇩🇪 💒 🇩🇰 📍 🇯🇲 💕 🐶🐕
Slide 3
Slide 3 text
Who am I?
• Head of Engineering at Test IO
• Open source developer
rails/rails
sparklemotion/sqlite3-ruby
oldmoe/litestack
joeldrapper/phlex
joeldrapper/literal
fractaledmind/activerecord-enhancedsqlite3-adapter
fractaledmind/litestream-ruby
fractaledmind/acidic_job
• Writer: https://fractaledmind.github.io
Stephen Margheim · @fractaledmind
🇺🇸 🛫 🇩🇪 💒 🇩🇰 📍 🇯🇲 💕 🐶🐕
Slide 4
Slide 4 text
rails new .
--database=sqlite3
--asset-pipeline=propshaft
--javascript=esbuild
--css=tailwind
--skip-jbuilder
--skip-spring
Slide 5
Slide 5 text
$ rails new .
--database=sqlite3
--asset-pipeline=propshaft
--javascript=esbuild
--css=tailwind
--skip-jbuilder
--skip-spring
Slide 6
Slide 6 text
Why?
Slide 7
Slide 7 text
Why not?
Slide 8
Slide 8 text
Linear Writes
Slide 9
Slide 9 text
No content
Slide 10
Slide 10 text
Why?
Slide 11
Slide 11 text
No content
Slide 12
Slide 12 text
No content
Slide 13
Slide 13 text
No content
Slide 14
Slide 14 text
No content
Slide 15
Slide 15 text
No content
Slide 16
Slide 16 text
Simplicity
Slide 17
Slide 17 text
Simplicity
Control
Slide 18
Slide 18 text
Simplicity
Control
Speed
Slide 19
Slide 19 text
Why?
Slide 20
Slide 20 text
No content
Slide 21
Slide 21 text
How?
Slide 22
Slide 22 text
Running SQLite on
Rails out-of-the-box
isn’t viable today.
Slide 23
Slide 23 text
$ bundle add activerecord-enhancedsqlite3-adapter
$ bundle add litestream
$ rails generate litestream:install
Slide 24
Slide 24 text
Thank you
Slide 25
Slide 25 text
No content
Slide 26
Slide 26 text
$ oha -c 1 -z 5s -m POST
--latency-correction
--disable-keepalive
--redirect 0
http://localhost:3000/benchmarking/post_create
Slide 27
Slide 27 text
No content
Slide 28
Slide 28 text
$ oha -c 4 -z 5s -m POST
--latency-correction
--disable-keepalive
--redirect 0
http://localhost:3000/benchmarking/post_create
Slide 29
Slide 29 text
No content
Slide 30
Slide 30 text
No content
Slide 31
Slide 31 text
SQLite3::BusyException
Slide 32
Slide 32 text
% successful/failed
0
25
50
75
100
Concurrent Requests
1 2 4 8 16
65%
61%
45%
22%
0%
35%
39%
55%
78%
100%
Slide 33
Slide 33 text
BEGIN IMMEDIATE TRANSACTION
Slide 34
Slide 34 text
BEGIN TRANSACTION;
SELECT * FROM posts WHERE id = ?;
SELECT * FROM comments WHERE post_id = ?;
UPDATE users
SET last_seen_at = ?
WHERE users.id = ?;
COMMIT;
Slide 35
Slide 35 text
BEGIN TRANSACTION;
SELECT * FROM posts WHERE id = ?;
SELECT * FROM comments WHERE post_id = ?;
UPDATE users
SET last_seen_at = ?
WHERE users.id = ?;
COMMIT;
Attempt to acquire write lock
Slide 36
Slide 36 text
TRANSACTION == write
Slide 37
Slide 37 text
BEGIN IMMEDIATE TRANSACTION;
SELECT * FROM posts WHERE id = ?;
SELECT * FROM comments WHERE post_id = ?;
UPDATE users
SET last_seen_at = ?
WHERE users.id = ?;
COMMIT;
Attempt to acquire write lock
Slide 38
Slide 38 text
default: &default
adapter: sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
default_transaction_mode: IMMEDIATE
Slide 39
Slide 39 text
% successful/failed
0
25
50
75
100
Concurrent Requests
1 2 4 8 16
1%
0%
0%
0%
0%
99%
100%
100%
100%
100%
Slide 40
Slide 40 text
latency in seconds
0
1,5
3
4,5
6
Concurrent Requests
4 8 16 32
p95 p99 p95 p99 p95 p99 p95 p99
5,446
5,193
1,112
0,064
5,259
0,075
0,129
0,013
Slide 41
Slide 41 text
default: &default
adapter: sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
default_transaction_mode: IMMEDIATE
Slide 42
Slide 42 text
PRAGMA busy_timeout = 5000
Slide 43
Slide 43 text
Queries
Slide 44
Slide 44 text
Queued Run
Slide 45
Slide 45 text
Queued Run
Slide 46
Slide 46 text
Queued Run
Slide 47
Slide 47 text
Run
Slide 48
Slide 48 text
No content
Slide 49
Slide 49 text
Worker #1 I/O
Worker #2 I/O
Slide 50
Slide 50 text
Worker #1 I/O
Worker #2 I/O
Slide 51
Slide 51 text
sqlite3_busy_handler()
Slide 52
Slide 52 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
Slide 53
Slide 53 text
latency in seconds
0
1,5
3
4,5
6
Concurrent Requests
4 8 16 32
p95 p99 p95 p99 p95 p99 p95 p99
0,585
0,368
0,148
0,067 0,186
0,145
0,048
0,020
Slide 54
Slide 54 text
latency in seconds
0
1,5
3
4,5
6
Concurrent Requests
4 8 16 32
p99.99 p99.99 p99.99 p99.99
1,525
1,073
0,785
0,211
Slide 55
Slide 55 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 0
Slide 56
Slide 56 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= true
Slide 57
Slide 57 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 1
Slide 58
Slide 58 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 0
Slide 59
Slide 59 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= false
Slide 60
Slide 60 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 0.001
Slide 61
Slide 61 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 10
Slide 62
Slide 62 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= true
Slide 63
Slide 63 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 50
Slide 64
Slide 64 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 178
Slide 65
Slide 65 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= false
Slide 66
Slide 66 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 0.050
Slide 67
Slide 67 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 58
Slide 68
Slide 68 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= false
Slide 69
Slide 69 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= 100
Slide 70
Slide 70 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
4928
Slide 71
Slide 71 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
= true
Slide 72
Slide 72 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
Slide 73
Slide 73 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
Slide 74
Slide 74 text
Queries
Slide 75
Slide 75 text
Queued Run
1
1
1
Slide 76
Slide 76 text
Queued Run
2
2
Slide 77
Slide 77 text
Queued Run
3
Slide 78
Slide 78 text
Queued Run
3
0
Slide 79
Slide 79 text
Queued Run
3
1
Slide 80
Slide 80 text
Queued Run
3
2
Slide 81
Slide 81 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep delay.fdiv(1000)
end
end
Slide 82
Slide 82 text
def busy_timeout(count)
delays = [1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100]
if count < delays.size
delay = delays[count]
prior = delays.take(count).sum
else
delay = delays.last
prior = delays.sum + ((count - delays.size) * delay)
end
if prior + delay > timeout
raise SQLite3::BusyException
else
sleep 0.001
end
end
Slide 83
Slide 83 text
busy_handler do |count|
now = Process.clock_gettime(Process::CLOCK_MONOTONIC)
if count.zero?
@timeout_deadline = now + timeout_seconds
elsif now > @timeout_deadline
next false
else
sleep(0.001)
end
end
Slide 84
Slide 84 text
latency in seconds
0
1,5
3
4,5
6
Concurrent Requests
4 8 16 32
p99.99 p99.99 p99.99 p99.99
0,501
0,472
0,534
0,051
Slide 85
Slide 85 text
Performance
Slide 86
Slide 86 text
1. IMMEDIATE transactions
2. non-GVL-blocking timeout mechanism
3. fair retry interval
4. Write-Ahead-Logging journal mode
Slide 87
Slide 87 text
Rails default PRAGMAs
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA mmap_size = 134217728; -- 128 megabytes
PRAGMA journal_size_limit = 67108864; -- 64 megabytes
PRAGMA cache_size = 2000;
>= 7.1.0
Slide 88
Slide 88 text
1. IMMEDIATE transactions
2. non-GVL-blocking timeout mechanism
3. fair retry interval
4. Write-Ahead-Logging journal mode
5. isolated connection pools
Slide 89
Slide 89 text
read
write
read
write
Queries
write
Connection Pool
Slide 90
Slide 90 text
read
read
Queries Connection Pool
write
write
write
Slide 91
Slide 91 text
production:
reader:
<<: *default
readonly: true
database: storage/production.sqlite3
writer:
<<: *default
pool: 1
database: storage/production.sqlite3
Slide 92
Slide 92 text
production:
reader:
<<: *default
readonly: true
database: storage/production.sqlite3
writer:
<<: *default
pool: 1
database: storage/production.sqlite3
Slide 93
Slide 93 text
production:
reader:
<<: *default
readonly: true
database: storage/production.sqlite3
writer:
<<: *default
pool: 1
database: storage/production.sqlite3
Slide 94
Slide 94 text
class ApplicationRecord < ActiveRecord::Base
primary_abstract_class
connects_to database: {
writing: :writer,
reading: :reader
}
end
Slide 95
Slide 95 text
Deferred Requests
Slide 96
Slide 96 text
class EnhancedResolver <
ActiveRecord::Middleware::DatabaseSelector::Resolver
def reading_request?(request)
true
end
end
Rails.application.configure do
config.active_record.database_selector = { delay: 0 }
config.active_record.database_resolver = EnhancedResolver
config.active_record.database_resolver_context =
ActiveRecord::Middleware::DatabaseSelector::Resolver::Session
end
Slide 97
Slide 97 text
module EnhancedSQLite3::Adapter
def transaction(...)
ActiveRecord::Base.connected_to(
role: ActiveRecord.writing_role, prevent_writes: false
) do
super(...)
end
end
end
ActiveSupport.on_load(:active_record_sqlite3adapter) do
prepend EnhancedSQLite3::Adapter
end
Slide 98
Slide 98 text
requests per second
0
125
250
375
500
Concurrent Requests
4 8 16 32
374
193
190
289
423
411
292
278
Slide 99
Slide 99 text
1. IMMEDIATE transactions
2. non-GVL-blocking timeout mechanism
3. fair retry interval
4. Write-Ahead-Logging journal mode
5. isolated connection pools
Slide 100
Slide 100 text
$ bundle add activerecord-enhancedsqlite3-adapter
Slide 101
Slide 101 text
config.enhanced_sqlite3.isolate_connection_pools = true
Slide 102
Slide 102 text
• deferred foreign keys,
• generated columns,
• returning values from inserts,
• PRAGMA tuning,
• extension loading, and
• improved concurrency support
Slide 103
Slide 103 text
add_reference :person, :alias,
foreign_key: { deferrable: :deferred }
add_reference :alias, :person,
foreign_key: { deferrable: :deferred }
Slide 104
Slide 104 text
create_table :virtual_columns, force: true do |t|
t.string :name
t.virtual :upper_name, type: :string,
as: "UPPER(name)", stored: true
t.virtual :lower_name, type: :string,
as: "LOWER(name)", stored: false
t.virtual :octet_name, type: :integer,
as: "LENGTH(name)"
end
Slide 105
Slide 105 text
Post.insert({
title: Faker::Lorem.sentence,
description: Faker::Lorem.paragraphs.join("\n"),
created_at: Faker::Time.forward(days: 23)
}, returning: [ :id, :created_at ])
Slide 106
Slide 106 text
default: &default
adapter: sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
pragmas:
temp_store: MEMORY
Slide 107
Slide 107 text
$ bundle add sqlite-vss
# /config/database.yml
default: &default
adapter: sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
extensions:
- sqlite_vss
Slide 108
Slide 108 text
busy_handler do |count|
now = Process.clock_gettime(Process::CLOCK_MONOTONIC)
if count.zero?
@timeout_deadline = now + timeout_seconds
elsif now > @timeout_deadline
next false
else
sleep(0.001)
end
end
Slide 109
Slide 109 text
default: &default
adapter: sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
default_transaction_mode: IMMEDIATE
Slide 110
Slide 110 text
Performance 👍
Slide 111
Slide 111 text
• Active Record (e.g. PostgreSQL, MySQL)
• Solid Cache (e.g. Redis, Memcached)
• Solid Queue (e.g. Sidekiq, Goodjob)
• Solid Cable* (e.g. Redis, PostgreSQL)
Slide 112
Slide 112 text
Fine-tuning compilation
$ bundle config set build.sqlite3 \
"--with-sqlite-cflags='
-DSQLITE_DQS=0
-DSQLITE_THREADSAFE=0
-DSQLITE_DEFAULT_MEMSTATUS=0
-DSQLITE_LIKE_DOESNT_MATCH_BLOBS
-DSQLITE_MAX_EXPR_DEPTH=0
-DSQLITE_OMIT_SHARED_CACHE
-DSQLITE_USE_ALLOCA'"
Slide 113
Slide 113 text
Branch-specific databases
# /config/database.yml
development:
<<: *default
database: storage/<%= `git branch --show-current`.chomp
|| 'development' %>.sqlite3
# /config/environments/development.rb
config.after_initialize do
ActiveRecord::Tasks::DatabaseTasks.prepare_all
end
Slide 114
Slide 114 text
Integrating Litestream
$ bundle add litestream
$ bin/rails generate litestream:install
Backups/replication
Slide 115
Slide 115 text
No content
Slide 116
Slide 116 text
Thank you