SQLiteを運用して新たに学んだいくつかのこと

 (jvns.ca)
1 ポイント 投稿者 GN⁺ 9 시간 전 | 1件のコメント | WhatsAppで共有
  • 小規模なWebサイトの本番環境でもSQLiteは使えるが、Django ORMに任せる処理が増えるにつれ、データベースの運用上の複雑さを実感
  • 4,000行のテーブルでのFTS5検索に5秒かかっていたが、ANALYZE 実行後は約0.05秒に短縮され、クエリプランナの統計情報が性能に大きく影響し得ることを確認
  • 大量のDELETEが5秒を超えると、ほかの書き込み処理もタイムアウトし、ワーカーやVMまで終了する可能性があるため、クリーンアップ処理を小さなバッチに分けて処理
  • バックアップにはVACUUM INTOとresticを使ったがOOM終了を経験し、より効率的な増分バックアップのためにLitestreamも試験中
  • テーブルを複数のSQLiteファイルに分けることもでき、Mess with DNSは2022年から4年間SQLiteで運用されており、プロジェクトに合った選択だったと評価

DjangoサイトでSQLiteを運用する

  • 小規模な本番サイトでもSQLiteを使えるという複数の記事を参考に、Djangoサイトのデータベースとして採用
  • SQLiteも運用知識が必要な複雑なデータベースであり、このプロジェクトではDjango ORMを通じて以前より多くの処理を任せている
  • 複数の推奨事項に従い、最初からWALモードを有効にして運用
  • このサイトはSQLiteを使った4つ目のWebサイト

ANALYZEが変えた検索性能

  • 4,000行のテーブルでSQLite FTS5を使った全文検索クエリに5秒かかった
  • ANALYZEを実行すると、同じクエリが約0.05秒に短縮され、それ以上調査する必要がないほどになった
  • 具体的にどのようなクエリ計画の問題があったのかは確認できておらず、意図しない二次時間計算量に似た状況だったのではないかと推測
  • ANALYZEはテーブルの行数などを含む統計情報を生成し、クエリプランナがよりよい選択をする助けになる
  • まだクエリ計画の読み方は習得できていない状態

データ整理と単一書き込みの制約

  • django-tasks-dbの完了済みタスクのように、不要な行を大量に削除すると連鎖的な問題が発生
    • 多数の行を処理するクリーンアップコマンドが5秒以上実行される
    • その間、ほかのワーカーのデータベース書き込みが設定された5秒制限を超えてタイムアウトする
    • 書き込みに失敗したワーカーがクラッシュし、VMも終了する
  • DELETEが遅い正確な理由は確認できておらず、トランザクション内で多くのPythonコードが実行されているためである可能性も考慮している
  • 現在は各データベースクエリが5秒を超えないように、クリーンアップ処理を小さなバッチに分割している
  • この経験を通じて、複数の書き込み処理を同時に扱えるPostgresのようなデータベースが必要になる理由を実感
  • 今後はこうした作業中にサイトを停止して予定メンテナンスを行う案も検討しているが、まだワークフローは用意できていない

ORMクエリと現在のデータ規模

  • これまでDjango ORMで望むクエリを作りながら性能を別途確認してこなかったが、ANALYZEの問題以外はほとんど正常に動作
  • データベースは約10,000行と小さく、今後も小規模のまま維持される見込み

SQLiteのバックアップ方式

  • SQLiteのバックアップにはresticとLitestreamの2つの方式を試した
  • バックアップ処理は通常デッドマンスイッチで監視しているが、実際のリストアテストはまだしていないようだ

  • resticを使ったバックアップ

    • VACUUM INTOでデータベースのコピーを作成し、gzipで圧縮してからS3にアップロード
    • resticでバックアップ作成、スナップショット確認、古いバックアップの削除と整理を実施
    • バックアップが時々OOMで終了し、ロックが残るためrestic unlockも実行
    sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite
restic -r s3://s3.amazonaws.com/some_bucket/ unlock
restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
restic -r s3://s3.amazonaws.com/some_bucket/ prune

  • Litestreamを使った増分バックアップ

    • resticバックアップのOOM終了を避け、増分バックアップの効率を試すためにLitestreamを使い始めた
    • 設定ファイルを作成したあと、次のコマンドでレプリケーションを実行
    litestream replicate -config litestream.yml
    • データベース履歴を一定期間保持するためにretention: 400hを指定したが、意図どおり動作しているかはまだ確認できていない
    • AWSにバックアップしているが、コンソールで認証情報を作成するのが面倒なため、今後ほかのS3互換サービスへ移行する案も検討

複数のSQLiteデータベースファイルを使う

  • 現在のプロジェクトではデータベースを1つだけ使っているが、Mess with DNSでは一緒に置く必要のないテーブルを3つのデータベースファイルに分割
  • この分割は運用に役立ったと判断
  • Mess with DNSは2022年から4年間SQLiteで運用されており、このプロジェクトではPostgresからSQLiteへ移行したことがよい選択だった

運用してから後になって見つけた基本機能

  • WebプロジェクトでSQLiteを初めて使ったのは2022年だが、ANALYZEの存在は今回初めて知った
  • 使っている技術の基本機能も、実際の運用を通じて何年もかけて学び続けることになる

関連記事

1件のコメント

 
GN⁺ 9 시간 전
Hacker News のコメント

  • SQLite の .expert モードを使えば、クエリプランの読み方を学ぶ日を少し先延ばしにできる: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
    SELECT * FROM x1 WHERE a=? AND b>?; を分析すると、CREATE INDEX x1_idx_000123a7 ON x1(a, b); のように推奨インデックスを示してくれ、それを作成したあとに再度分析すると、新しいインデックスは不要だと表示される
    Postgres のような「本物の」データベースでも、クリーンアップ作業は通常 小さなバッチに分けることが推奨される。単に小規模では非効率な処理であることが見えにくいだけなので、原文のアプローチは思った以上に正しかった

    • 一部のデータベースで 1,000万行を削除すると、その分の取り消しデータも記録される。Oracle ではそれを適時バックアップして空にできないと、アーカイブログ用のディスク容量が枯渇することがある
      頻繁にコミットすると助けにはなるが、大規模データベースを定期的に整理する場合はパーティショニングが最も効果的だった。最も古いパーティションを削除すれば、ほぼ即座に終わる
      ただし「ワーカーがデータベースに書き込めずにクラッシュし、VM が終了する」という点で、なぜ VM が終了するのかは不明。ここでの VM は、OS が動作する仮想マシンを指しているように見える
    • 行ベースレプリケーションを使う大規模な MySQL では、数百万行を変更する UPDATE や DELETE はバッチで実行する必要があった。1つのクエリで処理すると、更新された数百万行をすべてのレプリカへ一度に送信しなければならないため
    • EXPLAIN QUERY PLAN と似ているように見える: https://sqlite.org/eqp.html
      通常の EXPLAIN はたいてい必要以上に冗長なバイトコードを出力するが、EXPLAIN QUERY PLAN は要約されたプランを表示する
    • 「非効率な処理であることを見えにくくする」点を利点と見ているのか気になる

  • データベースを扱う立場からすると読んでいてもどかしく、自分で原因を突き止めて直したくなった。行数が 1万件だけのテーブルならフルテーブルスキャンでも非常に速いはずで、同じ物理サーバー上でプロセス内実行される SQLite ならなおさらそうだ
    頭に浮かんだ解決策は当然「インデックスを作れ」だった。遅い削除は、ORM ユーザーが内部のデータベース相互作用を理解する前によく遭遇する古典的な N+1 問題である可能性が高そうで、Julia が続編を書いてくれることを願う

  • AWS コンソールで認証情報を作るのがあまりに面倒だったので、数年前にその問題だけを解決するツールを作った
    uvx s3-credentials create my-existing-s3-bucket
    このコマンドは、そのバケットだけにスコープを限定した読み書き用の認証情報を出力する。--read-only、--write-only で権限をさらに制限したり、--prefix foo/bar で特定のプレフィックスで始まるキーだけを読み書きできるようにしたりできる
    Restic と Cloudflare R2 も一緒に使ってみたが、とてもよく動作した

    • 複雑な AWS サービスを扱うより一般的な方法は、Terraform を少しだけ学んで、残りを LLM に任せること。作成したリソースを覚えておく必要がなく、あとで撤去するのもずっと簡単になる
    • 似た既存ツールとして https://litestream.io/ もある
    • とても優れたツールなので、AWS CLI に取り込まれてもよさそうに見える。ただし 書き込み専用の認証情報 がいつ必要になるのかは気になる

  • LLM の時代になって、Julia の文章をますます高く評価するようになった。率直な探究の過程は、自信満々に何でも知っているふりをする自動生成記事への解毒剤だ

  • バックアップを次のように実行している:
    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"
    書き込みワーカーが WAL を使っていればそれを妨げず、圧縮率が良く同期もしやすいダンプが作られる。Home Assistant のデータベースは 1.8GB だが、圧縮されたダンプは 286MB で、毎日生成される内容の約 90% は同一だと推定している

    • Home Assistant で何をバックアップしているのか気になる。標準のバックアップは大きすぎるので、設定だけを残して動画、キャッシュ、HACS がダウンロードしたリポジトリは除外したが、重要なものを見落としているのか分からない
      データベースがそこまで大きくなった理由が、長期間保持した 時系列履歴 のためなのかも気になる
    • VACUUM INTO、.backup、sqlite3_rsync、Litestream も書き込みワーカーを妨げない。.backup はバックアップ API を使う
    • ライブデータベースで .dump が原因で一度ロックされたあと、.backup に切り替えた。それでも .part に書いてから mv する方法はすっきりしている

  • 「さらに調べていない」「最善の推測だ」「たぶん他にも？」「トランザクション内で Python コードが大量に実行されているのかもしれない」といった表現ばかりで、記事に実質的な内容がないように見える。きちんと把握したり調査したりしないまま、ときには誤った推測までしている
    Debian ユーザーとして、Linux 関連の検索結果に Ubuntu フォーラムが出てきても開かない理由も、誤った推測があまりに多いからだ。逆に Arch は Debian と大きく異なるが、知識のある人たちが書いた Arch Wiki はたいてい確認している

    • Julia Evans は技術知識が非常に豊富で、技術の神秘性を取り払い、実際の問題解決プロセスがどのようなものかを理解させることに特に優れている
      この記事は SQLite の世界最高の専門家による解説を装っているわけではなく、タイトルからして「SQLite の運用についていくつか学ぶ」と期待値を明確に設定している
      すべてを知っている必要も、知っているふりをする必要もなく、シンプルな方法で問題を把握しながら知識を積み上げられる、というメッセージが Julia の記事全体に流れている。これまでに分かったことをできるだけ明確に共有するのは良い姿勢だ
    • この記事は、その技術を使う賢い一般ユーザーの状況をよく示していて素晴らしいと思う。筆者の焦点はウェブサイトの運用であり、こうした問題は特定のツールだけを一日中扱っているわけではない日常的なユーザーがよくはまる部分だ
      私も昨日、ある機能を作りながら、プログラミング言語 2 つ、ビルドシステム 2 つ、クラウドプロバイダー、シークレット管理ツール、2 つの言語にまたがる複雑なクライアント・サーバー通信フレームワーク、バージョン管理システム、エディタ、CI ツールを使った。さらにオペレーティングシステムとランタイムのバージョン、データベース、リバースプロキシ、キャッシュ、ドメインロジックまで掘り下げることもできる
      出会うすべてのテーマを深掘りしていたら何も終わらないので、筆者のように集中する問題を選ぶ必要がある
    • Arch Wiki は最高の Linux 資料の一つだ。Mint を使っていた頃はいつも参照していたが、実際に Arch を使うようになってからは、Mint 時代ほどは参照しなくなった

  • DELETE の問題は、バッチ削除、バッチ間の遅延、SELECT であらかじめ rowid を取得しておく方法で簡単に緩和できる。SELECT はブロックを引き起こさない
    データが同じテーブルに順次追加されていたなら、ファイルにも似た順序で保存されている可能性が高いので、その順序または逆順で削除すると速くなることがある。ストレージ媒体や他の条件によって結果は変わる

    • 行 ID の事前ロードは SQLite だけでなく、大規模な Aurora MySQL や Postgres クラスターでも非常に効果的だった。SELECT をレプリカに送ることができ、行フィルタリングによるインデックスメモリへの圧迫がデータベースの CPU とバッファキャッシュに大きな負担をかけていたためだ
      パーティションプルーニングのような方法で不要なデータを主要な処理経路から外すのが難しい場合、非常に強力な戦略だ

  • 現在慣れている水準や業務上の要求よりもデータベースを少し深く掘ることは、今でもスキルを高める良い方法だ。多くのウェブ開発者はデータベースツールの前で行き詰まりがちで、私も K8s のような一部の運用技術では同じように行き詰まる
    SQL がどのようにディスク上のデータの読み書きに変換されるのかを知っておくと、どのアプローチがよさそうかを直感的に判断するうえで大いに役立つ。データベースのロック体系も併せて理解しておく必要がある
    こうした知識があれば、Postgres で単純な COUNT すらすぐに終わらないときに慌てる可能性が下がる

  • sqlite_stat1 と sqlite_stat4 には、インデックス値の分布に関する複数の統計情報が保存され、クエリプランナーはそれをもとにインデックスの選択度と有用性を推定する
    sqlite_stat1 はインデックスのレコード数と値あたりの平均レコード数だけを提供し、有効化すると sqlite_stat4 はヒストグラムデータも保存する

  • Litestream は非常に興味深く、S3 をバックエンドとして接続して実行することに成功した。SQLite を使う多数のアプリを、ファイルシステムの状態にほとんど依存しないようにできる
    S3 の状態ははるかに管理しやすく、バックアップと同期もプロバイダーが処理してくれる