SQLite VACUUM ve auto_vacuum: Boş Sayfaları Geri Kazanma

Silinen veri dosyada ne olur

SQLite veritabanı dosyası eşit boyutlu sayfalardan oluşur; tablolar ve indeksler bu sayfalara yerleştirilmiş B-ağaçlarıdır. Belgelere göre auto_vacuum kapalıyken veri silindiğinde dosya aynı boyutta kalır, kullanılmayan sayfalar freelist'e eklenir ve sonraki eklemelerde yeniden kullanılır. Yani alan kaybolmaz, ama dosya da küçülmez. Aşağıdaki deneme bunu sayılarla gösteriyor. generate_series tablo değerli fonksiyonu komut satırı aracında hazır gelir; kendi ortamınızda yoksa satırları başka bir yolla ekleyebilirsiniz.

PRAGMA auto_vacuum = NONE;   -- standart varsayılan; tablo oluşturmadan önce açıkça belirtiyoruz
CREATE TABLE kayit(id INTEGER PRIMARY KEY, metin TEXT);
INSERT INTO kayit(metin) SELECT printf('%.200c', 'x') FROM generate_series(1, 2000);
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
-- çıktı: 108|0

DELETE FROM kayit WHERE id > 200;
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
-- çıktı: 108|95

INSERT INTO kayit(metin) SELECT printf('%.200c', 'y') FROM generate_series(1, 300);
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
-- çıktı: 108|79

VACUUM;
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
-- çıktı: 29|0

2000 satır eklendiğinde dosya 108 sayfadır. Satırların onda dokuzu silindiğinde sayfa sayısı değişmez, ancak 95 sayfa boşa çıkıp freelist'e geçer. Ardından eklenen 300 satır yeni sayfa istemez; 16 boş sayfa yeniden kullanılır ve freelist 79'a iner. VACUUM sonrasında ise dosya 29 sayfaya küçülür ve freelist sıfırlanır. Sayfa boyutu 4096 bayt olduğundan bu, yaklaşık 442 KB'lık bir dosyanın 119 KB'a inmesi demektir.

VACUUM ne yapar ve ne zaman gerekir

Belgelere göre VACUUM veritabanının içeriğini geçici bir veritabanı dosyasına kopyalar, ardından özgün dosyanın üzerine bu geçici dosyanın içeriğini yazar. Üzerine yazma sırasında, sıradan her işlemde olduğu gibi geri alma günlüğü ya da WAL dosyası kullanılır; böylece işlem yarıda kesilse bile veritabanı tutarlı kalır. Bunun bedeli disk alanıdır: çalışma sırasında özgün dosyanın iki katı kadar boş alana ihtiyaç duyulabilir. Büyük bir veritabanında VACUUM çalıştırmadan önce diskte yeterli yer olduğundan emin olun.

Belgeler komutun dört gerekçesini sayar. Birincisi, çok sayıda satır silindikten sonra boş sayfaları geri vererek dosyayı küçültmektir. İkincisi parçalanmayı gidermektir: sık ekleme, güncelleme ve silme bir tablonun ya da indeksin verisini dosyanın farklı yerlerine dağıtır; VACUUM her nesneyi büyük ölçüde ardışık sayfalara yerleştirir ve kısmen dolu sayfaların sayısını azaltabilir. Üçüncüsü silinen içeriğin izlerini temizlemektir; silinen veri normalde silinmez, yalnızca alanı yeniden kullanılabilir olarak işaretlenir ve adli inceleme ile geri getirilebilir. Dördüncüsü ise sayfa boyutu ve auto_vacuum gibi normalde dosya oluşturulurken belirlenen özellikleri sonradan değiştirmektir.

