Performance | Outlier

PostgreSQL Performans Tuning

Filed in FreeBSD | Genel | Gezegen | PostgreSQL 7 Comments

Son Güncelleme : 19.07.2010

Default postgresql kurulumu performans tan daha çok daha geniş kitleye hitap etmek amacıyla konfigüre edilmiştir. Ayarlar genel sistemler için uygundur fakat genelde performans tarafında normalin altındadır. Bu sebeple sistemlerden maximum performans bekliyorsak sistemimize özel performans ayarları yaparak postgresql in çalışmasını daha da iyileştirebiliriz.

Postgresql ayarları farklı yollardan değiştirilebilir fakat en genel yoldan postgresql.conf dosyası vasıtasıyla istediğimiz değişikliği yapabiliriz. Spesifik ayarlar versiyondan versiyona değişiklik gösterebilir. PostgreSQL de yapılabilecek tüm ayarları ve son değerlerini pg_settings tablosunu kontrol ederek görebilirsiniz.

postgresql.conf dosyasındaki ayarlarınızın etkin olması için genelde postmaster servisini restart etmeniz gerekir. Daha sonra değişikliğin aktif olduğuna emin olmak için “SHOW config_parametresi” komutunu kullanabilirsiniz. “SHOW all” komutu ile tüm değerleri görebilirsiniz.

Postgresql performans arttırımı için şimdi anlatacağım top 10 listesindeki işlemleri uygulamanız sonucunda sisteminiz farkedilir bir şekilde hızlanacaktır. Tabiiki en iyi performans için ayarların hatasız yapılması ve her sistemin kendine göre en uygun ayarları belirlemesi gerekmektedir.

Burada anlatılacak performans ayarları sadece postgresql.conf ta yapılabilecek değişikliklerdir. Oysa bu sadece performans yükseltmenin ufak bir parçasıdır. Diğer iyileştirme işlemlerini daha sonraki makalelerimde anlatmayı düşünüyorum.

İşlemlere başlamadan önce postgresql.conf dosyasını yedeklemenizi öneririm.

#cp -rpv /usr/local/pgsql/data/postgresql.conf /usr/local/pgsql/data/postgresql.conf_org

Örnek olacak kullanılacak test sisteminin donanım bilgileri;

OS : FreeBSD

CPU: Dual-Core AMD Opteron(tm) Processor 1210 (1809.29-MHz K8-class CPU)

real memory = 4294967296 (4096 MB)

ad4: 238475MB <Seagate ST3250310AS 3.AAF> at ata2-master SATA150

1. `shared_buffers` miktarını arttırın;

shared buffer miktarı işlemdeki sorgu sonuçlarının tutulduğu hafıza miktarıdır. postgresql 8.4 te bu değer default olarak 28 MB olarak ayarlıdır. Genel kural olarak bu değerin sistem RAM miktarının %5 ile %15 i arasında olması uygundur. %15 üst limit yoğun database işlemleri yapan sistemler için uygundur. shared buffer miktarının aşırı derece arttırılması OS nin çalışmasını olumsuz etkileyebilmektedir.

Test sisteminin RAM miktarının 4GB ve shared buffers miktarının %15 olduğunu düşünürsek atanacak max değer 614 MB olmalıdır.

Eğer belirleyeceğimiz shared buffer miktarı işletim sisteminin çekirdeğinin shared memory miktarının üzerine çıkarsa şu tip bir hatayla karşılaşabilirsiniz;

FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=5432001, size=660709376, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.
You can either reduce the request size or reconfigure the kernel with larger SHMMAX.
To reduce the request size (currently 660709376 bytes), reduce PostgreSQL's shared_buffers parameter (currently 78592) and/or its max_connections parameter (currently 43).
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared memory configuration.

FreeBSD’de bir prosese tahsis edilebilecek maksimum bellek miktarı, kern.ipc.shmmax (kern.ipc.shmmax: Maksimum paylaşılan bellek dilimi hacmi, IPC=inter-process communication) kernel yapılandırma parametresine atanan rakam tarafından sınırlandırılır. Eğer bu rakamdan daha fazlasını ayırırsanız postgresql başlarken problem yaşayabilir. En iyi performans için bu parametre ile oynamalı ve en iyi uygulama için uygun şekilde kalibre etmelisiniz. Özellikle aynı sunucu üzerinde veritabanı ve ek olarak java uygulamaları da çalıştırıyorsanız shmax değerini en fazla fiziksel belleğinize kadar kademeli olarak arttırmalı ve değerlendirmelisiniz. FreeBSD 7.2 ve üzeri sürümlerinin 2GB.’den yüksek paylaşılan bellek miktarını destekleyebildiğinin altını çizerim (kern.ipc.shmmax=2147483648). Ayrıntılı bilgi için burayı inceleyebilirsiniz.

