SQLite işletirken yeni öğrendiğim birkaç şey

 (jvns.ca)
1 puan yazan GN⁺ 9 시간 전 | 1 yorum | WhatsApp'ta paylaş
  • Küçük ölçekli web sitelerinin üretim ortamında da SQLite kullanılabilir, ancak Django ORM’in üstlendiği işler arttıkça veritabanının operasyonel karmaşıklığını hissettim
  • 4.000 satırlık bir tabloda FTS5 araması 5 saniye sürüyordu; ANALYZE çalıştırıldıktan sonra yaklaşık 0,05 saniyeye düştü ve sorgu planlayıcı istatistiklerinin performansı ciddi biçimde etkileyebileceği görüldü
  • Büyük ölçekli DELETE işlemleri 5 saniyeyi aşarsa diğer yazma işlemleri de zaman aşımına uğrayabiliyor, worker’lar ve VM bile kapanabiliyor; bu yüzden temizlik işleri küçük batch’lere bölünerek işleniyor
  • Yedekleme için VACUUM INTO ve restic kullanıldı ancak OOM nedeniyle sonlanma yaşandı; daha verimli artımlı yedekleme için Litestream de deneniyor
  • Tablolar birden fazla SQLite dosyasına ayrılabilir; Mess with DNS’in 2022’den bu yana 4 yıl SQLite ile çalıştığı ve proje için uygun bir tercih olduğu değerlendiriliyor

Bir Django sitesinde SQLite işletmek

  • Küçük ölçekli üretim sitelerinde de SQLite kullanılabileceğini anlatan çeşitli yazılara bakılarak Django sitesinin veritabanı olarak seçildi
  • SQLite da operasyon bilgisi gerektiren karmaşık bir veritabanıdır; bu projede Django ORM üzerinden önceye göre daha fazla iş ona yaptırılıyor
  • Çeşitli önerilere uygun olarak en baştan WAL modu etkinleştirilerek işletildi
  • Bu site, SQLite kullanılan dördüncü web sitesi

ANALYZE arama performansını nasıl değiştirdi

  • 4.000 satırlık bir tabloda SQLite FTS5 kullanan tam metin arama sorgusu 5 saniye sürüyordu
  • ANALYZE çalıştırılınca aynı sorgu yaklaşık 0,05 saniyeye indi; daha fazla araştırmaya gerek kalmayacak kadar hızlandı
  • Tam olarak hangi sorgu planı sorunu olduğu doğrulanamadı; istemeden oluşan ikinci dereceden zaman karmaşıklığına benzer bir durum olduğu tahmin ediliyor
  • ANALYZE, tablo satır sayıları gibi istatistik bilgileri oluşturarak sorgu planlayıcının daha iyi seçimler yapmasına yardımcı olur
  • Sorgu planlarını okuma yöntemi henüz öğrenilmiş değil

Veri temizliği ve tek yazma kısıtı

  • django-tasks-db içindeki tamamlanmış işler gibi gereksiz satırlar toplu halde silinirken zincirleme sorunlar ortaya çıktı
    • Çok sayıda satırı işleyen temizlik komutu 5 saniyeden uzun çalıştı
    • Bu sırada diğer worker’ların veritabanına yazmaları, ayarlanmış 5 saniyelik sınırı aşıp zaman aşımına uğradı
    • Yazması başarısız olan worker çöktü ve VM de kapandı
  • DELETE işleminin neden yavaş olduğunun tam nedeni doğrulanamadı; bunun, transaction içinde çok fazla Python kodu çalışmasından kaynaklanıyor olabileceği de değerlendiriliyor
  • Şu anda her veritabanı sorgusunun 5 saniyeyi aşmaması için temizlik işleri küçük batch’lere bölünüyor
  • Bu deneyim, aynı anda birden fazla yazma işlemini işleyebilen Postgres gibi bir veritabanına neden ihtiyaç duyulabileceğini hissettirdi
  • Gelecekte bu tür işler sırasında siteyi kapatıp planlı bakım yapma seçeneği de değerlendiriliyor, ancak bunun için henüz bir iş akışı hazırlanmış değil

ORM sorguları ve mevcut veri ölçeği

  • Şimdiye kadar Django ORM ile istenen sorgular oluşturulurken performans ayrıca kontrol edilmedi, ancak ANALYZE sorunu dışında çoğu şey normal çalıştı
  • Veritabanı yaklaşık 10.000 satır ile küçük; gelecekte de küçük ölçekte kalması bekleniyor

SQLite yedekleme yöntemleri

  • SQLite yedekleri için restic ve Litestream olmak üzere iki yöntem denendi
  • Yedekleme işleri genellikle dead man's switch ile izleniyor, ancak gerçek geri yükleme testinin henüz yapılmadığı anlaşılıyor

  • restic ile yedekleme

    • VACUUM INTO ile veritabanının bir kopyası oluşturulup gzip ile sıkıştırıldıktan sonra S3’e yükleniyor
    • restic ile yedek oluşturma, snapshot kontrolü, eski yedekleri silme ve temizlik yapılıyor
    • Yedekleme bazen OOM nedeniyle sonlanınca kilit kaldı ve restic unlock da çalıştırıldı
    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

  • Litestream ile artımlı yedekleme

    • restic yedeklerinin OOM nedeniyle sonlanmasını önlemek ve artımlı yedeklemenin verimliliğini denemek için Litestream kullanılmaya başlandı
    • Yapılandırma dosyası yazıldıktan sonra çoğaltma şu komutla çalıştırılıyor
    litestream replicate -config litestream.yml
    • Veritabanı geçmişini belirli bir süre tutmak için retention: 400h belirtildi, ancak bunun amaçlandığı gibi çalışıp çalışmadığı henüz doğrulanmadı
    • AWS’e yedekleme yapılıyor, ancak konsoldan kimlik bilgisi oluşturmak zahmetli olduğu için gelecekte başka bir S3 uyumlu servise geçme seçeneği de değerlendiriliyor

Birden fazla SQLite veritabanı dosyası kullanmak

  • Mevcut proje yalnızca tek veritabanı kullanıyor, ancak Mess with DNS içinde birlikte durması gerekmeyen tablolar 3 veritabanı dosyasına ayrıldı
  • Bu ayrımın operasyon açısından faydalı olduğu düşünülüyor
  • Mess with DNS, 2022’den bu yana 4 yıl boyunca SQLite ile çalıştı ve o proje için Postgres’ten SQLite’a geçmek iyi bir tercihti

İşletme sırasında sonradan fark edilen temel özellikler

  • Bir web projesinde SQLite’ın ilk kullanıldığı zaman 2022 olsa da ANALYZE’ın varlığı bu kez ilk defa öğrenildi
  • Kullanılan teknolojinin temel özellikleri bile gerçek işletim sürecinde yıllar içinde öğrenilmeye devam ediyor

İlgili okumalar

1 yorum

 
GN⁺ 9 시간 전
Hacker News yorumları

  • SQLite’in .expert modunu kullanırsanız sorgu planı okumayı öğrenmeniz gereken günü biraz erteleyebilirsiniz: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
    SELECT * FROM x1 WHERE a=? AND b>?; sorgusunu analiz ettiğinizde CREATE INDEX x1_idx_000123a7 ON x1(a, b); gibi önerilen indeksi gösterir; bunu oluşturup yeniden analiz ettiğinizde yeni bir indeks gerekmediğini belirtir
    Postgres gibi “gerçek” veritabanlarında da temizlik işlerini genellikle küçük batch’lere bölmek önerilir. Sadece küçük ölçekte bunun verimsiz bir işlem olduğu gerçeği daha az görünür olur; bu yüzden asıl yazıdaki yaklaşım sanılandan daha doğruydu

    • Bazı veritabanlarında 10 milyon satır silmek, aynı miktarda geri alma verisinin de kaydedilmesi anlamına gelir. Oracle’da bu veriler zamanında yedeklenip boşaltılamazsa arşiv günlükleri için ayrılan disk alanı tükenebilir
      Sık commit etmek yardımcı olur, ancak büyük veritabanlarını düzenli olarak temizlerken en etkili yöntem partitioning oldu. En eski partition’ı silmek neredeyse anında biter
      Yine de “worker’lar veritabanına yazamadığı için çakışıyor ve VM kapanıyor” ifadesinde VM’in neden kapandığı belirsiz. Buradaki VM, işletim sisteminin çalıştığı sanal makineyi ifade ediyor gibi görünüyor
    • Satır tabanlı replikasyon kullanan büyük ölçekli MySQL’de milyonlarca satırı değiştiren UPDATE veya DELETE işlemlerini batch’ler halinde çalıştırmak gerekiyordu. Çünkü tek bir sorguyla yapılırsa güncellenen milyonlarca satırın tüm replikalara bir seferde gönderilmesi gerekir
    • EXPLAIN QUERY PLAN’a benziyor: https://sqlite.org/eqp.html
      Normal EXPLAIN genellikle gerekenden fazla ayrıntılı bytecode çıktısı üretir, ancak EXPLAIN QUERY PLAN özetlenmiş planı gösterir
    • “Verimsiz bir işlem olduğu gerçeğini daha az görünür kılar” noktasını bir avantaj olarak mı gördüğünü merak ediyorum

  • Veritabanlarıyla çalışan biri olarak okuması sinir bozucuydu; nedeni kendim bulup çözmek istedim. Yalnızca 10 bin satırlık bir tablo söz konusuysa tam tablo taraması bile çok hızlı olmalı; aynı fiziksel sunucuda süreç içinde çalışan SQLite için bu daha da geçerli
    Aklıma gelen çözüm doğal olarak “indeks oluştur”du. Yavaş silme işlemi, ORM kullanıcılarının alttaki veritabanı etkileşimlerini anlamadan önce sık yaşadığı klasik N+1 problemi olabilir gibi görünüyor; Julia’nın bir devam yazısı yazmasını umuyorum

  • AWS konsolunda kimlik bilgisi oluşturmak çok zahmetli olduğu için birkaç yıl önce yalnızca bu sorunu çözen bir araç yaptım
    uvx s3-credentials create my-existing-s3-bucket
    Bu komut, kapsamı yalnızca ilgili bucket ile sınırlı okuma-yazma kimlik bilgileri üretir. --read-only, --write-only ile yetkileri daha da kısıtlayabilir veya --prefix foo/bar ile yalnızca belirli bir prefix ile başlayan key’leri okuyup yazmasını sağlayabilirsiniz
    Restic ve Cloudflare R2 ile birlikte de denedim; çok iyi çalıştı

    • Karmaşık AWS servisleriyle uğraşmanın daha genel yolu, biraz Terraform öğrenip geri kalanını LLM’e bırakmak. Oluşturduğunuz kaynakları hatırlamanız gerekmez ve sonradan sökmek de çok daha kolaylaşır
    • Benzer mevcut bir araç olarak https://litestream.io/ da var
    • Çok iyi bir araç; AWS CLI tarafından içine alınabilecek gibi görünüyor. Yine de yalnızca yazma yetkili kimlik bilgilerine ne zaman ihtiyaç duyulduğunu merak ediyorum

  • LLM çağına girince Julia’nın yazılarını daha da takdir eder oldum. Samimi bir keşif süreci, kendinden emin biçimde her şeyi biliyormuş gibi yapan otomatik üretilmiş yazılara karşı panzehir gibi

  • Yedeklemeyi şöyle çalıştırıyorum:
    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"
    Yazma worker’ları WAL kullanıyorsa bunu engellemez ve hem iyi sıkıştırılan hem de senkronize etmesi kolay bir dump üretir. Home Assistant veritabanı 1,8 GB, ancak sıkıştırılmış dump 286 MB; her gün üretilen içeriğin yaklaşık %90’ının aynı olduğunu tahmin ediyorum

    • Home Assistant’ta neyi yedeklediğini merak ediyorum. Varsayılan yedekler çok büyük olduğu için yalnızca ayarları bıraktım; videoları, cache’i ve HACS’in indirdiği repoları hariç tuttum, ama önemli bir şeyi kaçırıp kaçırmadığımı bilmiyorum
      Veritabanının bu kadar büyümesinin uzun süre saklanan zaman serisi kayıtlarından kaynaklanıp kaynaklanmadığını da merak ediyorum
    • VACUUM INTO, .backup, sqlite3_rsync ve Litestream de yazma worker’larını engellemez. .backup, yedekleme API’sini kullanır
    • Canlı bir veritabanında .dump yüzünden bir kez kilitlenme yaşadıktan sonra .backup’a geçtim. Yine de .part dosyasına yazıp sonra mv yapmak temiz bir yöntem

  • “Daha fazla araştırmadım”, “en iyi tahminim bu”, “belki başka şeyler de?”, “transaction içinde çok fazla Python kodu çalışıyor olabilir” gibi ifadelerden ibaret olduğu için yazıda elle tutulur bir içerik olmadığını düşünüyorum. Doğru düzgün öğrenmeden ya da araştırmadan, bazen hatalı tahminler bile yapıyor.
    Debian kullanıcısı olarak Linux ile ilgili arama sonuçlarında Ubuntu forumları çıktığında açmamamın nedeni de yanlış tahminlerin çok fazla olması. Buna karşılık Arch, Debian’dan epey farklı olsa da bilgili kişilerin hazırladığı Arch Wiki’yi genelde kontrol ederim

    • Julia Evans’ın teknik bilgisi çok güçlü; özellikle teknolojinin etrafındaki gizemi kaldırıp gerçek problem çözme sürecinin nasıl olduğunu kavratmakta çok iyi.
      Bu yazı, dünyanın en büyük SQLite uzmanının açıklaması gibi davranmıyor; daha başlığından “SQLite işletimi hakkında birkaç şey öğrenmek” diyerek beklentiyi net biçimde belirliyor.
      Her şeyi bilmeye ya da biliyor gibi yapmaya gerek olmadan, basit yöntemlerle sorunu anlayıp bilgi biriktirebileceğimiz mesajı Julia’nın yazılarının genelinde var. Şimdiye kadar öğrendiklerini olabildiğince açık paylaşmak iyi bir tutum.
    • Bu yazının, ilgili teknolojiyi kullanan akıllı sıradan bir kullanıcının durumunu iyi gösterdiği için harika olduğunu düşünüyorum. Yazarın odağı web sitesi işletmek; bu tür sorunlar da tüm gün tek bir araçla uğraşmayan günlük kullanıcıların sıkça takıldığı noktalar.
      Ben de daha dün bir özellik geliştirirken 2 programlama dili, 2 build sistemi, bir bulut sağlayıcısı, gizli bilgi yönetimi aracı, iki dile yayılan karmaşık bir istemci-sunucu iletişim framework’ü, sürüm kontrol sistemi, editör ve CI aracı kullandım. Buna işletim sistemi ve runtime sürümleri, veritabanı, reverse proxy, cache ve domain logic de eklenebilir.
      Karşılaştığınız her konuyu derinlemesine kurcalarsanız hiçbir işi bitiremezsiniz; bu yüzden yazar gibi odaklanılacak problemi seçmek gerekir.
    • Arch Wiki, en iyi Linux kaynaklarından biri. Mint kullanırken sürekli bakardım; ne var ki Arch kullanmaya başladıktan sonra Mint zamanlarıma göre daha az bakar oldum.

  • DELETE sorunu toplu silme, gruplar arasında gecikme koyma ve SELECT ile rowid değerlerini önceden çekme yöntemiyle kolayca hafifletilebilir. SELECT bloklamaya neden olmaz.
    Veriler aynı tabloya sıralı biçimde eklendiyse dosyada da benzer sırayla saklanmış olma olasılığı yüksektir; bu nedenle o sırayla ya da ters sırayla silmek daha hızlı olabilir. Sonuç, depolama ortamına ve diğer koşullara göre değişir.

    • Satır ID’lerini önceden yükleme, yalnızca SQLite’ta değil, büyük Aurora MySQL veya Postgres kümelerinde de çok etkili oldu. SELECT sorgusunu replica’ya gönderebiliyorduk; satır filtrelemesinin yol açtığı indeks belleği baskısı, veritabanının CPU’su ve buffer cache’i üzerinde ciddi yük oluşturuyordu.
      Partition pruning gibi yöntemlerle gereksiz verileri ana işlem yolunun dışına çıkarmak zorsa, bu çok güçlü bir stratejidir.

  • Şu an aşina olduğunuz seviyeden ya da işin gerektirdiğinden veritabanını biraz daha derin kurcalamak hâlâ beceriyi artırmanın iyi bir yolu. Pek çok web geliştiricisi veritabanı araçları karşısında tıkanıp kalıyor; ben de K8s gibi bazı operasyon teknolojilerinde benzer şekilde tıkanıyorum.
    SQL’in diskte veri okuma ve yazma işlemlerine nasıl dönüştüğünü bilmek, hangi yaklaşımın makul olacağını sezgisel olarak değerlendirmede çok yardımcı olur. Veritabanının kilitleme modelini de birlikte anlamak gerekir.
    Bu bilgiye sahip olunca Postgres’te basit bir COUNT bile hızlı bitmediğinde paniğe kapılma ihtimaliniz azalır.

  • sqlite_stat1 ve sqlite_stat4, indeks değer dağılımına ilişkin çeşitli istatistikleri saklar; query planner da bunlara dayanarak indeksin seçiciliğini ve faydasını tahmin eder.
    sqlite_stat1, indeksin kayıt sayısı ve değer başına ortalama kayıt sayısını sağlar; etkinleştirildiğinde sqlite_stat4 histogram verilerini de saklar.

  • Litestream çok ilginç; S3’ü backend olarak bağlayıp çalıştırmayı başardım. SQLite kullanan sayısız uygulamayı dosya sistemi durumuna neredeyse hiç bağımlı olmayacak hâle getirebilir.
    S3’ün durumunu yönetmek çok daha kolaydır; yedekleme ve senkronizasyonu da sağlayıcı halleder.