lobste.rs, MariaDB'den SQLite'a geçti

• lobste.rs, MariaDB'den SQLite'a geçtikten sonra pazartesi günkü trafik sıçramasını da stabil biçimde karşıladı; CPU ve bellek kullanımında düşüş ile daha hızlı hissedilen yanıt süreleri gözlemlendi
• Ayrı MariaDB VPS'i kapatınca VPS maliyeti yarıya inecek ve 2019'da başlayan migrasyon görevi de tamamlanmış olacak
• İlk dağıtımda yalnızca salt okunur trafikle bile tüm CPU'lar %100'e ulaştığı için rollback yapıldı; ancak büyük tablolardaki 2 tam tablo taraması ve N+1 sorunu düzeltildikten sonra yeniden dağıtım başarıyla gerçekleşti
• SQLite kullanıcı tanımlı fonksiyonlarıyla regexp, if ve stddev uygulanırken, unsigned bigint desteğinin olmaması ve yalnızca ASCII işleyen NOCASE gibi uyumluluk kısıtlarının da çözülmesi gerekti
• Üretim veritabanına erişmeden temel DB'yi değiştirmek için gerçekçi boyutta test verisi, test suite'i, dağıtım/rollback kontrol listeleri ve yakın iş birliği gerekiyor

---

2019'daki tartışmadan SQLite geçişine

• Migrasyon tartışması 2019'da issue #539 ile başladı
  • O dönemde MariaDB ile uyumluluk göz önüne alınarak alternatif olarak MySQL önerilmişti, ancak PostgreSQL'e geçiş tartışmaları zaten sürüyordu
  • 2025'te Rahul, K1'in MariaDB'yi satın almasını gündeme getirince PostgreSQL migrasyonunun ayrıntılarına dair tartışma devam etti
  • Şubat ayında Rahul, lobsters'ın SQLite üzerinde çalışıp çalışamayacağını sordu ve Haziran 2025'ten itibaren projeye katılım ciddi biçimde başladı
• Ağustos 2025'teki ilk PR, çalışma durduktan sonra GitHub tarafından eski PR olarak kapatıldı ve yeniden açılamadı
  • İkinci PR performans testlerini, özel bir DB'ler arası migrasyon script'ini ve veri bütünlüğüyle ilgili hata ayıklamayı içeriyordu
  • Mevcut MariaDB/MySQL→SQLite dönüştürme script'leri içinde gereksinimleri karşılayan olmadığı için ayrı bir script yazıldı

İlk dağıtım hatası ve performans darboğazlarının düzeltilmesi

• 21 Şubat'taki ilk dağıtım, bir dağıtım kontrol listesi hazırlanarak yapıldı; ancak dağıtımın hemen ardından salt okunur durumdaki trafik bile tüm CPU'ları %100 seviyesine çıkardı
  • Neden bulunamadığı için rollback yapıldı
  • Üretim DB'sine erişilemediğinden gerçek performans sorunlarını önceden yeniden üretmek zordu
• Başarısızlıktan iki gün sonra üçüncü ve nihai PR açılarak aramayla ilgili sorunlar düzeltildi ve gerçek lobsters verisinin yarısı büyüklüğünde veriyi yerelde oluşturan bir toplu veri üretme script'i eklendi
  • Veriyi üretmek bir hafta sürdü
  • İlk dağıtımdaki darboğaz, büyük tablolarda iki sorgunun neden olduğu tam tablo taramaları ve ayrı bir N+1 sorunuydu
  • Bunları çözen değişiklik 1, değişiklik 2, değişiklik 3 dahil edildi
  • Ek performans sorunlarını izleyebilmek için dağıtım sabahı yavaş sorgu log'u eklendi

Yeniden dağıtım sonucu ve operasyon maliyetlerinde düşüş

