Tech Racho エンジニアの「？」を「！」に。
  1. TOP
  2. Ruby / Rails関連
  3. SQLite on Railsシリーズ（02）SQLiteをチューニングで強化する（翻訳）
  • Ruby / Rails関連

SQLite on Railsシリーズ（02）SQLiteをチューニングで強化する（翻訳）

概要

原著者の許諾を得て翻訳・公開いたします。

参考: Rails 8はSQLiteで大幅に強化された「個人が扱えるフレームワーク」（翻訳）｜YassLab 株式会社

日本語タイトルは内容に即したものにしました。

本記事のチューニングは、Rails 7.1以降に取り込まれています。参考までに、以下はRails 8.0.1のSQLiteデフォルト設定です。

      DEFAULT_PRAGMAS = {
        "foreign_keys"        => true,
        "journal_mode"        => :wal,
        "synchronous"         => :normal,
        "mmap_size"           => 134217728, # 128 megabytes
        "journal_size_limit"  => 67108864,  # 64 megabytes
        "cache_size"          => 2000
      }

SQLite on Railsシリーズ（02）SQLiteをチューニングで強化する（翻訳）

本記事は、Ruby on RailsのデータベースエンジンにSQLiteを使うことでアプリケーションを強化する方法を紹介するシリーズ記事の第2弾です。
今回は、production環境のWebアプリケーションをより適切にサポートするためのSQLite設定の調整方法を詳しく解説します。

チューニング済みの設定に飛びつく前に、SQLiteがどのように設定されるかについておさらいしておきましょう。

SQLiteの設定は、カスタムSQLステートメントを使って行います。
たとえばPRAGMAステートメントの説明は以下のようになっています。

PRAGMAステートメントは、SQLite固有のSQL拡張機能であり、SQLiteライブラリの操作を変更したり、SQLiteライブラリ内部（つまりテーブル以外）のデータを照会するのに使われます。

この定義からわかるように、プラグマステートメントは基本的に以下の2種類です。

  • SQLiteライブラリの動作を変更する
  • SQLiteライブラリの内部データを照会する

SQLiteを設定するうえで関心があるのは、後者ではなく前者です。
SQLiteのドキュメント↓には、プラグマのあらゆるステートメントに関する概要が掲載されています。

参考: PRAGMA statements supported by SQLite

その中から非推奨化されたものや特殊なもの、内部データ用のものを除外していくと、以下のように40件の「設定用」PRAGMAが残ります。

application_id
auto_vacuum
automatic_index
busy_timeout
cache_size
cache_spill
case_sensitive_like
cell_size_check
checkpoint_fullfsync
data_version
defer_foreign_keys
encoding
foreign_keys
freelist_count
fullfsync
hard_heap_limit
ignore_check_constraints
integrity_check
journal_mode
journal_size_limit
legacy_alter_table
locking_mode
max_page_count
mmap_size
page_count
page_size
query_only
quick_check
read_uncommitted
recursive_triggers
reverse_unordered_selects
secure_delete
soft_heap_limit
synchronous
temp_store
threads
trusted_schema
user_version
wal_autocheckpoint

私はRails 7.0.7.2アプリケーションを作成して、これらのPRAGMAの値が新規Railsアプリケーションでどのようにセットアップされているかをチェックしてみました。

{"analysis_limit"=>0,
 "application_id"=>0,
 "auto_vacuum"=>0,
 "automatic_index"=>1,
 "timeout"=>5000,
 "cache_size"=>-2000,
 "cache_spill"=>483,
 "cell_size_check"=>0,
 "checkpoint_fullfsync"=>0,
 "data_version"=>1,
 "defer_foreign_keys"=>0,
 "encoding"=>"UTF-8",
 "foreign_keys"=>1,
 "freelist_count"=>0,
 "fullfsync"=>0,
 "hard_heap_limit"=>0,
 "ignore_check_constraints"=>0,
 "integrity_check"=>"ok",
 "journal_mode"=>"delete",
 "journal_size_limit"=>-1,
 "legacy_alter_table"=>0,
 "locking_mode"=>"normal",
 "max_page_count"=>1073741823,
 "mmap_size"=>0,
 "page_count"=>5,
 "page_size"=>4096,
 "query_only"=>0,
 "quick_check"=>"ok",
 "read_uncommitted"=>0,
 "recursive_triggers"=>0,
 "reverse_unordered_selects"=>0,
 "secure_delete"=>0,
 "soft_heap_limit"=>0,
 "synchronous"=>2,
 "temp_store"=>0,
 "threads"=>0,
 "trusted_schema"=>1,
 "user_version"=>0,
 "wal_autocheckpoint"=>1000}

