Veritabanı Bakımı

Performans optimizasyonu ve kesinti yaşamamak için her veritabanının olduğu gibi
Postgresql’in de rutin bakım işlemleri vardır.
Bakım işlemleri script ve crontab ile otomatize edilip başarılı çalışıp çalışmadığı
izlenmelidir.

En önemli bakım işlemlerinden biri yedeklemedir. Bu konu bölüm 11’de detaylı
olarak anlatılmıştır. Eğer güncel yedek yok ise dosya bozulması, disk arızası, yangın veya
önemli bir tablonun silinmesi gibi kötü senaryolarda veri kaybı yaşanacaktır.
Diğer önemli bakım işlemi ise Vacuum’dur. SQL çalıştırma planları (execution plan),
atıl alanların tekrar sisteme kazandırılması ve performans için hayati önem taşır.
Üçüncü bakım işlemi ise log dosyalarının yönetimidir. Zabbix, Nagios, MRTG ve
check_postgres gibi monitoring araçları veritabanlarınızı monitor edebilir normal
olmayan durumlar için mail ile alarm mekanizması kurabilirsiniz.

Vacuum

Aşağıda listelenen nedenlerden dolayı vacuum işlemi düzenli olarak tablolar üzerinde
çalıştırılmalıdır ;
Vacuum prosesisinin işlevi ;

update ve delete işlemlerinden sonra atıl duruma düşen disk alanının sisteme
yeniden kazandırılmasını ya da bu atıl alanların yeniden kullanılabilmesini sağlar.
Tablolardan veri silindiğinde yani satır silme işlemi gerçekleştirildiğinde
(delete from ekip where no=5) bu satırlar hemen veri dosyasından (data file)
silinmez.
Bu satırlar silinmiş (deleted) olarak işaretlenir.
Aynı şekilde bir satır güncellendiğinde (update), bir silme (delete) ve bir ekleme
(insert) işlemine karşılık gelir.
Satırın güncellenmeden (update) önceki hali halen veri dosyasında (data file) yer
almaya devam eder.
Bu satırlara dead record adı verilir.
Sonuç olarak veri dosyalarında (data file) kullanılamayan yani atıl alanlar oluşur.
Vacuum prosesi dead record’ları tekrar kullanılabilir (reusable) olarak işaretler.
Vacuum işlemi tabloyu kitlemez. (‘vacuum full’ kitler)
Vacuum komutu manuel de çalıştırılabilir.

Sorgu planlayıcı (Query Planner) için istatistiklerin (tablo satır sayısı) güncel
tutulmasını sağlar.
Tabloların kayıt sayılarını okuyup sorgu planlayıcısı (query planner) için istatistikleri
günceller. İstatistikler güncel değilse performansı düşük sorgu planları (query plan)
oluşturulabilir.
vacuum analyze tablo_ismi

