SQLite Performans Ayarları: İşlem Gruplama, WAL, synchronous, cache_size ve mmap

1. Yazmaları tek bir işlemde gruplayın

SQLite SSS sayfası, "INSERT çok yavaş, saniyede ancak birkaç düzine yapabiliyorum" sorusuna şöyle cevap verir: SQLite saniyede çok sayıda INSERT ifadesini rahatça işler, ama saniyede yapabileceği işlem (transaction) sayısı çok daha azdır. Çünkü varsayılan ayarlarda her işlem, verinin diske gerçekten ve güvenle yazıldığını bekleyerek tamamlanır; bu bekleme elektrik kesintisinde veya işletim sistemi çöktüğünde verinizi korur. Ayrıca bir işlem başlatmazsanız her INSERT kendi işlemidir.

CREATE TABLE log(id INTEGER PRIMARY KEY, mesaj TEXT);
.timer on

-- Yavaş: her INSERT kendi işlemi, her biri ayrı commit ve disk eşitlemesi
INSERT INTO log(mesaj) VALUES ('tekil 1');
INSERT INTO log(mesaj) VALUES ('tekil 2');
-- ... 1000 satır

-- Hızlı: aynı 1000 INSERT tek bir işlem içinde, tek commit
BEGIN;
INSERT INTO log(mesaj) VALUES ('toplu 1');
INSERT INTO log(mesaj) VALUES ('toplu 2');
-- ... 1000 satır
COMMIT;

Birçok ifadeyi BEGIN ... COMMIT içine aldığınızda commit maliyeti bütün ifadelere paylaştırılır ve ifade başına süre büyük ölçüde düşer. SSS sayfasının 2024 tarihli güncellemesi, eski cevaptaki sayıların artık geçerli olmadığını, SSD'ler sayesinde her şeyin çok daha hızlı olduğunu, ancak ana fikrin değişmediğini vurgular. Deneme ortamımızda tek tek yapılan 1000 INSERT birkaç saniye sürerken, aynı 1000 INSERT tek bir işlemde milisaniyeler içinde bitti; sizin donanımınızda sayılar farklı olacaktır ama aradaki fark genellikle açıktır. Hata durumunda işlemin nasıl geri alınacağı transaction yazısında anlatılıyor.

2. WAL kipi ve synchronous ayarı

synchronous, SQLite'ın kritik anlarda işletim sistemine "veriyi gerçekten diske yaz" diye ne sıklıkla çağrı yapacağını belirler. Dört değeri vardır ve sorgulandığında sayı döndürür:

PRAGMA synchronous;               -- bu derlemede varsayılan FULL
-- çıktı: 2
PRAGMA synchronous = NORMAL;
PRAGMA synchronous;
-- çıktı: 1
PRAGMA synchronous = OFF;
PRAGMA synchronous;
-- çıktı: 0

Belgelerdeki bu tablo seçimin özünü verir. FULL rollback kipinde varsayılandır. NORMAL ayarında SQLite yine en kritik anlarda eşitleme yapar ama FULL'dan daha seyrek; WAL kipinde NORMAL bozulmaya karşı güvenlidir ve her zaman tutarlıdır, yalnızca dayanıklılıktan ödün verir: elektrik kesintisi veya sistem çökmesinden sonra son işlenen işlem geri alınmış olabilir. Belgeler WAL kipinde çalışan çoğu uygulama için en iyi performans ve güvenlik dengesinin NORMAL olduğunu söyler. Uygulamanın kendisinin çökmesi ise hangi ayar olursa olsun işlenmiş işlemleri kaybettirmez.

WAL kipinde NORMAL ile çoğu işlemde hiç eşitleme yapılmaz; WAL dosyası checkpoint öncesinde, veritabanı dosyası checkpoint sonrasında eşitlenir. FULL ise her commit sonrası WAL'ı ayrıca eşitler. Belgeler, checkpoint ayrı bir iş parçacığında çalıştırıldığında ana iş parçacığının hiç sync beklemeyeceğini de not eder. EXTRA, rollback kipinde günlük silindikten sonra dizini de eşitleyerek commit'in hemen ardından gelen bir elektrik kesintisine karşı ek dayanıklılık sağlar; WAL kipinde FULL'dan farkı yoktur. WAL'ın kendisi WAL modu yazısında ayrıntılı anlatılıyor.

3. Bellek ayarları: cache_size, temp_store, mmap_size

PRAGMA cache_size;                -- negatif: KiB cinsinden, yaklaşık 2 MB
-- çıktı: -2000
PRAGMA cache_size = -20000;       -- yaklaşık 20 MB
PRAGMA cache_size;
-- çıktı: -20000