原注

上の出力は以下のコマンドで得ました。

pragmas.reduce({}) do |memo, pragma|
  memo.merge!(ActiveRecord::Base.connection.execute("PRAGMA #{pragma}").first)
end

これは興味深い結果ですが、もちろん全てのプラグマがRailsやActive Recordのチューニングで同じように重要というわけではありません。そこで、最も影響力のあるプラグマに絞って見ていきましょう。特にWebアプリケーションのコンテキストでは、パフォーマンスに大きな役割を果たすプラグマが6つほどあります。

  • journal_mode
  • synchronous
  • journal_size_limit
  • mmap_size
  • cache_size
  • busy_timeout/busy_handler

これらのプラグマの機能や用途を理解し、RailsとActive Recordでの利用方法に最も適した調整方法を理解しておくことが重要です1。

🔗 1: journal_modeプラグマ

最初のjournal_modeは、十分理解してからチューニングすべき最も重要なプラグマです。SQLite 3.7.0（2010-07-21）以降は、アトミックトランザクションをサポートするために以下の2種類の実装を提供しています。

ロールバックジャーナルはオリジナルのデフォルト実装ですが、ログ先行書き込みは新しい実装です。

journal_modeプラグマには以下のオプションがあります。

  • ロールバックジャーナル実装の振る舞いを変更する5つのオプション（DELETE、TRUNCATE、PERSIST、MEMORY、OFF）
  • ログ先行書き込み実装の利用を指定する1つのオプション（WAL）

新規作成直後のRailsアプリは、デフォルトではロールバックジャーナルをDELETEジャーナルモードで利用します。これは、トランザクションがコミットされるたびにロールバックジャーナルのファイルがディスク上から削除されるという意味です。

しかしWebアプリケーションの場合は、ログ先行書き込みがオプションとして優れています。SQLiteドキュメントにも書かれているように、ログ先行書き込みは、特にWebアプリケーションのコンテキストで、ロールバックジャーナルよりも優れた点がいくつかあります。

  • WALはほとんどのシナリオで著しく高速化される
  • WALのリーダー（reader）はライター（writer）をブロックせず、ライターもリーダーをブロックしないため、コンカレンシーが高まる。読み取りも書き込みもコンカレントに行われる。

私たちのアプリケーションで欲しいのはこれです。
Webではクエリの高速化とコンカレンシーの向上が必要なので、最初に行う必要がある設定変更は、journal_modeプラグマをWALに設定することです（Rails でこれを行う方法の技術的な詳細については、本記事の後半で説明します）。

🔗 2: synchronousプラグマ

SQLiteでサポートされているsynchronousプラグマは、SQLiteがコンテンツをディスクに反映するタイミングや方法を制御する4つの異なるモードをサポートしています。

FULLとNORMALは一般的なオプションです。
FULLは「すべての書き込み時に同期」、NORMALは「書き込み1000回ごとに同期」を意味します。

4つのモードはそれぞれ整数値にも対応しているので、新規作成直後のRailsアプリでは、デフォルトの"synchronous"=>2はFULLモードに対応します。つまりSQLiteはすべての書き込みのたびにファイルに書き込みます。ドキュメントにも以下のように書かれています。

このモードはOSクラッシュや電源障害でデータベースが破損しないようにします。FULL同期は非常に安全な代わりに低速です。

遅いモードは欲しくありませんし不要です。SQLiteドキュメントにも以下のように書かれています。

WALモードで実行されているほとんどのアプリでは、synchronous=NORMAL設定がよい選択となります。

要するに、journal_modeをWALにしたら、synchronousをNORMALに設定すればよいのです。この2つの設定はピーナッツバターとジャムのように相性バツグンです。

