Posted inデータベース

SQLiteパフォーマンス最適化：WALモード、PRAGMAチューニング、並行処理の実践本番ガイド

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

背景と導入の理由

6か月前、私たちのチームは毎秒約450リクエストを処理する軽量なテレメトリ収集サービスを本番環境へリリースしました。運用の複雑さを徹底的に排除するため、データベースにはSQLiteを採用しました。独立したデータベースコンテナも不要で、ネットワークホップもゼロ、運用オーバーヘッドも最小限に抑えられるからです。しかしリリースから48時間も経たないうちに、ログには見慣れたエラーが大量に出力され始めました：sqlite3.OperationalError: database is locked

多くのエンジニアはSQLiteをおもちゃやインメモリのテスト用モックにすぎないと軽視しがちです。しかし、その認識は誤っています。SQLiteはモバイルOS、車載インフォテインメント、主要ブラウザ、そして高スループットなエッジプロキシのバックエンドを支えています。ボトルネックはストレージエンジンそのものにあるのではなく、NVMe SSDを搭載した現代のマルチコアサーバー向けではなく、2004年当時の低スペックな組み込みマイコン向けに設定された極めて保守的な初期設定にあります。

デフォルト状態のSQLiteは、ロールバックジャーナルモード（DELETEまたはPERSIST）を使用します。このモードでは、書き込み処理が発生するたびにデータベースファイル全体が排他的にロックされます。書き込みトランザクションのコミット中は読み取りができず、読み取りトランザクションが開かれている間は書き込みが一切できません。この状態で複数のアプリケーションワーカーから30件の並行リクエストが届くだけでクエリは滞留し、デフォルトのタイムアウト（0ミリ秒）に到達してクラッシュしてしまいます。

この問題を解決するには、読み取りと書き込みを完全に分離するWAL（Write-Ahead Logging）モードへの切り替え、メモリ関連PRAGMAフラグの最適化、そしてアプリケーションコード側での適切な接続タイムアウト設定という、3つの実践的なアプローチが必要です。

インストール

標準的なLinuxイメージや最小構成のコンテナベースには、古いバージョンのSQLiteパッケージが含まれていることがよくあります。最新のクエリ実行計画、モダンなJSON関数、信頼性の高いチェックポイント機能を利用するために、ビルド環境が適切に更新されていることを確認してください。

DebianやUbuntu LTS環境では、CLIおよび開発用ヘッダーをインストールします：

sudo apt update
sudo apt install -y sqlite3 libsqlite3-dev

Alpine Linuxコンテナの場合：

apk add --no-cache sqlite sqlite-dev

次に、SQLiteバイナリがマルチスレッドをサポートしているか確認します：

sqlite3 --version
sqlite3 :memory: "PRAGMA compile_options;" | grep -E "THREADSAFE|ENABLE_JSON1|ENABLE_RTREE"

出力にTHREADSAFE=1（シリアライズ）またはTHREADSAFE=2（マルチスレッド）が含まれていることを確認してください。どちらの設定でも、異なる接続ハンドルを使用していれば、複数のスレッドからデータ破損のリスクなく安全に並行操作を実行できます。

設定

SQLiteのチューニングには、ファイルヘッダーに直接保存されるデータベースレベルの設定と、接続を確立するたびに実行する必要がある接続レベルのPRAGMA設定の2種類が存在します。

1. Write-Ahead Logging（WALモード）の有効化

最も劇的な効果をもたらす変更は、ジャーナルモードの切り替えです：

PRAGMA journal_mode = WAL;

この設定は永続的です。SQLiteはデータベースヘッダーに直接書き込むため、それ以降に開かれた接続にも自動的に引き継がれます。メインのデータベースページを直接上書きする代わりに、WALモードでは変更内容が付随する.db-walファイルへ追記されます。書き込み処理がログへ追記を行っている間も、読み取り処理はメインファイルから変更前のページを読み出し続けます。書き込みがアクティブな読み取りをブロックすることはなく、読み取りが書き込みを妨げることもありません。

2. PRAGMAパフォーマンスチューニング

メモリおよび同期関連のPRAGMAは接続ごとにスコープが限定されます。アプリケーションが新しい接続を確立するたびに、以下のコマンドを実行してください：

-- WALモードで完全なACID耐久性を維持しながらfsyncの呼び出し頻度を削減
PRAGMA synchronous = NORMAL;

-- ページキャッシュ用に64MBのRAMを割り当て（負の値 = KiB単位）
PRAGMA cache_size = -64000;

-- 一時テーブル、インデックス、ソートバッファをRAM上に保持
PRAGMA temp_store = MEMORY;

-- データベースの最大256MB分を仮想メモリアドレス空間にマッピング
PRAGMA mmap_size = 268435456;

-- エラーをスローする前にロック解除を最大5000ミリ秒待機
PRAGMA busy_timeout = 5000;

-- INSERTおよびDELETE時に外部キー制約を強制
PRAGMA foreign_keys = ON;

高負荷環境下において、これらのフラグは次のような効果を発揮します：

  • synchronous = NORMAL: 従来のロールバックモードではNORMALに設定すると停電時に破損のリスクがありました。しかしWALモードであれば、データの完全性を担保しつつ、物理ディスクへの同期をチェックポイント時のみに限定できます。AWS c6iインスタンスでの検証では、FULLからNORMALに変更したことで書き込みスループットが約4.2倍に向上しました。
  • cache_size = -64000: デフォルトのキャッシュサイズはわずか2MB（4KBページ×2,000ページ）と極めて小さく設定されています。接続ごとに64MBを割り当てることで、インデックスやテーブルページをメモリ上に常駐させ、不要なディスク読み取りを大幅に削減します。
  • mmap_size: メモリマップドI/Oにより、Linuxカーネルはページをユーザー空間メモリに直接マッピングします。カーネル空間とアプリケーション空間の間の余分なバッファコピーを回避できるため、読み取り負荷の高い環境でCPUサイクルを大幅に節約できます。
  • busy_timeout: 一時的な書き込みスパイクが発生した場合でも、即座にエラーで失敗させるのではなく、最大5秒間バックオフしながらリトライを試みるようになります。

