Vài điều mới học được khi vận hành SQLite

 (jvns.ca)
1 điểm bởi GN⁺ 9 giờ trước | 1 bình luận | Chia sẻ qua WhatsApp
  • Có thể dùng SQLite ngay cả trong môi trường production của website quy mô nhỏ, nhưng khi Django ORM đảm nhận ngày càng nhiều việc, tác giả cảm nhận rõ độ phức tạp vận hành của cơ sở dữ liệu
  • Tìm kiếm FTS5 trên bảng 4.000 dòng từng mất 5 giây, nhưng sau khi chạy ANALYZE giảm xuống khoảng 0,05 giây, cho thấy thống kê của query planner có thể ảnh hưởng lớn đến hiệu năng
  • Khi DELETE hàng loạt kéo dài quá 5 giây, các thao tác ghi khác cũng bị timeout, thậm chí worker và VM có thể bị tắt, nên tác vụ dọn dẹp được chia thành các batch nhỏ
  • Việc sao lưu dùng VACUUM INTO và restic nhưng gặp tình trạng bị kill do OOM; tác giả cũng đang thử Litestream để có sao lưu tăng dần hiệu quả hơn
  • Có thể tách bảng ra nhiều file SQLite; Mess with DNS đã vận hành bằng SQLite trong 4 năm kể từ 2022 và được đánh giá là lựa chọn phù hợp cho dự án

Vận hành SQLite cho một site Django

  • Tác giả chọn SQLite làm cơ sở dữ liệu cho site Django sau khi tham khảo nhiều bài viết cho rằng có thể dùng SQLite cả với các site production quy mô nhỏ
  • SQLite cũng là một cơ sở dữ liệu phức tạp cần kiến thức vận hành, và trong dự án này tác giả giao cho nó nhiều việc hơn trước thông qua Django ORM
  • Theo nhiều khuyến nghị, tác giả bật chế độ WAL ngay từ đầu khi vận hành
  • Đây là website thứ tư tác giả dùng SQLite

ANALYZE đã thay đổi hiệu năng tìm kiếm

  • Một truy vấn tìm kiếm toàn văn dùng SQLite FTS5 trên bảng 4.000 dòng mất 5 giây
  • Sau khi chạy ANALYZE, cùng truy vấn đó rút xuống khoảng 0,05 giây, nhanh đến mức không cần điều tra thêm
  • Tác giả chưa xác định chính xác vấn đề nằm ở query plan nào, và đoán rằng tình huống có thể tương tự độ phức tạp bậc hai ngoài ý muốn
  • ANALYZE tạo ra thông tin thống kê, bao gồm số dòng của bảng, giúp query planner đưa ra lựa chọn tốt hơn
  • Tác giả vẫn chưa học cách đọc query plan

Dọn dẹp dữ liệu và giới hạn chỉ một thao tác ghi

  • Khi xóa hàng loạt các dòng không cần thiết, chẳng hạn những task đã hoàn tất trong django-tasks-db, các vấn đề dây chuyền đã xảy ra
    • Lệnh dọn dẹp xử lý nhiều dòng chạy hơn 5 giây
    • Trong thời gian đó, các thao tác ghi vào cơ sở dữ liệu từ worker khác vượt quá giới hạn 5 giây đã cấu hình và bị timeout
    • Worker ghi thất bại bị crash và VM cũng bị tắt
  • Tác giả chưa xác định được lý do chính xác khiến DELETE chậm, và cũng đang cân nhắc khả năng là do nhiều mã Python chạy bên trong transaction
  • Hiện tại, tác vụ dọn dẹp được chia thành các batch nhỏ để mỗi truy vấn cơ sở dữ liệu không vượt quá 5 giây
  • Trải nghiệm này giúp tác giả cảm nhận rõ vì sao đôi khi cần cơ sở dữ liệu như Postgres, có thể xử lý nhiều thao tác ghi đồng thời
  • Trong tương lai, tác giả cũng cân nhắc hạ site xuống để thực hiện bảo trì theo lịch trong lúc chạy các tác vụ như vậy, nhưng chưa xây dựng được quy trình

Truy vấn ORM và quy mô dữ liệu hiện tại

  • Cho đến nay, tác giả tạo được các truy vấn mong muốn bằng Django ORM mà không kiểm tra hiệu năng riêng, nhưng ngoài vấn đề với ANALYZE, hầu hết đều hoạt động bình thường
  • Cơ sở dữ liệu nhỏ, khoảng 10.000 dòng, và dự kiến sẽ tiếp tục duy trì ở quy mô nhỏ