しかしNORMAL同期モードとはそもそも何でしょうか？
NORMALは、SQLiteのディスク反映を書き込みのたびに行うのではなく、もっと低い頻度で行うという意味です。

SQLiteには、ディスクに書き込む「最も重要な瞬間」を決定する独自のアルゴリズムがあります。SQLiteはそのタイミングで、すべてのwal_autocheckpointページ（デフォルトでは1000）を同期します。すなわち、wal_autocheckpointプラグマを変更すると、そのページ数の書き込みが行われた後でNORMALモードの同期が行われます。つまり、耐久性と引き換えに速度を得る強気の方法というわけです。

しかしSQLiteは耐久性をなるべく落とさないようさまざまなことを行っていますが、正直に言うと、これはよほどのエッジケースです。実際SQLiteは、OSやファイルシステムの障害でない限り潜在的なデータ損失はまず起きないようになっています。プロセスがクラッシュしてもデータの耐久性には影響しません。つまりこの最適化は1%のケースではなく99%のケースに対応するためのものであり、これはRailsアプリケーションに適していると思います。

🔗 3: journal_size_limitプラグマ

次はjournal_size_limitプラグマです。

これは、ログ先行書き込みデータをどの程度ディスク上に保持するかをSQLiteに指示します。
デフォルトの-1はファイルサイズに上限がないことを意味するため、このディスクファイルのサイズは無制限に大きくなります。これは望ましくありません。
ディスクがログファイルで溢れてアプリがダウンしてしまった経験があれば、ファイルサイズ上限なしという設定は、いつダウンするかわかったものではない頭痛の種となることがわかるでしょう。

そこでファイルサイズを適切なサイズに制限する必要があります。しかし、適切なサイズとは正確にはいくつなのでしょうか。

小さすぎるのは望ましくないでしょう。ジャーナルファイル内のデータ量が多いほど、（一般的には）その分SQLiteも高速になります。かといって大きすぎると、読み込みパフォーマンスが悪化するため望ましくありません。production環境での利用状況や実験に基づいて、確実なデフォルト値として64MBという値に落ち着きました。

🔗 4: mmap_sizeプラグマ

次はmmap_sizeプラグマです。この設定は、「メモリマップドI/O（mmap）でアクセスされるデータベースファイルの最大バイト数」を制御します。長い説明ですが、要するにSQLiteのメモリマップドI/Oを有効にすると複数のプロセス間でデータを共有できるようになります。

メモリマップはPostgreSQLのバッファプールと同様の役割を果たすため、無効にせずに、デフォルトのPostgreSQLバッファプールと同じ安全な値（128MB）に設定する必要があります。

🔗 5: cache_sizeプラグマ

cache_sizeプラグマは、開いているデータベースファイルごとにSQLiteがメモリ内に一度に保持するデータベースディスクページの最大数」を設定します。

デフォルト値-2000は負の数で、SQLite はこれをバイト数の上限として解釈します。正の数を指定すると、SQLiteはこれをページ数の上限として解釈します。なお、デフォルトの上限は最大2MB（2,048,000バイト）ですが、これはページ数とは無関係です。

大きなキャッシュを確保して、ページ間で分割されないようにしたいので、cache_sizeには正の数を指定してキャッシュ制限をページ数に設定しましょう。

cache_sizeの値は2000（最大2,000ページ）がおすすめです。これは、デフォルトのページサイズが4,096バイトであるため、キャッシュ制限が最大8MB（4,096x2,000 = 8,192,000バイト）となることを意味します。

ページキャッシュについて完全に理解するために、次の点を知っておくとよいでしょう。
ページキャッシュは個別のコネクション（同じプロセス内のコネクションも含む）ごとにプライベートであり、別のコネクションからデータベースファイルに書き込むとページキャッシュが効きません。それでもページキャッシュは、ステートメントまたはトランザクションの範囲内でコンカレンシーの実行速度を最大化するために非常に有用です。

🔗 6: busy_timeout/busy_handlerプラグマ

訳注

参考までにRails 8.0.1では、busy_timeoutがbusy_handler_timeoutに置き換えられました（#51958）。

