Merhaba,
bu yazımızda PostgreSQL veritabanı TOAST (The Oversized Attribute Storage Technique) hakkında kısaca bilgi vermeye ve text,varchar gibi kolonlara sahip tablolarda TOAST parametrelerinin performansı nasıl etki edebileceğini incelemeye çalışacağız.
PostgreSQL veritabanı verileri sabit page boyutunda (genelde 8kB) tutar ve satırların birden fazla page’lere dağılmasına izin vermez. Bu yüzden PostgreSQL çok büyük alan verilerini direkt tutmak yerine bunları chunk’lara bölerek tutar. İşte TOAST tam bu noktada devreye girerek büyük alan verilerini sıkıştırarak ve/veya chunk’lara bölerek veritabanında tutar. Kısaca TOAST PostgreSQL veritabanında büyük alan verilerini sıkıştırmak ve chunk’lara bölmek için kullanılan bir tekniktir diyebiliriz ve bu teknik kullanıcıları etkilemeden arka planda internal olarak çalışmaktadır. PostgreSQL veritabanı TOAST yapabilmesi için ilgili kolonların variable-length olmak zorundadır. Mesela varchar, bytea, text gibi veri tiplerindeki kolonlar TOAST yapılabilirken integer gibi sabit genişlikteki veri tipleri hiçbir şekilde TOAST yapılamaz. Peki bu TOAST nasıl tetiklenir. Eğer tablodaki bir satırın değeri TOAST_TUPLE_THRESHOLD (varsayılan 2kB) değerini geçerse TOAST tetiklenir. İşte bu satır TOAST_TUPLE_TARGET (genelde 2kB) değerine sığacak şekilde sıkıştırılır ve/veya TOAST_TUPLE_TARGET değerine sığması için chunk’lara bölünmek üzere satır dışına taşınır(out-of-line) . Bu sıkıştırma ve taşıma işlemi aslında 4 farklı TOAST stratejisi kullanılarak yapılır,biz burada default “EXTENDED” stratejisi üzerinden ilerleyeceğiz. Diğer TOAST stratejileri için https://www.postgresql.org/docs/12/storage-toast.html dökümanını inceleyebilirsiniz. Eğer bir tabloda TOAST yapılabilecek kolonlar varsa bu tabloyla ilişkili TOAST tablosu otomatik olarak oluşturulur. Mesela aşağıdaki örnekte görüldüğü üzere tabloda text veri tipi olduğu için bu TOAST yapılabilir bir veri tipidir ve bundan dolayı bu tablo ile ilişkili TOAST tablosu otomatik olarak oluşturulmuştur. Bu toast tablosunda ana tabloda TOAST_TUPLE_TARGET değerine sığmayan veriler tutulur.
forendabilisim=# CREATE TABLE toast_test (id SERIAL, value TEXT);
CREATE TABLE
forendabilisim=# SELECT relname, reltoastrelid FROM pg_class WHERE relname = 'toast_test';
relname | reltoastrelid
------------+---------------
toast_test | 24592
(1 row)
forendabilisim=# SELECT relname FROM pg_class WHERE oid = 24592;
relname
----------------
pg_toast_24588
(1 row)
Yukarıdaki örnekte görüldüğü üzere tabloda value kolonu text veri tipi ve bu veri tipi de variable-length olduğu için PostgreSQL bu veri için otomatik olarak TOAST tablosunu oluşturdu. Peki bu TOAST tablosunda hangi kolonlar var derseniz:
forendabilisim=# select * from pg_toast.pg_toast_24588;
chunk_id | chunk_seq | chunk_data
----------+-----------+------------
(0 rows)
forendabilisim=# \d pg_toast.pg_toast_24588
TOAST table "pg_toast.pg_toast_24588"
Column | Type
------------+---------
chunk_id | oid
chunk_seq | integer
chunk_data | bytea
chunk_id: Her bir chunk için TOAST edilmiş değerleri birbirinden ayırmak için kullanılır.
chunk_seq: Aynı chunk_id değerine sahip chunk’ların sıralanması için kullanır.
chunk_data: TOAST edilmiş verinin gerçek halini tutar.
İlgili test tablomuza tek bir kayıt ekleyip tekrar tabloyu ve TOAST tablosunu sorguladığımızda TOAST tablosu hala boş gelmektedir çünkü eklenen kayıt “Forenda Bilisim” in-line olarak sığdığı için bu değeri sıkıştırmak ve out-of-line yapma ihtiyacı kalmadı.
forendabilisim=# INSERT INTO toast_test (value) VALUES ('Forenda Bilisim');
INSERT 0 1
forendabilisim=# select * from toast_test;
id | value
----+-----------------
1 | Forenda Bilisim
(1 row)
forendabilisim=# select * from pg_toast.pg_toast_24588;
chunk_id | chunk_seq | chunk_data
----------+-----------+------------
(0 rows)
Aslında in-line satır aşağıda şekildeki gibi düşünebilirsiniz:
Aşağıdaki örnekte ise büyükçe bir değer girdikten sonra out-of-line yapılmış tablo görünüyor.
forendabilisim=# INSERT INTO toast_test(value) SELECT (SELECT string_agg(chr(floor(random() * 26)::int + 65), '') FROM generate_series(1,10000)) FROM generate_series(1,2);
INSERT 0 2
forendabilisim=# select * from pg_toast.pg_toast_24588;
chunk_id │ chunk_seq │ chunk_data
24595 | 1 | \x4e47484a...
24595 | 2 | \x48454a53...
24595 | 3 | \x494c5651...
Yukarıdaki işlemin özeti aşağıdaki şekilden de anlaşılacağı üzere satır değeri 2kB değerinden büyük olduğu için TOAST mekanizması bu tablo için çalışarak ilgili toast_test tablosuna sığmayan değerleri pg_toast.pg_toast_24588 tablosuna taşımıştır. Aslında toast_test tablosundan buraya pointer oluşturulmuştur diyebiliriz.
Şimdiye kadar açıklamalar ve örneklerimizle aslında kısaca TOAST nedir ve nasıl çalışırı incelemeye çalıştık. Bundan sonraki kısımda ise TOAST yapılan verilerin performansı nasıl etkilediği ile alakalıdır. Burada rastgele string değer üreten bir fonksiyon ile küçük,orta ve büyük diyebileceğimiz tablolar oluşturup 500K kayıt ekledikten sonra bu tabloların performansını kıyaslayacağız. (Bu fonksiyonun farklı yöntemlerini kendiniz de oluşturabilirsiniz)
CREATE OR REPLACE FUNCTION generate_random_string( length INTEGER, characters TEXT default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' ) RETURNS TEXT AS $$ DECLARE result TEXT := ''; BEGIN IF length < 1 then RAISE EXCEPTION 'Invalid length'; END IF; FOR __ IN 1..length LOOP result := result || substr(characters, floor(random() * length(characters))::int + 1, 1); end loop; RETURN result; END; $$ LANGUAGE plpgsql; CREATE FUNCTION forendabilisim=# SELECT generate_random_string(10); generate_random_string G3skDsiFEH (1 row) forendabilisim=# forendabilisim=# CREATE TABLE toast_test_small (id SERIAL, value TEXT); CREATE TABLE forendabilisim=# CREATE TABLE toast_test_medium (id SERIAL, value TEXT); CREATE TABLE forendabilisim=# CREATE TABLE toast_test_large (id SERIAL, value TEXT); CREATE TABLE forendabilisim=# SELECT c1.relname,c2.relname AS toast_relname FROM pg_class c1 JOIN pg_class c2 ON c1.reltoastrelid = c2.oid WHERE c1.relname LIKE 'toast_test%' AND c1.relkind = 'r'; relname | toast_relname -------------------+---------------- toast_test | pg_toast_24588 toast_test_large | pg_toast_24630 toast_test_medium | pg_toast_24621 toast_test_small | pg_toast_24612 (4 rows) forendabilisim=# forendabilisim=# INSERT INTO toast_test_small (value) SELECT 'small value' FROM generate_series(1, 500000); INSERT 0 500000 forendabilisim=# WITH str AS (SELECT generate_random_string(1800) AS value) INSERT INTO toast_test_medium (value) SELECT value FROM generate_series(1, 500000), str; INSERT 0 500000 forendabilisim=# WITH str AS (SELECT generate_random_string(4096) AS value) INSERT INTO toast_test_large (value) SELECT value FROM generate_series(1, 500000), str; INSERT 0 500000 forendabilisim=# --Tablo boyutları: forendabilisim=# SELECT c1.relname,pg_size_pretty(pg_relation_size(c1.relname::regclass)) AS size,c2.relname AS toast_relname,pg_size_pretty(pg_relation_size(('pg_toast.' || c2.relname)::regclass)) AS toast_size FROM pg_class c1 JOIN pg_class c2 ON c1.reltoastrelid = c2.oid WHERE c1.relname LIKE 'toast_test_%' AND c1.relkind = 'r'; relname | size | toast_relname | toast_size -------------------+--------+----------------+------------------------ toast_test_large | 25 MB | pg_toast_24630 | 2604 MB toast_test_medium | 977 MB | pg_toast_24621 | 0 bytes toast_test_small | 21 MB | pg_toast_24612 | 0 bytes (3 rows) forendabilisim=#
Yukarıdaki son sorgudan da anlaşılacağı üzere small ve medium tabloları 2kB sınırını geçmediği için TOAST tablo oluşturulmadı ama large tablo için 2604MB boyutunda TOAST tablosu oluşturuldu. Daha temiz bir execution plan almak için paralel sorgulamayı kaldırıp performans değerlerini kıyaslayalım:
Not: Cache,disk okuma vs durumları süreyi etkilemesin yani her seferinde çok farklı çalışma süreleri çıkmasın diye sorgular en az ikişer defa çalıştırılmıştır:
--small tablo için:
forendabilisim=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_small WHERE id = 6000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on toast_test_small (cost=0.00..6994.01 rows=1716 width=36) (actual time=0.506..28.659 rows=1 loops=1)
Filter: (id = 6000)
Rows Removed by Filter: 499999
Planning Time: 0.064 ms
Execution Time: 28.679 ms
(5 rows)
forendabilisim=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_small WHERE id = 6000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on toast_test_small (cost=0.00..6994.01 rows=1716 width=36) (actual time=0.784..29.388 rows=1 loops=1)
Filter: (id = 6000)
Rows Removed by Filter: 499999
Planning Time: 0.051 ms
Execution Time: 29.435 ms
(5 rows)
--medium tablo için:
forendabilisim=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_medium WHERE id = 6000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on toast_test_medium (cost=0.00..323437.50 rows=79375 width=36) (actual time=3.794..216.482 rows=1 loops=1)
Filter: (id = 6000)
Rows Removed by Filter: 499999
Planning Time: 0.042 ms
Execution Time: 216.499 ms
(5 rows)
forendabilisim=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_medium WHERE id = 6000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on toast_test_medium (cost=0.00..323437.50 rows=79375 width=36) (actual time=3.128..218.503 rows=1 loops=1)
Filter: (id = 6000)
Rows Removed by Filter: 499999
Planning Time: 0.116 ms
Execution Time: 218.521 ms
(5 rows)
--Large tablo için:
forendabilisim=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_large WHERE id = 6000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on toast_test_large (cost=0.00..8241.19 rows=2022 width=36) (actual time=0.333..27.272 rows=1 loops=1)
Filter: (id = 6000)
Rows Removed by Filter: 499999
Planning Time: 0.040 ms
Execution Time: 27.287 ms
(5 rows)
forendabilisim=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM toast_test_large WHERE id = 6000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on toast_test_large (cost=0.00..8241.19 rows=2022 width=36) (actual time=0.798..29.705 rows=1 loops=1)
Filter: (id = 6000)
Rows Removed by Filter: 499999
Planning Time: 0.046 ms
Execution Time: 29.722 ms
(5 rows)
Yuarıdaki süreler ve tabloların boyutları aşağıdaki tabloda özetlenmiştir. Aslında burada genel beklenti large tablosundaki sorgunun çok daha uzun sürmesi şeklinde ama gerçekte çok daha farklı bir tablo mevcut. Yani:
1- small tablo sorgusu: Bu sorgudaki small tablonun TOAST verisi yok ve 25 MB boyutundaki tabloyu tarayarak 29ms civarında veriyi getirdi.
2- large tablo sorgusu: Bu sorgudaki large tablonun TOAST verisi var ancak ilgili veriyi sadece ana tabloyu okuyarak getirdi.
3- Medium tablo sorgusu: Bu sorgudaki tablosundaki veri 2kB sınırını aşmadığı için TOAST tablo oluşturulmadı ve ilgili veriyi tüm tabloyu tarayarak getirdi.
TABLO ADI | SÜRE | TABLO BOYUTU | TOAST TABLO BOYUTU |
toast_test_small | 29.435 ms | 25 MB | 0 bytes |
toast_test_medium | 218.521 ms | 977 MB | 0 bytes |
toast_test_large | 29.722 ms | 21 MB | 2604 MB |
Peki yukarıdaki sorgu performansını bir de text alanı için inceleyelim:
-- Large tablo:
forendabilisim=# SELECT * FROM toast_test_large WHERE value LIKE 'foo%';
id | value
----+-------
(0 rows)
Time: 1973.698 ms (00:01.974)
forendabilisim=# SELECT * FROM toast_test_large WHERE value LIKE 'foo%';
id | value
----+-------
(0 rows)
Time: 1990.967 ms (00:01.991)
--Medium tablo:
forendabilisim=# SELECT * FROM toast_test_medium WHERE value LIKE 'foo%';
id | value
----+-------
(0 rows)
Time: 234.368 ms
forendabilisim=# SELECT * FROM toast_test_medium WHERE value LIKE 'foo%';
id | value
----+-------
(0 rows)
Time: 237.560 ms
--Small tablo:
forendabilisim=# SELECT * FROM toast_test_small WHERE value LIKE 'foo%';
id | value
----+-------
(0 rows)
Time: 52.951 ms
forendabilisim=# SELECT * FROM toast_test_small WHERE value LIKE 'foo%';
id | value
----+-------
(0 rows)
Time: 52.235 ms
Gördüğünüz üzere eğer sorguda text alanı olarak aratırsak işler değişiyor ve aşağıdaki tablodan da anlaşılacağı üzere large tablosundaki text verisini getirmek çok daha uzun sürüyor çünkü veriyi getirmesi için ilgili texti taramak zorundadır.
TABLO ADI | SÜRE | TABLO BOYUTU | TOAST TABLO BOYUTU |
toast_test_small | 52.235 ms | 25 MB | 0 bytes |
toast_test_medium | 237.560 ms | 977 MB | 0 bytes |
toast_test_large | 1990.967 ms | 21 MB | 2604 MB |
Buraya kadar olan performans karşılaştırmalarında aslında sorun olarak görebileceğimiz medium boyuttaki tablolar diyebiliriz. Bu tarz tablolarda ciddi performans sorunları ile karşılaşyorsanız bu yavaşlamanın arkasındaki sebep TOAST olabilir mi diye inceleyerek sorgu analizlerinizi yapmanızda fayda var. Eğer değiştirilebiliyorsa bu tarz tabloları birden fazla farklı tablolara bölerek toast yapılabilen kolonlar için ayrı bir tablo oluşturabilir ve bu tablodaki toast parametlerini daha düşük değerlere ayarlayarak performansı iyileştirebilirsiniz. Mesela aşağıdakiki gibi tablonun default 2kB olan toast_tuple_target
değerini 128 byte olacak şekilde ayarlayarak yeni bir tablo oluşturabilirsiniz.
CREATE TABLE toast_test_128_threshold (id SERIAL, value TEXT) WITH (toast_tuple_target=128);
CREATE TABLE
Referanslar:
https://www.postgresql.org/docs/12/storage-toast.html
https://hakibenita.com/sql-medium-text-performance
https://prog.world/save-a-lot-of-money-on-large-volumes-in-postgresql/
https://malisper.me/postgres-toast/
Faydalı olması dileğiyle.
dba@forenda.com.tr