Cách sao chép cơ sở dữ liệu SQLite giữa các máy tính nhanh hơn

 (alexwlchan.net)
1 điểm bởi GN⁺ 2025-05-02 | 1 bình luận | Chia sẻ qua WhatsApp
  • Khi chuyển một cơ sở dữ liệu SQLite lớn từ máy chủ từ xa về máy cục bộ, thay vì sao chép trực tiếp tệp .db gốc thì sao chép bản dump SQL được nén bằng gzip có thể giảm đáng kể lượng dữ liệu truyền đi
  • Nguyên nhân chính khiến tệp phình to là chỉ mục; để tăng tốc truy vấn, chúng lưu trữ lặp lại dữ liệu bảng và làm tăng dung lượng đĩa
  • .dump của SQLite biến cơ sở dữ liệu thành văn bản câu lệnh SQL, và chỉ mục được biểu diễn bằng một dòng CREATE INDEX thay vì dữ liệu thực tế, nhờ đó tránh truyền lặp
  • Trong ví dụ, cơ sở dữ liệu gốc là 3.4GB, bản dump văn bản là 1.3GB, bản nén gzip là 240MB, và bản dump nén nhỏ hơn bản gốc 14 lần
  • Nếu cố định tệp dump trước khi sao chép, có thể giảm tình huống dữ liệu gốc thay đổi giữa lúc truyền và gây lỗi database disk image is malformed

Nén bản dump SQLite để sao chép

  • Với cơ sở dữ liệu SQLite nhỏ, sao chép trực tiếp bằng rsync là đủ
    • Ví dụ: rsync --progress username@server:my_remote_database.db my_local_database.db
  • Khi cơ sở dữ liệu lớn hơn, sao chép trực tiếp có thể chậm và kém ổn định
    • Tải một cơ sở dữ liệu 250MB từ máy chủ web qua mạng Internet gia đình mất khoảng 1 phút
    • Với cơ sở dữ liệu nhiều GB, thời gian chờ còn lớn hơn
  • Điểm nghẽn khiến kích thước tăng lên nằm ở chỉ mục
    • Chỉ mục giúp tăng tốc truy vấn đáng kể nhưng cũng làm tệp cơ sở dữ liệu lớn hơn
    • Trong một cơ sở dữ liệu, một chỉ mục cụ thể chiếm tới một nửa dung lượng đĩa
    • Chỉ mục không chứa dữ liệu độc nhất mà chủ yếu lặp lại dữ liệu từ bảng khác để truy xuất nhanh hơn
  • SQLite có thể dump toàn bộ cơ sở dữ liệu ra tệp văn bản
    • Lệnh: sqlite3 my_database.db .dump > my_database.db.txt
    • Tệp kết quả gồm các câu lệnh SQL như CREATE TABLE, INSERT INTO, CREATE INDEX
    • Chỉ mục được lưu dưới dạng lệnh tạo như CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]);, chứ không phải dữ liệu chỉ mục thực tế
  • Có thể khôi phục lại cơ sở dữ liệu SQLite từ tệp dump
    • Lệnh: cat my_database.db.txt | sqlite3 my_reconstructed_database.db
  • Bản dump SQL có nhiều phần lặp nên gzip nén rất hiệu quả
    • Ví dụ: sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
    • So sánh kích thước:
      • Cơ sở dữ liệu SQLite gốc: 3.4GB
      • Bản dump văn bản: 1.3GB
      • Bản văn bản nén gzip: 240MB
    • Bản nén gzip nhỏ hơn cơ sở dữ liệu SQLite gốc 14 lần

