Postgresql’e Genel Bakış

Disk : Veritabanı için IOPS (input/output per second – saniyedeki işlem sayısı)
performansı çok önemlidir. Hata toleranslı IOPS performansı (NVME, SSD, Flash) yüksek
disklerden oluşturulacak RAID 10 tercih edilebilir.
RAID 10’unun hem okuma hem yazma performası yüksektir. Bu yüzden ozellikle yoğun
DML alan veritabanları için RAID 10 tercih edilmesinde fayda vardır.
RAID 5’in okuma hızı yüksek iken yazma hızı düşüktür. Bu yüzden raporlama (select)
veritabanlarına daha uygundur.

iostat 2 4 #2 saniyede bir 4 kez io istatiklerini gösterir.

iotop      #Anlık olarak IO yapan prosesleri gösterir.

top        # wa değeri %25 ve üzeri IO oranının yüksek olduğunu gösterir.

sar        # Zamanlanarak sistem aktiviteleri toplar ve raporlayabilir.

dd vb. araçlarla disklerin okuma yazma performansları test edilirken yukarıdaki
monitoring araçları ile işlemci disk ve hafıza kullanımı izlenebilir.
dd if=/dev/zero of=/pgdata/test1.img bs=8KB count=1024 /pgdata dizinine test1.img dosyasına 1024 kez 8KB’lik yazma işlemi
gerçekleştirir.

Memory : Postgresql, çok kullanılan veri bloklarını memory’de tutan clock sweep
algoritmasını kullanır. İhtiyaç duyulan memory, veritababanının amacına, veri
büyüklüğüne, veri tiplerine, uygulamaya ve SQL/PLpgSQL kodlarına göre değişkenlik
gösterir.

shared_buffers	Fiziksel hafızanın (memory) ¼’ ü verilebilir.
wal_buffers	Default ayarı “-1” dir. shared_buffers’ ın 32’de birini alır. (shared_buffers/32) 64KB’den düşük, WAL segment boyutundan büyük olamaz. Statik bir parametredir, değişikliğin aktif olması için instance’ı kapatıp açmak gerekir. shared_buffers değeri çok büyük ise statik bir değer verilebilir. Özellikle DML sayısının çok olduğu sistemlerde ufak artışların bile performansa katkısı büyük olabilir.
effective_cache_ size	Postgresql, kendisine dedike edilmiş (shared_buffers) hafızayı (memory) hem de dosya sistem keşini ( filesystem cache) kullanmak ister. Execution planner bir sorguda index kullanıp kullanmamayı toplam hafızaya (OS cache + shared_buffers) göre karar verir. Indeks kullanımı performansı artıracak olsa bile kullanabileceği hafıza alanı küçükse indeks kullanmayabilir. effective_cache_size için fiziksel hafızanın %50 - %75 verilebilir gibi görünse de işletim sistemini top ve free uygulamaları ile monitor ederek doğru rakamı vermekte fayda var.
work_mem	Sıralama (sort) ve hash tablo sorgu operasyonları için kullanılan hafıza (memory) alanıdır. work_mem alanı yeterli olmazsa diskteki temporary dosyalara ($PGDATA/base/pgsql_tmp) yazar (fiziksel I/O). Paylaşımlı değildir. Her oturum (session) ve proses kendi özel alanını kullanır. Basit bir hesapla, outurum (session) sayısı * work_mem alanı kadar sistemden hafıza (memory) alır. Default değer 4MB’tır. Eş zamanlı oturum sayısını da gözönünde bulundurularak artırılabilir. Raporlama veritabanlarında önemli bir parametredir. Kompleks sorgularda birkaç sort ve hash operasyonu paralel çalışabilir, bu operasyonların her biri kendisi için ayrı ayrı work_mem alanı tahsis edebilir. Oturuma özel değer verilip raporların performansı artırılabilir ; `SET LOCAL work_mem = ‘2GB’;`
maintenance_ work_mem	vacuum, reindexing gibi bakım işlemleri için kullanılabilecek hafıza miktarını belirler. Default değer 64MB’dır.