理解してから調整することが重要な最後のプラグマはbusy_timeoutです。
これは、新しいコネクションの確立を試みるときに、データベースへの接続に成功するまでにSQLiteにどのくらいの時間待機するかを指示します。

SQLiteを使うRailsを新規作成すると、/config/database.ymlファイル内のtimeoutオプションは5000ミリ秒に設定されます。

SQLiteはタイムアウトを指定している間、指数バックオフ（exponential backoff）アルゴリズムを用いてコネクション試行をリトライします（バックオフは各試行の間に1、2、5、10、15、20、25、25、25、50、50、100ミリ秒ずつ待機し、12回リトライすると100ミリ秒ごとに再試行します2。これは妥当なデフォルトですが、より積極的なパフォーマンスチューニングを行うには、代わりに busy_handler を手動で設定することもできます。

busy_timeoutは、SQLiteで使うbusy_handlerを設定するための高レベルインターフェイスを提供しますが、カスタムbusy_handler関数を自分で設定することも可能です。一般的なアプローチは、指数バックオフを用いてできるだけ早期にコネクションをリトライし、できるだけ早期にコネクションを確立します。無限の再試行を防ぐためにリトライの最大回数を制限できます。Ruby用のSQLite3-rubyアダプタを使っている場合は、以下のようにprocを渡すことでbusy_handlerを設定できます。

@raw_connection.busy_handler do |count|
  count <= @config[:retries]
end

この後の実装セクションでは、Railsアプリケーションで「最大タイムアウト」ではなく最大リトライ回数を設定する方法について説明します。
さしあたって、busy_timeoutを使うかbusy_handlerを使うかは、BUSY例外が発生する可能性とタイミングがどのぐらい楽観的であるかによって決まることを知っておけば十分でしょう3。ほとんどのRailsアプリケーションでは、できるだけ早期にコネクションを確立できるようにbusy_handlerを設定することが推奨されます。

🔗 プラグマのまとめ

SQLiteの6つのプラグマは、以下のSQLで設定できます。

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA journal_size_limit = 67108864; -- 64MB
PRAGMA mmap_size = 134217728;         -- 128MB
PRAGMA cache_size = 2000;
PRAGMA busy_timeout = 5000;

これにより、Webアプリケーションに適した微調整済みのSQLiteデータベースが提供されます。実際、このデフォルト設定は、LitestackのLitedbモジュールで使われているデフォルト設定とまったく同じです。RailsにおけるSQLiteの利用に最適なデフォルト設定を見つけるための道筋を切り開くという大変な作業をしてくれたLitestackと@oldmoe に心から感謝いたします。

oldmoe/litestack - GitHub

これで、設定したい6つのプラグマと、それぞれに設定したい値が決まりました。残るは、Railsアプリケーションを実際にこの通りに設定する作業だけです。本シリーズのテーマとして、Railsをオーバーライドするのではなく、Railsを強化したいのです。そのためには、Rails上に構築され、Railsと同様の柔軟性を提供するメカニズムが必要です。

🔗 Railsアプリケーションをチューニングする

Railsのデータベースアダプタを自然に設定できるフックポイントがあるようですが、残念ながら、まだ拡張用に公開されていません。これはconfigure_connectionメソッドです。このメソッドのコメントは以下の通りです。

新しく確立された@raw_connectionで必要な初期化を実行します。ここでは、アダプタのコネクション設定を変更したり、アプリケーショングローバルの「セッション」変数を構成するためのクエリを実行したりします。

私たちが必要としているのはまさにこれのようです。まだ自然にフックをかけられないので、代わりに自己責任でSQLiteアダプタにモンキーパッチを適用しましょう。現時点のSQLiteアダプタは、timeoutオプションが設定されている場合はbusy_timeoutプラグマを設定し、configure_connectionメソッドでforeign_keysプラグマをオンにします。このメソッドを拡張するために、SQLiteアダプタを拡張する初期化ファイルを作成しましょう。

