SQLite PRAGMA Ayarları: Production İçin Optimize Bağlantı

PRAGMA: SQLite Motoruyla Konuşma Şeklin

PRAGMA, SQLite'a özgü bir komuttur; motorun nasıl davrandığını okumanı ya da değiştirmeni sağlar. Tıpkı diğer SQL ifadeleri gibi çalıştırırsınız — ama verilerine değil, veritabanının ayarlarına dokunur.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

PRAGMA komutunu sorgu olarak çalıştırdığınızda mevcut değeri döner; atama şeklinde çalıştırdığınızda ise değeri değiştirir:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

Mantık şu: PRAGMA'ların büyük çoğunluğu bağlantıya özeldir. Yeni bir bağlantı açtığınızda varsayılanlar geri gelir. Bu yüzden production kodlarında genellikle her bağlantı kurulduğunda hemen çalıştırılan küçük bir PRAGMA bloğu bulunur.

Production İçin Temel SQLite PRAGMA Ayarları

Aklınızda yalnızca beş PRAGMA tutacaksanız, şunlar olsun:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;

SELECT 'yapılandırıldı' AS status;

Çoğu uygulamada SQLite'ı birincil veri deposu olarak kullanıyorsanız, bunlar gayet makul varsayılanlardır. Her birini ayrı ayrı anlamakta fayda var — sayfanın geri kalanında bunları tek tek ele alacağız.

journal_mode = WAL

Journal modu, SQLite'ın yazma işlemlerini kalıcı hale getirme şeklini belirler. Varsayılan olan DELETE, geri alma günlüğü (rollback journal) kullanır: yazıcılar okuyucuları, okuyucular da yazıcıları bloklar. Bir CLI aracı için sorun değil ama bir web uygulamasında başınıza bela olur.

WAL (Write-Ahead Logging) bu durumu tersine çevirir. Okuyucular ile yazıcılar birbirini bloklamaz — bir yazıcı commit yaparken okuyucular tutarlı bir snapshot görmeye devam eder. Yine aynı anda tek bir yazıcı olur, ama yoğun yük altında okumalar hızlı kalır.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

Bilmeniz gereken birkaç şey:

• journal_mode kalıcıdır — bir kez ayarladığınızda, veritabanı dosyası için öyle kalır. Her bağlantıda tekrar ayarlamanıza gerek yok, ama ayarlasanız da bir zararı olmaz.
• WAL, .db dosyanızın yanında iki ek dosya oluşturur: bir -wal ve bir -shm. Veritabanı açıkken bunları silmeyin.
• WAL, ağ dosya sistemlerinde (NFS, SMB) iyi çalışmaz. Veritabanını yerel diskte tutun.

WAL modu ve eşzamanlılık konusunu daha derinlemesine ele alan ayrı bir doküman var. Şimdilik şu kadarı yeter: açın gitsin.

synchronous = NORMAL

synchronous ayarı, SQLite'ın diske ne kadar agresif şekilde yazma (flush) yapacağını belirler. Buradaki denge, dayanıklılık ile hız arasında.

• FULL (varsayılan) — her commit'ten sonra diske yazar. Maksimum dayanıklılık. Daha yavaş.
• NORMAL — güvenli kontrol noktalarında diske yazar. WAL ile birlikte güvenlidir. Daha hızlı.
• OFF — kararı işletim sistemine bırakır. Hızlıdır ama elektrik kesintisinde bozulma riski vardır.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

Sonuçtaki sayı (2), NORMAL değerine karşılık geliyor. WAL modunda önerilen ayar NORMAL'dir — çökme durumunda commit edilmiş işlemleri kaybetmezsiniz; sadece elektrik kesintisinde en son birkaç işlemi kaybetme riski olur. Çoğu uygulama için doğru denge bu.

Tek kullanımlık, sıfırdan yeniden oluşturabileceğiniz bir veritabanı doldurmuyorsanız OFF kullanmayın.

foreign_keys = ON

İşte burası çoğu kişiyi yanıltıyor. SQLite foreign key destekler, ama bu desteğin zorlanması varsayılan olarak kapalıdır ve üstelik her bağlantı için ayrı ayrı ayarlanır:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('Hayaletler', 999);

foreign_keys = ON açıkken bu son insert sorgusu hata verir — id'si 999 olan bir yazar yok çünkü. PRAGMA olmadan SQLite o yetim satırı sessiz sedasız yazar, siz de aylar sonra ortalığın ne kadar dağıldığını fark edersiniz.

Açtığınız her yeni bağlantıda ilk iş olarak PRAGMA foreign_keys = ON; çalıştırın. ORM kullanıyorsanız çoğu bunu sizin yerinize halleder; ama ham sürücüyle çalışıyorsanız sorumluluk sizde.

busy_timeout = 5000

SQLite aynı anda tek bir yazıcıya izin verir. İlk bağlantı transaction ortasındayken ikinci bir bağlantı yazmaya kalkışırsa, varsayılan davranışta SQLITE_BUSY döner ve hemen pes eder.

busy_timeout ise SQLite'a "hemen vazgeçme, biraz bekle ve tekrar dene" demenin yolu:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

Değer milisaniye cinsinden. 5000 demek, "kilit için en fazla 5 saniye bekle, sonra vazgeç" anlamına geliyor. WAL ile birleştiğinde, eşzamanlı uygulamalarda karşılaşılan database is locked hatalarının büyük çoğunluğunu ortadan kaldırır.

Eğer bu değeri 30 saniyenin üzerine çıkarmak zorunda kalıyorsanız, asıl çözüm büyük ihtimalle timeout'u uzatmak değil, transaction'larınızı kısaltmaktır.