https://pgtune.leopard.in.ua/#/ sitesinde otomatik hesaplama yaptırabilir.

CPU : Postgresql 9.6 sürümü ile parallelizm özelliği geldi. Instance seviyesinde ve/
veya SQL kodlarda iş yükünün tüm CPU’lara dağıtılması gerekir. Bunun için kodlara hint
girilebildiği gibi aşağıdaki parametreler de ayarlanabilir.

max_worker_processes	Sistemin destekleyeceği maksimum background proses sayısını belirler. Default değer 8’dir. Çekirdek sayısı kadar artırılabilir.
max_parallel_workers_per_gather	Her bir gather ve gather merge işlemi için başlatılabilecek maksimum worker sayısını belirler. (Explain plandaki “workers planned 2”) Her bir worker kendisi için work_mem alanı tahsis eder. Gather : Tüm worker proseslerin sonuçlarının toplanıp birleştirilip (merge) tek sonuç haline getirilme işlemi.
max_parallel_workers	Paralel işlemlerde desteklenecek maksimum worker sayısını belirler. Default değer 8’dir. Çekirdek (core) sayısı kadar artırılabilir.
max_parallel_maintenance_workers	Her bir bakım (maintenance) aracı başlatılabilecek maksimum worker sayısını belirler. Default değer 2’dir.

# 2 saniyede bir 22 kez tüm çekirdeklerin kullanımını gösterir.
mpstat -P ALL 2 22
# 3 saniyede bir 11 kez kaynak kullanımlarını gösterir.
vmstat -P ALL 3 11
# 1’e basılarak tüm çekirdeklere gelen yük izlenebilir.
top
# vmstat, mpstat ve top systat paketi ile gelir.
apt install sysstat

vmstat, mpstat, top vb. linux araçları ile yükün çekirdeklere (core) dağılımı izlenebilir. Sorgu sonucu geç geliyor ve çekirdeklerden sadece bir kaçı tam yükte, diğer çekirdekler atıl ise paralelizm ayarları gözden geçirilmelidir. Gerekirse SQL hint kullanılmayabilir.

Network

Şekilde başlangıç seviyesi netwok diyagramı verilmiştir. Burada istemci uygulamaya
bağlanıp çalışmaya başladığında TCP paketleri tüm bu 1,2,3,4 ve 5 network
bağlantılarından geçmektedir. Bu bağlantıların herhangi birindeki yavaşlık, veritabanı
performans problemi olarak algılanabilir ya da veritabanı ekibine bu şekilde
aktarılabilir.
Uygulama server ile veritabanı sunucusu arasında;
Mümkünse 10GBPS ve üzeri bağlantı olmalı,
Jumbo frame ayarlanmış olmalı (MTU 9000),
Arada aktif cihazlar varsa NAT yerine routing tercih edilmeli,
Ek olarak sunucular arasında scp, ftp, sftp vb. araçlarla dosya gönderilerek hız testi
yapılabilir.
Not: Uygulama ve veritabanı sunucuları ayrı olmalıdır.

# Tüm NIC’lerin istatistiklerini getirir.
ip -s link
# Uygulamların bağlantı bilgilerini gösterir.
netstat -neopa |more
# UDP’den gelen hatalı paket istatistiklerini verir.
netstat -su
# Hatalı paket istatistiklerini verir.
ethtool -S enp0s31f6
# hard interrupts’ ın CPU maliyeti fazladır.
egrep “CPU0|enp0s31f6” /proc/interrupts

errors, dropped, overrun, carrier ve collisions istatistikleri
incelenerek network sorunu tespit edilebilir.
Sorunun network’ten kaynaklandığını tespit etmenin en kolay yolu;
istemcinin çalıştırdığı SQL’i, veritabanı sunucusuna SSH ile bağlantı yapıp,
bu SQL’i veritabanının lokalinde \timing on yaptıktan sonra çalıştırıp süreleri
karşılaştırmaktır