Quy trình thực tế và lợi ích về độ ổn định

  • Cách làm mới là tạo bản dump nén trên máy chủ từ xa, chỉ tải tệp đó về, rồi tái dựng cơ sở dữ liệu ở máy cục bộ
  • Quy trình sao chép như sau
    • Tạo tệp văn bản nén gzip trên máy chủ: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
    • Sao chép về máy cục bộ: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
    • Xóa tệp nén tạm trên máy chủ: ssh username@server "rm my_remote_database.db.txt.gz"
    • Giải nén ở máy cục bộ: gunzip my_local_database.db.txt.gz
    • Tái dựng cơ sở dữ liệu cục bộ: cat my_local_database.db.txt | sqlite3 my_local_database.db
    • Xóa tệp văn bản cục bộ: rm my_local_database.db.txt
  • Bản dump nén đóng vai trò là nguồn sao chép nhất quán
    • Nếu cơ sở dữ liệu lớn bị cập nhật trong lúc sao chép kéo dài, phần đầu của tệp kết quả có thể là trước khi cập nhật còn phần sau là sau khi cập nhật
    • Những tệp như vậy có thể gây lỗi database disk image is malformed khi mở ở máy cục bộ
  • Nếu tạo bản dump văn bản trước khi sao chép, tệp mà rsync truyền đi sẽ không bị thay đổi giữa chừng
    • Nhờ đó khả năng sao chép được một tệp văn bản đầy đủ và nhất quán sẽ cao hơn
  • Nếu bạn thường xuyên di chuyển các cơ sở dữ liệu SQLite lớn, cách này có thể giúp giảm thời gian tải xuống và tăng độ tin cậy khi sao chép

Bài viết liên quan