3. アプリケーションへの組み込み（Python接続パターン）

これらの設定は集約された接続ファクトリにまとめてカプセル化します。以下は、Pythonの標準sqlite3ライブラリを使用した、本番環境向けのコードスニペットです：

import sqlite3
import contextlib
from pathlib import Path

DB_PATH = Path("/var/lib/app/production.db")

def get_connection(db_file: Path = DB_PATH) -> sqlite3.Connection:
    conn = sqlite3.connect(
        db_file,
        timeout=5.0,
        isolation_level=None,  # オートコミットモード：トランザクションは明示的に制御
        check_same_thread=False
    )
    
    # 接続レベルのPRAGMAを適用
    conn.execute("PRAGMA journal_mode = WAL;")
    conn.execute("PRAGMA synchronous = NORMAL;")
    conn.execute("PRAGMA cache_size = -64000;")
    conn.execute("PRAGMA temp_store = MEMORY;")
    conn.execute("PRAGMA mmap_size = 268435456;")
    conn.execute("PRAGMA busy_timeout = 5000;")
    conn.execute("PRAGMA foreign_keys = ON;")
    
    return conn

@contextlib.contextmanager
def transaction(conn: sqlite3.Connection):
    """デッドロックを防止するため、即座に書き込みロックを取得する"""
    conn.execute("BEGIN IMMEDIATE;")
    try:
        yield conn
        conn.execute("COMMIT;")
    except Exception:
        conn.execute("ROLLBACK;")
        raise

ここで重要なのがBEGIN IMMEDIATEです。デフォルトのSQLiteは、最初のINSERTやUPDATEが発行されるまで書き込みロックの取得を遅延させます。2つの接続が遅延読み取りを開始し、双方が同時に書き込みへ昇格しようとするとデッドロックが発生します。書き込みトランザクションをBEGIN IMMEDIATEで開始することで、この競合状態を完全に排除できます。

検証とモニタリング

これらの変更を反映した後は、本番ファイルに対して現在の設定値を直接確認してください。

1. 実行時PRAGMAの確認

CLIからアクティブな設定値を問い合わせます：

sqlite3 /var/lib/app/production.db << 'EOF'
PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA cache_size;
PRAGMA mmap_size;
PRAGMA busy_timeout;
EOF

期待される出力値は、wal、1（NORMALに該当）、-64000、268435456、5000です。

2. WALファイルの肥大化とチェックポイントの健全性監視

稼働中のデータディレクトリを確認すると、以下の3つのファイルが存在しているはずです：

ls -lh /var/lib/app/production.db*

production.dbのほかに、production.db-walおよび共有メモリインデックスであるproduction.db-shmが確認できます。通常、SQLiteはWALが1,000ページ（約4MB）に達すると自動的にチェックポイントを実行します。しかし、閉じられていない読み取りクエリが古いトランザクションスナップショットを開いたままにしていると、ログの切り捨て（truncate）ができなくなります。その結果、WALファイルが数ギガバイトにまで肥大化し、読み取り処理が膨大なログを走査せざるを得なくなって、読み取りレイテンシが0.4msから25ms以上にまで悪化するおそれがあります。

チェックポイントの進捗状況は、次のクエリで即座に確認できます：

-- (ビジーフラグ, ログの総ページ数, チェックポイント済みページ数) を返却
PRAGMA wal_checkpoint(PASSIVE);

大量のバッチ処理などによってWALが50MBを超えた場合は、アクセスの少ない時間帯に明示的な切り捨てチェックポイントを実行してください：

sqlite3 /var/lib/app/production.db "PRAGMA wal_checkpoint(TRUNCATE);"

3. 並行処理ベンチマーク：適用前後の比較

スループットの違いを測定するため、読み取りと書き込みの混在トランザクションを発行する10本のスレッドを実行します：

import time
import threading
from pathlib import Path
from get_db import get_connection, transaction

def test_worker(worker_id: int, total_ops: int):
    conn = get_connection(Path("/tmp/benchmark.db"))
    for i in range(total_ops):
        if i % 3 == 0:
            with transaction(conn):
                conn.execute("INSERT INTO metrics (val) VALUES (?);", (f"val-{worker_id}-{i}",))
        else:
            cursor = conn.execute("SELECT COUNT(*) FROM metrics;")
            cursor.fetchone()
    conn.close()

# スキーマの初期化
init_conn = get_connection(Path("/tmp/benchmark.db"))
init_conn.execute("CREATE TABLE IF NOT EXISTS metrics (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT);")
init_conn.close()

start = time.perf_counter()
threads = [threading.Thread(target=test_worker, args=(w, 200)) for w in range(10)]
for t in threads: t.start()
for t in threads: t.join()
elapsed = time.perf_counter() - start

print(f"10スレッドにわたる2,000回の並行処理をエラーなしで {elapsed:.2f}秒 で完了しました。")

デフォルトのロールバックジャーナル設定では、このテストは80回のイテレーションも経たずにdatabase is lockedで失敗しました。しかしWALモード、synchronous = NORMAL、5,000ミリ秒のbusy_timeoutを有効にした結果、2,000回の操作すべてがエラーなく0.38秒で完了し、毎秒5,200件以上のトランザクションを安定して維持できました。

Share:
Tags: