SQLiteの運用について学んだこと
Learning a few things about running SQLite (jvns.ca)
要約
この記事は、DjangoサイトでSQLiteを本番環境で使用する際に筆者が直面した課題と学びについて述べています。特に、クエリパフォーマンス向上のためのANALYZEコマンドの重要性、大量データ削除時のロックやタイムアウト問題、そしてバックアップ方法(resticやlitestream)について解説しています。SQLiteは小規模サイトには適していますが、データベースとしての複雑さを理解することの重要性を強調しています。
全文翻訳
SQLiteの運用について学んだこと
こんにちは!最近Djangoサイトを開発していて、データベースとしてSQLiteを使うことにしました。ウェブサイトのデータベースとしてSQLiteを使い始めるとき、小規模サイトなら本番環境でSQLiteを使うのは全く問題ないというブログ記事をたくさん読みました。そして、それは確かに全く問題ないと思いますが、SQLiteもやはりデータベースであり、データベースは複雑で、私はデータベースの運用についてあまり知らないということを十分に理解していませんでした。
そこで、SQLiteを運用する上で私が学んだいくつかの小さなことを紹介します。これは私がSQLiteを4つ目のウェブサイトで使った経験ですが、Django ORMの力で、以前よりも多くの処理をデータベースにさせているため、今回のサイトはより難しくなっていると思います。
まず、すべてのブログ記事で推奨されているようにWALモードを有効にして、最善を願いました。
ANALYZEは明らかに重要
今日、4000行のテーブルに対してクエリ(フルテキスト検索のためにSQLiteのFTS5を使用)を実行したところ、5秒かかりました。これは間違っていると思いました。コンピューターは速いのですから!
解決策はANALYZEを実行することだと判明しました!問題のクエリは、実行時間が5秒から約0.05秒(あるいはそれ以下で、それ以上調査する必要がないほど)に短縮されました。クエリプランで何が間違っていたのか正確にはまだわかりませんが、私の推測では、何らかの意図せず二次関数的になるようなものでした。
ANALYZEは「統計情報」(各テーブルの行数に関するものだと思いますか?おそらく他の情報も?)を生成するため、クエリプランナーはより良い選択をすることができます。
いつかクエリプランの読み方を学ぶかもしれません。
データベースのクリーンアップは難しい
時々、データベースに不要な行がたくさん入ってしまう状況に遭遇しました(例えば、django-tasks-dbの完了したタスクなど)。それらをクリーンアップしたいのです。
この場合、数回起こったことは次のとおりです。
- 行をクリーンアップするコマンドを実行する
- コマンドの実行に5秒以上かかる(行がたくさんあるため)(ただし、これらのDELETE文がなぜこれほど遅いのかについては、まだ疑問があります。トランザクション内で多くのPythonコードが実行されているのかもしれません、確信はありません)
- 他のワーカーがこの処理中にデータベースへの書き込みを試み、5秒後にタイムアウトする(5秒のタイムアウトを設定しています)
- データベースへの書き込みができなかったためワーカーがクラッシュし、VMがシャットダウンする
これまでの私のアプローチは、5秒以上かかるデータベースクエリを実行する必要がないように、これらのクリーンアップ操作を小さなバッチで実行することでした。この経験全体を通して、なぜPostgresのような「本格的な」データベースを使いたくなるのか、同時に複数の書き込みができるという点で、より理解が深まりました。
将来、このようなことを行う必要がある場合は、計画メンテナンスのためにサイトを一時停止するかもしれませんが、まだそのためのワークフローは確立していません。
ORMクエリのパフォーマンスに関するノートはまだない
これまでのところ、クエリパフォーマンスに全く注意を払わずに必要なクエリをすべて実行するためにDjangoのORMを使用していますが、ANALYZEの件を除けば、ほとんどうまくいっています。データベースはかなり小さく(約10000行?)、今後もずっと小さいままだと予想されるため、その計画が今後も有効であり続けることを願っています。
SQLiteのバックアップ
SQLiteのバックアップはいくつかの方法で行ってきました。バックアップからの復元を実際にテストしたことはありませんが、デッドマンズスイッチで監視するようにしています。
方法1: restic
sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite
# バックアップをS3にアップロード
# 時々バックアップがOOMキルされてロックされたままになるので、アンロックする
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
方法2: Litestream
最近Litestreamを試してみることにしました。増分バックアップの方が効率的かもしれないと感じたからです。resticのバックアップが時々OOMキルされてしまい、それにうんざりしていました。基本的に設定ファイルを作成して実行するだけです。
litestream replicate -config litestream.yml
設定ファイルにretention: 400hと設定して、ある程度の履歴を保持しようとしましたが、それが機能するかどうかは全くわかりません。
AWSにバックアップしていますが、AWSコンソールを操作して認証情報を作成するのが面倒なので、常に手間がかかります。いつか他のS3互換の代替サービスに移行するかもしれません。
複数のデータベースを使用できる
私の現在のプロジェクトはデータベースが1つだけですが、Mess with DNSで使ったトリックの1つは、テーブルを3つの別々のデータベースファイルに分割することでした。実際にはテーブルを同じDBに入れる必要がなかったからです。それが役立ったと思います。
Mess with DNSは4年間(2022年から)SQLiteで稼働しており、素晴らしいです。Postgresから移行したことは、そのプロジェクトにとって素晴らしい選択だったと思います。
以上です!
自分が使っているテクノロジーに関する基本的なことを学ぶのにどれくらいの時間がかかるかを見るのは、いつも楽しいです。初めてウェブプロジェクトでSQLiteを使ったのは2022年で、ANALYZEの存在を知ったのは今日です!1、2年もすれば、また別の非常に基本的な機能について学ぶことになるでしょう。
参考資料
公式ドキュメント以外で参照したブログ記事の一部です。