1 bình luận

 
GN⁺ 2025-05-02
Các ý kiến trên Hacker News

  • Nếu cứ sao chép nguyên xi một tệp cơ sở dữ liệu đang chạy và đang nhận cập nhật thì tất nhiên gần như chắc chắn nó sẽ bị hỏng
    Để sao chép cơ sở dữ liệu SQLite một cách an toàn, có Litestream: https://github.com/benbjohnson/litestream

    • Litestream cũng hoạt động trên SFTP thông thường, nên bạn có thể stream bản sao cơ sở dữ liệu tới gần như mọi endpoint UNIX có thể truy cập qua SSH
      Có dịch vụ được khuyên dùng, nhưng bất kỳ máy chủ SFTP nào cũng được
      [1] https://github.com/benbjohnson/litestream/issues/140
      [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
    • Câu nói “chắc chắn” sẽ hỏng có ngoại lệ. Nếu sao chép nguyên tử một tệp hoặc subvolume trên Btrfs hay ZFS, thì với cơ sở dữ liệu ACID hoặc cây LSM, trường hợp xấu nhất cũng chỉ là rollback
      Tất nhiên nếu có nhiều tệp thì phải gói chúng vào một subvolume để tất cả được sao chép trong cùng một transaction, và chỉ dùng cp --reflink=always thôi là chưa đủ
      Dừng tiến trình bằng SIGSTOP cũng có thể cho kết quả tương tự, nhưng tôi không muốn trông cậy vào cách đó
    • Lệnh .backup tích hợp sẵn cũng là công cụ chính thức nhằm tạo một phiên bản snapshot của cơ sở dữ liệu đang chạy để có thể sao chép
    • Trên hệ thống của tôi, tôi dùng Litestream rất ổn, nhưng cũng có một tài liệu hay hướng dẫn khá chi tiết cách làm thủ công việc tương tự chỉ bằng công cụ tích hợp: https://litestream.io/alternatives/cron/
    • Litestream thực sự rất tuyệt. Tôi định dùng nó ở cấp container để sao lưu và khôi phục SQLite, giống như một cựu nhân viên Google từng lập một startup KVM nhỏ đã làm khi nhà kho bị ngập nước trong lúc đi nghỉ
      Nếu tôi nhớ chính xác thì anh ấy đã viết một hướng dẫn hoàn hảo, nhưng khả năng tôi tìm lại được gần như bằng 0. Mong ai biết tham chiếu này có thể đăng link

  • Nói là sao chép cơ sở dữ liệu giữa các máy tính mà cảm giác như chỉ vẽ mỗi hình tròn rồi bỏ qua phần còn lại của con cú
    Như những người khác đã nói, rsync gia tăng sẽ nhanh hơn nhiều, nhưng điều khiến tôi khó chịu hơn là bài viết nói gửi các câu lệnh SQL nhanh hơn gửi cơ sở dữ liệu, nhưng lại hoàn toàn bỏ qua việc các câu lệnh SQL đó phải được thực thi. Sau đó còn phải chạy /optimize/ và /vacuum/ nữa
    Hiện tôi đang ở tình huống phải “tái dựng gia tăng” cơ sở dữ liệu từ các tệp CSV, và trong trường hợp của tôi, tạo lại từ đầu vẫn tối ưu hơn. Dù đã tối ưu khá nhiều, chỉ riêng việc chèn theo batch vào cơ sở dữ liệu rỗng trong bộ nhớ rồi tạo index đã mất 30 phút

    • Hy vọng bạn đã xem https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite
      Đây là bài tổng hợp rất tốt về cách chèn nhanh trong SQLite3
    • Với bất kỳ tối ưu hóa nào, điều quan trọng ở đây là nút thắt cổ chai nằm ở đâu. Trường hợp trong bài gốc dường như bị nghẽn ở băng thông, còn CPU/I/O đĩa thì đủ
      Bài nói tải cơ sở dữ liệu 250MB mất 1 phút, còn tôi nhờ đường truyền cáp quang 1Gbps nên đã kéo một cơ sở dữ liệu SQLite thử nghiệm 2GB từ máy chủ công ty trong 15 giây
    • 30 phút nghe có vẻ lâu. Dữ liệu nhiều lắm à? Tôi đang làm việc bootstrap một cơ sở dữ liệu SQLite từ rất nhiều dữ liệu JSON, và đã tìm được một điểm sweet spot về hiệu năng khá tốt: gom các giá trị thành danh sách rồi chèn mỗi lần 10.000 mục, có thể đưa hàng triệu dòng vào trong vài phút
      Tôi đã phải dùng vài mẹo như Bloom filter và LRU cache, nhưng giờ có thể tạo cơ sở dữ liệu 6GB trong khoảng 20 phút

  • SQLite có công cụ chính thức cho mục đích này: https://www.sqlite.org/rsync.html
    Nó hoạt động ở cấp trang
    “Giao thức hoạt động bằng cách bản sao gửi hàm băm mật mã của từng trang về phía bản gốc, còn bản gốc gửi lại toàn bộ nội dung của những trang có hàm băm không khớp”

    • Đúng, nhưng đáng tiếc là nhóm SQLite không đưa công cụ đó vào autotools tarball mà hầu hết các bản phân phối và brew dùng để đóng gói SQLite
      Muốn dùng công cụ này thì chỉ còn cách tự biên dịch

  • Tiện ích sqlite_rsync mới được phát hành gần đây dùng một phiên bản thuật toán rsync được tối ưu theo cấu trúc nội bộ của cơ sở dữ liệu SQLite. Nó so sánh hiệu quả các trang dữ liệu bên trong rồi chỉ đồng bộ những trang đã thay đổi hoặc còn thiếu
    Các mẹo trong bài cũng hay, nhưng giờ dùng tiện ích tích hợp sẽ dễ hơn :)
    Tôi đã viết chi tiết cách nó hoạt động trên blog: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works

    • sqlite3_rsync hiện đã được tích hợp vào nền tảng rsync.net
      ssh user@rsync.net sqlite3_rsync … blah blah …
      Nó mới được thêm tuần trước nên chưa triển khai ở tất cả các khu vực, nhưng những người dùng đầu tiên đều nói rằng nó hoạt động đúng như mong đợi
    • sqlite_rsync chỉ dùng được ở chế độ WAL. Do ràng buộc bổ sung của chế độ WAL, tệp cơ sở dữ liệu phải nằm trên đĩa cục bộ
      Hầu hết trường hợp hiển nhiên là như vậy, nhưng nếu không thể thì bạn sẽ không dùng được tiện ích này
    • Nếu đồng bộ thường xuyên, vì băng thông bạn nên tăng kích thước trang

  • Việc không thử nén trong quá trình truyền mà rsync cung cấp là điều khá bất ngờ
    -z, --compress nén dữ liệu tệp trong khi truyền, còn --compress-level=NUM chỉ định rõ mức nén
    Có lẽ nén bằng gzip rồi truyền có thể nhanh hơn, nhưng việc có thể cải thiện quá trình truyền chỉ bằng một cờ là điểm hay

    • Nếu đã nhắc đến vấn đề hỏng dữ liệu thì dùng sqlite3_rsync(https://sqlite.org/rsync.html) cùng với -z sẽ tốt hơn
      Tức là rsync nhận biết được transaction và WAL của SQLite, lại có thêm nén trong khi truyền
    • Trọng tâm là bỏ qua index, và việc này phải làm trước khi nén
      Khi làm những việc kiểu này, tôi thường stream dump thẳng vào gzip. Thường cũng có thể tìm cách stream trực tiếp đến đích mà không cần tệp trung gian
      Hơn nữa, làm vậy thì ở đích dữ liệu được lưu ở trạng thái đã nén. Đặc biệt nếu mục đích là backup chứ không phải kiểu sao chép nghèo nàn
    • Tôi cho rằng nén chỉ có lợi trên mạng chậm
    • Đây là việc nhất định phải làm. Nếu dùng rsync trên tệp đã nén, bạn sẽ hoàn toàn đi vòng qua điểm cốt lõi của rsync: truyền phần khác biệt dựa trên rolling checksum
    • Lẽ ra có thể dùng --remove-source-files để khỏi phải SSH vào lại rồi rm

  • Lưu dưới dạng tệp văn bản là không hiệu quả. Tôi lưu cơ sở dữ liệu SQLite bằng VACUUM INTO
    sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"
    Theo https://sqlite.org/lang_vacuum.html, lệnh VACUUM có mệnh đề INTO là một phương án thay thế cho backup API để tạo bản sao lưu của cơ sở dữ liệu đang chạy. Ưu điểm là kích thước cơ sở dữ liệu backup kết quả được tối thiểu hóa, nên có thể giảm I/O hệ thống tệp

    • Cách đó hay, nhưng không giải quyết được vấn đề index mà bài gốc nói tới. Ý chính là không gửi dữ liệu index qua đường truyền chậm, còn cách VACUUM INTO vẫn giữ index
      Tệp văn bản tự thân có thể kém hiệu quả, nhưng nén rất tốt ngay cả bằng công cụ thô sơ như gzip. Tôi không chắc định dạng nhị phân của SQLite có nén tốt như vậy không, nhưng cũng có khả năng
    • Cách đó có giữ lại index không? Bài gốc nói lý do tệp SQLite lớn là do index

  • Trong DuckDB có thể làm việc tương tự nhưng xuất ra Parquet, khi đó kích thước dữ liệu nhỏ hơn các câu lệnh SQL dạng văn bản ở mức chênh lệch một bậc độ lớn. Truyền cũng nhanh hơn và nạp cũng nhanh hơn
    https://duckdb.org/docs/stable/sql/statements/export.html

    • Trên dòng lệnh có thể làm như sau
      duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"
      Với cơ sở dữ liệu thử nghiệm của tôi, nó nhỏ hơn khoảng 20% so với các câu lệnh SQL văn bản được nén bằng gzip

  • SQLite có extension session để theo dõi thay đổi của một tập bảng và tạo changeset/patchset có thể áp dụng cho phiên bản cũ hơn của cơ sở dữ liệu SQLite
    https://www.sqlite.org/sessionintro.html

    • Tôi vẫn chưa thấy binding SQLite nào hỗ trợ cái này. Vì vậy nếu bạn không định viết ứng dụng bằng C hoặc patch binding ngôn ngữ thì nó khá kém hữu dụng
      Trong một dự án của tôi, tôi tự triển khai kiểu session nghèo nàn bằng cách ghi lại tất cả câu lệnh SQL và tham số vào một cơ sở dữ liệu riêng, rồi đồng bộ và phát lại chúng. Với cơ sở dữ liệu khoảng 30GB mà mỗi ngày chỉ thay đổi khoảng 0,1%, cách này hoạt động đủ tốt
    • Đã có ai dùng chưa? Tôi đã đọc tài liệu, nhưng chưa từng nghe ai nói là thực sự dùng extension này
    • Sẽ thật tuyệt nếu SQLite có một cách mặc định để làm change data capture bằng session hoặc cơ chế tương tự

  • Nếu đồng bộ định kỳ từ phiên bản cũ sang phiên bản mới, có thể tối ưu thêm bằng tùy chọn --rsyncable của gzip
    Tỷ lệ nén giảm khoảng 1%, nhưng phần khác biệt từ phiên bản này sang phiên bản tiếp theo sẽ được cục bộ hóa thay vì lan truyền dây chuyền ra toàn bộ đầu ra đã nén
    Một cách khác là không nén đầu ra dump, để rsync tính phần khác biệt giữa dump chưa nén trước đó và dump hiện tại, rồi chỉ nén tập thay đổi được gửi qua mạng. Tức là dùng rsync -z

  • Có vẻ có thể xử lý việc này bằng một pipeline duy nhất
    ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

    • Nếu bật nén SSH cho lời gọi SSH bằng -oCompression=on hoặc -C thì gzip/gunzip có thể là thừa
    • Đó cũng là điều đầu tiên tôi nghĩ tới. Và việc nên có thêm pv trong đó cũng có vẻ khá hiển nhiên