最適なパフォーマンスのための SQLite on Rails の方法と理由
(fractaledmind.github.io)- RailsでSQLiteをデフォルト設定のまま本番環境で使うにはまだ不十分だが、いくつか設定を追加すれば、高性能で回復力のあるアプリケーションを構築できる
- 同時書き込み負荷がかかると、SQLiteのグローバルな書き込みロックにより
SQLITE_BUSYが発生し、Railsの一般的な書き込みトランザクションの流れはデフォルトのdeferredモードと相性がよくない sqlite3-ruby1.6.9以降ではdatabase.ymlでデフォルトのトランザクションモードを IMMEDIATE に変更でき、書き込みロック取得失敗時に、より安全に待機・再試行できるようになるbusy_timeoutは待機中もRubyのGVLを保持してPumaの並列性を損なうため、busy_handlerでsleepを使ってGVLを解放し、1ms間隔で再試行するとロングテールのレイテンシを減らせる- WALモード、IMMEDIATEトランザクション、GVLを解放するbusy handler、Rails 7.1のSQLiteデフォルト設定、そして任意の読み取り/書き込みコネクションプール分離が、SQLite on Railsの性能改善の中核となる
基本的な Rails + SQLite 設定で見えてくる限界
- RailsアプリケーションをSQLiteベースで高性能かつ回復力のある形で運用するには、現在のデフォルト設定だけでは不十分
- 目標は、Rails 8でSQLiteのデフォルト体験を本番環境対応完了の状態にすること
- デモアプリケーションは Lorem News
- Hacker News風の基本クローンで、ユーザー、投稿、コメントを含む
- コンテンツは Lorem Ipsum で構成されている
- 負荷テストには oha load testing CLI と、アプリに組み込まれた benchmarking routes を使用
post#createエンドポイントに5秒間シーケンシャルにリクエストを送ると、RPSは安定し、すべてのリクエストが成功する- 同じエンドポイントに4つの同時リクエストを5秒間送ると、一部のリクエストが500レスポンスを返す
SQLITE_BUSY と IMMEDIATE トランザクション
- ログで最初に確認できる問題は
SQLITE_BUSY例外 - SQLiteは同時に1つの書き込み処理だけを許可するため、データベースに書き込みロックを使う
- 一度に1つのコネクションだけが書き込みロックを保持できる
- 別のコネクションがロックを保持している状態で、新しいコネクションが書き込みロックを取得しようとすると
SQLITE_BUSYが発生する
- Railsアプリがより大きな同時負荷を受けると、
SQLITE_BUSYによって失敗するリクエストの割合も増える - SQLiteのデフォルトのトランザクションモードは deferred で、実際に書き込みが発生するまでロックを取得しない
- 単一コネクション環境や読み取り専用トランザクションが多い環境では、性能面で有利
- 本番のRailsアプリは複数スレッドで複数コネクションを使い、Railsは書き込みクエリをトランザクションで包むため、このデフォルトとは衝突する
- トランザクションの途中で書き込みロック取得に失敗すると、SQLiteはシリアライズ可能分離を壊さないために、そのクエリを安全に再試行できず、即座に例外を投げる
IMMEDIATEトランザクション は、トランザクション開始時点から書き込みロックの取得を試みる- SQLiteが書き込みクエリをキューに入れ、後から再びロック取得を試せるようになる
- Railsの書き込みトランザクションパターンには、deferredよりこちらの方が適している
sqlite3-rubygem は 1.6.9 からデフォルトトランザクションモードの設定をサポートしている- Railsは
database.ymlのトップレベルキーをsqlite3-rubyのデータベース初期化に渡す database.ymlでRailsのSQLiteトランザクションを IMMEDIATEモード で実行するよう設定できる
- Railsは
- この変更後、単純な負荷テストでは同時負荷下でもほとんど500エラーなしで処理できるが、16同時リクエストでは再び一部エラーが現れる
busy_timeout、GVL、custom busy_handler
- 次のボトルネックは、同時リクエスト数がPumaワーカー数に近づく、またはそれを超えたときに、p99レイテンシが急激に増加する現象
- 実際のリクエスト処理時間はPumaワーカー数の3倍の同時負荷でも安定しているが、約5秒かかるリクエストが出始めると
SQLITE_BUSYの500レスポンスも一緒に発生する - この5秒は
database.ymlの timeout 設定と一致し、SQLiteのbusy_timeout設定に対応する busy_timeoutは即座にBUSY例外を投げる代わりに、指定ミリ秒のあいだ待機しながら書き込みロックの再取得を試みる- SQLiteは一種の指数バックオフ方式でロック再取得を試みる
- timeout 内にロックを取得できなかった場合にのみ
BUSY例外を投げる - Webアプリケーションは複数コネクションを開いても、書き込み順序を自前で調整せずSQLiteに任せられる
- ボトルネックは、Rubyプロセス内にSQLiteが組み込まれており、
sqlite3-rubyがSQLiteのCコードを呼び出す際にRubyのGVLを解放しないことから生じる- 1つのPumaワーカーは、データベースクエリの返却を待つあいだもGVLを保持し続ける
- 他のPumaワーカーが同時に書き込みクエリをSQLiteへ送ることすら難しくなる
- SQLiteの逐次書き込み特性がRailsのリクエスト処理までより直列的にしてしまい、スループットを大きく下げる
- SQLiteは
busy_timeoutより低レベルなbusy_handlerフックを提供しているbusy_timeoutはSQLiteが提供する特定のbusy_handler実装sqlite3-rubyはsqlite3_busy_handlerC関数へのバインディングを提供しているため、クエリがキューに入ったときに呼ばれるRubyコールバックを作れる
- Rubyコールバックで
Kernel.sleepを使えば、クエリが書き込みロックの再試行を待つあいだGVLを解放できる - この方法は同時負荷時のp99レイテンシを大きく改善するが、p99.99レイテンシでは、同時負荷が増えるほど最も遅いリクエストがさらに遅くなる現象が残る
指数バックオフのロングテールと1ms再試行
- Rubyで再実装したSQLiteの
busy_timeoutロジックは、長く待たされているクエリほど不利になる構造 - 初期の再試行では待機時間は短いが、コールバック呼び出し回数が増えるほど待機時間が長くなる
- 最初の待機では1ms待つ
- 10回目の呼び出しでは50ms待つ
- 12回目以降は100msずつ待つ
- 累積待機時間が timeout の5000msを超えると例外が発生する
- 継続的に新しい書き込みクエリが入ってくると、新しいクエリは短い待機時間でより頻繁にロック取得を再試行できる
- すでに3回待機した古いクエリが10ms待っているあいだに、新しいクエリは1ms、2ms、5msの待機を経て3回再試行できる
- この増加型バックオフは、古いクエリが書き込みロックを取得できずにtimeoutする可能性を高める
- 解決策は、クエリの古さに関係なく、すべてのクエリが同じ頻度で再試行するようにすること
sqlite3-rubyの main ブランチ には、この変更が反映されている- この機能は執筆時点ではまだタグ付きリリースには含まれていない
- Rubyコールバックは
sleepによって待機中にGVLを解放する - 常に 1ms だけ sleep する
- この変更後、ベンチマークでは p99.99 レイテンシ曲線が平坦化する
- 同時性がPumaワーカー数の半分を超えたときのジャンプは残る
- その後のロングテールレイテンシは約0.5秒程度で平坦になる
WALモードと読み取り/書き込みコネクションプールの分離
- SQLite on Rails の性能のために必要な4つの条件は、IMMEDIATEトランザクション、GVLを解放するbusy handler、適切なSQLite設定、そしてWALモード
- write-ahead log は、SQLiteが複数の同時読み取りを処理できるようにする
- デフォルトの rollback journal mode では、読み取りでも書き込みでも一度に1つのクエリしか許可されない
- WALモードでは複数のreaderを同時に許可するが、writerは同時に1つだけ
- Rails 7.1 以降、RailsはSQLiteデータベースにより良いデフォルト設定を適用している
- これらの設定は、SQLiteがWebアプリケーション文脈でうまく動作するために重要
- 詳細設定とその理由は、別の ブログ記事 で扱っている
- 任意の5つ目の性能レバーは、読み取り専用プールと書き込み専用プールを分離すること
- SQLiteのWALモードは、複数の読み取りコネクションと1つの書き込みコネクションをサポートする
- Active Recordのコネクションプールが書き込みコネクションで飽和すると、読み取り処理まで不要にブロックされる可能性がある
- Railsの multiple databases support を活用すれば、readerとwriterの設定を同じSQLiteデータベースへ向けられる
- 実際には別データベースではなく、同じ単一データベースを指している
- その結果、分離されたコネクションプールとコネクション設定を作れる
- readerコネクションプールは読み取り専用コネクションで構成し、writerコネクションプールは1つのコネクションだけを持つようにする
- Active Recordモデルは役割に応じて適切なコネクションプールに接続するよう設定する
- Railsの automatic role switching を使って、すべてのWebリクエストのデフォルトコネクションをreaderプールにし、データベース書き込みが必要なときだけwriterプールへ切り替える
- 同じデータベースを書いているため、
read your own writes保証のためのdelayは不要 - ActiveRecordアダプタの
transactionメソッドにパッチを当てて、トランザクションがwriterデータベースへ接続されるようにする
- 同じデータベースを書いているため、
- この「deferred requests」と分離コネクションプールの組み合わせは、comment create エンドポイントのテストで、単純なRPS基準の性能改善を示す
gemとしてパッケージ化された改善点
- すべての改善をRailsアプリ内で直接実装する必要はない
activerecord-enhancedsqlite3-adapterを導入すれば、関連する改善を適用できる- 分離コネクションプール機能はより新しい実験的機能のため、設定を追加してopt-inする必要がある
- このアプローチは、SQLiteをRails本番環境で高速かつ柔軟に使うためのツール、手法、デフォルト設定をまとめて提供する
- RailsはSQLiteと組み合わせて使うのに適したWebアプリケーションフレームワークであり、関連ツールとgemエコシステムも成長している
1件のコメント
Hacker News のコメント
SQLite + Railsを使おうとしている人なら、Oldmoe（X/GitHub）のLitestackプロジェクトはぜひ見る価値があります
Litestackは、RubyとRuby on RailsアプリでSQLiteの組み込み性を活用し、SQLデータベース、高速キャッシュ、ジョブキュー、メッセージブローカー、全文検索エンジン、メトリクス基盤を1つのパッケージで提供するRuby gemです
今のプロジェクトで使っていますが、とても満足しており、リンクはこちらです: https://github.com/oldmoe/litestack
これほど詳しい記事を書くのに何日かかったのか想像もつきません
Railsに限らず、SQLiteのWebアプリケーションのスケーリングを考えている人に役立つ記事です
SQLiteを扱う人は、使用言語やフレームワークに関係なくこの記事を読むべきです
数年前、こうした内容の大半を自力で見つけなければならなかった立場として、まとめてくれてありがたいです
FOSSの分析システムを作っているのですが、インストールを簡単にする必要があるため、イベントデータをメインアプリのデータとは分離した別のSQLiteデータベースに送りたいと考えています
そこそこ忙しいWebサイトでも毎秒1000件以上のイベントが発生し得るので、スケーリングが心配です
サーバーメモリにイベントをためて、1秒ごとに一度バッチ書き込みする方式は、SQLiteの複数書き込み制限を避ける合理的な方法なのか、あるいはもっと良いアイデアがあるのか気になります
ベンチマークでも簡単に証明できますし、まとめた項目全体を単一トランザクションに入れることもできます
バッチ処理であれば、実質的にバッチを取り出して実際の書き込みを行うスレッドが1つできるため、SQLiteの同時書き込みは1つまでという制限とも相性が良いです
ただし複雑さは少し増します。単一のバッチ書き込みが1秒以内に終わらなかったらどうするのか、メモリ内に保存するキューサイズを無制限にするのか、無制限ならサーバー過負荷時にOOMで落ちないと確信できるのか、制限を設けるなら項目の欠落を許容するのか、捨てるならどの項目を捨てるのか、キューの上限はいくつにするのかを決める必要があります
こうした問いは、キューを必要とするほぼすべてのシステムで必ず出てくるもので、今だけでなく今後の状況にも役立ちます
SQLiteのトランザクション並行性の性能を信頼しにくいなら、特定のスレッドやプロセスですべての書き込みを直列化すればよいです
ClickHouseのasync-insert[0]機能を使えば、アプリケーション側でイベントのバッチ処理を気にしなくて済みます
組み込み型のソリューションを探しているなら、ClickHouseベースのchDBを使えます
[0] https://clickhouse.com/blog/asynchronous-data-inserts-in-cli...
読み取りワーカーはいくらでも置けますし、ライターはキューのようなものから入力を受け取ればよいです
これだけで、性能が本当に驚くほど高いことが多いです
SQLite をプロダクションのバックエンドデータベースとして使おうという流れは、まだよく理解できない
SQLite はスマートフォンのアドレス帳のような、小さく組み込み可能なクライアント側アプリケーションのデータベースとしては優れているが、開発者自身もその範囲を超えて拡張されることを一貫して拒んできた
たとえば日付/時刻や UUID のような有用なネイティブ型を追加していない。コードと組み込みオブジェクトのサイズが大きくなるためで、その結果「すべてが文字列」という状態に閉じ込められている
参照整合性は有効化できるが、制約条件のオプションも非常に限られている
なぜ合っておらず、きちんとサポートもされていない役割に、無理やり押し込み続けようとするのか分からない
理由は単純。読み取り中心のアクセスパターンでは SQLite は非常に速く、十分に速いのでデータベースアクセスコードを単純化でき、たとえば N+1 クエリも実際には問題にならないことが多い
また SQLite は N 層アーキテクチャから 1 つの層を取り除き、その分だけ故障しうる要素も減らす。Postgres や MySQL を自分で運用したことがあるなら、実際に多くのことがうまくいかなくなり得ると分かるはず
すべてのアプリ、まして多数のアプリにとって完璧な選択ではないが、今の流れは SQLite が「小さな組み込み型クライアントアプリのデータベース」にしか適さないという明らかに誤った考えに対するバランス調整に近い
そう見れば、同じデータを扱う DBMS の横に、さらに別の DBMS がある構造はかなり滑稽に見える
そこで、クライアントを Postgres に直接接続させるか、Postgres を外して独自の DBMS により集中するかに分かれる
2 番目を選ぶなら、SQLite はその上に構築しやすいエンジンだ。完璧ではないが、今ある道具である
こうした気づきが大規模に広がったのは比較的最近なので、何ができて何ができないのかを探る実験が多く進んでいる
コンピューティングにおける自然な循環であり、古いものがまた新しくなるということだ
Postgres は MySQL、MSSQL、Oracle、または他の DBMS に読み替えてもよい
こうした単純化は SQLite の開発者や低スペックハードウェアだけでなく、アプリ開発者にもさまざまな面で利益をもたらす
ドキュメントが単純になり、学習曲線が短くなり、バグの表面積やバイナリサイズも小さくなる
最近のソフトウェアには、あらゆるものに肥大化と複雑性を加えようとする傾向があるが、SQLite のようないくつかのプロジェクトがそれに抗っているのは非常に良い
素晴らしい記事で、Django にも似た資料があるのか気になる
ArchiveBox は Django 経由で SQLite を使っているが、記事で Rails について説明されているのとまったく同じ問題にかなり頻繁に遭遇している
アプリの別経路ですべての書き込みを直列化しなくても済む SQLite レイヤーのソリューションがあるとよい
sqlite3-rubygem は設計上、SQLite 呼び出し中に GVL を解放しないが、リンク先の issue コメント https://github.com/sparklemotion/sqlite3-ruby/issues/287#iss... を見ると、ロックを再取得するコストが大きいという疑いはあったものの、検証はしていないように見えるこうした回避策のすべてを考えると、少し疑わしい
Python の拡張文化なら逆の設計になっていそうだが、実際にそちらではどうしているのか気になる
さらにリンク先の issue には、「extralite gem はブロッキング中に GVL を解放する代替 SQLite クライアントであり、並行性に関する内容は https://github.com/digital-fabric/extralite?tab=readme-ov-fi... にある。一般的にこの gem よりずっと速く、並行性の問題もない」というコメントもある
SQLite 仮想マシンのすべての
stepごとに単純に GVL を解放すると、シングルスレッド性能が大きく悪化することが検証されているシングルスレッド性能とマルチスレッド性能の中間点を見つけるのは難しい
Rails では接続プールがあるためマルチスレッドであることが分かっているが、低レベルの gem はシングルスレッド環境で使われる他のライブラリやツールでも多く使用されている
個人の実験的な Web サービスで維持しているいくつかの調整値は次のとおり
PRAGMA journal_mode = WAL;PRAGMA busy_timeout = 5000;PRAGMA synchronous = NORMAL;PRAGMA cache_size = 1000000000;PRAGMA foreign_keys = true;PRAGMA temp_store = memory;そして BEGIN IMMEDIATE トランザクションを使っている
https://kerkour.com/sqlite-for-servers
cache_sizeとmmap_sizeについてはどう考えているのか気になるSQLite も Rails も好きだが、これは本番環境で MS Access を使うのに似て見える
それに、Access の利用がより多かった数十年前より、今はコンピュータもディスクもずっと速くなっている
読み取り中心なら、SQLite はもちろん Jet でさえ、秒間数万リクエストにはかなり容易に到達できるはず
ほとんどのアプリケーションは同時ユーザー数が数十万人に達しないので、SQLite がうまく合う場合がある
SQLite は、ほぼあらゆるプラットフォームと言語にクライアントがある点も強み
アーカイブ、バックアップ、移植性も SQLite に向いた用途。特定期間に限られたデータ入力があるプロジェクトで、ボックスごとに SQLite を使うことを強く推したことがあり、今でもそのほうが良かっただろうと感じている
複雑なスキーマやユーザー定義のエクスポート／アーカイブ機能を作る代わりに、1つのファイルをコピーしてアーカイブやバックアップにでき、時間とともに生じるスキーマ変更もそこまで深く考えずに済んだはず
状況によるが、多くの問題に対してかなり良い解決策。ほとんどのアプリケーションでは、よりスケーラブルな NoSQL の選択肢より PostgreSQL や他のリレーショナル DBMS のほうが良い選択であることが多いのと似ている
過剰に設計しがちだったが、今ではコンピューティングと入出力性能が、そのような努力を正当化しにくい水準に近づきつつある
とても有益でよく書かれた記事
デフォルトの
busy_timeoutメソッドが、古いクエリにペナルティを与える 指数的な遅延 をなぜ持つのか気になるこれがデフォルト値として合理的な理由は何だろう？