Posted inDatabase

SQLiteを本番環境（Production）へ：WALモードとSynchronousの最適化で実負荷に耐える

Database tutorial - IT technology blog
Database tutorial - IT technology blog

クイックスタート：5分でSQLiteを高速化する

リクエストが多い時にアプリケーションが頻繁にフリーズしたり、database is locked というエラーが出たりしても、諦める必要はありません。SQLiteには、サーバー上で非常に安定した動作を維持しながら、毎秒数千のトランザクションを処理できるようにする「黄金の設定」があります。

-- 接続初期化時にこれらのコマンドを実行する
PRAGMA journal_mode = WAL;          -- WALモード（先行書き込みログ）、非常に重要
PRAGMA synchronous = NORMAL;         -- 速度と安全性のバランス
PRAGMA temp_store = MEMORY;          -- 一時データをRAM上に保存
PRAGMA mmap_size = 2147483648;       -- Memory Mapを使用（例：2GB）
PRAGMA busy_timeout = 5000;          -- DBがビジーな場合に5秒間待機
PRAGMA cache_size = -64000;          -- 約64MBのキャッシュを割り当て

実際、この設定により、一般的なSSDでの書き込み速度を毎秒50〜100トランザクションから2000トランザクション以上に引き上げることができます。また、読み取りスレッドと書き込みスレッド間の競合の問題もほぼ完全に解決されます。

なぜデフォルトのSQLiteは遅いのか？

SQLiteはデモアプリやローカル保存にしか向かないと思われがちですが、それは誤解です。SQLiteは非常に強力ですが、デフォルト設定では高い処理速度よりも絶対的な安全性（Safe by default）が優先されているだけなのです。

古いロールバックジャーナル（Rollback Journal）の仕組み

デフォルトモード（DELETE）では、データを書き込むたびに、SQLiteは古いデータをジャーナルファイルにコピーしてからメインファイルに上書きします。このプロセスにより、システムは何度もファイル書き込みを行う必要があります。特に、誰かが書き込みを行っている間、すべての読み取りスレッドがブロックされるのが大きな問題です。これが、トラフィックが増えたときにアプリケーションがハングアップするボトルネックとなります。

以前、システムログを処理するTelegramボットを運用していた際、デフォルト設定ではログが大量に流れてくるたびにボットが Error: database is locked というエラーを出し続けました。データベースがジャーナルファイルの処理に追われていたため、ユーザーはその間データを検索することができませんでした。

WALモード – 本番環境向けの鍵となるソリューション

Write-Ahead Logging (WAL) は、SQLiteをサーバー環境に導入する上で最も重要な変更です。データを直接上書きする代わりに、SQLiteは変更内容を -wal という拡張子の別のファイルに書き込みます。

WALの優れた利点：

  • 読み書きの並列処理をサポート： 読み取りが書き込みをブロックせず、その逆も同様です。5,000行のログをインサートしながら、遅延なくレポートのセレクトを行うことができます。
  • シーケンシャルI/O： データはWALファイルに追記される形で書き込まれるため、メインのデータベースファイル内をランダムにアクセスするよりもはるかに高速です。
  • ディスク負荷の軽減： OSにディスクへの書き込み完了（fsync）を要求する回数が減り、I/O待ちが大幅に改善されます。

注意：WALを有効にすると、データベースのディレクトリに .db-wal と .db-shm というファイルが生成されます。これらはメインファイルに統合（チェックポイント）される前の一時データが保存されている場所なので、削除しないでください。

Synchronous – 速度と信頼性のトレードオフ

PRAGMA synchronous パラメータは、データがハードディスクに安全に書き込まれたことをOSが確認するのをSQLiteがどの程度待つかを制御します。

  • FULL (2): デフォルト設定。SQLiteはディスクへの書き込みが完全に完了するまで次の処理を待ちます。非常に安全ですが、極めて低速です。
  • NORMAL (1): WALを使用する場合の最適な選択肢です。アプリケーションがクラッシュしてもデータは安全です。リスクが生じるのは、OS全体がダウン（突然の停電など）した場合のみです。現在のクラウドサーバー環境では、NORMAL で十分です。
  • OFF (0): 爆速ですが、非常に危険です。停電が発生すると、データベースの構造が破損（Corrupt）しやすくなります。一時的なデータにのみ使用してください。

アドバイス：常に journal_mode = WAL と synchronous = NORMAL を組み合わせてください。これは、データの安全性を確保しつつ、パフォーマンスを数倍に引き上げる最強の組み合わせです。

その他の価値ある微調整

1. Busy Timeout（ビジータイムアウト）

マルチスレッド環境では、2つのプロセスが同時に書き込みを行おうとすることがあります。すぐにエラーを返すのではなく、PRAGMA busy_timeout = 5000;（5秒間待機）というコマンドで、SQLiteに少し待つように指示しましょう。

2. キャッシュサイズの増量

デフォルトでSQLiteは約2MBのキャッシュしか使用しません。サーバーのRAMに余裕がある場合は、ディスク読み取り操作を減らすために64MB以上に増やしましょう：PRAGMA cache_size = -64000;。

3. メモリマップドI/O (mmap)

従来の read() 関数の呼び出しの代わりに、mmap を使用すると仮想メモリを介してデータベースに直接アクセスできます。これにより、メモリ層間でのデータコピーが不要になるため、大規模な SELECT クエリが明らかに高速化されます。

Pythonによる実装例

以下は、Pythonで最大限のパフォーマンスを得るための標準的な接続設定方法です：

import sqlite3

def get_production_conn(db_path):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    
    # 最適な設定を適用する
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA synchronous = NORMAL")
    conn.execute("PRAGMA cache_size = -64000")
    conn.execute("PRAGMA foreign_keys = ON")
    
    return conn

# 実際の使用例
db = get_production_conn("data_prod.db")
db.execute("INSERT INTO events (type) VALUES (?)", ("ユーザーログイン",))
db.commit()

実務経験と最後の注意点

2年前、コスト削減のために50GBのトラッキングシステムをMySQLからSQLiteに移行したことがあります。当初はI/O待ちが高すぎてサーバーのCPUが常に100%に達していました。しかし、WALとSynchronous NORMALを有効にした後、CPU使用率は10%未満に下がり、APIのレスポンスはかつてないほど高速になりました。

ただし、リスクを避けるために以下の3つの「黄金律」を覚えておいてください：

  • ネットワークドライブを使用しない： SQLiteファイルをNFSやSMB上に置くことは絶対に避けてください。ネットワーク経由のファイルロック機構は非常に不安定で、データの破損を招きやすいです。
  • 過度な書き込み負荷（Write Heavy）の制限： 最適化しても、SQLiteは一度に一人の「書き込み担当者」しか持てません。毎秒数千件の書き込みが継続的に必要なシステムであれば、PostgreSQLの方が適しています。
  • 正しいバックアップ方法： WALを使用している場合、単に .db ファイルをコピーするだけでは不十分です。バックアップの完全な整合性を確保するために、VACUUM INTO 'backup.db' コマンドを使用してください。

SQLiteは手懐け方さえ知っていれば、非常に強力な味方になります。今回の共有が、今後の本番プロジェクトで自信を持ってSQLiteを採用する助けになれば幸いです。

Share:
Tags: