SQLite veritabanlarını bilgisayarlar arasında daha hızlı kopyalamanın yolu

 (alexwlchan.net)
1 puan yazan GN⁺ 2025-05-02 | 1 yorum | WhatsApp'ta paylaş
  • Büyük SQLite veritabanlarını uzak bir sunucudan yerele taşırken, özgün .db dosyası yerine gzip ile sıkıştırılmış SQL dökümünü kopyalamak aktarım miktarını ciddi ölçüde azaltır
  • Dosyanın büyümesinin başlıca nedeni indekslerdir; sorguları hızlandırmak için tablo verilerini yinelenmiş biçimde saklayarak disk kullanımını artırırlar
  • SQLite’ın .dump komutu veritabanını SQL ifadelerinden oluşan metne dönüştürür; indeksler ise gerçek veri olarak değil, tek satırlık CREATE INDEX olarak temsil edilip yinelenen aktarımı önler
  • Örnek veritabanında özgün dosya 3.4GB, döküm metni 1.3GB, gzip sıkıştırılmış hali 240MB idi; sıkıştırılmış döküm özgün dosyadan 14 kat daha küçüktü
  • Kopyalamadan önce döküm dosyasını sabitlemek, aktarım sırasında özgün dosyanın değişmesiyle database disk image is malformed hatasının oluştuğu durumları azaltabilir

SQLite dökümünü sıkıştırarak kopyalamak

  • Küçük SQLite veritabanlarını doğrudan rsync ile kopyalamak yeterlidir
    • Örnek: rsync --progress username@server:my_remote_database.db my_local_database.db
  • Veritabanı büyüdükçe doğrudan kopyalama yavaş ve kararsız hale gelebilir
    • 250MB’lık bir veritabanını bir web sunucusundan ev internetiyle indirmek yaklaşık 1 dakika sürer
    • Birkaç GB boyutundaki veritabanlarında bekleme süresi daha da artar
  • Boyut artışındaki darboğaz indekslerdedir
    • İndeksler sorgu hızını büyük ölçüde artırır, ancak veritabanı dosyasını da büyütür
    • Bir veritabanında belirli tek bir indeks, disk boyutunun yarısını kaplıyordu
    • İndeksler benzersiz veri barındırmaktan çok, sorguları hızlandırmak için diğer tablo verilerini yineler
  • SQLite, veritabanının tamamını bir metin dosyasına dökebilir
    • Komut: sqlite3 my_database.db .dump > my_database.db.txt
    • Ortaya çıkan dosya CREATE TABLE, INSERT INTO, CREATE INDEX gibi SQL ifadelerinden oluşur
    • İndeksler gerçek indeks verisi olarak değil, CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]); gibi oluşturma komutları olarak saklanır
  • Döküm dosyası tekrar SQLite veritabanına geri yüklenebilir
    • Komut: cat my_database.db.txt | sqlite3 my_reconstructed_database.db
  • SQL dökümlerinde tekrar çok olduğundan gzip sıkıştırması yüksek verim sağlar
    • Örnek: sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
    • Boyut karşılaştırması:
      • Özgün SQLite veritabanı: 3.4GB
      • Metin dökümü: 1.3GB
      • gzip sıkıştırılmış metin: 240MB
    • gzip sıkıştırılmış dosya, özgün SQLite veritabanından 14 kat daha küçüktü

Gerçek prosedür ve kararlılığa etkisi

  • Yeni yöntem, uzak sunucuda sıkıştırılmış bir döküm oluşturup yalnızca bu dosyayı indirdikten sonra veritabanını yerelde yeniden kurar
  • Kopyalama prosedürü şöyledir
    • Sunucuda gzip sıkıştırılmış metin dosyası oluşturma: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
    • Yerele kopyalama: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
    • Sunucudaki geçici sıkıştırılmış dosyayı silme: ssh username@server "rm my_remote_database.db.txt.gz"
    • Yerelde sıkıştırmayı açma: gunzip my_local_database.db.txt.gz
    • Yerel veritabanını yeniden oluşturma: cat my_local_database.db.txt | sqlite3 my_local_database.db
    • Yerel metin dosyasını silme: rm my_local_database.db.txt
  • Sıkıştırılmış döküm, tutarlı bir kopyalama kaynağı görevi görür
    • Büyük bir veritabanı uzun süre kopyalanırken özgün dosya güncellenirse, sonuç dosyasının baş kısmı güncellemeden önceki, son kısmı ise güncellemeden sonraki durumda olabilir
    • Böyle bir dosya yerelde açılırken database disk image is malformed hatası verebilir
  • Kopyalamadan önce metin dökümü oluşturmak, rsync tarafından aktarılan dosyanın aktarım sırasında değişmemesini sağlar
    • Eksiksiz ve tutarlı bir metin dosyasının kopyalanma olasılığı artar
  • Büyük SQLite veritabanlarını sık sık taşıyorsanız bu yöntemle indirme süresini azaltabilir ve kopyalama güvenilirliğini artırabilirsiniz

İlgili okumalar

1 yorum

 
GN⁺ 2025-05-02
Hacker News yorumları

  • Çalışmakta olan ve güncelleme alan bir veritabanı dosyasını olduğu gibi kopyalarsanız elbette bozulması kaçınılmazdır.
    SQLite veritabanını güvenli biçimde çoğaltmak için Litestream var: https://github.com/benbjohnson/litestream

    • Litestream sıradan SFTP üzerinde de çalıştığı için, SSH ile erişilebilen neredeyse her UNIX uç noktasına veritabanı replikasyonunu stream edebilirsiniz.
      Tercih ettiği servisler var ama herhangi bir SFTP sunucusu da olur.
      [1] https://github.com/benbjohnson/litestream/issues/140
      [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
    • “Mutlaka” bozulur sözünün istisnaları var. Btrfs veya ZFS üzerinde bir dosyayı ya da alt birimi atomik olarak kopyalarsanız, ACID veritabanı veya LSM ağacı ise en kötü durumda sadece rollback olur.
      Elbette birden fazla dosya varsa hepsinin aynı işlem içinde kopyalanması için bunları bir alt birimde toplamak gerekir; yalnızca cp --reflink=always yeterli değildir.
      Süreci SIGSTOP ile durdurmak da benzer bir sonuç verebilir ama buna güvenmek istemem.
    • Yerleşik .backup komutu da çalışan bir veritabanının anlık görüntü sürümünü oluşturup kopyalamayı mümkün kılmak için sunulan resmi araçtır.
    • Kendi sistemimde Litestream’i sorunsuz kullanıyorum; ama yalnızca yerleşik araçlarla benzer işi elle yapmayı oldukça ayrıntılı anlatan şu belge de iyi: https://litestream.io/alternatives/cron/
    • Litestream gerçekten harika. Tatildeyken deposu su basan, küçük bir KVM girişimi kurmuş eski bir Google çalışanının yaptığı gibi, konteyner düzeyinde SQLite yedekleme ve kurtarma için kullanmayı planlıyorum.
      Doğru hatırlıyorsam mükemmel bir rehber yazmıştı ama onu bulma ihtimalim sıfıra yakın. Bu referansı bilen biri linki paylaşırsa iyi olur.

  • Bilgisayarlar arasında veritabanı kopyalama denmiş ama sanki sadece daireleri çizip baykuşun geri kalanını atlamışlar.
    Başkalarının dediği gibi artımlı rsync çok daha hızlı olurdu; ama daha rahatsız edici olan, SQL ifadelerini göndermenin veritabanını göndermekten hızlı olduğunu söyleyip o SQL ifadelerinin çalıştırılması gerektiği gerçeğini tamamen atlamış olmaları. Ardından /optimize/ da çalıştırmanız, /vacuum/ da çalıştırmanız gerekir.
    Şu anda CSV dosyalarından veritabanını “artımlı olarak yeniden kurmam” gereken bir durum var; benim durumumda baştan yeniden oluşturmak daha optimum. Yine de çok optimize etmeme rağmen boş bir bellek içi veritabanına toplu ekleme yapmak ve indeksleri oluşturmak tek başına 30 dakika sürüyor.

    • https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite bağlantısına bakmış olmanı umarım.
      SQLite3’te hızlı ekleme yapmayı çok iyi özetleyen bir yazı.
    • Her optimizasyonda olduğu gibi burada önemli olan darboğazın nerede olduğu. Asıl örnekte darboğaz bant genişliği gibi görünüyor; CPU/disk I/O ise yeterli.
      250 MB veritabanını indirmek 1 dakika sürüyor denmişti; ben 1 Gbps fiber bağlantı sayesinde şirket sunucusundan 2 GB’lık SQLite test veritabanını 15 saniyede çektim.
    • 30 dakika uzun görünüyor. Veri çok mu fazla? Çok sayıda JSON verisiyle bir SQLite veritabanını bootstrap ettiğim bir iş üzerinde çalışıyorum; değerleri listelerde toplayıp 10 binlik gruplar halinde ekleyince, milyonlarca satırı da birkaç dakika içinde ekleyebilen oldukça iyi bir performans sweet spot’u buldum.
      Bloom filtresi ve LRU cache gibi hileler kullanmam gerekti ama artık 6 GB’lık veritabanını yaklaşık 20 dakikada oluşturabiliyorum.

  • SQLite’ta bu amaç için resmi bir araç var: https://www.sqlite.org/rsync.html
    Sayfa düzeyinde çalışıyor.
    “Protokolde replika, her sayfanın kriptografik hash’ini kaynak tarafa gönderir; kaynak da hash’i eşleşmeyen sayfaların tam içeriğini geri gönderir.”

    • Doğru, ama ne yazık ki SQLite ekibi bu aracı çoğu dağıtımın ve brew’un SQLite paketlemek için kullandığı autotools tarball içine dahil etmiyor.
      Bu aracı kullanmak için tek yol kendiniz derlemek.

  • Yakın zamanda yayımlanan sqlite_rsync yardımcı programı, SQLite veritabanlarının iç yapısına göre optimize edilmiş bir rsync algoritması sürümünü kullanıyor. İç veri sayfalarını verimli biçimde karşılaştırdıktan sonra yalnızca değişmiş ya da eksik sayfaları senkronize ediyor.
    Yazıdaki püf noktaları da iyi ama artık yerleşik yardımcı programı kullanmak daha kolay :)
    Nasıl çalıştığını blogda ayrıntılı yazdım: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works

    • sqlite3_rsync artık rsync.net platformuna yerleşik olarak dahil.
      ssh user@rsync.net sqlite3_rsync … blah blah …
      Daha geçen hafta eklendiği için henüz tüm bölgelere dağıtılmadı, ama ilk kullanıcıların hepsi beklendiği gibi çalıştığını söylüyor.
    • sqlite_rsync yalnızca WAL modunda kullanılabilir. WAL modunun ek kısıtı olarak veritabanı dosyasının yerel diskte olması gerekir.
      Çoğu durumda zaten böyle olur, ama bunun mümkün olmadığı durumlarda bu yardımcı programı kullanamazsınız.
    • Sık senkronizasyon yapıyorsanız bant genişliği nedeniyle sayfa boyutunu büyütmeniz gerekir.

  • rsync'in aktarım sırasında sunduğu sıkıştırmanın denenmemiş olması şaşırtıcıydı
    -z, --compress aktarım sırasında dosya verilerini sıkıştırır; --compress-level=NUM ise sıkıştırma seviyesini açıkça belirtir
    Muhtemelen gzip ile sıkıştırıp sonra aktarmak daha hızlı olabilir, ama tek bir bayrakla aktarımı iyileştirebilmek güzel

    • Bozulma sorunundan bahsedildiyse -z ile birlikte sqlite3_rsync(https://sqlite.org/rsync.html) kullanmak daha iyi olur
      Bu, SQLite işlemlerini ve WAL'ı bilen bir rsync'e aktarım sırasında sıkıştırma eklemek gibi olur
    • Asıl nokta indeksleri atlamak; bu da sıkıştırmadan önce yapılmalı
      Böyle işler yaparken dump çıktısını doğrudan gzip'e stream ederim. Genellikle ara dosya olmadan doğrudan hedefe stream etmenin yolunu da bulabilirsiniz
      Üstelik böylece hedefte sıkıştırılmış hâlde saklanır. Amaç yoksul işi replikasyon değil de yedekleme ise bu özellikle geçerli
    • Sıkıştırmanın yalnızca yavaş ağlarda avantajlı olduğunu düşünüyorum
    • Bu mutlaka yapılmalı. Sıkıştırılmış bir dosyada rsync kullanırsanız, rsync kullanmanın asıl sebebi olan rolling checksum tabanlı fark aktarımını tamamen devre dışı bırakmış olursunuz
    • --remove-source-files kullanılsaydı yeniden SSH ile girip rm çalıştırmaya gerek kalmazdı

  • Metin dosyası olarak saklamak verimsiz. Ben SQLite veritabanını VACUUM INTO ile kaydediyorum
    sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"
    https://sqlite.org/lang_vacuum.html adresine göre, INTO yan tümcesiyle kullanılan VACUUM komutu, çalışan veritabanının yedek kopyasını oluşturan backup API'ye bir alternatiftir. Avantajı, ortaya çıkan yedek veritabanının boyutunun en aza inmesi ve dosya sistemi I/O'sunun azalabilmesidir

    • Güzel, ama asıl yazıda bahsedilen indeks sorununu çözmüyor. Temel fikir, yavaş bağlantı üzerinden indeks verisi göndermemekti; VACUUM INTO yöntemi ise indeksleri korur
      Metin dosyası olduğu gibi verimsiz olabilir, ama gzip gibi ilkel araçlarla bile iyi sıkıştırılır. SQLite ikili biçiminin de o kadar iyi sıkışıp sıkışmadığından emin değilim, ama mümkün
    • Bu yöntem indeksleri koruyor mu? Asıl yazıda SQLite dosyasının büyük olmasının sebebinin indeksler olduğu söylenmişti

  • DuckDB'de aynı işi yapıp Parquet olarak dışa aktarabilirsiniz; bu durumda veri boyutu metin tabanlı SQL ifadelerine göre tek haneli bir çarpan kadar küçülür. Aktarım da daha hızlı, yükleme de daha hızlı olur
    https://duckdb.org/docs/stable/sql/statements/export.html

    • Komut satırından şöyle yapılabilir
      duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"
      Benim test veritabanımda, gzip ile sıkıştırılmış metin SQL ifadelerinden yaklaşık %20 daha küçüktü

  • SQLite'ta, bir tablo kümesindeki değişiklikleri izleyen ve SQLite veritabanının önceki bir sürümüne uygulanabilecek changeset/patchset üreten bir session extension var
    https://www.sqlite.org/sessionintro.html

    • Bunu destekleyen tek bir SQLite binding'i bile henüz görmedim. Bu yüzden uygulamayı C ile yazmayı ya da dil binding'ini patch etmeyi düşünmüyorsanız pek işe yaramıyor
      Projelerimden birinde tüm SQL ifadelerini ve parametreleri ayrı bir veritabanına kaydedip onu senkronize ederek yeniden oynatan yoksul işi bir session'ı kendim uygulamıştım. Her gün yalnızca yaklaşık %0,1'i değişen 30 GB civarında bir veritabanı için yeterince iyi çalışıyor
    • Hiç kullandınız mı? Dokümanları okudum ama bu extension'ı gerçekten kullanan birini hiç duymadım
    • SQLite'ta session ya da benzer bir yöntemle change data capture yapmak için yerleşik bir yol olsa gerçekten güzel olurdu

  • Eski sürümden yeni sürüme düzenli olarak senkronize ediyorsanız gzip'in --rsyncable seçeneği ile daha da optimize edebilirsiniz
    Sıkıştırma oranı yaklaşık %1 düşer, ama bir sürümden sonrakine olan fark sıkıştırılmış çıktının tamamına zincirleme yayılmak yerine yerelleşir
    Bir diğer yöntem de dump çıktısını sıkıştırmamak, rsync'in önceki sıkıştırılmamış dump ile mevcut dump arasındaki farkı hesaplamasına izin vermek ve ağ üzerinden gönderilecek değişiklik kümesini sıkıştırmasını sağlamaktır. Yani rsync -z kullanmak

  • Bu, tek bir pipeline olarak işlenebilir gibi görünüyor
    ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

    • SSH çağrısında -oCompression=on veya -C ile SSH sıkıştırması açılırsa gzip/gunzip gereksiz tekrar olabilir
    • Benim de ilk düşündüğüm buydu. Ayrıca oraya pv eklenmesi gerektiği de oldukça açık görünüyor