cache_size

cache_size, SQLite'ın bellekte tuttuğu veritabanı sayfası sayısını belirler. Daha fazla önbellek, daha az disk okuması demek; bu da sık erişilen veri üzerindeki sorguların daha hızlı çalışması anlamına gelir.

Değerin iki farklı kullanım biçimi var:

• Pozitif sayı — sayfa sayısı. Varsayılan 4 KB sayfa boyutuyla 2000 değeri 8 MB'a denk gelir.
• Negatif sayı — kibibayt cinsinden. -20000 ise sayfa boyutundan bağımsız olarak 20 MB demektir.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

Negatif form üzerinden düşünmek daha kolay — sayfa boyutuyla hesap yapmaktansa doğrudan "bana 20 MB cache ver" demiş oluyorsunuz. Küçük bir uygulama için 20–50 MB fazlasıyla yeter. Daha büyük bir veritabanında okuma ağırlıklı bir iş yükün varsa bu değeri rahatlıkla yukarı çekebilirsiniz. synchronous gibi cache_size de bağlantı bazında çalışır.

mmap_size

Memory-mapped I/O sayesinde SQLite, veritabanı dosyasının bir kısmını doğrudan işletim sisteminin sayfa önbelleğinden okuyabiliyor; böylece bir kopyalama adımı atlanmış oluyor. Büyük veritabanlarında okuma performansını ciddi şekilde artırabilir:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

Yani 256 MB. Eğer yer varsa SQLite veritabanının bu kadarını belleğe map'ler. Sayfalama işini OS hallediyor, dolayısıyla 256 MB'ı baştan ayırmıyorsunuz — sadece o kadarına kadar map'lemeye izin vermiş oluyorsunuz.

mmap_size, okuma ağırlıklı işlerde gerçekten parlıyor. Küçük veritabanlarında da bir zararı yok. Varsayılanlar oldukça temkinli olduğu için bu değeri yükseltmek genelde işine yarar.

PRAGMA optimize kullanımı

Sorgu planlayıcı, hangi indeksi kullanacağına karar verirken istatistiklere bakar. İstatistikler eskidiyse planlar da kötü çıkar. PRAGMA optimize bu istatistikleri çok düşük maliyetle günceller:

PRAGMA optimize;

Önerilen yaklaşım, bunu uzun ömürlü bağlantıları kapatmadan hemen önce çalıştırmaktır — uygulama kapanışında ya da bağlantıyı bir süre elinizde tutan request handler'ın sonunda. Hızlıdır (genelde milisaniyeler mertebesinde) ve yalnızca gerçekten güncellenmesi gereken bir şey varsa iş yapar.

Bunu ANALYZE ile karıştırmamak lazım; ANALYZE istatistikleri sıfırdan baştan kurar. optimize ise onun sık sık çalıştırılabilen, hafif sıklet kuzeni gibi düşünülebilir.

Tüm Ayarları Okumak

Bir bağlantının o anki PRAGMA ayarlarını görmek istiyorsanız, atama yapmadan PRAGMA'ları sorgulamanız yeterli:

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

Hata ayıklarken işe yarar — farklı bir sürücüden bağlanıp davranışın neden değiştiğini merak ettiğiniz durumların altından neredeyse her zaman bir PRAGMA farkı çıkar.

Bir de PRAGMA pragma_list; var; bu da derlemenin desteklediği tüm PRAGMA'ları döker:

PRAGMA pragma_list;

Ezbere bilmeniz gereken bir şey değil ama lazım olduğunda elinizin altında dursun.

Çalışma Anında Değil, CREATE Aşamasında Yapılması Gereken Ayarlar

Bazı PRAGMA'lar doğrudan veritabanı dosyasının kendisini yapılandırır ve yalnızca herhangi bir tablo oluşturulmadan önce etkili olur:

• PRAGMA page_size = 8192; — diskteki sayfa boyutu. Varsayılan 4096'dır ve çoğu senaryo için yeterlidir. Sayfa boyutunu büyütmek, geniş satırlarla çalışırken işe yarar.
• PRAGMA encoding = 'UTF-8'; — metin kodlaması.

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

Mevcut bir veritabanında page_size değerini değiştirirseniz, bunun devreye girmesi için VACUUM çalıştırmanız gerekir. Bu ayarları veritabanını oluştururken bir kez verin ve bir daha dönüp bakmayın.

Gerçek Bir Bağlantı Kurulum Örneği

Uygulama kodunda bu tarz ayarlar genellikle bağlantıyı açan kısımda yer alır. Mantık olarak şöyle düşünün:

-- Her yeni bağlantıda bir kez çalıştırın:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- Periyodik olarak veya kapatmadan önce çalıştırın:
PRAGMA optimize;

temp_store = MEMORY ayarı, geçici tablo ve indeksleri RAM'de tuttuğu için indekssiz sıralama veya toplama (aggregate) gerektiren sorguları belirgin biçimde hızlandırır.

İşte production için tüm checklist bu kadar. Topu topu yarım düzine satırla SQLite, "geliştirme için yeterli" seviyesinden "gerçek bir yükü kaldırabilir" seviyesine çıkıyor.

Sırada: Sık Karşılaşılan Hatalar

PRAGMA ayarlarını ne kadar iyi yaparsanız yapın, klasik SQLite hatalarıyla yine de tanışacaksınız: database is locked, disk I/O error, constraint failed. Sonraki sayfada bu hataların gerçekte ne anlama geldiğini ve nasıl çözüleceğini tek tek ele alacağız.