• 11 Temmuz'daki ikinci dağıtımda dağıtım ve rollback kontrol listeleri yeniden hazırlandı; geçişten sonra da site normal çalıştı, CPU ve bellek kullanımı stabil kaldı
  • Site metrikleri ve IRC izlenirken bildirilen sorunlar düzeltme 1, düzeltme 2 ile hemen ele alındı
  • Pazartesi günkü trafik sıçramasında da sorun yaşanmadı; CPU ve bellek kullanımının düştüğü ve yanıtların daha hızlı hissedildiği doğrulandı
  • MariaDB VPS kapatıldığında VPS maliyeti yarıya inecek ve migrasyon issue'su da kapanacak

SQLite'ın MariaDB'den farklı kısıtları

• SQLite gem'inin kullanıcı tanımlı fonksiyon (UDF) desteği kullanılarak SQLite'ta bulunmayan regexp, if, stddev uygulandı
  • SQL migrasyonu için aşırı miktarda workaround eklemeye gerek kalmadı
• MariaDB'de bazı ID'ler için kullanılan unsigned bigint SQLite tarafından desteklenmediğinden bigint'e değiştirildi
• Sıralama kuralları da MariaDB'ye göre daha sınırlı
  • MariaDB'de utf8mb4_general_ci kullanılıyordu, SQLite'ta ise NOCASE uygulandı
  • NOCASE, tüm UTF büyük/küçük harf katlamasını desteklemez ve yalnızca ASCII karakterleri işler
• SQLite tam metin arama tabloları için varsayılan olmayan ama önerilen yaklaşım olan Contentless-Delete Tables uygundur
• Rails'in varsayılan PRAGMA ayarları lobsters'ta sorunsuz çalıştı
  • Veritabanı migrasyonları DB'ye göre değiştiğinden mevcut migrasyonlar old_migrations dizinine taşındı ve db:migrate'in çalışmaya devam etmesi sağlandı

Test ve iş birliğinden çıkarılan dersler

• lobsters kod tabanında arama parser'ı ve render hızını artıran bir hack olan heinous_inline_partials görüldü
  • Test suite'i sayesinde, geniş kapsamlı manuel test olmadan SQLite geçişinin doğruluğu doğrulanabildi
• Katılımcılar arasındaki sorunsuz iletişim migrasyonun başarısını belirledi
  • Üretim DB'sine erişim olmadan temel veritabanını değiştirmek çok zordu
  • Aynı iş yeniden yapılacak olsa önceden gerçekçi veri ölçeği hazırlanmalı
• Test sırasında tam tablo taraması oluştuğunda başarısız olacak şekilde ayar yapılabilseydi, ilk dağıtımdaki performans sorunu önceden yakalanabilirdi
• Üretime benzer veri setlerini doğrudan yazmadan veya birer hafta beklemeden kolayca oluşturabilecek araçlara ihtiyaç var

[GN⁺]

Lobste.rs görüşleri

• @thomas0 büyük ölçekli migrasyonu titizlikle üstlendi, @355E3B planlama ve operasyon tarafında yardımcı oldu; ayrıca #539 ile ilgili katkı sunanlara ve #lobsters sayesinde salt okunur dönemi de keyifle atlatabildiler
  Aslında #539'da PostgreSQL planlanmıştı, ancak işi fiilen üstlenen gönüllü SQLite kullandı ve beklenen ihtiyaçtan daha büyük ve karmaşık bir çözümden kaçınmak istedikleri için SQLite seçildi. PostgreSQL normalde varsayılan tercihleri olsa da ayrı bir servisi çalıştırma, ayarlama ve bakım yükü getiriyor
  Şaşırtıcı biçimde migrasyondan sonra CPU ve RAM kullanımı da azaldı; ayrıntılı grafikler #539'un son kısmında yer alıyor

• SQLite'ın varsayılan ayarlarına her baktığımda şaşırıyorum. WAL, foreign key, synchronous=NORMAL gibi ayarları varsayılan sunan yeni proje modu olsa iyi olurdu; bunun dışında da iyileştirilebilecek başka varsayılanlar var gibi görünüyor

  • Disk değişikliklerini zamanlama ya da biçimlendirme tarafında HDD için avantajlı ama NVMe için epey uygunsuz kalan bir nokta da vardı diye silik biçimde hatırlıyorum

