PostgreSQL’de SELECT Performansını Etkileyen Faktörler
Merhabalar, daha önceki bir çalışmamda PostgreSQL’de yazma hızına etki eden faktörleri aktarmaya çalışmıştım. Bu yazımda da okuma hızına etki eden faktörleri tartışmaya çalışacağım yazabildiğim kadarı ile.
PostgreSQL’de yazma hızına etki eden faktörleri okumak için aşağıdaki bağlantıyı kullanabilirsiniz.
PostgreSQL’de yazma hızını etkileyen faktörler.
Başlamadan önce PostgreSQL’de okuma hızına etki eden birçok faktör mevcuttur. Bu yazıda ve çalışmada sadece bir kısmına değineceğiz. Çünkü üzerinde çalıştığımız konu çok ucu açık ve yoruma müsait bir konudur. SELECT performanslarının değişmesine etki edecek birçok farklı değişiklik,mimari ya da teknolojik altyapı olabilir. Bu çalışmada üzerinde durmaya çalışacağım konular ise aşağıdaki gibidir.
- PostgreSQL tablo boyutunun SELECT performansı üzerindeki etkisi
- PostgreSQL table partitioning çözümünün SELECT performansı üzerindeki etkisi
- PostgreSQL konfigürasyonlarının SELECT performansı üzerindeki etkisi
- PostgreSQL pgbouncer SELECT performansı üzerindeki etkisi
PostgreSQL Tablo Boyutu ve SELECT Performansı
PostgreSQL veritabanı sistemide ve aslında birçok veritabanı sisteminde veri boyutu arttıkça SELECT sorgularının performanslarında düşüş bekleyebiliriz. Çünkü SELECT sorguları disk üzerinde I/O işlemi yapmaktadır. Veri boyutu büyüdükçe sorgunun tamamlanması için gerekli I/O miktarındaki artış sorgu performansındaki azalmaya yol açabilir. Örnek olarak, sürekli büyümeye devam eden ve içerisinden hiç kayıt silinmeyen bir tablo modelini ele alabiliriz. Bu tablonun x,4x,8x boyutlarında aynı sorguya nasıl cevap verdiğini inceleyebilriz. Tablo modeli ve performansını deneyeceğimiz sorgu ise aşağıdaki gibidir.
CREATE TABLE orders (o_id serial, cargo_id int,
order_date timestamp without time zone, total_price int);CREATE TABLE orders_2x (o_id serial, cargo_id int,
order_date timestamp without time zone, total_price int);CREATE TABLE orders_4x (o_id serial, cargo_id int,
order_date timestamp without time zone, total_price int);CREATE TABLE orders_8x (o_id serial, cargo_id int,
order_date timestamp without time zone, total_price int);INSERT INTO orders(cargo_id,order_date,total_price)
VALUES (1,generate_series('2016-01-01'::timestamp,'2016-12-31'::timestamp,'1 second'),
round(random()*100)::int);INSERT INTO orders_2x(cargo_id,order_date,total_price)
VALUES (1,generate_series('2016-01-01'::timestamp,'2017-12-31'::timestamp,'1 second'),
round(random()*100)::int);INSERT INTO orders_4x(cargo_id,order_date,total_price)
VALUES (1,generate_series('2016-01-01'::timestamp,'2020-12-31'::timestamp,'1 second'),
round(random()*100)::int);INSERT INTO orders_8x(cargo_id,order_date,total_price)
VALUES (1,generate_series('2016-01-01'::timestamp,'2024-12-31'::timestamp,'1 second'),
round(random()*100)::int);-- tablolar başlamadan önce vacuum full işlemine tabi tutulmuştur.VACUUM FULL orders;
VACUUM FULL orders_2x;
VACUUM FULL orders_4x;
VACUUM FULL orders_8x;-- indexler hazırlandıCREATE INDEX idx_order_date_btree ON orders (order_date);
CREATE INDEX idx_order_date_btree ON orders_2x (order_date);
CREATE INDEX idx_order_date_btree ON orders_4x (order_date);
CREATE INDEX idx_order_date_btree ON orders_8x (order_date);
Tablo boyutları ise aşağıdaki gibidir.
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+---------+-------------
public | orders | table | postgres | 1569 MB |
public | orders_2x | table | postgres | 3139 MB |
public | orders_4x | table | postgres | 7851 MB |
public | orders_8x | table | postgres | 14 GB |Aşağıdaki sorguda ortalama cevap süreleri mevcuttur. Bununla birlikte toplam karşılanan transaction sayısı ve ortalama TPS miktarları da analiz edildi.
Kullanılan sorgu;
EXPLAIN (ANALYZE)
SELECT
AVG(total_price)
FROM
table_name
WHERE
order_date >= '2016-04-04'
AND order_date < '2016-04-05';
Bu sonuçları birden fazla açıdan yorumlamak mümkündür. Tablo boyutunun SELECT performansına etkisi görülmektedir. Burada, öncelikle tablo boyutu büyüdükçe belirlenen süre zarfı içerisinde başarılı gerçekleşen SELECT işlemlerinin sayısında düşüş görülmektedir. Tablo boyutu daha da arttığında bu açık daha da artmaktadır. Yani tablo boyutu ve karşılanabilecek SELECT işlemlerinin sayısı arasında ters orantı mevcuttur. Bu testlerdeki boyut oranları arasında oran olarak 2x 4x 8x gibi farklar olsada boyut olarak bu
rakam arttıkça bu fark daha da açığa çıkmaktadır.
Buradan çıkarabilecek en sonuç ->> Tablonun boyutu(veri miktarı,satır sayısı toplam kapladığı alan) ne kadar küçük tutulursa SELECT işlemlerinde performans/TPS artışı sağlamak mümkündür.
İkinci nokta ise, sorgulardaki ortalama latency(gecikme) değeridir. Bu noktada, yine tablo boyutu büyüdükçe latency değerlerinde yukarı doğru bir artış görebiliriz. Yani, ortalama latency ve tablo boyutu arasında doğru bir oran vardır. Tablo boyutu arttıkça ortalama latency süreleri de artacaktır.
PostgreSQL Table Partitioning SELECT Performansı
İkinci bir faktör ise, PostgreSQL üzerinde kullanabileceğimiz table partitioning çözümüdür. Bu çözüm ile birlikte, tek bir vm/resource üzerinde tablomuzu mantıksal parçalara bölmek ve ölçeklemek mümkündür. Partition çözümü iyi analiz edilmesi gereken ve iş ihtiyaçlarına göre planlanıp uygulanması gereken bir süreçtir. SELECT sorgularınında da performans anlamında partition çözümünün etkisini inceleyeğim.
Bu senaryoda, aynı veri setine ve aynı konfigürasyonlara sahip 2 farklı tablo kullanacağım. Bir tablo partitioned olarak deploy edilecek diğeri ise partition olmayan modelde deploy edilecek. Deployment için aşağıdaki scriptleri kullanabilirsiniz. Yine sorgu ve tablo modeli yukarıdaki örnekteki gibi olacaktır.
CREATE TABLE orders_8x_partitioned (
o_id serial,
cargo_id int,
order_date timestamp without time zone,
total_price int,
CONSTRAINT comments_pkey PRIMARY KEY (o_id, order_date)
)PARTITION BY RANGE(order_date);CREATE TABLE public.orders_8x_partitioned_1 PARTITION OF public.orders_8x_partitioned
FOR VALUES FROM ('2016-01-01 00:00:00') TO ('2016-02-01 00:00:00');
CREATE TABLE public.orders_8x_partitioned_2 PARTITION OF public.orders_8x_partitioned
FOR VALUES FROM ('2016-02-01 00:00:00') TO ('2016-03-01 00:00:00');
CREATE TABLE public.orders_8x_partitioned_3 PARTITION OF public.orders_8x_partitioned
FOR VALUES FROM ('2016-03-01 00:00:00') TO ('2016-05-01 00:00:00');
CREATE TABLE public.orders_8x_partitioned_4 PARTITION OF public.orders_8x_partitioned
FOR VALUES FROM ('2016-05-01 00:00:00') TO ('2016-06-01 00:00:00');
......CREATE TABLE public.orders_8x_partitioned_811 PARTITION OF public.orders_8x_partitioned
FOR VALUES FROM ('2023-12-01 00:00:00') TO ('2024-01-01 00:00:00');
Görüleceği üzere, partition model ve partition olmayan modelin performansı arasında farklar mevcuttur. Ancak bu veri modeli,partition modeli ve sorgu modeli kapsamında, partition olan modelin zaman zaman partition olmayan modele göre daha farklı sonuçlar verdiği görülmüştür. Burada tercih edilen partition belirli tarih aralıgına göre uygulanmıştır. Bu nedenle, partitioned model her zaman sorgulara ve ihtiyaca göre analiz edilmeli ve performans beklentileri ile birlikte teste tabi tutulmalıdır.
PostgreSQL Konfigürasyonların SELECT Performansı
PostgreSQL konfigürasyonları veritabanın davranışını ve performansını önemli ölçüde etkilemektedir. Bu nedenle de konfigürasyonlar sürekli olarak duruma göre analiz edilmeli ve gerekli durumlarda değiştirilmelidir. Burada bahsi geçen konfigürasyonların listesi aşağıdaki gibidir.
- work_mem
- shared_buffers
- effective_io_concurrency
- random_page_cost
- seq_page_cost
- default_statistics_target
Bu konfigürasyonları sonucunda da SELECT performansı için gözle görülür bir fark mevcut değildir.
PostgreSQL Sorgu Kapsamı SELECT Performansı
Veritabanında performansı en çok etileyen faktörlerden biriside bir adet sorgunun etki ettiği ya da dokunduğu veri setinin boyutudur. Buradaki amaç, yukarıda çalıştığımız sorgunun zaman aralığının küçültülmüş halini test etmektedir. Aşağıdaki 2 query ile birlikte aynı test yapılmıştır.
-- Query 1
EXPLAIN (ANALYZE,BUFFERS)
SELECT
AVG(total_price)
FROM
orders_8x
WHERE
order_date >= '2016-04-04 00:00:00'
AND order_date < '2016-04-04 03:00:00';-- Query 2
EXPLAIN (ANALYZE,BUFFERS)
SELECT
AVG(total_price)
FROM
orders_8x
WHERE
order_date = '2016-04-04 '
AND order_date < '2016-04-05';
Buradaki durumda, sorgunun taraması gereken veri seti ne kadar küçülürse sorgu başına düşen I/O maliyeti ve buna bağlı olarak performansı artış gösterir. Bu performans artışıda TPS, birim zamanda toplam başarılı işlem sayısı ve ortalama latency değerlerini iyileştirir.
Bu tarz durumlar için bu veri setlerini ya da sorgunun kapsamını daraltıp bu sorguları bölerek çalışmak performans kazanımı sağlayacaktır.
Grafiklerden de görüleceği üzere, etki alanı küçültülmüş yani veri seti miktarı küçültülmüş sorgunun diğer sorguya göre performans anlamında üstünlüğü mevcut. Query 1 sorgusu diğer sorguya göre yaklaşık 4.5 kat daha fazla işlem gerçekleştirmiş. Bununla birliktede saniyede yine 4.5 kat daha fazla işlem gerçekleştirmiş.Çıkarılabilecek sonuç ise, sorguları modellerken ya da sorguları oluştururken ne kadar küçük veri setleri ile çalışabilirsek performans ve işlem hacmi olarak o kadar ileri gidebiliyoruz.
Pgbouncer PostgreSQL SELECT Performansı
Pgbouncer PostgreSQL için geliştirilmiş açık kaynak bir connection pooling aracıdır. Temel motivasyonu, connection pooling çözümü ile sistemdeki kaynak tüketimini optimum seviyede tutarak performans ve stabilite avantajı sağlamaktır. Bu sayede, PostgreSQL ortamında SELECT ve DML işlemlerinin performansının iyileşmesini bekleyebiliz. Bu senaryoda, 2 eşlenik tabloya ve aynı benchmark işlemini hem pgbouncer hem de pgbouncer olmadan test edeceğiz ve sonuçları tartışacağız.
Not: Bu çalışmada pgbouncer kurulum ve konfigürasyonları ile ilgili detaylar mevcut değildir kapsam dışına çıkmamak için. Ancak, aşağıdaki gitlab repository’sinden kısaca bu aracı kullanmaya başlamak için gerekli detayları ve entegrasyonları bulabilirsiniz.
Pgbouncer ile dikkat edebileceğimiz bir noktada, sistemin yogun yük altında daha uzun süre stabil kalmasıdır. Bu çalışmada, yukarıda bahsedilen yapıya 2 farklı test yapılmıştır. Hem 120 sn hem de 300 sn olacak şekilde iki farklı zaman diliminde strese tabi tutulmustur. Sonuçlar ise aşağıdaki gibidir.
Buradan birkaç sonuç çıkarmak mümkün.
- pgbouncer ile çalıştığımız durumda ortalama latency süreleri %25 oranında azaldığı görülüyor. Bu oran büyük ölçeklerde ve daha büyük tablolarda daha da net görülecektir.
- pgbouncer ve pgbouncer’sız gruplarda TPS ve toplam başarılı işlem sayısı eşit ya da fark çok az olsada yine de ortalama latency’de farklar mevcuttur.
- Bu çalışmanın kapsamının dışına çıkabilir bu madde ancak, bouncer daha az connectionla ve kaynakla bu isteklere cevap verebiliyor. Bu da yoğun yük altında sistemin kaynak tüketimini daha da stabil durumda tutacağı için yine performansa olumlu etkileri olacaktır.
Tüm Olumlu Faktörlerin Birleşimi
Çalışmanın sonunda ise, aslında yukarıda bahsettiğimiz faktörlerden tüm hepsini devreye aldığımızda ya da dikkat ettiğimizde oluşabilecek sonuçları tartışacağım. Aslında yazının sonunda da genel olarak bu sonuçlardan yaptığım çıkarımları, potansiyel diğer faktörleri ve çalışmanın kapsamını kısaca tekrar özetleyeceğim. Bu senaryoda olumlu etki bırakan tüm faktörlerin birleşimi olan bir ortam ile en başında başladığımız ortamın performans farkını analiz edeceğim. Kısa aşağıdaki değişiklikleri uygulayacağım.
İyileştirilmiş Bölüm
- Pgbouncer aktif edildi
- Tablo boyutu %50 azaltıldı
- sorgu veri seti %50 azaltıldı
- PostgreSQL konfigürasyonları değiştirildi.
Buradaki sonuçlarda aslında bu faktörlerin hepsinden olumlu anlamda yararladığımızda aradaki farkı daha da net gösteriyor. Özetle, bu çalışmada izlediğimiz metrikleri yani, aynı sürede karşılanabilen toplam başarılı işlem sayısı, birim zamanda gerçekleşen işlem miktarı ve ortlama latency değelerinin 6–6.5 kat iyileştiğini görebiliriz. Ancak belirtmekte fayda var, bunlar tek başına doğru,yanlış ya da kalıcı çözüm olamaz. Bu nedenle iyi analiz edilmeli bu işlemler ve çözümler.
Yukarıda üzerinde çalışılan maddeler dışında birçok madde daha olabilir. Çünkü veritabanında SELECT performansı çok detaylı,kapsamlı ve birçok bileşene dokunan bir operasyonudur. Bu maddeler içine başka değişkenler de eklenebilir. Örnek olarak, işletim sistemi seçimi, dosya sistemi seçimi ya da dosya sisteminin konfigürasyonları (xfs,ext4 etc..), farklı storage tipi seçimleri ve tablodaki data tipi seçimleri bile etki edebilir.
Tüm bu sebeplerden ötürü veritabanı yönetimi,performansı,mimarisi ve tasarımı başlı başına kapsamlı bir konudur. Veritabanından beklentileri ve kabul koşullarını belirleyip buna göre veritabanı sorguları/veritabanı veri modeli uygulanmalıdır. Bu mimarilerde ve modellerde değişiklik yapmadan önce de mutlaka production sistemin her anlamda eşleniği olan stage sistemde bu değişikliklerin etkisi analiz edilmelidir.
Herhangi bir sorunuz varsa, aşağıdaki mecralardan iletişim kurabilirsiniz.
Sevgiler,
Demir.
