Cara menyalin database SQLite antar komputer dengan lebih cepat

 (alexwlchan.net)
1 poin oleh GN⁺ 2025-05-02 | 1 komentar | Bagikan ke WhatsApp
  • Saat memindahkan database SQLite besar dari server jarak jauh ke lokal, menyalin dump SQL yang dikompresi gzip alih-alih file .db asli dapat mengurangi jumlah data yang ditransfer secara signifikan
  • Penyebab utama membesarnya file adalah indeks; untuk mempercepat kueri, indeks menyimpan duplikasi data tabel sehingga meningkatkan penggunaan disk
  • .dump SQLite mengubah database menjadi teks berisi pernyataan SQL, dan indeks direpresentasikan sebagai satu baris CREATE INDEX, bukan data indeks sebenarnya, sehingga menghindari transfer data duplikat
  • Pada contoh database, file asli berukuran 3,4GB, teks dump 1,3GB, dan hasil kompresi gzip 240MB; dump terkompresi 14 kali lebih kecil daripada file asli
  • Membuat file dump tetap sebelum menyalin dapat mengurangi situasi ketika sumber berubah selama transfer dan memunculkan galat database disk image is malformed

Menyalin dump SQLite yang dikompresi

  • Untuk database SQLite kecil, menyalinnya langsung dengan rsync sudah cukup
    • Contoh: rsync --progress username@server:my_remote_database.db my_local_database.db
  • Ketika database membesar, penyalinan langsung bisa menjadi lambat dan kurang stabil
    • Mengunduh database 250MB dari server web lewat internet rumahan membutuhkan sekitar 1 menit
    • Untuk database berukuran beberapa GB, waktu tunggunya akan lebih lama
  • Bottleneck pertumbuhan ukuran ada pada indeks
    • Indeks sangat meningkatkan kecepatan kueri, tetapi juga memperbesar file database
    • Dalam satu database, satu indeks tertentu mengambil separuh ukuran disk
    • Indeks mempercepat pencarian dengan menduplikasi data dari tabel lain, alih-alih menyimpan data unik
  • SQLite dapat men-dump seluruh database menjadi file teks
    • Perintah: sqlite3 my_database.db .dump > my_database.db.txt
    • File hasilnya terdiri dari pernyataan SQL seperti CREATE TABLE, INSERT INTO, dan CREATE INDEX
    • Indeks disimpan sebagai perintah pembuatan seperti CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]);, bukan sebagai data indeks sebenarnya
  • File dump dapat dipulihkan kembali menjadi database SQLite
    • Perintah: cat my_database.db.txt | sqlite3 my_reconstructed_database.db
  • Dump SQL memiliki banyak pengulangan, sehingga efisiensi kompresi gzip tinggi
    • Contoh: sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
    • Perbandingan ukuran:
      • Database SQLite asli: 3,4GB
      • Dump teks: 1,3GB
      • Teks terkompresi gzip: 240MB
    • Hasil kompresi gzip 14 kali lebih kecil daripada database SQLite asli

Prosedur nyata dan efek terhadap stabilitas

  • Metode baru ini membuat dump terkompresi di server jarak jauh, hanya mengunduh file tersebut, lalu merekonstruksi database secara lokal
  • Prosedur penyalinannya sebagai berikut
    • Membuat file teks terkompresi gzip di server: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
    • Menyalin ke lokal: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
    • Menghapus file kompresi sementara di server: ssh username@server "rm my_remote_database.db.txt.gz"
    • Mendekompresi di lokal: gunzip my_local_database.db.txt.gz
    • Merekonstruksi database lokal: cat my_local_database.db.txt | sqlite3 my_local_database.db
    • Menghapus file teks lokal: rm my_local_database.db.txt
  • Dump terkompresi berperan sebagai sumber salinan yang konsisten
    • Jika sumber diperbarui saat database besar sedang disalin dalam waktu lama, bagian awal file hasil bisa berada dalam keadaan sebelum pembaruan sementara bagian akhirnya setelah pembaruan
    • File seperti ini dapat memunculkan galat database disk image is malformed saat dibuka di lokal
  • Jika dump teks dibuat sebelum penyalinan, file yang ditransfer oleh rsync tidak berubah di tengah proses
    • Peluang menyalin file teks yang utuh dan konsisten menjadi lebih tinggi
  • Jika Anda sering memindahkan database SQLite besar, metode ini dapat mengurangi waktu unduh dan meningkatkan keandalan penyalinan

Bacaan terkait

1 komentar

 
GN⁺ 2025-05-02
Komentar Hacker News

  • Jika menyalin begitu saja file database yang sedang berjalan dan menerima pembaruan, tentu saja file itu pasti bisa rusak
    Untuk mereplikasi database SQLite dengan aman, ada Litestream: https://github.com/benbjohnson/litestream

    • Litestream juga berjalan di atas SFTP biasa, sehingga replikasi database bisa di-streaming ke hampir semua endpoint UNIX yang dapat diakses lewat SSH
      Ada layanan yang lebih disukai, tetapi server SFTP apa pun bisa digunakan
      [1] https://github.com/benbjohnson/litestream/issues/140
      [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
    • Ada pengecualian untuk pernyataan bahwa itu “pasti” rusak. Jika menyalin file atau subvolume secara atomik di Btrfs atau ZFS, untuk database ACID atau LSM tree, skenario terburuknya hanya rollback
      Tentu saja, jika ada beberapa file, semuanya harus dikelompokkan sebagai subvolume agar disalin dalam transaksi yang sama, dan sekadar cp --reflink=always saja tidak cukup
      Menghentikan proses dengan SIGSTOP juga bisa menghasilkan hasil serupa, tetapi saya tidak ingin bergantung pada itu
    • Perintah .backup bawaan juga merupakan tool resmi yang dimaksudkan untuk membuat versi snapshot dari database yang sedang berjalan agar bisa disalin
    • Di sistem saya Litestream berjalan baik, tetapi ada juga dokumen yang cukup detail menjelaskan cara melakukan pekerjaan serupa secara manual hanya dengan tool bawaan: https://litestream.io/alternatives/cron/
    • Litestream benar-benar keren. Saya berencana memakainya untuk backup dan pemulihan SQLite di level container, seperti yang dilakukan mantan karyawan Google yang membuat startup KVM kecil, yang gudangnya kebanjiran saat ia sedang berlibur
      Kalau ingatan saya benar, ia menulis panduan yang sempurna, tetapi peluang saya menemukannya nyaris 0. Semoga ada yang tahu referensi ini dan membagikan tautannya

  • Katanya menyalin database antar-komputer, tetapi rasanya seperti hanya menggambar lingkaran lalu melewatkan sisa burung hantunya
    Seperti yang dikatakan orang lain, rsync inkremental akan jauh lebih cepat, tetapi yang lebih mengganggu adalah bagian yang mengatakan mengirim pernyataan SQL lebih cepat daripada mengirim database, sambil sepenuhnya melewatkan fakta bahwa pernyataan SQL itu harus dieksekusi. Setelah itu /optimize/ juga harus dijalankan, dan /vacuum/ juga
    Saat ini saya punya situasi yang mengharuskan “membangun ulang secara inkremental” database dari file CSV, dan dalam kasus saya lebih optimal untuk membuatnya lagi dari awal. Meski sudah banyak dioptimalkan, memasukkan batch ke database kosong di memori lalu membuat indeks saja memakan waktu 30 menit

    • Semoga sudah melihat https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite
      Itu tulisan yang merangkum dengan sangat baik cara melakukan insert cepat di SQLite3
    • Optimasi apa pun di sini bergantung pada di mana letak bottleneck-nya. Dalam kasus artikel asli, bottleneck-nya tampak ada di bandwidth, sementara CPU/I/O disk tampaknya cukup memadai
      Disebutkan bahwa mengunduh database 250MB butuh 1 menit; dengan koneksi fiber 1Gbps, saya mengambil database uji SQLite 2GB dari server perusahaan dalam 15 detik
    • 30 menit terdengar lama. Datanya sangat banyak? Saya sedang mengerjakan bootstrap database SQLite dengan banyak data JSON, dan dengan mengumpulkan nilai dalam list lalu memasukkan 10 ribu sekaligus, saya menemukan sweet spot performa yang cukup bagus sehingga jutaan baris bisa dimasukkan dalam beberapa menit
      Memang harus memakai trik seperti Bloom filter dan cache LRU, tetapi sekarang saya bisa membuat database 6GB dalam sekitar 20 menit

  • SQLite punya tool resmi untuk tujuan ini: https://www.sqlite.org/rsync.html
    Bekerja pada level halaman
    “Protokolnya adalah replika mengirim hash kriptografis dari setiap halaman ke sisi asal, lalu asal mengirim kembali seluruh isi halaman yang hash-nya tidak cocok”

    • Benar, tetapi sayangnya tim SQLite tidak menyertakan tool itu dalam tarball autotools yang dipakai sebagian besar distro dan brew untuk memaketkan SQLite
      Satu-satunya cara untuk memakai tool ini adalah mengompilasinya sendiri

  • Utilitas sqlite_rsync yang baru dirilis menggunakan versi algoritma rsync yang dioptimalkan sesuai struktur internal database SQLite. Ia membandingkan halaman data internal secara efisien, lalu hanya menyinkronkan halaman yang berubah atau tidak ada
    Tips di artikelnya juga bagus, tetapi sekarang lebih mudah memakai utilitas bawaan :)
    Saya menulis detail cara kerjanya di blog: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works

    • sqlite3_rsync sekarang sudah terintegrasi di platform rsync.net
      ssh user@rsync.net sqlite3_rsync … blah blah …
      Baru ditambahkan minggu lalu dan belum digelar ke semua region, tetapi para pengguna awal mengatakan semuanya berjalan persis seperti yang diharapkan
    • sqlite_rsync hanya bisa digunakan dalam mode WAL. Sebagai batasan tambahan mode WAL, file database harus berada di disk lokal
      Kebanyakan orang tentu begitu, tetapi jika itu tidak memungkinkan, utilitas ini tidak bisa digunakan
    • Jika sering melakukan sinkronisasi, sebaiknya memperbesar ukuran halaman demi bandwidth

  • Saya cukup heran rsync tidak mencoba kompresi saat transfer yang memang disediakan
    -z, --compress mengompresi data file selama transfer, dan --compress-level=NUM menetapkan level kompresi secara eksplisit
    Mungkin mengompres dengan gzip lalu mentransfernya bisa lebih cepat, tetapi bagus bahwa transfer bisa diperbaiki hanya dengan satu flag

    • Jika menyinggung masalah korupsi, lebih baik memakai sqlite3_rsync(https://sqlite.org/rsync.html) bersama -z
      Artinya rsync yang memahami transaksi SQLite dan WAL ditambah kompresi saat transfer
    • Intinya adalah melewati indeks, dan ini harus dilakukan sebelum kompresi
      Saat melakukan hal seperti ini, saya biasanya men-stream dump langsung ke gzip. Biasanya juga bisa ditemukan cara untuk men-stream langsung ke tujuan tanpa file perantara
      Selain itu, dengan cara ini data tersimpan dalam keadaan terkompresi di tujuan. Terutama jika tujuannya backup, bukan replikasi ala orang miskin
    • Menurut saya kompresi hanya menguntungkan pada jaringan yang lambat
    • Ini wajib dilakukan. Jika memakai rsync pada file terkompresi, Anda sepenuhnya melewati inti penggunaan rsync, yaitu transfer perbedaan berbasis rolling checksum
    • Bisa saja memakai --remove-source-files sehingga tidak perlu masuk lagi lewat SSH untuk menjalankan rm

  • Menyimpan sebagai file teks itu tidak efisien. Saya menyimpan database SQLite dengan VACUUM INTO
    sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"
    Menurut https://sqlite.org/lang_vacuum.html, perintah VACUUM dengan klausa INTO adalah alternatif untuk backup API yang membuat salinan cadangan dari database yang sedang berjalan. Keuntungannya adalah ukuran database backup yang dihasilkan diminimalkan sehingga I/O filesystem bisa berkurang

    • Memang keren, tetapi tidak menyelesaikan masalah indeks yang disebut di tulisan asli. Ide utamanya adalah tidak mengirim data indeks melalui link lambat, sedangkan metode VACUUM INTO mempertahankan indeks
      File teks mungkin tidak efisien apa adanya, tetapi bisa dikompresi dengan baik bahkan dengan alat sederhana seperti gzip. Saya tidak yakin format biner SQLite juga bisa dikompresi sebaik itu, meski mungkin saja
    • Apakah cara itu mempertahankan indeks? Tulisan asli mengatakan alasan file SQLite besar adalah indeks

  • Di DuckDB, Anda bisa melakukan hal yang sama tetapi mengekspor ke Parquet, sehingga ukuran data menjadi lebih kecil hingga satu orde besaran dibanding pernyataan SQL berbasis teks. Transfernya juga lebih cepat dan pemuatannya juga lebih cepat
    https://duckdb.org/docs/stable/sql/statements/export.html

    • Dari command line bisa dilakukan seperti ini
      duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"
      Pada database pengujian saya, ukurannya sekitar 20% lebih kecil daripada pernyataan SQL teks yang dikompresi dengan gzip

  • SQLite memiliki ekstensi session yang melacak perubahan pada sekumpulan tabel dan membuat changeset/patchset yang dapat diterapkan ke versi lama database SQLite
    https://www.sqlite.org/sessionintro.html

    • Saya belum pernah melihat satu pun binding SQLite yang mendukung ini. Jadi, jika Anda tidak berniat menulis aplikasi dalam C atau mem-patch binding bahasa, kegunaannya cukup terbatas
      Di salah satu proyek saya, saya mengimplementasikan sendiri session ala orang miskin dengan mencatat semua pernyataan SQL dan parameternya ke database terpisah, lalu menyinkronkan dan memutarnya ulang. Ini bekerja cukup baik untuk database sekitar 30GB yang hanya berubah sekitar 0,1% per hari
    • Pernah memakainya? Saya sudah membaca dokumentasinya, tetapi belum pernah mendengar ada orang yang benar-benar memakai ekstensi ini
    • Akan sangat bagus jika SQLite punya cara bawaan untuk melakukan change data capture lewat session atau mekanisme serupa

  • Jika menyinkronkan secara berkala dari versi lama ke versi baru, ini bisa dioptimalkan lagi dengan opsi --rsyncable pada gzip
    Rasio kompresi turun sekitar 1%, tetapi perbedaan dari satu versi ke versi berikutnya menjadi terlokalisasi dan tidak merambat ke seluruh output terkompresi
    Cara lain adalah tidak mengompresi output dump, membiarkan rsync menghitung perbedaan antara dump lama yang tidak terkompresi dan dump saat ini, lalu hanya mengompresi kumpulan perubahan yang dikirim lewat jaringan. Dengan kata lain, gunakan rsync -z

  • Sepertinya ini bisa ditangani sebagai satu pipeline
    ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

    • Jika mengaktifkan kompresi SSH pada pemanggilan SSH dengan -oCompression=on atau -C, gzip/gunzip mungkin menjadi duplikasi
    • Itu juga pikiran pertama saya. Dan cukup jelas juga bahwa pv seharusnya masuk di sana