Virtualization layer

Sanal veritabanı sunucusunda tüm performans iyileştirmeleri yapılmış olsa dahi
alınabilecek maksimum performans sanallaştırma katmanındaki limitlere takılacaktır.
Örneğin bulut (cloud) şirketlerinden SSD diskli sunucu kiralanıyor ama bunların IOPS
kapasitesi limitli olabiliyor. Bu sanal makinenin işletim sisteminde instance’da ve kodda
tüm performans iyileştirmeleri yapılsa da IOPS limitinden dolayı istenilen değerlere
ulaşılamama ihtimali yüksektir.
Bu yüzden sanal veritabanı sunucularında dedike kaynaklar verilmelidir. Özellikle diskler
dedike olmalıdır ya da uygulamanın ihtiyacı olan IOPS kapasitesi garanti edilmelidir.

Instance Tuning : Memory, lock, vacuum, checkpoint, paralelizm, hugepage, connection
vb. parametreler uygulamanın ihtiyaçlarına göre ve/veya veritabanının amacına göre
ayarlanarak performans iyileştirme yapılabilir.
Özellikle DML (insert update delete) işlemlerinin yoğun geldiği tabloların
istatistiklerinin güncel olması, maliyeti (cost) düşük, çalıştırma planlarının (execution
plan) oluşturulabilmesi için önemlidir.
Örneğin, 2-3 bin satır veri bulunduran bir tabloya bulk işlemlerle milyarca kaydın
eklenmesi durumunda eğer vacuum analyze çalışmamışsa postgresql halen bu tabloda
2-3 bin satır olduğunu varsayıp, index kullanmadan tam tablo taraması (FTS – Full Table
Scan) yapıp ciddi performans kaybına neden olabilir.

Indeks : İndeksler raporlama (select) işlemlerinin performansını artırırken yazma
işlemlerinin performansını olumsuz etkiler. Bu yüzden kullanılmayan ve mükerrer
indekslerin tespit edilerek silinmelidir.
Bir tabloya indeks eklendiğinde her kayıt (insert) ile indekse de kayıt eklenir. Yani
daha fazla yazma işlemine neden olur. Güncelleme ve silme işlemlerinde (update ,
delete) fragmantasyona neden olur.

Kullanılmayan indexler :
SELECT relname AS tabl0_ismi, indexrelname AS indeks_ismi, idx_ scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_ size(indexrelname::regclass)) FROM pg_stat_all_indexes WHERE schemaname = ‘public’ AND idx_scan = 0 AND idx_tup_read = 0 AND idx_tup_fetch = 0 ORDER BY pg_relation_size(indexrelname::regclass) DESC;

Mükerrer indexler :
SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS B0YUT, (array_agg(idx))[1] AS index1, (array_agg(idx))[2] AS index2, (array_agg(idx))[3] AS index3, (array_agg(idx))[4] AS index4 FROM ( SELECT indexrelid::regclass AS idx, (indrelid::text ||E’\n’|| indclass::text ||E’\n’|| indkey::text ||E’\n’|| COALESCE(indexprs::text,’’)||E’\n’ || COALESCE(indpred::text,’’)) AS KEY FROM pg_index) sub GROUP BY KEY HAVING COUNT(*)>1 ORDER BY SUM(pg_relation_size(idx)) DESC;

Partition

CREATE TABLE uretim
( uid uuid NOT NULL,
tarih date NOT NULL,
bant bigint NOT NULL,
lokasyon bigint NOT NULL,
tpsno character varying(30) COLLATE pg_catalog."default"
) PARTITION BY RANGE (tarih);
PARTITION TABLOLARI CREATE
CREATE TABLE IF NOT EXISTS t_2000_2001 PARTITION OF uretim
(
CONSTRAINT "t_2000_2001_Unique_" PRIMARY KEY (uid, tarih)
)
FOR VALUES FROM ('2000-01-01') TO ('2001-01-01');
CREATE TABLE IF NOT EXISTS t_2001_2002 PARTITION OF uretim
(
CONSTRAINT "t_2001_2002_Unique_" PRIMARY KEY (uid, tarih)
)
FOR VALUES FROM ('2001-01-01') TO ('2002-01-01');