• SQLite veritabanı için yedekleme stratejisinin ne olduğunu merak ediyorum. litestream gibi bir araç mı kullanılıyor, yoksa başka bir yöntem mi var bilmek isterim

  • Her gece çalışan görev restic'i çağırıyor

• lobste.rs'te güncelleme işleri nasıl ele alınıyor, merak ediyorum

  • SQLite'ın update ifadesi kullanılıyor. Başka tür bir güncellemeyi kastediyorsanız lütfen daha spesifik olun

• SQLite'ta zaten iif fonksiyonu var, ama aynı alanda kullanıcı tanımlı fonksiyonlar çalıştırma fikri de başlı başına ilginç

• SQLite'ın PostgreSQL ya da MySQL düzeyinde eşzamanlılık desteklemediğini sanıyordum, ancak genel bakış belgesine bakınca artık birden fazla proses tarafından kullanılabildiğini görüyorum; bunu lobste.rs'in yine de tek sunucuda çalışması gerektiği şeklinde mi anlamalıyım
  Prosesler paylaşımlı bellek veya mmap benzeri bir mekanizmayla SQLite içinde senkronize olup aynı anda yalnızca tek yazmaya mı izin veriyor ve birden çok okumayı mı destekliyor; bekleyen yazma işleri de sırayla kuyrukta mı bekliyor, onu da merak ediyorum

  • SQLite SSS bazı sorulara yanıt verebilir. Birden fazla proseste birden çok okuma işlemi yapılabilir, ancak yazma aynı anda yalnızca bir tane olabilir; SQLite bunu dosya sistemi kilitlemesiyle garanti eder
    Yazma bekleme tarafında açık bir paylaşımlı bellek kuyruğu yerine kısa süre bekleyip yeniden deneme yaklaşımı kullanılıyor gibi görünüyor. SQLite'ı birden fazla makinede kullanmanın tamamen imkansız olup olmadığından emin değilim, ancak dosyayı NFS ile paylaşma gibi basit yaklaşım açıkça önerilmediği için tek sunucu diye düşünmek makul bir yaklaşık olur; zaten günümüzde tek makineyle de oldukça büyük ölçekler taşınabiliyor
  • Yazma işlemlerini bir ölçüde bekletebiliyor, ancak varsayılan ayarlar iyi değil, ince ayar gerekiyor. Şu anda kullanılan ayarlar şöyle; ayrıca busy_timeout'un çalışması için immediate transaction mode da etkinleştirilmeli

    "PRAGMA foreign_keys=ON;"  
    "PRAGMA journal_mode = WAL;"  
    "PRAGMA synchronous = NORMAL;"  
    "PRAGMA busy_timeout = 5000;"  
    "PRAGMA temp_store = MEMORY;"  
    "PRAGMA mmap_size = 134217728;"  
    "PRAGMA journal_size_limit = 67108864;"  
    "PRAGMA cache_size = 2000;"  
    

    Başvuru için https://fractaledmind.github.io/2024/04/… ile https://kerkour.com/sqlite-for-servers ve https://www.sqlite.org/lang_transaction.html#immediate bağlantılarına bakılabilir; ayrıntılar özellikle ikinci bağlantıdaki SQLITE_BUSY bölümünde ve üçüncü bağlantıda yer alıyor

• PR, issue ve Gist'te ayrıntıları çok iyi belgelenmiş, etkileyici bir yükseltme olmuş. Bu değişiklikle site içi arama gibi işlevsel farklar ortaya çıktı mı ve şu anki SQLite dosyasının diskteki boyutu ne kadar, merak ediyorum

  • Arama sonuçları için SQLite'ın sıralama hesaplama özelliği kullanılıyor; bu yüzden MariaDB'deki sonuç sırasından farklı olabilir. Arama testleri var ama sıralamayı doğrulayan testler az, dolayısıyla işlevsel değişiklik olasılığı var; ancak henüz buna dair kanıt yok
    SQLite dosya boyutunun bir yerde 3.8GB olarak söylendiğini hatırlıyorum