kern.ipc.shmall (Paylaşılan bellek için kullanılabilir maksimum page sayısı) parametresini de aynı zamanda uygun bir şekilde ayarlamamız gerekecektir.Bunun için kullanacağımız eşitlik aşağıdadır;

kern.ipc.shmall = kern.ipc.shmmax / hw.pagesize

Test sistemimizde işletim sistemi kern.ipc.shmmax ve kern.ipc.shmall değerleri default kurulumdan dolayı düşük miktadır. Bu değerleri arttıralım ve kern.ipc.shmmax değerini 2GB, kern.ipc.shmall değerini de hw.pagesize değerini sabit tutarak eşitliğ göre ayarlayalım;

# sysctl -w kern.ipc.shmmax=2147483648
kern.ipc.shmmax: 536870912 -> 2147483648
# sysctl -w kern.ipc.shmall=524288
kern.ipc.shmall: 131072 -> 524288

kernel değerlerini ayarladıktan sonra postgresql için atayacağımız shared buffer miktarının max. değerini 614 MB bulmamıza rağmen her ihtimale karşın 512 MB olarak atayalım.

# vi /usr/local/pgsql/data/postgresql.conf

değeri atayalım;

shared_buffers = 512MB

veritabanını restart edelim ve durumu gözleyelim. Eğer restart düzgün yapıldı ise değerimiz başarılı bir şekilde aktif olmuştur.

# /usr/local/etc/rc.d/postgresql restart

veritabanından ayarlarımızın doğruluğunu kontrol edelim;

# SHOW shared_buffers ;
 shared_buffers
----------------
 512MB
(1 row)

belirlediğimiz kernel değerlerinin açılışta aktif olmasını istiyorsak bu değerleri /etc/sysctl.conf dosyasına girmemiz gerekmektedir.

kern.ipc.shmmax=2147483648
kern.ipc.shmall=524288

2. `effective_cache_size` miktarını arttırın;

Bu değer postgresql sorgu optimizer ına ne kadarlık bir OS dosya sistemi cache nin elverişli olduğunu gösterir. Sadece postgresql in koştuğu sistemlerde bu değer genellikle fiziksel memory e yakın yapılmaktadır. Siz bu değer ile postgresql optimizer ına OS cache inde elverişli ne kadarlık bir boyut olduğunu söylemiş oluyorsunuz. Postgresql de bu miktara göre sorguları optimize etmekte daha rahat veya daha temkinli davranabilir.

Bu değeri total RAM in 1/2 si kadar ayarlamak gayet normal bir optimizasyondur. 3/4 ü kadar ayarlamak ise biraz daha agresif fakat hala uygun bir durumdur. En uygun değeri ayarlamak için OS nin sistem istatistiklerini kontrol edebilirsiniz. Unix benzeri sistemlerde free veya top komutu ile free-cached number değerini görebilirsiniz. Postgresql de bu değer default olarak 128 MB atanmıştır. Biz bu değeri de RAMimizin 1/2 si kadarına yani 2GB a çıkaralım. (bu değeri değiştirdikten sonra restart gerekli değildir. HUP ile aktive edebilirsiniz.);

# SHOW effective_cache_size ;
 effective_cache_size
----------------------
 128MB
(1 row)

postgresql.conf ayarı;

effective_cache_size = 2048MB

restarttan sonra;

# SHOW effective_cache_size ;
effective_cache_size
----------------------
 2GB
(1 row)

3. VACUUM işlemini sıklıkla yapın;

veritabanında sürekli silme veya update işlemleri yapıldığında dead tuple(ölü data setleri) lar oluşur. Bu tuple lar otomatik olarak silinmezler. Eğer bir tablodaki tuple lar silinmez ise postgresql bu tuple ları gereksiz bir şekilde okuyup işe yaramadığını anlaması gerekecek. Eğer veritabanında binlerce dead tuple varsa hem zaman kaybı olacak hem de performans olumsuz etkilenecektir. Bu nedenle postgreSQL de VACUUM işlemi 3 neden için yapılması gerekir;

update ve delete edilmiş satırlardaki dead tuple ları temizleyerek disk alanı kazanma. (windowstaki defrag işlemi gibi.)
PostgreSQL sorgu optimizer ın kullandığı istatistiklerin update edilmesi.
transaction ID wraparound problemine karşın eski verilerin kaybolmasından kurtulmak

VACUUM işlemi sırasında tablolara ALTER TABLE işlemi dışında SELECT, UPDATE, DELETE ve INSERT sorguları çekilip işlem yapılabilir. Ayrıca VACUUM işlemi yapılırken hatrı sayılır bir miktarda I/O trafiği oluşmaktadır. Bu yüzden diğer işlemlerin etkilenmemesi için VACUUM işleminin serverın I/O yükü bakımından yoğun olmadığı zamanları tercih ediniz.

Ayrıntılı bilgi için routine vacuuming sayfasını kontrol edebilirsiniz.