PRAGMA temp_store = MEMORY;
PRAGMA temp_store;
-- çıktı: 2

PRAGMA mmap_size = 268435456;     -- 256 MiB
-- çıktı: 268435456

cache_size

cache_size, her açık veritabanı dosyası için bellekte tutulacak en fazla sayfa sayısı önerisidir. Pozitif değer sayfa sayısıdır; negatif değer, yaklaşık abs(N*1024) bayt kullanacak kadar sayfa demektir. Varsayılan -2000, yani yaklaşık 2 MB'tır. Belgeler üç noktayı vurgular: değişiklik yalnızca o bağlantının oturumu boyunca sürer; varsayılan önbellek belleği bir kerede ayırmaz, gerektikçe parça parça alır, yani bu değer bir üst sınırdır; ve SQLite bellek miktarını değil sayfa sayısını hatırlar, bu yüzden sayfa boyutunu sonradan değiştirirseniz bellek üst sınırı da orantılı değişir. Sık okunan çalışma kümeniz varsayılan önbelleğe sığmıyorsa bu değeri artırmak disk okumalarını azaltabilir; çok sayıda bağlantı açıyorsanız toplam belleği hesaba katın.

temp_store

Geçici tablolar, geçici indeksler ve büyük sıralamalar için kullanılan geçici veritabanının dosyada mı bellekte mi tutulacağını belirler. MEMORY (2) ile geçici nesneler saf bellek içi veritabanındaki gibi tutulur. Belgelerdeki tabloya göre derleme sırasındaki SQLITE_TEMP_STORE değeri bu pragmayı geçersiz kılabilir: 0 ile derlenmiş bir kütüphane her durumda dosya, 3 ile derlenmiş olan her durumda bellek kullanır. Ayar değiştiğinde mevcut geçici tablolar, indeksler, tetikleyiciler ve görünümler hemen silinir. Sorgu planında USE TEMP B-TREE sık görüyorsanız önce uygun bir indeksle bu sıralamayı tamamen ortadan kaldırmayı deneyin; bkz. EXPLAIN QUERY PLAN.

mmap_size

Bellek eşlemeli giriş/çıkış (memory-mapped I/O) açıldığında SQLite veritabanı sayfalarını çekirdek ile kullanıcı alanı arasında kopyalamadan doğrudan işletim sisteminin sayfa önbelleğinden okur. Belgelere göre avantajları, özellikle giriş/çıkış yoğun işlemlerin hızlanabilmesi ve SQLite'ın daha az RAM'e ihtiyaç duymasıdır. Yazmalarda kazanç azdır: SQLite bir sayfayı değiştirmeden önce her zaman yığın belleğe kopyalar; mmap esas olarak sorgulara yarar.

Dezavantajları da belgelerde açıkça sayılır: bellek eşlemeli bir dosyadaki giriş/çıkış hatası SQLite tarafından yakalanamaz ve yakalanmazsa programı çökerten bir sinyale dönüşür; işletim sisteminin birleşik bir tampon önbelleği olması gerekir ve bazı sistemlerde bu uygulama hatalıdır; mmap her zaman hız kazandırmaz, yavaşlattığı durumlar da kurulabilir; Windows bellek eşlemeli bir dosyayı kesemediği için VACUUM gibi dosyayı küçülten işlemler orada sessizce başarısız olur ve dosya sonunda kullanılmayan alan kalır. Bu nedenlerle varsayılan değer genellikle sıfırdır, yani özellik kapalıdır. Açmak için genellikle 256 MB veya daha büyük bir değer verilir; değer her veritabanı dosyası için ayrı uygulanır, bu yüzden kullanılabilecek toplam adres alanı, değerin açık dosya sayısıyla çarpımı kadardır. Belgeler değerin, uygulamanızın ne kadar adres alanı ayırabildiğine göre seçilmesini söyler. Derleme sırasında konan bir üst sınır pragmayla aşılamaz ve mmap'i desteklemeyen sistemlerde pragma sessizce hiçbir şey yapmaz.

4. Sayfa boyutu

page_size, 512 ile 65536 arasında ikinin kuvveti olmalıdır. 3.12.0 sürümünden beri varsayılan 4096 bayttır ve belgeler çoğu uygulama için varsayılanı önerir. Yeni değer hemen uygulanmaz: dosya henüz yoksa oluşturulurken, varsa WAL kipinde değilken çalıştırılacak bir sonraki VACUUM ile uygulanır.