Tabloyu ufak parçalara bölme işlemidir. Örnekte tarih kolonuna göre her bir yıl için
range partiton oluşturulmuştur. Böylece yılllara göre filtremelerde tüm yıllarda arama
yapmak yerine sadece where koşulunda verilen yılda tarama yapılır. Postgresql range,
list ve hash partition tiplerini destekler.

IO Operasyonlarının Diskleri Dağıtılması : pg_wal, temp_tablespaces ve
uygulama tablespacelerini farklı disklerde barındırarak performans iyileştirilebilir.
İşletim sistemi seviyesinde sembolik linklerle ya da bu pathler farklı disklere mount
edilerek yapılabilir.
Raporlamalar hot/logical standbylara yönlendirilebilir. Hot standby primarinin birebir
aynısıdır ve sadece okumaya açıktır.
Logical standby ise okuma ve yazmaya açıktır. Primary veritabanında sadece istenen
tabloları replike etmek mümkündür. Yazmaya açık olduğu için bu tablolara index
eklenebilir. View vs. oluşturulabilir.

Application Tuning

explain select * from rental where rental_id = 622 ;
QUERY PLAN
---------------------------------------------------------------
Index Scan using rental_pkey on rental (cost=0.29..8.30
rows=1 width=36)
Index Cond: (rental_id = 622)

explain (ANALYZE, BUFFERS, FORMAT JSON) select * from film where
film_id = 100;

Explain, SQL’lerin çalışma planlarını (execution planlarını) gösterir.
track_io_timing aktif olmalıdır. Maliyeti (cost) yüksek aşamalardaki (node)
sorunlar tespit edilip giderilir.
Bu istatistikler pgbadger vb. performans raporlama araçları ile de alınabilir,
raporlanabilir.
log_min_duration_statement parametresine verilen değerden uzun süren
SQL’ler loglanabilir.
Performansı artırmak için;
Hint kullanılarak daha performanslı yani maliyetleri (cost) düşük bir çalışma planına
zorlanabilir.
Çok kullanılan tablolar pg_prewarm ile sürekli memory de tutulması sağlanabilir
Tüm tablo taranıyorsa istatistiklerin güncelliği kontrol edilir. Filtreleme (where)
koşullarına göre indeks eklenebilir.

Explain Opsiyonları
ANALYZE : SQL’i gerçekten çalıştırır.
VERBOSE : Daha detaylı bilgi verir.
COSTS : Başlangıç (ilk satır) ve toplam maliyetleri verir.
BUFFERS : Shared read diskten, shared hit keşten okuma istatistiklerini verir.
WAL : WAL kayıt bilgilerini verir.
TIMING : Başlangıç zamanı ve her nodda geçen süreyi verir.
SUMMARY : Toplam süre gibi özet bilgiler verir.
FORMAT : Çıktının formatı belirlenir (TEXT, XML, JSON, YAML).

Lock

Kilitler (lock) her ne kadar uygulama kurgusu ve zamanlama ile ilgili olsa da performans
sorunu olarak algılanır. Kilit meydana geldiğinde ekranda kum saati belirir. Kullanıcı
sistemin yavaş çalıştığını düşünür, kilit meydana geldiğini haklı olarak bilemez. Kilitler
uygulama katmanında ve biraz da instance seviyesindeki ayarlarla çözülülebilir.
Bu ayarlar (lock timeout) uygulama geliştiricilere danışılarak değiştirilmelidir.
Hızlı çözüm gerektiğinde, uygulama geliştiricilerle ya da destek ekibi istişare edildikten
sonra kilitleyen oturumlar sonlandırılabilebilir. Lock ile ilgili detaylar için bölüm 6
incelenebilir.