Cách sao lưu SQLite

  • Tác giả đã thử hai cách sao lưu SQLite: restic và Litestream
  • Tác vụ sao lưu thường được giám sát bằng dead man's switch, nhưng có vẻ tác giả vẫn chưa thực sự kiểm thử khôi phục

  • Sao lưu bằng restic

    • Tạo bản sao cơ sở dữ liệu bằng VACUUM INTO, nén bằng gzip rồi upload lên S3
    • Dùng restic để tạo backup, kiểm tra snapshot, xóa các backup cũ và dọn dẹp
    • Backup thỉnh thoảng bị kill do OOM, để lại lock nên tác giả cũng chạy 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

  • Sao lưu tăng dần bằng Litestream

    • Tác giả bắt đầu dùng Litestream để tránh việc backup bằng restic bị kill do OOM và thử hiệu quả của sao lưu tăng dần
    • Sau khi viết file cấu hình, chạy replication bằng lệnh sau
    litestream replicate -config litestream.yml
    • Tác giả đặt retention: 400h để giữ lịch sử cơ sở dữ liệu trong một khoảng thời gian, nhưng chưa xác nhận liệu nó có hoạt động đúng như mong muốn hay không
    • Hiện đang backup lên AWS, nhưng việc tạo thông tin xác thực trong console khá phiền, nên tác giả cũng cân nhắc chuyển sang một dịch vụ tương thích S3 khác trong tương lai

Sử dụng nhiều file cơ sở dữ liệu SQLite

  • Dự án hiện tại chỉ dùng một cơ sở dữ liệu, nhưng trong Mess with DNS, tác giả tách các bảng không cần đặt chung ra 3 file cơ sở dữ liệu
  • Tác giả cho rằng việc tách như vậy đã giúp ích cho vận hành
  • Mess with DNS đã vận hành bằng SQLite trong 4 năm kể từ 2022, và với dự án đó, việc chuyển từ Postgres sang SQLite là một lựa chọn tốt

Những tính năng cơ bản phát hiện muộn trong quá trình vận hành

  • Tác giả bắt đầu dùng SQLite cho dự án web từ 2022, nhưng đây là lần đầu biết đến sự tồn tại của ANALYZE
  • Ngay cả các tính năng cơ bản của công nghệ đang dùng cũng tiếp tục được học dần qua nhiều năm vận hành thực tế

Bài viết liên quan

