$30 off During Our Annual Pro Sale. View Details »

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

Stephen
April 29, 2024

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.

Stephen

April 29, 2024
Tweet

More Decks by Stephen

Other Decks in Programming

Transcript

  1. Stephen Margheim · 13 April 2024 SQLite on Rails The

    how and why of optimal performance
  2. 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 🇺🇸 🛫 🇩🇪 💒 🇩🇰 📍 🇯🇲 💕 🐶🐕
  3. $ oha -c 1 -z 5s -m POST --latency-correction --disable-keepalive

    --redirect 0 http://localhost:3000/benchmarking/post_create
  4. $ oha -c 4 -z 5s -m POST --latency-correction --disable-keepalive

    --redirect 0 http://localhost:3000/benchmarking/post_create
  5. % successful/failed 0 25 50 75 100 Concurrent Requests 1

    2 4 8 16 65% 61% 45% 22% 0% 35% 39% 55% 78% 100%
  6. BEGIN TRANSACTION; SELECT * FROM posts WHERE id = ?;

    SELECT * FROM comments WHERE post_id = ?; UPDATE users SET last_seen_at = ? WHERE users.id = ?; COMMIT;
  7. 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
  8. 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
  9. default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 }

    %> timeout: 5000 default_transaction_mode: IMMEDIATE
  10. % successful/failed 0 25 50 75 100 Concurrent Requests 1

    2 4 8 16 1% 0% 0% 0% 0% 99% 100% 100% 100% 100%
  11. 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
  12. default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 }

    %> timeout: 5000 default_transaction_mode: IMMEDIATE
  13. Run

  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. 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
  33. 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
  34. 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
  35. 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
  36. 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
  37. 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
  38. 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
  39. 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
  40. 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
  41. 1. IMMEDIATE transactions 2. non-GVL-blocking timeout mechanism 3. fair retry

    interval 4. Write-Ahead-Logging journal mode 5. isolated connection pools
  42. 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
  43. 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
  44. requests per second 0 125 250 375 500 Concurrent Requests

    4 8 16 32 374 193 190 289 423 411 292 278
  45. 1. IMMEDIATE transactions 2. non-GVL-blocking timeout mechanism 3. fair retry

    interval 4. Write-Ahead-Logging journal mode 5. isolated connection pools
  46. • deferred foreign keys, • generated columns, • returning values

    from inserts, • PRAGMA tuning, • extension loading, and • improved concurrency support
  47. 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
  48. $ bundle add sqlite-vss # /config/database.yml default: &default adapter: sqlite3

    pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> timeout: 5000 extensions: - sqlite_vss
  49. 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
  50. default: &default adapter: sqlite3 pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 }

    %> timeout: 5000 default_transaction_mode: IMMEDIATE
  51. • Active Record (e.g. PostgreSQL, MySQL) • Solid Cache (e.g.

    Redis, Memcached) • Solid Queue (e.g. Sidekiq, Goodjob) • Solid Cable* (e.g. Redis, PostgreSQL)
  52. 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'"
  53. 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