lobste.rs、MariaDBからSQLiteへ移行

 (lobste.rs)
1 ポイント 投稿者 GN⁺ 3 시간 전 | 1件のコメント | WhatsAppで共有
  • lobste.rs は MariaDBからSQLiteへ移行した後、月曜のトラフィック急増まで安定して処理し、CPU・メモリ使用量の低下と、より速い体感レスポンスを確認した
  • 別途運用していた MariaDB VPS を停止すれば VPS コストは半減し、2019年に始まったマイグレーション課題も完了する
  • 初回デプロイでは読み取り専用トラフィックだけで全 CPU が 100% に達してロールバックしたが、大規模テーブルでの 全テーブルスキャン 2件と N+1 問題を修正した後、再デプロイに成功した
  • SQLite のユーザー定義関数で regexp・if・stddev を実装する一方、unsigned bigint 非対応や ASCII のみを処理する NOCASE など、互換性の制約を解決する必要があった
  • 本番データベースにアクセスせずに基盤 DB を変更するには、現実的な規模のテストデータ、テストスイート、デプロイ・ロールバックのチェックリスト、そして密接な協力が必要である

2019年の議論から SQLite 移行まで

  • マイグレーションの議論は 2019年の issue #539 で始まった
    • 当時は MariaDB との互換性を考慮して MySQL を代替案として提案したが、すでに PostgreSQL への移行議論が進んでいた
    • 2025年に Rahul が K1 による MariaDB 買収 に言及し、PostgreSQL マイグレーションの詳細な議論が続いた
    • 2月には Rahul が lobsters を SQLite で動かせるか と質問し、2025年6月からプロジェクトへの参加が本格化した
  • 2025年8月の 最初の PR は、作業が中断された後に GitHub 上で古い PR として閉じられ、再オープンできなかった
    • 2つ目の PR には、性能テスト、自前の DB 間マイグレーションスクリプト、データ整合性に関するデバッグが含まれていた
    • 既存の MariaDB/MySQL→SQLite 変換スクリプトの中に要件を満たすものがなかったため、別途スクリプトを作成した

初回デプロイ失敗と性能ボトルネックの修正

  • 2月21日の初回デプロイは デプロイチェックリスト を準備して実施したが、デプロイ直後、読み取り専用状態のトラフィックだけで全 CPU が 100% に達した
    • 原因を特定できず、ロールバック した
    • 本番 DB にアクセスできず、実際の性能問題を事前に再現するのは困難だった
  • 失敗の2日後、3つ目にして最終の PR を開いて検索関連の問題を修正し、実際の lobsters データの半分の規模をローカルで生成する 大規模データ生成スクリプト を追加した
    • データ生成には 1週間 かかった
    • 初回デプロイのボトルネックは、大規模テーブルで2つのクエリが引き起こした全テーブルスキャンと、別の N+1 問題だった
    • これを解消する 変更 1、変更 2、変更 3 を反映した
    • 追加の性能問題を追跡できるよう、デプロイ当日の朝に スロークエリログ を追加した

再デプロイの結果と運用コスト削減

  • 7月11日の2回目のデプロイでは、デプロイ・ロールバックのチェックリストを再度用意し、移行後もサイトは正常に稼働し、CPU とメモリ使用量も安定していた
    • サイトの指標と IRC を監視しつつ、報告された問題を 修正 1、修正 2 で即座に対処した
    • 月曜のトラフィック急増でも問題はなく、CPU・メモリ使用量の低下と、より速い体感レスポンスを確認した
    • MariaDB VPS を停止すれば VPS コストは半減し、マイグレーション issue もクローズされた

MariaDB と異なる SQLite の制約

  • SQLite gem の ユーザー定義関数 (UDF) サポートを利用し、SQLite に存在しない regexp, if, stddev を実装した
    • SQL マイグレーション向けの回避処理を過剰に追加せずに済んだ
  • MariaDB で一部の ID に使っていた unsigned bigint を SQLite がサポートしていないため、bigint に変更した
  • 照合順序も MariaDB より制限がある
    • MariaDB では utf8mb4_general_ci を使っていたが、SQLite では NOCASE を適用した
    • NOCASE は UTF 全体の大文字小文字折りたたみをサポートせず、ASCII 文字のみ処理する
  • SQLite の全文検索テーブルには、デフォルトではない推奨方式の Contentless-Delete Tables が適していた
  • Rails の デフォルト PRAGMA 設定 は lobsters で問題なく動作した
    • データベースマイグレーションは DB ごとに異なるため、既存のマイグレーションを old_migrations ディレクトリへ移し、db:migrate が引き続き動作するようにした

