關於維運 SQLite 學到的幾件事
原文由 Julia Evans 于 發布，訂閱此部落格
哈囉！最近我在做一個 Django 網站，決定用 SQLite 當資料庫。剛開始要用 SQLite 架網站時，我看了一堆部落格文章，都說小網站用 SQLite 上正式環境完全沒問題，我也覺得確實沒問題，但我當時沒有真正意識到的是，SQLite 終究還是個資料庫，資料庫是很複雜的東西，而我對維運資料庫其實懂得不多。
所以，這裡想分享幾個我在維運 SQLite 時學到的小心得。這已經是我第四個使用 SQLite 的網站了，我覺得這次比較有挑戰性，因為有了 Django ORM 的加持，我讓資料庫做了比以前沒用 Django 時更多的工作。
一開始我就像所有部落格文章說的那樣，開啟了 WAL 模式，然後就聽天由命了。
ANALYZE 原來很重要
今天我在一個有 4000 筆資料的資料表上跑了一個查詢（用SQLite 的 FTS5做全文檢索），結果花了 5 秒。這讓我覺得不太對勁：電腦應該很快才對啊！
結果發現，我需要的只是執行ANALYZE！那個有問題的查詢馬上從 5 秒變成大概 0.05 秒（或是其他小到我懶得再去細究的數字）。我到現在還是不太清楚查詢計畫到底哪裡出了問題，但我猜大概是某種意外變成平方複雜度的狀況。
ANALYZE 會產生「統計資料」（我猜是關於每個資料表有多少筆資料？還有其他東西吧？），讓查詢規劃器可以做出更好的選擇。
也許哪天我會學會怎麼看查詢計畫。
清理資料庫很棘手
偶爾我會遇到不小心在資料庫裡塞了一堆不想要的資料（例如來自django-tasks-db已完成的任務），然後想要清掉它們的情況。
在這種情況下，我遇過好幾次的狀況是：
- 我執行某種指令來清理這些資料
- 因為資料很多，指令執行超過 5 秒（老實說我還是不太懂為什麼這些 DELETE 語句會這麼慢，也許是有很多 Python 程式碼在交易內執行，我不確定）
- 這時另一個 worker 試圖寫入資料庫，結果 5 秒後逾時（我設定的逾時時間是 5 秒）
- 該 worker 因為無法寫入資料庫而當掉，VM 也跟著關閉
到目前為止，我的做法就是把這些清理操作分小批次執行，這樣就不需要執行超過 5 秒的資料庫查詢。不過，整個經驗也讓我更能理解，為什麼有人會想用像 Postgres 這種「真正的」資料庫，它可以同時讓多個寫入者並行運作。
也許以後需要做這種事時，我就直接把網站下線做排程維護，不過我還沒想到要怎麼安排流程。
關於 ORM 查詢效能目前還沒什麼心得
到目前為止，我都用 Django 的 ORM 隨心所欲地下各種查詢，完全沒在管查詢效能，除了 ANALYZE 那件事之外，大致上都還算順利。資料庫還很小（大概 10000 筆資料？），而且我預期它會一直維持很小，所以希望這個做法能繼續管用。
備份 sqlite
我試過幾種備份 SQLite 的方式。我想我其實還沒真的測試過從備份還原，不過我通常會用 dead man's switch 來監控備份有沒有正常執行。
方法一：restic
sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite
# Upload backup to S3
# Sometimes the backup gets OOM killed and so it stays locked, do an unlock
restic -r s3://s3.amazonaws.com/some_bucket/ unlock
# Do the backup & prune old backups
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
我最近開始嘗試 Litestream，因為我覺得做增量備份可能會更有效率：我的 restic 備份有時會因為 OOM 被砍掉，讓我有點厭煩。基本上我只要寫一個設定檔，然後執行：
litestream replicate -config litestream.yml
我在設定檔裡設了 retention: 400h，想保留一定時間的資料庫歷史紀錄，但我也不確定到底有沒有用。
我都是備份到 AWS，但這總是很麻煩，因為要在 AWS 控制台裡找來找去產生憑證真的很煩。也許哪天我會換成其他相容 S3 的替代方案。
你可以使用多個資料庫
我目前的專案只有一個資料庫，不過我在Mess with DNS用的其中一個技巧，是把資料表拆到三個不同的資料庫檔案裡，因為我的資料表其實不需要放在同一個 db 裡。我覺得這還滿有幫助的。
Mess with DNS 用 SQLite 已經跑了 4 年了（從 2022 年開始），一直都很順利，我覺得從 Postgres 搬過來對那個專案來說是個很棒的決定。
就這樣！
每次看到自己要花多久才學會正在用的技術的一些基本東西，總覺得有點有趣。我想我第一次在網頁專案裡用 SQLite 是在 2022 年，結果到今天才知道有 ANALYZE 這個東西！我想再過一兩年，我大概又會學到其他非常基本的東西。
一些參考資料
除了官方文件之外，我參考過的一些部落格文章：
留言
登入後參與討論