Sadece index taramalarını (index-only scan) hızlandıran visibility map’ in
güncellenmesini sağlar.
Visibility map (VM), Dead tuple (ölü satır / dead row) bilgilerini tutar. Her tablonun
kendine özel VM’i vardır ($PGDATA/$OID/*_vm). Tablo dosyalarından (heap) çok
daha küçüktür kolaylıkla keşlenebilir. Vacuum işlemi dead (ölü satır / dead row) tuple
olmayan tabloları işleme dahil etmeyebilir.
VM içeriğini incelemek için pg_visibility modülü kullanılabilir.

Sarmal transaction ID hatalarına (Transaction ID Wraparound) karşı eski verilerin
korunmasını sağlar.
MVCC için 32 bit uzunluğundaki transaction ID (XID) kullanılır. Daha uzun transaction ID’
ler kullanılması her bir satırın boyutu çok fazla büyümesine neden olacağından 32 bit ile
sınırlandırılmıştır.
32 bit ile yaklaşık 4 milyar transaction ID üretilebilir lakin 2 milyara kadar transaction ID
atayabiliyor (2 milyar eski 2 milyar yeni XID).
Limit değere ulaşılırsa tekil XID bulunmadığından, yeni gelen transactionlara daha önce
kullanılmış XID atanabilir bu durum MVCC’ni tutarsız çalışmasına ve veri kaybına neden
olabilir.
Aşağıdaki sorgu ile risk analizi yapılabilir. Bir scriptle alarm mekanizması kurulabilir.
SELECT datname, age(datfrozenxid), (age(datfrozenxid)::numeric/1000000000*100)::numeric(4,2) as “% wraparount riski” FROM pg_database ORDER BY age DESC;

Bazı Kavramlar

heap page : $PGDATA/$OID/* dizinindeki dosyalar.
Daha detaylı bilgi için pageinspect ile gelen fonksiyonlar kullanılabilir.
Tuple : Satır (row)
Page : Veritabanı bloğu (database block) default değer 8KB’dır.
Relation : Tablo

Kullanılışı

vacuum <opsiyon> <tablo-name> (kolon)
vacuum; # Vacuum işlemini tüm tabloya uygular.
vacuum ekip ; # Sadece ekip tablosuna vacuum işlemini uygular.
vacuum analyze; # vacuum + analiz
vacuum full ; # Tüm veritabanı uygulanır, kilide neden olduğu için tehlikelidir.
vacuum full verbose ekip ;
# sadece ekip tablosune uygulanır. verbose ile detaylı çıktı
alınır.

vacuum full

Vacuum prosesinin kullanılabilir (reusable) olarak işaretlediği satırları siler, tabloyu
yeniden organize eder ve atıl alanları sisteme yeniden kazandırır.
Vacuum full, boş bir veri dosyası (data file) oluşturur.
Dead record olmayan satırları/kayıtları (record) yeni veri dosyasına (datafile) kopyalayıp
orijinal dosyayı boşaltır.
Dikkat, vacuum full, işlemi tabloyu exclusive modda kilitler. Transaction’ın az olduğu
zamanlar tercih edilmeli. Gerekiyorsa kesinti alınmalı.
Tablonun bir kopyasını oluşturacağı için diskte yeterli alan olması gerekir.

vacuum analyse

vacuum ve analyze işlemini beraber gerçekleştirir. Analyze tablo istatistiklerini toplar
ve pg_statistic sistem kataloğuna yazar. Query planner bu istatistik bilgilerini
kullanarak sorgu planı (query planner / execution plan) oluşturur. Tüm veritabanına
veya tablo bazında uygulanabilir.

vacuum freeze

XID’si vacuum_freeze_min_age değerinden eski olan satırlar frozen rows olarak
işaretlenir. Amaç dead rows’larda olduğu gibi bunların kullandığı alanları sisteme geri
kazandırmak.
Normalde vacuum, pg_class.relfrozenxid vacuum_freeze_table_age‘
de belirlenen değere gelince agresif tarama yapar.
Agresif tarama (aggressive scan) standart vacuum taramasından farklıdır. Agresif
tarama sadece ölü satırları (dead rows) taramaz statüsü frozen olsun olmasın bütün
blokları tek tek tarar.
vacuum freeze komutu verildiğinde ise vacuum_freeze_min_age ve
vacuum_freeze_table_age değerlerine 0 vererek vacuum işleminin başlatılması
ile eş değerdir. Sadece tablonun yeniden yazılmasına ihtiyaç duyulduğunda
kullanılmalıdır yani vacuum full ile benzerdir.
Autovacuum launcher prosesi, bu vacuum komutlarını yönetir. Eşik değerlere göre
vacuum proseslerini çalıştırır.

Örnek

testdb=# create extension pgstattuple ;
testdb=# create table ekip(isim varchar(20), kurum varchar(64),
per_no serial) ;
# 200.000 satır örnek veri girişi yapılır
insert into ekip(isim, kurum) select ‘Mehmed’, ‘dataera’ from
generate_series(1, 200000);
INSERT 0 200000

# Ekip tablosunun veri dosyasını buluyoruz.
testdb=# select pg_relation_filepath(‘ekip’);
pg_relation_filepath
----------------------
base/16422/16675
# 200.000 kayıt girilmiş veri dosyasının boyutu
du -sh /var/lib/postgresql/14/main/base/16422/16675
10M /var/lib/postgresql/14/main/base/16422/16675

200.000 satır içeren ekip tablosunun işletim sistemi üzerindeki veri dosyasının boyutu
10MB’ dır.

testdb=# SELECT * FROM pgstattuple(‘ekip’);
table_len          | 10436608
tuple_count        | 200000
tuple_len          | 8800000
tuple_percent      | 84.32
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 936
free_percent       | 0.01

pgstattuple extension’ın oluşturduğu pgstattuple fonksiyonu ile ekip
tablosunun row-level istatistikleri görüntülenir.
Güncelleme/silme (update/delete) işlemi yapılmadığı için dead tuple (dead row) bulunmamakta.

testdb=# delete from ekip where per_no < 100001 ;
# veya update kullanılabilir.
# update ekip set isim = ‘Ahmed’ where per_no > 150000 ;
testdb=# SELECT * FROM pgstattuple(‘ekip’);
[ RECORD 1 ]------+---------
table_len          | 10436608
tuple_count        | 100000
tuple_len          | 4400000
tuple_percent      | 42.16
dead_tuple_count   | 100000
dead_tuple_len     | 4400000
dead_tuple_percent | 42.16
free_space         | 936
free_percent       | 0.01

# 100.000 satır silindiği halde boyut aynı kalır.
testdb=# \! du -sh /var/lib/postgresql/14/main/base/16422/16675
10M /var/lib/postgresql/14/main/base/16422/16675

Ekip tablosundan 100.000 satır silinip pgstattuple fonksiyonu ile dead tuple
(dead row) kontrol edilir. 100.000 satır yani tablodaki verilerin yarısı silindiği halde veri
dosyasının boyutu aynı kalmıştır.

testdb=# vacuum FULL verbose ekip ;
testdb=# select pg_relation_filepath(‘ekip’);
pg_relation_filepath
----------------------
base/16422/16679
# Disk üzerindeki boyutu tekrar kontrol edilir.
testdb=# \! du -sh /var/lib/postgresql/14/main/
base/16422/16679
2.5M /var/lib/postgresql/14/main/base/16422/16679

Vacuum full işleminde yeni bir veri dosyası oluşturulup canlı veriler bu dosyaya taşınır
ve eski veri dosyası silinir bu yüzden veri dosyasının ismi değişir.
Vacuum full işleminden sonra tablo boyutunun küçüldüğü görülür.
Vacuum full işlemi tabloyu kilitlediği için performans kaybına ve/veya uygulamayı
durdurabilir. Bu yüzden planlı kesintilerle yapılmasında fayda var. Büyük tabloların
vacuum full işleminin süresini kestirmek zordur boyut, IOPS ve CPU kaynaklarına göre
değişkenlik gösterir.
Diğer önemli bir faktör ise 1TB’lık bir tablonun vacuum full işlemi için minimum 1 TB’lik
boş alana ihtiyaç vardır. Disk dolduğunda veritabanı da durur ve uygulamaya cevap
veremez.

Ayarlar

autovacuum = on
track_counts = on
# Vacuum ile ilgili tüm ayarları görmek için
select name, setting from pg_settings where name like ‘%vacuum%’;
alter system set autovacuum_work_mem = ‘256MB’;
alter system set autovacuum_vacuum_threshold = ‘3000’;
alter system set autovacuum_analyze_threshold = ‘1000’;
alter system set autovacuum_vacuum_scale_factor = ‘0.03’;
alter system set autovacuum_analyze_scale_factor = ‘0.01’;
# Tablo bazında aktif edilebilir.
alter table ekip set (autovacuum_enabled=TRUE) ;

Autovacuum default olarak aktif gelir. Gene de kontrol etmekte fayda var.
autovacuum ve track_counts değerleri “on” olmalı. track_counts
istatistik toplamayı aktif eder, autovacuum da bu istatistiklere göre vacuum çalıştırıp
çalıştırmayacağına karar verir.

autovacuum_work_mem : Herbir vacuum prosesisinin maksimum kullanabileceği
memory miktarını belirler.

autovacuum_vacuum_threshold : Bir tabloda vacuum işleminin tetikleneceği
minimum update ve delete sayısını belirler. Default değer 3000’dir.

autovacuum_vacuum_scale_factor : vacuum işleminin tetikleyip
tetiklemeyeceğine karar verirken autovacuum_vacuum_threshold değerine
eklenen tablo oranıdır. Default değer 0.2‘ dir, tablonun % 20’ si fragmante olduğunda
vacuum başlatılır.

autovacuum_analyze_threshold : Bir tabloda analyze işleminin tetiklenmesi
için gereken toplam insert, update ve delete sayısı. Default değer 1000’ dir.

autovacuum_analyze_scale_factor : analyze işleminin tetikleyip
tetiklemeyeceğine karar verirken autovacuum_analyze_threshold değerine
eklenen tablo oranıdır. Default değer 0.1’ dir.

autovacuum_naptime : Herhangi bir veritabanında otomatik iki vacuum işlemi
arasındaki minimum gecikmeyi belirler. Daemon her turda veritabanını inceler ve bu
veritabanındaki tablolar için VACUUM ve ANALYZE işlemlerini başlatır. Bu değer birimsiz
belirtilirse saniye olarak alınır. Varsayılan değer bir dakikadır (1dk).

autovacuum_max_workers : Maksimum worker proses sayısını belirler.
(Autovacuum launcher hariç tutulur.)

log_autovacuum_min_duration : Burada verilen değerin (ms) üzerinde
süren autovacuum işlemlerini loglar. Değer 0 yapılırsa tüm vacuum işlemlerini loglar.
Değer -1 yapılırsa vacuum işlemlerini loglamaz.

vacuum_cost_delay : Girilen değer (ms) aşıldığında vacuum prosesi sleep moda
geçer. Default değer 0’dır yani aktif değildir.

vacuum_cost_limit : Girilen kümülatif maliyet aşıldığında vacuum prosesi sleep
moda geçer. Default değer 200’dür.

vacuum_freeze_min_age : Girilen değerden eski XID’ler frozen olarak
işaretlenir.

vacuum_freeze_table_age : Tablonun pg_class.relfrozenxid’de
girilen değere ulaştığında agresif tarama başlatır. Default değer 150 milyon transaction
dır.