テストと協力から得た教訓

  • lobsters のコードベースでは、検索パーサー や、レンダリング速度を高めるハックである heinous_inline_partials を確認した
    • テストスイート により、大規模な手動テストを行わずに SQLite 移行の正確性を検証できた
  • 参加者どうしの円滑なコミュニケーションがマイグレーション成功を左右した
    • 本番 DB にアクセスできない状態で基盤データベースを置き換えるのは非常に難しかった
    • 同じ作業をやり直すなら、事前に 現実的なデータ規模 を用意すべきだった
  • テスト中に全テーブルスキャンが発生したら失敗するよう設定できていれば、初回デプロイの性能問題を事前に発見できたはずだ
  • 本番に近いデータセットを自分で書いたり、1週間も待ったりせずに容易に作成できるツールが必要である

関連記事

1件のコメント

 
GN⁺ 3 시간 전
Lobste.rsの意見

  • @thomas0 が大規模な移行を丁寧に担当し、@355E3B は計画と運用を支援し、#539 の関係者や #lobsters のおかげで読み取り専用の時間も楽しく乗り切れた
    もともと #539 では PostgreSQL を予定していたが、実際の作業支援者が SQLite を使っており、想定需要より大きく複雑な解決策を避けたかったため SQLite を選択した。PostgreSQL は普段の第一選択だが、別サービスを運用・調整・保守しなければならない負担がある
    驚いたことに移行後は CPU と RAM の使用量がともに減少しており、詳しいグラフは #539 の最後の部分 にある

  • SQLite のデフォルト設定を見るたびに驚かされる。WAL、外部キー、synchronous=NORMAL などをデフォルトで有効にする新規プロジェクト向けモードがあるとよく、そのほかにも改善すべきデフォルト値がありそうだ

    • ディスク変更の予約や形式が HDD には有利だが NVMe にはかなり不向きだった部分もあったようにうっすら記憶している

  • SQLite データベースの バックアップ戦略 が気になる。litestream のようなツールを使っているのか、別の方法なのか知りたい

  • lobste.rs では 更新処理 をどう扱っているのか気になる

    • SQLite の update 文を使っている。別の種類の更新を指していたなら、具体的に教えてほしい

  • SQLite にはすでに iif 関数 があるが、同じ領域で ユーザー定義関数 を運用していること自体も興味深い

  • SQLite は PostgreSQL や MySQL と同程度の 同時実行性 をサポートしていないと思っていたが、概要ドキュメント を見ると、いまは複数プロセスから使えるものの、lobste.rs は単一サーバーで動かす必要があると理解すればよいのか気になる
    プロセス同士は共有メモリや mmap のような仕組みで SQLite 内部で同期され、同時書き込みは 1 つだけ可能で読み取りは複数許可されるのか、待機中の書き込み処理はキューで順番を待つのかも知りたい

    • SQLite FAQ が疑問の一部に答えてくれるかもしれない。複数プロセスにまたがって 複数の読み取り処理 を行うことはできるが、書き込みは一度に 1 つだけで、SQLite はファイルシステムロックでこれを保証する
      書き込み待ちは明示的な共有メモリキューではなく、少し待ってから再試行する方式が使われているようだ。複数マシンで SQLite を使うのが完全に不可能かは断言できないが、NFS でファイルを共有する単純な方法は明示的に推奨されていないため、単一サーバー と理解するのが妥当な近似であり、今どきは 1 台でもかなりの規模を処理できる
    • 書き込み処理はある程度待たせられるが、デフォルト設定はよくないので調整が必要。現在使っている設定は次のとおりで、busy_timeout を機能させるために即時トランザクションモードも有効にする必要がある 
      "PRAGMA foreign_keys=ON;"  
"PRAGMA journal_mode = WAL;"  
"PRAGMA synchronous = NORMAL;"  
"PRAGMA busy_timeout = 5000;"  
"PRAGMA temp_store = MEMORY;"  
"PRAGMA mmap_size = 134217728;"  
"PRAGMA journal_size_limit = 67108864;"  
"PRAGMA cache_size = 2000;"
      参考資料は https://fractaledmind.github.io/2024/04/… と https://kerkour.com/sqlite-for-servers および https://www.sqlite.org/lang_transaction.html#immediate で、2 つ目のリンクの SQLITE_BUSY の部分と 3 つ目のリンクで詳しい内容を確認できる

  • PR、Issue、Gist に詳細が非常によく整理された印象的なアップグレードだ。この変更で サイト検索のような機能差 が生じたのか、現在の SQLite ファイルのディスクサイズはいくつなのか気になる

    • 検索結果では SQLite の ランキング機能 を使っているため、MariaDB の結果順とは異なる可能性がある。検索テスト はあるがランキング検証は少ないので、機能的な変化がある可能性はあるものの、まだ根拠はない
      SQLite ファイルのサイズはどこかで 3.8GB と言っていたように記憶している