Silinen verinin izleri konusunda alternatif PRAGMA secure_delete ayarıdır. Bu ayar açıkken SQLite silinen içeriğin üzerine sıfır yazar; FAST değeri yalnızca ek disk G/Ç gerektirmeyen durumlarda üzerine yazar ve freelist sayfalarında iz bırakabilir. Belgeler bu ayarın sanal tabloların gölge tablolarındaki içeriği, örneğin FTS5 indekslerindeki kopyaları, temizlemeyebileceğini de not eder. Varsayılan değer SQLITE_SECURE_DELETE derleme seçeneğine bağlıdır ve normalde kapalıdır; kendi ortamınızda PRAGMA secure_delete ile okuyun.

Her veritabanının düzenli VACUUM istemesi gerekmez. Freelist zaten yeniden kullanıldığı için, büyüyüp küçülen ama genel olarak benzer boyutta kalan bir veritabanında dosyayı sürekli küçültmek kalıcı bir kazanç sağlamaz; dosya kısa sürede yeniden büyür. Komutu büyük bir temizlikten sonra, arşivleme işleminin ardından ya da dbstat ölçümleri sayfaların ciddi biçimde boş veya dağınık olduğunu gösterdiğinde çalıştırmak daha mantıklıdır.

auto_vacuum modları

PRAGMA auto_vacuum üç değer alır: 0 veya NONE, 1 veya FULL, 2 veya INCREMENTAL. FULL modunda her işlem onaylandığında freelist sayfaları dosyanın sonuna taşınır ve dosya bu sayfalar kadar kısaltılır. Belgeler bunun bir bedeli olduğunu açıkça yazar: auto_vacuum yalnızca boş sayfaları keser, veritabanını birleştirmez ve sayfaları yeniden paketlemez; sayfaları dosya içinde taşıdığı için parçalanmayı artırabilir de.

PRAGMA auto_vacuum = FULL;   -- yeni, boş bir veritabanında
CREATE TABLE kayit(id INTEGER PRIMARY KEY, metin TEXT);
INSERT INTO kayit(metin) SELECT printf('%.200c', 'x') FROM generate_series(1, 2000);
PRAGMA auto_vacuum;
-- çıktı: 1

DELETE FROM kayit WHERE id > 200;
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
-- çıktı: 14|0

Bu modun çalışabilmesi için her sayfanın kendisine işaret eden üst sayfaya geri izlenebilmesi gerekir ve bu ek bilgi dosyada saklanır. Bu yüzden auto_vacuum tablo oluşturulmadan önce açılmalıdır. Silmeden sonra dosya, ayrıca bir komut çalıştırmaya gerek kalmadan 14 sayfaya iner; freelist ise hiç birikmez.

INCREMENTAL modu ve incremental_vacuum

INCREMENTAL modunda gerekli ek bilgi yine dosyada tutulur, ancak boş sayfalar her işlemde otomatik olarak kesilmez. Kesme işini siz PRAGMA incremental_vacuum(N) ile istediğiniz zaman ve istediğiniz miktarda yaparsınız. N sayfa freelist'ten çıkarılır ve dosya aynı miktarda kısalır. N verilmezse, 1'den küçükse ya da freelist'te N'den az sayfa varsa bütün liste temizlenir. Veritabanı INCREMENTAL modda değilse komutun hiçbir etkisi olmaz.

PRAGMA auto_vacuum = INCREMENTAL;   -- yeni, boş bir veritabanında
CREATE TABLE kayit(id INTEGER PRIMARY KEY, metin TEXT);
INSERT INTO kayit(metin) SELECT printf('%.200c', 'x') FROM generate_series(1, 2000);
DELETE FROM kayit WHERE id > 200;
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
-- çıktı: 109|95

PRAGMA incremental_vacuum(50);
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
-- çıktı: 59|45

PRAGMA incremental_vacuum;
SELECT page_count, freelist_count FROM pragma_page_count, pragma_freelist_count;
-- çıktı: 14|0