ActiveSupport.on_load(:active_record_sqlite3adapter)フックを使うと、アダプタが読み込まれた場合にのみ拡張できるようになります。このブロックにはSQLite3Adapterが渡されるので、ブロック内でprependを呼び出すだけです。つまり、configure_connectionメソッドを拡張するモジュールを定義して、そのモジュールをアダプタクラスにprependするだけです。Rails 拡張機能をRailsExtモジュール名前空間の下に配置したので、RailsExt::SQLite3Adapterモジュールを作成しましょう。

# /config/initializers/active_record_sqlite3adapter.rb
module RailsExt
  module SQLite3Adapter
    # Perform any necessary initialization upon the newly-established
    # @raw_connection -- this is the place to modify the adapter's
    # connection settings, run queries to configure any application-global
    # "session" variables, etc.
    #
    # Implementations may assume this method will only be called while
    # holding @lock (or from #initialize).
    #
    # extends https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L691
    def configure_connection
      super

      # ...
    end
  end
end

ここで行いたいのは、Railsデータベース設定のセットアップに機能強化を提供することで。私見では、データベース設定のデフォルト部分にpragmasセクションを設定すれば、明確かつ柔軟な開発者エクスペリエンスを提供できると思います。後は、プラグマハッシュを反復処理し、SQLite呼び出しを実行して、拡張モジュールでプラグマを設定すればよいのです。これは簡単に実装できます。

def configure_connection
  super

  @config[:pragmas].each do |key, value|
    raw_execute("PRAGMA #{key} = #{value}", "SCHEMA")
  end
end

これで、データベース設定を以下のように強化できます。

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  # 例外発生前に書き込みロックを取得するために待機する時間（ミリ秒単位）
  # https://www.sqlite.org/pragma.html#pragma_busy_timeout
  timeout: 5000
  pragmas:
    # データベースの耐久性（durability）のレベル。
    # 2 = "FULL"（書き込みごとに同期）、その他の値には1 = "NORMAL"（1000ページの書き込みごとに同期）、0 = "NONE"がある。
    # https://www.sqlite.org/pragma.html#pragma_synchronous
    synchronous: "NORMAL"
    # ジャーナルモードのWALでは高コンカレンシー（多数のリーダーと1つのライター）が実現する。
    # https://www.sqlite.org/pragma.html#pragma_journal_mode
    journal_mode: "WAL"
    # WALファイル数に上限を設けて無制限な増加を防ぐ（読み取りパフォーマンスにも悪影響を及ぼす）
    # https://www.sqlite.org/pragma.html#pragma_journal_size_limit
    journal_size_limit: <%= 64.megabytes %>
    # すべてのプロセスがデータを共有できるようにグローバルメモリマップを設定する
    # https://www.sqlite.org/pragma.html#pragma_mmap_size
    # https://www.sqlite.org/mmap.html
    mmap_size: <%= 128.megabytes %>
    # ローカルのコネクションキャッシュを2000ページに増やす
    # https://www.sqlite.org/pragma.html#pragma_cache_size
    cache_size: 2000

このような感じで、アプリケーションのSQLiteデータベースを Webアプリケーション用途に適した設定に調整したことで、必要に応じて追加のSQLiteプラグマを設定するための明確でシンプルなメカニズムも提供されました。

最後に、timeoutオプションではなくretriesオプションをサポートする方法について説明しましょう。ここで重要な詳細は2つあります。

  • 第1に、retriesオプションとtimeoutオプションは両方同時に設定できない点が重要です（busy_handlerとbusy_timeoutは相互排他的であるため）。

  • 第2に、このプラグマは最初に設定される点が重要です（ビジー処理ロジックが他のプラグマクエリで尊重されるようにするため）。

これらの機能をサポートするには、configure_connectionメソッドを以下のように更新します。

def configure_connection
  if @config[:timeout] && @config[:retries]
    raise ArgumentError, "Cannot specify both timeout and retries arguments"
  elsif @config[:retries]
    # 参考: https://www.sqlite.org/c3ref/busy_handler.html
    @raw_connection.busy_handler do |count|
      count <= @config[:retries]
    end
  end

  super

  @config[:pragmas].each do |key, value|
    raw_execute("PRAGMA #{key} = #{value}", "SCHEMA")
  end
end

