wroclove.rb 2024 — How and why to run SQLite on Rails in production

Learn how to run a Rails application in production with SQLite, covering performance, resilience, enhancements, and deployment, with a particular deep-dive into performance.

Transcript

1. Stephen Margheim · 13 April 2024 SQLite on Rails The how and why of optimal performance
2. Who am I? Stephen Margheim · @fractaledmind 🇺🇸 🛫 🇩🇪 💒 🇩🇰 📍 🇯🇲 💕 🐶🐕
3. 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 🇺🇸 🛫 🇩🇪 💒 🇩🇰 📍 🇯🇲 💕 🐶🐕
4. rails new . --database=sqlite3 --asset-pipeline=propshaft --javascript=esbuild --css=tailwind --skip-jbuilder --skip-spring
5. $ rails new . --database=sqlite3 --asset-pipeline=propshaft --javascript=esbuild --css=tailwind --skip-jbuilder --skip-spring
6. Why?
7. Why not?
8. Linear Writes
10. Why?
16. Simplicity
17. Simplicity Control
18. Simplicity Control Speed
19. Why?
21. How?
22. Running SQLite on Rails out-of-the-box isn’t viable today.
23. $ bundle add activerecord-enhancedsqlite3-adapter $ bundle add litestream $ rails generate litestream:install
24. Thank you
26. $ oha -c 1 -z 5s -m POST --latency-correction --disable-keepalive --redirect 0 http://localhost:3000/benchmarking/post_create
28. $ oha -c 4 -z 5s -m POST --latency-correction --disable-keepalive --redirect 0 http://localhost:3000/benchmarking/post_create
31. SQLite3::BusyException
32. % successful/failed 0 25 50 75 100 Concurrent Requests 1 2 4 8 16 65% 61% 45% 22% 0% 35% 39% 55% 78% 100%
33. BEGIN IMMEDIATE TRANSACTION
34. BEGIN TRANSACTION; SELECT * FROM posts WHERE id = ?; SELECT * FROM comments WHERE post_id = ?; UPDATE users SET last_seen_at = ? WHERE users.id = ?; COMMIT;
35. 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
36. TRANSACTION == write
37. 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
38. default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 default_transaction_mode: IMMEDIATE
39. % successful/failed 0 25 50 75 100 Concurrent Requests 1 2 4 8 16 1% 0% 0% 0% 0% 99% 100% 100% 100% 100%
40. 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
41. default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 default_transaction_mode: IMMEDIATE
42. PRAGMA busy_timeout = 5000
43. Queries
44. Queued Run
45. Queued Run
46. Queued Run
47. Run
49. Worker #1 I/O Worker #2 I/O
50. Worker #1 I/O Worker #2 I/O
51. sqlite3_busy_handler()
52. 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
53. 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
54. 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
55. 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
56. 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
57. 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
58. 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
59. 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
60. 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
61. 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
62. 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
63. 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
64. 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
65. 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
66. 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
67. 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
68. 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
69. 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
70. 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
71. 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
72. 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
73. 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
74. Queries
75. Queued Run 1 1 1
76. Queued Run 2 2
77. Queued Run 3
78. Queued Run 3 0
79. Queued Run 3 1
80. Queued Run 3 2
81. 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
82. 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
83. 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
84. 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
85. Performance
86. 1. IMMEDIATE transactions 2. non-GVL-blocking timeout mechanism 3. fair retry interval 4. Write-Ahead-Logging journal mode
87. 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
88. 1. IMMEDIATE transactions 2. non-GVL-blocking timeout mechanism 3. fair retry interval 4. Write-Ahead-Logging journal mode 5. isolated connection pools
89. read write read write Queries write Connection Pool
90. read read Queries Connection Pool write write write
91. production: reader: <<: *default readonly: true database: storage/production.sqlite3 writer: <<: *default pool: 1 database: storage/production.sqlite3
92. production: reader: <<: *default readonly: true database: storage/production.sqlite3 writer: <<: *default pool: 1 database: storage/production.sqlite3
93. production: reader: <<: *default readonly: true database: storage/production.sqlite3 writer: <<: *default pool: 1 database: storage/production.sqlite3
94. class ApplicationRecord < ActiveRecord::Base primary_abstract_class connects_to database: { writing: :writer, reading: :reader } end
95. Deferred Requests
96. 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
97. 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
98. requests per second 0 125 250 375 500 Concurrent Requests 4 8 16 32 374 193 190 289 423 411 292 278
99. 1. IMMEDIATE transactions 2. non-GVL-blocking timeout mechanism 3. fair retry interval 4. Write-Ahead-Logging journal mode 5. isolated connection pools
100. $ bundle add activerecord-enhancedsqlite3-adapter
101. config.enhanced_sqlite3.isolate_connection_pools = true
102. • deferred foreign keys, • generated columns, • returning values from inserts, • PRAGMA tuning, • extension loading, and • improved concurrency support
103. add_reference :person, :alias, foreign_key: { deferrable: :deferred } add_reference :alias, :person, foreign_key: { deferrable: :deferred }
104. 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
105. Post.insert({ title: Faker::Lorem.sentence, description: Faker::Lorem.paragraphs.join("\n"), created_at: Faker::Time.forward(days: 23) }, returning: [ :id, :created_at ])
106. default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 pragmas: temp_store: MEMORY
107. $ bundle add sqlite-vss # /config/database.yml default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 extensions: - sqlite_vss
108. 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
109. default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 default_transaction_mode: IMMEDIATE
110. Performance 👍
111. • Active Record (e.g. PostgreSQL, MySQL) • Solid Cache (e.g. Redis, Memcached) • Solid Queue (e.g. Sidekiq, Goodjob) • Solid Cable* (e.g. Redis, PostgreSQL)
112. 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'"
113. 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
114. Integrating Litestream $ bundle add litestream $ bin/rails generate litestream:install Backups/replication
116. Thank you