HN 日本語サマリー

← 一覧へ戻る
その他

Lobste.rs が SQLite で稼働するように

Lobste.rs is now running on SQLite (lobste.rs)

144 pointsby abetusk110 コメント

要約

Lobste.rs は、そのインフラストラクチャを SQLite データベースで実行するように移行したことを示唆するタイトルです。具体的な技術的変更点やその影響については、本文が提供されていないため詳細不明です。

全文翻訳

先週の土曜日、@pushcx と私はSQLite プルリクエストを本番環境にデプロイしました。月曜日のトラフィック増加に対する反応を見るために今朝まで待ってから、この投稿をすることにしました。言うまでもなく、SQLite は見事に合格したようです。CPU 使用率は低下し、メモリ使用量も減少し、サイトは少なくとも私にとってはより高速になったようです。MariaDB VPS が停止されれば、VPS コストは半分になり、そしてついに #539 SQLite への移行が今朝クローズされました。

移行についてご質問があればお知らせください。

背景ストーリー:

私がこの移行に関わるようになったのは、2019 年に #539 を見つけたことがきっかけです。 largish データベースの管理や移行に多くの経験があったため、MariaDB と MySQL の互換性から MySQL を代替案として提案するコメントを残しました。当時は、PostgreSQL への移行に関する議論がすでに進んでいたため、関与するつもりはありませんでした。

2025 年に早送りすると、Rahul が K1 による MariaDB の買収に言及するコメントをしました。PostgreSQL への移行の詳細に関する議論が進みました。その後 2 月に、Rahul が「lobsters は sqlite で動作しますか?」と尋ね、非常に詳細な SQLite に関する投稿が含まれていました。

私がこのプロジェクトを引き受けることに正式に関心を示したのは 2025 年 6 月です。これがlobsters のオフィスアワーで言及されたのかもしれませんが、それから時間が経ちすぎているため、確実ではありません。

2025 年 8 月に、最初のプルリクエストの試みを開きましたが、忙しくなって PR に対応できなくなりました。GitHub はそれを期限切れとして閉じ、再オープンできなかったため、別の PR を開きました。2 回目の PR の試みには、パフォーマンス テスト、データベース x からデータベース y へのスクリプト(既存の mariadb/mysql から sqlite へのスクリプトが満足できなかったため)、デバッグ、データ整合性に関する検討が含まれていました。

そして 2 月 21 日に最初のデプロイが行われました。@pushcx と私は電話で、デプロイのチェックリストを作成しました。PR のデプロイまで、すべてが順調に進みました。デプロイ後、サイトは読み取り専用モードになりましたが、読み取り専用トラフィックだけですべての CPU が 100% にスパイクしました。問題の原因を特定できなかったため、ロールバックすることにしました。本番データベースへのアクセスがなかったためパフォーマンスが問題になる可能性があることを知っていたので、この最初のデプロイ失敗後、気分は良くありませんでした。

デプロイ失敗の 2 日後、3 回目で最後の PR の試みを開きました。最初のデプロイ中に発見された検索に関する軽微な問題を修正し、バルクデータ作成スクリプトを作成しました(これはローカルで lobsters のデータセットサイズの半分を作成するのに 1 週間かかりました)。そして、最初のデプロイ中のパフォーマンス問題を解決した 3 つの変更をコミットしました:1、2、3。パフォーマンス問題は、SQLite が最大のテーブルに対して 2 つのクエリでフルテーブルスキャンを実行していたこと、そして 3 つ目の変更で n+1 問題を解決したことに起因していました。2 回目のデプロイの朝、さらにパフォーマンス問題が発生した場合に備えて、スロークエリログも追加しました。

そして 7 月 11 日に 2 回目のデプロイが行われました。@pushcx と私は朝の電話で、デプロイとロールバックのチェックリストを作成しました。すべてが順調に進み、@pushcx が PR をマージしてデプロイしました。デプロイ後もサイトは稼働しており、CPU/メモリ使用率も良好でした。これは私にとって大きな安堵でした。サイトのメトリクスと、問題について言及した IRC のユーザーを監視し、いくつかあった問題は迅速に修正されました:1、2。全体としてサイトは機能しているように見えたため、電話を終え、トラフィックがスパイクする月曜日まで待ちました。

月曜日になり、サイトはまだ良好な状態なので、これを勝利とみなし、先に進みます。

SQLite の教訓:

  1. SQLite gem はユーザー定義関数 (udf) をサポートしており、regexp、if、stddev のような SQLite で不足している関数を実装するために使用しました。これにより、多くの SQL 移行ワークアラウンドに対処する必要がなくなりました。
  2. SQLite は符号なし bigint をサポートしていません。以前は MariaDB で特定の ID に符号なし bigint を使用していましたが、移行のためにそれらを bigint に切り替える必要がありました。
  3. SQLite の照合順序は MariaDB と比較してかなり弱いです。Lobste.rs は MariaDB で utf8mb4_general_ci を使用していましたが、SQLite ではNOCASEを使用しました。NOCASE の欠点は、ASCII 文字のみをサポートし、完全な UTF ケース折り返しをサポートしないことです。
  4. 全文検索テーブルには、推奨されるSQLite の Contentless-Delete Tablesを使用してください。これらはデフォルトではありません。SQLite のデフォルトの選択肢には常に驚かされます。

Rails の教訓:

  1. Rails のデフォルト PRAGMAsは lobsters にとって機能しているようです。
  2. 通常は気になりませんが、データベースの移行はデータベース固有です。db:migrate が引き続き機能するように、古い移行をold migrations ディレクトリに移動する必要がありました。

Lobsters コードベースの教訓:

  1. Lobsters コードベースには検索パーサーがあります。
  2. レンダリングを高速化するためのハックであるheinous_inline_partialsについて学びました。
  3. Lobsters のテストスイートは、大量の手動テストなしで SQLite に移行できることを確認する上で不可欠でした。

全体的な教訓:

  1. この作業を成功させた重要な要素は、参加者全員からの良好なコミュニケーションだったと思います。そうでなければ、これは不可能だったでしょう。
  2. 本番データベースへのアクセスなしで基盤となるデータベースを移行するのは、正しく行うのが非常に困難です。これは、本番データベースへのアクセスなしで行った最初の基盤データベース移行でした。この経験から得た教訓は、将来基盤データベースを移行する際には、現実的なデータセットサイズを確保することです。

願い:

  1. テストで「フルテーブルスキャンが発生した場合は失敗する」と言えるようになればと思います。これにより、最初のデプロイ中に経験したパフォーマンス問題が検出されたでしょう。
  2. 本番環境のようなデータセットを作成することが、手動で何かを書き、1 週間待つよりもはるかに簡単になることを願っています。