-- sqlite3 yeni.db (dosya henüz yok)
PRAGMA page_size;
-- çıktı: 4096
PRAGMA page_size = 8192;          -- dosya oluşturulurken kullanılacak
CREATE TABLE t(x);
PRAGMA page_size;
-- çıktı: 8192

PRAGMA page_size = 16384;         -- mevcut dosyada ancak VACUUM ile uygulanır
VACUUM;
PRAGMA page_size;
-- çıktı: 16384

PRAGMA journal_mode = WAL;
-- çıktı: wal
PRAGMA page_size = 4096;
VACUUM;
PRAGMA page_size;                 -- WAL kipinde değişmedi
-- çıktı: 16384

Son adımda WAL kipindeki dosyada VACUUM sayfa boyutunu değiştirmedi; WAL belgesi de bu sınırlamayı açıkça listeler. Sayfa boyutunu değiştirmek istiyorsanız önce journal_mode=DELETE ile rollback kipine dönün, VACUUM çalıştırın, sonra WAL'a geri geçin. VACUUM'un kendisi VACUUM yazısında anlatılıyor.

5. İndeksler ve istatistikler

Belgelerin planlayıcı kontrol listesi, SQL performans sorunlarının çoğunun planlayıcıdan değil uygun indekslerin eksikliğinden kaynaklandığını ve çoğunun bir iki CREATE INDEX ile uygulama koduna dokunmadan çözülebildiğini söyler. Yavaş bir sorgunun ilk teşhis aracı EXPLAIN QUERY PLAN, ikinci adımı ise planlayıcının güncel istatistiklere sahip olmasını sağlayan PRAGMA optimize komutudur. Ayrıntılar için EXPLAIN QUERY PLAN, ANALYZE ve sorgu planlayıcı ve indeks oluşturma yazılarına bakın. Genel sorgu yazım ilkeleri SQL sorgu optimizasyonu rehberimizde.

6. N+1 sorgu SQLite'ta neden sorun değil?

İstemci-sunucu veritabanlarında her SQL ifadesi uygulama ile sunucu arasında bir mesaj gidiş dönüşü gerektirir; bir sayfada yüzlerce küçük sorgu çalıştırmak bu yüzden "N+1 sorgu sorunu" olarak bilinen bir karşı kalıptır. SQLite belgeleri bu durumun SQLite için geçerli olmadığını açıklar: SQLite uygulamayla aynı süreçte çalışır, sorgu bir mesaj değil bir fonksiyon çağrısıdır ve tek sorgunun gecikmesi çok düşüktür. Belgeler, SQLite web sitesindeki dinamik sayfaların sayfa başına yaklaşık 200 SQL ifadesi çalıştırdığını ve bunun sorun yaratmadığını örnek verir.

Bu, karmaşık tek bir sorgu yerine çok sayıda basit sorgu yazmanın her zaman doğru olduğu anlamına gelmez; belgeler SQLite'ın büyük ve karmaşık sorguları da verimli çalıştırabildiğini vurgular. Pratik sonuç şudur: kodunuzun okunabilirliği için sorguları bölmeyi seçebilirsiniz ve ORM'lerin ürettiği çok sayıda küçük sorgu tek başına performans sorunu sayılmaz. Asıl dikkat edeceğiniz şey her sorgunun indeks kullanmasıdır.

7. Küçük dosyaları veritabanında saklamak

Belgelerdeki bir çalışma, küçük blob'ların (örneğin küçük resimlerin) SQLite'tan okunup yazılmasının aynı içeriği ayrı dosyalarda tutup tek tek açıp kapatmaktan genellikle daha hızlı olduğunu ve 10 KB'lık blob'ları tutan bir veritabanının ayrı dosyalara göre daha az disk alanı kullandığını bildirir. Belgeler farkın, dosyaların her biri için yapılan açma ve kapama çağrılarından ve dosya sistemi blok boyutuna yuvarlanan boşluklardan kaynaklandığını düşünür. Aynı belge, sonuçların donanıma, işletim sistemine ve soğuk ya da sıcak önbelleğe göre değiştiğini ve bazı okurların farklı sonuç ölçtüğünü de not eder. Doğru çıkarım şudur: SQLite'ın okuma ve yazma gecikmesi tek tek dosyalarla rekabet edebilir düzeydedir.

8. Ölçmeden ayar yapmayın

Komut satırı aracındaki .timer on her SQL ifadesinden sonra geçen gerçek süreyi ve işlemci süresini yazar. 3.51.0 sürüm notlarına göre bu ölçümün hassasiyeti mikrosaniyeye çıkarıldı; 3.53.0 sürüm notlarına göre ise .timer once yalnızca bir sonraki ifadeyi ölçer. Deneme ortamımızdaki 3.50.6 aracında bu ikinci özellik bulunmadığı için çalıştırmadık.