***autovacuum işlemi;

PostgreSQL de şiddetle yapılması tavsiye edilen bu özellik VACUUM işleminin otomatik olarak yapılmasını sağlar. Buna göre yoğun miktarda eklenmiş, update edilmiş veya silinmiş tuple lar kontrol edilir ve istatistikler sürekli güncellenir. autovacuum işlemi VACUUM ve ANALYZE işlemini beraber yapar. Default olarak postgreSQL de açıktır. Kapatsanız bile transaction ID wraparound problemine karşın gerektiğinde çalışır.

Biz kendi sistemimizde autovacuum özelliğini aktif edip aynı zamanda gece belirli bir saatte hem VACUUM hemde ANALYZE işleminin yapılması için bir script düzenleyeceğiz. Scriptimize ekleyeceğimiz satır yaklaşık şu şekilde olacaktır;

/usr/local/bin/vacuumdb -z -a -U database_user

4.ANALYZE işlemini sıklıkla yapın;

PostgreSQL in query optimizer ı cost-based yapıda olmasında dolayı istatistiklere göre işlem yapmaktadır. ANALYZE işlemi bu istatistiklerin güncellenmesini sağlar ve optimizerin işini kolaylaştırır. ANALYZE işlemi sonrasında pg_statistic tablosu güncellenir. Bu nedenle düzenli periyotlarla ANALYZE işleminin yapılması performans için uygun bir yöntemdir.

5. VACUUM FULL komutunu çok sık çalıştırmayın;

VACUUM FULL işlemi VACUUM a göre daha çok disk alanı kazandırır. Fakat VACUUM a göre daha yavaş çalışır.
VACUUM FULL çalışırken tabloları kilitler ve bu tablolara ulaşılamaz.
VACUUM FULL tablo boyutunu düşürmesine rağmen index boyutunu aynı oranda düşüremez. Tam tersine index boyutunu yükseltir.

Bu sebeplerden dolayı sık çalıştırılması uygun değildir.

6. WAL dosyalarını fiziksel olarak ayrı diske taşıyın;

pg_xlog dizini sıklıkla update edilen dataların bulunduğu dizinidir. Bu dizinin ayrı fiziksel bir diske kopyalanması performansta %25 lere varan artış sağlar.

7. `sort_mem` değerini arttırın;

Eğer sorgularınızda birçok komplek sıralama işlemleri mevcut ise ve memory nizde yeterli seviyede ise sort_mem değerini arttırarak memory de sıralama işlemleri kapasitesini yükseltmiş olursunuz.

NOT: sort_mem parametresi postgreSQL 8.4 versiyonunda kaldırılmıştır.

8. `random_page_cost` değerini düşürün;

Eğer RAID konfigürasyonlu SCSI disklere sahipseniz bu değeri düşürerek query optimizerın rasgele index scan yapmasını sağlayabilir ve bu işlemi hızlandırabilirsiniz. Postgresql de default olarak değeri 4 atanmıştır. Hızlı disklere sahip iseniz bu değeri 2 ye düşürebilirsiniz.

9.`max_fsm_pages` ve `max_fsm_relations` değerlerini arttırın;

postgresql de sıklıkla yapılan update ve delete işlemleri sonucunda dead tuble ların oluştuğunu daha önce söylemiştik. Vacuum sayesinde bu tuble lar tekrar kullanılabilir hale gelmektedir. Free Space Map değeri doğrudan doğruya bu tubleların kayıt altına alınmasıyla alakalıdır. Vacuum işlemi sonucunda eğer bu boşluklar max_fsm_pages değerini aşıyorsa kalan dead tuble lar belirlenemez. Dolayısıyla da VACUUM işlemi tam başarımla yapılamaz. Bu yüzden max_fsm_pages değeri VACUUM ANALYZE VERBOSE komutu sonrasında gerekli olan max_fsm_pages sayısına göre ayarlanabilir. Sonuç olarak ta VACUUM veya VACUUM FULL komutuna olan gereksiniminiz azalmış olur.

NOT: PostgreSQL 8.4 te FSM komple tekrardan yazılmıştır. max_fsm_pages ve max_fsm_relations değerleri çıkarılmıştır. Artık yeni FSM kendi başına adapte olmaktadır. Ayrıntılı bilgi için burayı ziyaret edin.

10. wal_buffers değerini arttırın;

WAL(Write Ahead Log). Eğer veritabanınızda yazma işlemleri sıklıkla yapılıyorsa bu değeri arttırmanız boş disk alanının iyi kullanılması açısından yararınıza olacaktır. Benchmarking testleri bu değeri 1MB yapmanın ideal olduğunu göstermiştir.

Yukarıda anlatmış olduğumuz tuning ayarları iyileştirme sürecinin sadece küçük bir parçasıdır bunun yanında yapılabilecek optimizasyon aşamaları;