İlk örnekte 108 sayfa olan dosyanın bu modda 109 sayfa olması rastlantı değildir: geri izleme bilgisi ayrı sayfalarda saklanır. Bu mod, uzun süren bir kesme işinin kullanıcı isteklerini bekletmesini istemediğiniz durumlar için uygundur. Örneğin bir arka plan görevi sistem boştayken birkaç yüz sayfa keserek dosyayı yavaş yavaş küçültebilir. Belgelere göre bir bağlantı FULL ve INCREMENTAL arasında her zaman geçiş yapabilir.

Var olan bir veritabanında modu değiştirmek

NONE modundan FULL veya INCREMENTAL moduna geçiş yalnızca henüz tablo içermeyen yeni bir veritabanında ya da VACUUM ile mümkündür. Ters yönde, FULL veya INCREMENTAL modundan NONE moduna dönmek boş bir veritabanında bile her zaman VACUUM gerektirir. Pragma ayarı önce hatırlanır ama uygulanmaz; değeri sorguladığınızda eski modu görürsünüz:

-- İlk örnekteki, tabloları zaten olan veritabanında:
PRAGMA auto_vacuum;
-- çıktı: 0

PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA auto_vacuum;
-- çıktı: 0

VACUUM;
PRAGMA auto_vacuum;
-- çıktı: 2

Sayfa boyutunu VACUUM ile değiştirmek

Sayfa boyutu 512 ile 65536 arasında ikinin bir kuvveti olmalıdır. Belgeler 3.12.0 sürümünden beri varsayılanın 4096 bayt olduğunu ve çoğu uygulama için varsayılan değerin önerildiğini belirtir. Yeni bir boyut verdiğinizde değişiklik hemen uygulanmaz; boyut ya veritabanı ilk oluşturulurken ya da WAL modunda değilken aynı bağlantıda çalıştırılan bir sonraki VACUUM sırasında kullanılır. WAL modunda VACUUM yalnızca auto_vacuum özelliğini değiştirebilir; sayfa boyutunu değiştirmek için önce geri alma günlüğü moduna dönmeniz gerekir.

PRAGMA page_size;
-- çıktı: 4096

PRAGMA page_size = 8192;
PRAGMA page_size;
-- çıktı: 4096

VACUUM;
PRAGMA page_size;
-- çıktı: 8192

-- WAL modunda sayfa boyutu değiştirilemez:
PRAGMA journal_mode = WAL;
-- çıktı: wal
PRAGMA page_size = 4096;
VACUUM;
PRAGMA page_size;
-- çıktı: 8192
PRAGMA journal_mode = DELETE;
-- çıktı: delete

VACUUM rowid değerlerini değiştirebilir

Belgelerdeki önemli bir uyarı şudur: VACUUM, açıkça tanımlanmış bir INTEGER PRIMARY KEY sütunu olmayan tablolarda rowid değerlerini değiştirebilir. Uygulamanız bu tür bir tablonun rowid değerini başka bir yerde saklıyorsa, örneğin başka bir tabloda referans olarak kullanıyorsa, VACUUM sonrasında bağlantılar kopar. Denememizde aradaki boşluklar kapanarak değerler yeniden numaralandı:

PRAGMA auto_vacuum = NONE;
CREATE TABLE t(x);                 -- açık INTEGER PRIMARY KEY yok
INSERT INTO t VALUES ('a'), ('b'), ('c'), ('d'), ('e');
DELETE FROM t WHERE x IN ('b', 'c');
SELECT rowid, x FROM t;
-- çıktı:
-- 1|a
-- 4|d
-- 5|e

VACUUM;
SELECT rowid, x FROM t;
-- çıktı:
-- 1|a
-- 2|d
-- 3|e

Kalıcı bir kimliğe ihtiyacınız varsa tabloyu id INTEGER PRIMARY KEY sütunuyla tanımlayın; bu sütun rowid'in takma adıdır ve sizin verdiğiniz değer olarak saklandığı için yeniden numaralandırılmaz. Konunun ayrıntıları SQLite PRIMARY KEY, rowid ve AUTOINCREMENT yazısında.