.timer on          -- her ifadeden sonra süreyi yazar
.timer off

-- SQLite 3.53.0 ve sonrası (deneme ortamımızda çalıştırılamadı):
.timer once        -- yalnızca bir sonraki SQL ifadesini ölçer

Ölçerken iki şeye dikkat edin. Birincisi, ilk çalıştırmada veri diskten, sonrakilerde işletim sisteminin önbelleğinden okunur; tek bir ölçüme güvenmeyin. İkincisi, boş veya küçük bir test veritabanında ölçülen süre ve seçilen plan, gerçek boyuttaki veriyle aynı olmayabilir.

Uygulamalı örnek: bir web uygulaması için açılış ayarları

Aşağıdaki oturum, tek bir sunucuda çalışan ve birden çok iş parçacığı veya süreçle aynı dosyaya erişen tipik bir web uygulaması için makul bir başlangıç noktasıdır. Ayarlar bağlantıya özel olduğu için uygulamanızın her bağlantıyı açtıktan sonra bunları çalıştırması gerekir. Son sorgu, dört ayarı pragma fonksiyonlarıyla tek satırda doğrular:

-- sqlite3 app.db
PRAGMA journal_mode = WAL;
-- çıktı: wal
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
-- çıktı: 5000
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;
PRAGMA foreign_keys = ON;
PRAGMA optimize = 0x10002;

SELECT journal_mode, synchronous, cache_size, temp_store
  FROM pragma_journal_mode, pragma_synchronous, pragma_cache_size, pragma_temp_store;
-- çıktı: wal|1|-20000|2

Burada WAL ve NORMAL birlikte yazma hızını ve okuyucu-yazar eşzamanlılığını artırır; busy_timeout kısa süreli kilit çakışmalarında hemen hata vermek yerine bekler; cache_size önbelleği yaklaşık 20 MB'a çıkarır; temp_store geçici sıralamaları belleğe alır. Uzun ömürlü bağlantı açılışında PRAGMA optimize=0x10002 istatistikleri gerekiyorsa tazeler. mmap_size'ı yalnızca ölçüm yapıp fayda gördüyseniz ve yukarıdaki riskleri kabul ediyorsanız ekleyin. Bu ayarlar sunucunuzun yerel diskinde duran bir veritabanı içindir; ağ paylaşımları için geçerli değildir.

Sık yapılan hatalar

• Tek tek commit edilen binlerce INSERT: En büyük yavaşlık kaynağıdır; BEGIN ... COMMIT kullanın.
• Canlı veritabanında synchronous=OFF veya journal_mode=OFF/MEMORY: Hız kazandırır ama elektrik veya işletim sistemi kesintisinde bozulma riski taşır.
• cache_size'ın kalıcı olduğunu sanmak: Her bağlantıda yeniden ayarlanmalıdır.
• WAL kipindeyken page_size değiştirmeye çalışmak: Değişiklik uygulanmaz.
• Ölçmeden mmap açmak: Belgelere göre her zaman hız kazandırmaz ve hata durumunda süreci çökertebilir.
• İndeks eksikliğini pragma ile çözmeye çalışmak: Tam tarama yapan bir sorguyu hiçbir önbellek ayarı uygun bir indeks kadar hızlandıramaz.

Sık Sorulan Sorular

En büyük kazancı hangi ayar sağlar?

Belgelerin vurguladığı iki şey yazmaları işlemlerde gruplamak ve uygun indeksleri oluşturmaktır. Pragma ayarları bunların yerine geçmez, üzerine eklenir.

synchronous=NORMAL veri kaybına yol açar mı?

WAL kipinde veritabanı bozulmaz ve tutarlı kalır, ancak elektrik kesintisi veya sistem çökmesinden hemen önce işlenen son işlemler kaybolabilir. Bu kabul edilemezse FULL kullanın.

cache_size'ı ne kadar büyütmeliyim?

Belgeler tek bir değer önermez. Sık eriştiğiniz verinin boyutunu, bağlantı sayınızı ve sunucunuzdaki boş belleği düşünerek seçin ve ölçerek ayarlayın.

Bu ayarlar programlama dilimin kütüphanesinde de geçerli mi?

Evet, pragmalar SQL komutu olduğu için her bağlayıcıdan çalıştırılabilir. Kütüphanenizin kendi işlem yönetimi için Python, Node.js ve PHP PDO yazılarımıza bakın.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · SQLite WAL modu · SQLite PRAGMA komutları · SQLite transaction · SQLite EXPLAIN QUERY PLAN · SQLite ANALYZE