1 bình luận

 
GN⁺ 9 giờ trước
Ý kiến Hacker News

  • Dùng chế độ .expert của SQLite có thể giúp trì hoãn thêm một chút việc phải học cách đọc kế hoạch truy vấn: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
    Khi phân tích SELECT * FROM x1 WHERE a=? AND b>?;, nó sẽ gợi ý chỉ mục như CREATE INDEX x1_idx_000123a7 ON x1(a, b);; sau khi tạo chỉ mục đó rồi phân tích lại, nó sẽ hiển thị rằng không cần thêm chỉ mục mới
    Ngay cả với các cơ sở dữ liệu “thực thụ” như Postgres, thông thường cũng nên chia các tác vụ dọn dẹp thành những lô nhỏ. Chỉ là ở quy mô nhỏ, tính kém hiệu quả của công việc đó ít lộ rõ hơn, nên cách tiếp cận trong bài gốc hóa ra còn đúng hơn tưởng tượng

    • Trong một số cơ sở dữ liệu, khi xóa 10 triệu hàng thì lượng dữ liệu undo tương ứng cũng được ghi lại. Với Oracle, nếu không sao lưu và dọn phần đó kịp lúc thì có thể làm cạn dung lượng đĩa dành cho archive log
      Commit thường xuyên sẽ giúp ích, nhưng khi dọn dẹp định kỳ các cơ sở dữ liệu lớn thì partitioning là hiệu quả nhất. Xóa partition cũ nhất gần như xong ngay lập tức
      Tuy vậy, ở đoạn “worker không thể ghi vào cơ sở dữ liệu, va chạm xảy ra và VM bị tắt” thì chưa rõ vì sao VM lại bị tắt. Ở đây VM có vẻ là máy ảo đang chạy hệ điều hành
    • Với MySQL quy mô lớn dùng row-based replication, UPDATE hoặc DELETE thay đổi hàng triệu hàng phải được chạy theo lô. Nếu xử lý bằng một truy vấn duy nhất thì hàng triệu hàng đã cập nhật sẽ phải được gửi tới mọi replica cùng một lúc
    • Có vẻ tương tự EXPLAIN QUERY PLAN: https://sqlite.org/eqp.html
      EXPLAIN thông thường hay in ra bytecode dài dòng hơn mức cần thiết, còn EXPLAIN QUERY PLAN thì cho thấy bản tóm tắt kế hoạch
    • Tôi tự hỏi liệu việc “ít làm lộ ra rằng đây là một công việc kém hiệu quả” có đang được xem là một ưu điểm hay không

  • Đọc từ góc nhìn của người làm với cơ sở dữ liệu thì khá bức bối, và tôi muốn tự tìm nguyên nhân rồi giải quyết. Nếu là một bảng chỉ có 10 nghìn hàng thì quét toàn bộ bảng cũng phải rất nhanh, nhất là với SQLite chạy trong tiến trình trên cùng máy vật lý
    Cách giải quyết bật ra trong đầu dĩ nhiên là “hãy tạo chỉ mục”. Việc xóa chậm nhiều khả năng là vấn đề N+1 kinh điển mà người dùng ORM hay gặp trước khi hiểu rõ các tương tác bên trong với cơ sở dữ liệu; mong Julia sẽ có bài viết tiếp theo

  • Vì việc tạo thông tin xác thực trong AWS console quá phiền phức, vài năm trước tôi đã làm một công cụ chỉ để giải quyết đúng vấn đề đó
    uvx s3-credentials create my-existing-s3-bucket
    Lệnh này sẽ in ra thông tin xác thực đọc-ghi được giới hạn phạm vi chỉ cho bucket đó. Có thể giới hạn thêm quyền bằng --read-only, --write-only, hoặc dùng --prefix foo/bar để chỉ cho phép đọc và ghi các key bắt đầu bằng tiền tố cụ thể
    Tôi cũng đã dùng nó cùng với Restic và Cloudflare R2, hoạt động rất tốt

    • Một cách tổng quát hơn để xử lý các dịch vụ AWS phức tạp là học một chút Terraform rồi giao phần còn lại cho LLM. Không cần nhớ mình đã tạo tài nguyên nào, và sau này dỡ bỏ chúng cũng dễ hơn nhiều
    • Một công cụ tương tự đã có từ trước là https://litestream.io/
    • Công cụ rất tuyệt, có vẻ đáng để AWS CLI tích hợp. Tuy vậy, tôi vẫn thắc mắc khi nào thì thông tin xác thực chỉ ghi lại thực sự cần thiết

  • Trong thời đại LLM, tôi càng đánh giá cao bài viết của Julia hơn. Quá trình khám phá chân thành là liều giải độc cho những bài viết tự động tạo ra nhưng luôn tỏ ra như biết hết mọi thứ

  • Tôi chạy sao lưu như sau:
    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"
    Nếu writer dùng WAL thì cách này không chặn nó, đồng thời tạo ra các bản dump có tỷ lệ nén tốt mà vẫn dễ đồng bộ. Cơ sở dữ liệu Home Assistant của tôi là 1.8GB nhưng bản dump nén chỉ 286MB, và tôi đoán khoảng 90% nội dung được tạo ra mỗi ngày là giống nhau

    • Tôi tò mò trong Home Assistant thì bạn đang sao lưu những gì. Bản sao lưu mặc định quá lớn nên tôi chỉ giữ lại cấu hình, loại trừ video, cache và các repository mà HACS tải về, nhưng không biết mình có đang bỏ sót thứ gì quan trọng không
      Tôi cũng thắc mắc liệu cơ sở dữ liệu lại phình to như vậy có phải vì lịch sử chuỗi thời gian được giữ quá lâu hay không
    • VACUUM INTO, .backup, sqlite3_rsync, và Litestream cũng không chặn writer. .backup dùng backup API
    • Tôi từng bị khóa một lần trong cơ sở dữ liệu đang hoạt động do .dump, rồi chuyển sang .backup. Dù vậy, cách ghi vào .part rồi mv vẫn rất gọn gàng

  • Tôi cho rằng bài viết hầu như không có nội dung thực chất, chỉ toàn những cách nói như “tôi chưa അന്വേഷ sâu hơn”, “đây là phỏng đoán tốt nhất”, “có lẽ còn những thứ khác nữa?”, “có thể có nhiều mã Python chạy bên trong transaction”. Không tìm hiểu hay điều tra kỹ, đôi khi còn đưa ra phỏng đoán sai
    Với tư cách là người dùng Debian, lý do tôi không mở kết quả tìm kiếm liên quan đến Linux nếu nó dẫn đến diễn đàn Ubuntu cũng là vì có quá nhiều phỏng đoán sai. Ngược lại, dù Arch khá khác Debian, tôi vẫn thường tham khảo Arch Wiki vì được viết bởi những người thực sự hiểu biết

    • Julia Evans có kiến thức kỹ thuật rất sâu rộng và đặc biệt giỏi trong việc gỡ bỏ vẻ huyền bí của công nghệ, giúp người đọc hiểu quá trình giải quyết vấn đề thực tế diễn ra như thế nào
      Bài này không giả vờ là lời giải thích của chuyên gia số một thế giới về SQLite, và ngay từ tiêu đề “Học được vài điều khi vận hành SQLite” đã đặt kỳ vọng rất rõ ràng
      Xuyên suốt các bài viết của Julia là thông điệp rằng không cần biết hết mọi thứ hay giả vờ như biết, vẫn có thể dùng những cách đơn giản để nắm bắt vấn đề và tích lũy kiến thức. Việc chia sẻ những gì mình đã tìm ra một cách rõ ràng nhất có thể là một thái độ tốt
    • Tôi nghĩ bài này rất hay vì nó thể hiện rõ tình huống của một người dùng phổ thông thông minh đang sử dụng công nghệ đó. Trọng tâm của tác giả là vận hành website, và những vấn đề kiểu này chính là chỗ mà các người dùng thường ngày, không dành cả ngày chỉ để làm việc với một công cụ cụ thể, rất dễ mắc phải
      Hôm qua khi làm một tính năng, tôi cũng đã dùng 2 ngôn ngữ lập trình, 2 hệ thống build, nhà cung cấp cloud, công cụ quản lý secret, framework giao tiếp client-server phức tạp trải qua hai ngôn ngữ, hệ thống quản lý phiên bản, editor và công cụ CI. Từ đó còn có thể đào sâu vào hệ điều hành và phiên bản runtime, cơ sở dữ liệu, reverse proxy, cache, và logic nghiệp vụ
      Nếu đào sâu mọi chủ đề mình gặp phải thì sẽ chẳng hoàn thành được việc gì, nên giống như tác giả, cần chọn đúng vấn đề để tập trung
    • Arch Wiki là một trong những tài liệu Linux tốt nhất. Khi còn dùng Mint tôi luôn tra ở đó, nhưng từ khi thật sự chuyển sang dùng Arch thì lại tra ít hơn thời còn dùng Mint

  • Vấn đề với DELETE có thể được giảm nhẹ dễ dàng bằng xóa theo lô, thêm độ trễ giữa các lô, và dùng SELECT để nạp trước rowid. SELECT không gây blocking
    Nếu dữ liệu được thêm tuần tự vào cùng một bảng, rất có thể chúng cũng được lưu trong file theo thứ tự tương tự, nên xóa theo thứ tự đó hoặc theo thứ tự ngược lại có thể nhanh hơn. Kết quả sẽ khác nhau tùy thiết bị lưu trữ và các điều kiện khác

    • Nạp trước row ID đã tỏ ra rất hiệu quả không chỉ với SQLite mà cả trên các cụm Aurora MySQL hoặc Postgres quy mô lớn. Có thể gửi SELECT sang replica, và áp lực bộ nhớ index do lọc row đang tạo gánh nặng lớn lên CPU và buffer cache của cơ sở dữ liệu
      Nếu không dễ loại bỏ dữ liệu không cần thiết khỏi đường xử lý chính bằng các cách như partition pruning, đây là một chiến lược rất mạnh

  • Đào sâu cơ sở dữ liệu hơn một chút so với mức mình đang quen hoặc so với yêu cầu công việc vẫn là một cách tốt để nâng cao năng lực. Nhiều web developer thường bị khựng lại trước các công cụ cơ sở dữ liệu, và với tôi cũng có cảm giác tương tự ở một số công nghệ vận hành như K8s
    Nếu hiểu cách SQL được chuyển thành các thao tác đọc và ghi dữ liệu trên đĩa, bạn sẽ có trực giác tốt hơn rất nhiều để đánh giá cách tiếp cận nào là ổn. Cũng cần hiểu cả cơ chế khóa của cơ sở dữ liệu
    Có kiến thức này thì khi ngay cả một câu COUNT đơn giản trên Postgres cũng không chạy nhanh, bạn sẽ bớt hoang mang hơn

  • sqlite_stat1 và sqlite_stat4 lưu nhiều thông tin thống kê về phân bố giá trị của index, và query planner dùng chúng để ước lượng độ chọn lọc cũng như mức độ hữu ích của index
    sqlite_stat1 chỉ cung cấp số bản ghi của index và số bản ghi trung bình trên mỗi giá trị, còn sqlite_stat4 nếu được bật thì sẽ lưu cả dữ liệu histogram

  • Litestream rất thú vị, và tôi đã chạy thành công khi dùng S3 làm backend. Nó có thể khiến rất nhiều ứng dụng dùng SQLite gần như không còn phụ thuộc vào trạng thái của file system
    Trạng thái của S3 dễ quản lý hơn nhiều, và việc backup cùng đồng bộ cũng do nhà cung cấp xử lý