dbstat ile alan kullanımını ölçmek

Ne zaman VACUUM gerektiğine karar vermek için tahmin yerine ölçüm yapabilirsiniz. dbstat sanal tablosu, veritabanındaki her B-ağacının ne kadar alan kullandığını raporlar. Belgelere göre SQLite SQLITE_ENABLE_DBSTAT_VTAB derleme seçeneğiyle derlendiğinde bütün bağlantılarda kullanılabilir; denemelerimizdeki 3.50.6 komut satırı aracında hazırdı. Tabloyu oluşturmanız gerekmez, adını doğrudan sorguda kullanırsınız. aggregate = TRUE koşulu her sayfa için ayrı satır yerine her tablo ve indeks için tek özet satır üretir.

PRAGMA auto_vacuum = NONE;
CREATE TABLE kayit(id INTEGER PRIMARY KEY, metin TEXT, kategori INTEGER);
CREATE INDEX kayit_kat ON kayit(kategori);
INSERT INTO kayit(metin, kategori)
  SELECT printf('%.200c', 'x'), value % 7 FROM generate_series(1, 2000);
DELETE FROM kayit WHERE id % 3 <> 0;

SELECT name, pageno, printf('%.1f', (pgsize - unused) * 100.0 / pgsize) AS doluluk
  FROM dbstat WHERE aggregate = TRUE ORDER BY name;
-- çıktı:
-- kayit|44|78.6
-- kayit_kat|4|35.4
-- sqlite_schema|1|6.8

SELECT freelist_count FROM pragma_freelist_count;
-- çıktı: 65

VACUUM;
SELECT name, pageno, printf('%.1f', (pgsize - unused) * 100.0 / pgsize) AS doluluk
  FROM dbstat WHERE aggregate = TRUE ORDER BY name;
-- çıktı:
-- kayit|37|93.4
-- kayit_kat|3|47.2
-- sqlite_schema|1|6.8

pageno sütunu toplu modda nesnenin kapladığı toplam sayfa sayısıdır. pgsize toplam alanı, unused kullanılmayan baytları verdiği için ikisinden doluluk oranını hesaplayabilirsiniz. Satırların üçte ikisi silindikten sonra tablo sayfaları yüzde 78,6 doluydu ve freelist 65 sayfaydı. VACUUM sonrasında tablo 37 sayfaya indi ve doluluk yüzde 93,4'e çıktı; indeks de 4 sayfadan 3 sayfaya küçüldü. Belgeler freelist sayfalarının, işaretçi haritası sayfalarının ve kilit sayfasının bu analizde yer almadığını belirtir; boş sayfa sayısı için freelist_count kullanılır. Oranı round() yerine printf() ile yazdırmamızın nedeni, 3.53.0 ile ondalık sayıların metne çevrilmesinin 17 anlamlı basamağa çıkmasıdır; ayrıntılar SQLite matematik fonksiyonları yazısında.

Belgeler, sayfaların ne kadarının dosyada ardışık durduğunu ölçen bir sorgu da önerir ve bunu VACUUM zamanlamasına karar vermek için yararlı bir ölçü olarak sunar. 3.54.0 sürümüyle komut satırına eklenen .diskused komutu ise tablo ve indekslerin kapladığı alanı insan tarafından okunabilir bir rapor olarak verir ve belgelere göre artık kullanımdan kaldırılan sqlite3_analyzer aracının yerini alır. Bu komut 3.54.0 veya sonrasını gerektirdiği için deneme ortamımızda çalıştırılamadı:

-- SQLite 3.54.0 ve sonrası (belgelerden; çalıştırılmadı)
.diskused

VACUUM'un kısıtları