これで、timeout: 5000設定をretries: 1000設定に置き換えて適切なbusy_handlerが設定されるようになります。

原注

本記事で行った最終的な実装についてはGistで参照できます。

さらに、Nate Hopkinsが共有してくれたGistには、いくつかのパフォーマンス最適化を含んだSQLiteを手動でダウンロードしてコンパイルするDockerfileが含まれています。

SQLiteのドキュメントには、SQLiteを自分でコンパイルする場合に行うべき推奨されるコンパイル最適化が概説されており、NateはこれをDockerfileにうまく統合しています。そのいくつかは、私たちが採用しているPRAGMAステートメントと重複していますが、それ以外はすべてコンパイル最適化です。つまり、NateはSQLiteをコンパイルするときに次のフラグを渡しています。

SQLITE_DEFAULT_MEMSTATUS=0 \
SQLITE_DEFAULT_PAGE_SIZE=16384 \
SQLITE_DEFAULT_WAL_SYNCHRONOUS=1 \
SQLITE_DQS=0 \
SQLITE_ENABLE_FTS5 \
SQLITE_LIKE_DOESNT_MATCH_BLOBS \
SQLITE_MAX_EXPR_DEPTH=0 \
SQLITE_OMIT_PROGRESS_CALLBACK \
SQLITE_OMIT_SHARED_CACHE \
SQLITE_USE_ALLOCA"

SQLITE_DEFAULT_WAL_SYNCHRONOUS設定は、私たちのPRAGMA journal_mode = WAL;やPRAGMA synchronized = NORMAL;設定と重複していますが、メモリトラッキングやBLOBフィールドでのLIKE検索を無効にする最適化はコンパイル時にのみ有効です。

今回もいつものように、可能であればデータベースをチューニングしてニーズに合わせる方法を必ず学んでおきましょう。SQLiteドキュメントのコンパイル時の推奨オプションとNateのDockerfileのページを読み、SQLiteのインストールから余分なサイクルを絞り出してRailsアプリをスムーズに動かす方法を学んでください。

関連記事

Rails 7.1: SQLite3アダプタのデフォルトコネクション設定が最適化された（翻訳）

SQLite on Railsシリーズ（01）Gitブランチごとにデータベースを切り替える（翻訳）

  1. 原注: SQLiteをproduction環境で利用するためのプラグマ調整法について詳しい記事として、SQLite performance tuning - Scaling SQLite databases to many concurrent readers and multiple gigabytes while maintaining 100k SELECTs per second - phiresky's blogやIncreasing SQLite Performance - Devart Blogなどがあります。 ↩
  2. 原注: これはSQLiteのメンテナーがフォーラムで返信したものであり、main.cファイル内のsqliteDefaultBusyCallbackメソッドで見られます。 ↩
  3. 原注: SQLiteが例外をスローする方法については、『Understanding SQLITE_BUSY • ActiveSphere - Software consulting』という良記事に詳しくまとまっています。 ↩

タグ
この記事を書いた人

hachi8833

Twitter: @hachi8833、GitHub: @hachi8833 コボラー、ITコンサル、ローカライズ業界、Rails開発を経てTechRachoの編集・記事作成を担当。 これまでにRuby on Rails チュートリアル第2版のコンテンツ監修、Railsガイドのコンテンツ作成を担当。 かと思うと、正規表現の粋を尽くした日本語エラーチェックサービス enno.jpを運営。 実は最近Go言語が好きで、Goで書かれたRubyライクなGoby言語のメンテナーでもある。 仕事に関係ないすっとこブログ「あけてくれ」は2000年頃から多少の中断をはさんで継続、現在はnote.comに移転。Amazonウィッシュリスト: https://bit.ly/32aAmiI

hachi8833の書いた記事一覧へ

本記事の内容へのお問い合せはTwitterで@techrachoへMentionまたはDMにてご連絡頂くか、運営会社であるBPS株式会社のお問い合せフォームよりお問い合せ下さい。

CONTACT

TechRachoでは、パートナーシップをご検討いただける方からの
ご連絡をお待ちしております。ぜひお気軽にご意見・ご相談ください。
CONTACT FORM