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からの移行は素晴らしい選択だったと思います。 以上です！ 私が使用しているテクノロジーに関する基本的なことを学ぶのにどれくらいの時間がかかるかを見るのは、いつも楽しいです。2022年に初めてウェブプロジェクトでSQLiteを使ったと思いますが、ANALYZEの存在を知ったのは今日でした！1、2年もすれば、また別の非常に基本的な機能について学ぶことになるでしょう。 いくつかの参考資料 公式ドキュメント以外で参照したブログ記事： - The definitive guide to using Django with SQLite in production - gist on sqlite performance tuning