VACUUM, komutu çalıştıran bağlantıda açık bir işlem varsa başarısız olur. Aynı bağlantıda sonlandırılmamış bir sorgu da okuma işlemini açık tuttuğu için komutu engelleyebilir. Ayrıca VACUUM bir yazma işlemidir; başka bir bağlantı yazmayı engelleyen bir kilit tutuyorsa komut çalışmaz. Uygulamanız sürekli yazıyorsa komutu bakım penceresine ya da trafiğin düşük olduğu bir saate planlayın ve bağlantıda busy_timeout tanımlayın.

BEGIN;
DELETE FROM kayit WHERE id > 100;
VACUUM;
-- hata: cannot VACUUM from within a transaction
COMMIT;
VACUUM;

Varsayılan olarak VACUUM ana veritabanında çalışır. ATTACH ile bağlanmış bir veritabanını temizlemek için şema adını eklersiniz: VACUUM arsiv;. Belgeler bu özelliğin 3.15.0 sürümünde geldiğini, daha eski sürümlerin şema adını sessizce yok sayıp ana veritabanını temizlediğini belirtir. Birden fazla dosyayla çalışma SQLite ATTACH DATABASE yazısında anlatılıyor.

Hangi yaklaşımı seçmeli

Disk G/Ç ve bekleme süresinin performansa etkisi ile toplu işlemlerin nasıl hızlandırılacağı SQLite performans ayarları yazısında, pragma komutlarının genel kullanımı ise SQLite PRAGMA komutları yazısında anlatılıyor.

Sık yapılan hatalar

• auto_vacuum'u sonradan açmaya çalışmak: Tablolar oluşturulduktan sonra verilen pragma yalnızca VACUUM çalıştırıldığında uygulanır. Değeri sorgulayıp gerçekten değiştiğini kontrol edin.
• auto_vacuum'u VACUUM yerine görmek: FULL modu yalnızca boş sayfaları keser; sayfaları yeniden paketlemez ve parçalanmayı artırabilir.
• Disk alanını hesaba katmamak: VACUUM sırasında özgün dosyanın iki katına kadar boş alan gerekebilir.
• rowid'e bağımlı olmak: Açık INTEGER PRIMARY KEY olmayan tablolarda rowid değerleri VACUUM sonrasında değişebilir.
• İşlem içinde çalıştırmak: BEGIN ile açılmış bir işlemin içinde VACUUM reddedilir.
• Derleme varsayılanını bilmemek: auto_vacuum ve secure_delete varsayılanları derlemeye göre değişebilir; kendi ortamınızda değerleri okuyun.

Sık Sorulan Sorular

VACUUM verilerimi siler mi?

Hayır. Mantıksal içerik aynı kalır; yalnızca dosyanın fiziksel düzeni yeniden kurulur. Tek istisna, açık INTEGER PRIMARY KEY sütunu olmayan tablolarda rowid değerlerinin değişebilmesidir.

VACUUM ne kadar sürer?

Süre veritabanının boyutuna ve diske bağlıdır, çünkü bütün içerik önce geçici dosyaya, sonra özgün dosyaya yazılır. Belgeler genel bir süre vermez; kendi verinizin bir kopyasında deneyerek ölçmeniz en güvenilir yoldur.

auto_vacuum performansı düşürür mü?

FULL modunda her işlem onayında boş sayfalar taşınıp dosya kısaltıldığı için ek iş yapılır ve belgelerin belirttiği gibi parçalanma artabilir. Bu bedeli kabul etmek istemiyorsanız INCREMENTAL modunu seçip kesme işini kendiniz planlayın.

freelist_count yüksekse sorun var mı demektir?

Kendi başına sorun değildir. Boş sayfalar sonraki eklemelerde kullanılır. Yalnızca dosya boyutunun disk kotası, yedek süresi veya aktarım maliyeti açısından önem taşıdığı durumlarda küçültmeyi düşünü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 yedekleme · SQLite PRAGMA komutları · SQLite performans ayarları · SQLite PRIMARY KEY ve rowid · SQLite WAL modu