- Depolama alanı yetersizliği uyarısı üzerine PostgreSQL temizliği yapıldı ve indeks ya da veri silmeden 70 GB’tan fazla optimize edilmemiş/kullanılmayan alan geri kazanıldı
- Başta kullanılmayan indeksler, tablo ve indeks bloat’u,
REINDEX CONCURRENTLY, pg_repack, PostgreSQL 13’ün B-Tree deduplication özelliği gibi genel geri kazanım teknikleri incelendi
- Belirleyici fark, Oracle’ın aksine PostgreSQL’in NULL değerleri de indekslemesi oldu; çoğu NULL olan
cancelled_by_user_id tam indeksi partial index’e çevrilince 769 MB’tan 5 MB’ın altına düştü
pg_stats.null_frac değeri yüksek tek sütunlu indeksler bulunarak WHERE column IS NOT NULL koşullu partial index adayları seçildi; istatistik sayaçları sıfırlandıktan sonra gerçek kullanım pg_stat_all_indexes ile doğrulandı- Django’da nullable
ForeignKey varsayılan B-Tree indeksi oluşturabileceğinden, db_index=False açıkça belirtilmeli ve models.Index(..., condition=Q(...__isnull=False)) ile partial index doğrudan tanımlanmalı
Depolama alanı yetersizliği uyarısıyla başlayan temizlik
- Birkaç ayda bir veritabanı izleme sisteminde depolama alanı yetersizliği uyarısı oluşuyordu; normalde buna depolama ekleyerek yanıt veriliyordu
- O dönemde karantina nedeniyle sistem yükü düşüktü; bu da normalden daha riskli/masraflı olan temizlik çalışmasını denemek için uygun bir durumdu
- Sonuçta indeksleri silmeden veya verileri kaldırmadan 70 GB’tan fazla optimize edilmemiş/kullanılmayan alan geri kazanıldı
- Sadece genel indeks ve tablo yeniden düzenlemeleriyle bile çok alan azaltıldı; ardından beklenmedik bir keşifle yaklaşık 20 GB kullanılmayan indeks değeri daha kaldırıldı
İlk şüphelenilen nedenler
-
Kullanılmayan indeksler
- Kullanılmayan indeksler sorgu performansı için oluşturulur; ancak yer kaplar ve
INSERT ile UPDATE işlemlerini yavaşlatabilir
pg_stat_all_indexes içinde idx_scan = 0, idx_tup_read = 0, idx_tup_fetch = 0 olan indeksler bulunarak son istatistik sıfırlamasından bu yana taranmamış veya fetch edilmemiş indeksler kontrol edildi- Dışarıdan kullanılmıyor gibi görünse de kaldırılmaması gereken durumlar olabilir
- PostgreSQL optimizer’ın indeksin kendisini değil metadata’sını kullandığı durumlar
- Uzun süredir güncellenmeyen tabloların unique veya primary key kısıtlarını garanti eden indeksler
- Bir indeksin gerçekten silinebilir olup olmadığı liste üzerinden tek tek incelenmelidir; istatistik sayaçları da denetimden sonra sıfırlanacak şekilde yönetilir
- Bu örnekte aynı işlem düzenli olarak yapıldığından silinecek kullanılmayan indeks yoktu
-
İndeks ve tablo bloat’u
- PostgreSQL bir satırı güncellediğinde mevcut tuple’ı dead olarak işaretler ve yeni tuple’ı başka bir alana ekler; bu nedenle bloat oluşabilir
- Bloat yalnızca tabloları değil indeksleri de etkiler ve gerçekte gerekenden daha fazla depolama alanı kullanılmasına yol açar
- Bloat tahmini basit değildir; PostgreSQL wiki’sindeki ve
pgsql-bloat-estimation içindeki sorgularla tablo ve B-Tree indeks bloat’u tahmin edildi
İndeks bloat’unu azaltma yöntemleri
-
İndeksi yeniden oluşturma/düzenleme
- İndeks bloat’unu kaldırmak için indeksi yeniden oluşturmak gerekir
- Doğrudan drop edip create etmek, optimize edilmiş biçimde yeni bir indeks oluşturmayı sağlar
- PostgreSQL’in
REINDEX komutu mevcut indeksi yerinde yeniden düzenler
- Genel yeniden oluşturma yöntemi tabloya lock koyup değişiklikleri engellediği için, production ortamında
REINDEX INDEX CONCURRENTLY index_name yöntemi daha uygundur
REINDEX CONCURRENTLY, _ccnew sonekli yeni bir indeks oluşturur, değişiklikleri senkronize eder; tamamlandığında eski indeksle değiştirir ve eski indeksi siler- Yeniden düzenleme ortada kesilirse yeni indeks invalid durumda kalıp alan kaplayabilir; bu nedenle
_ccnew olan ve indisvalid olmayan indeksler bulunup kaldırılmalıdır
-
PostgreSQL 13’te B-Tree deduplication
- PostgreSQL 13, B-Tree indekslerde yinelenen değerleri daha verimli depolayan B-Tree Deduplication özelliğini getirdi
- PostgreSQL 12’ye kadar, yinelenen değerler çok olsa bile her tekrar indeks leaf’lerinde tekrar tekrar saklanabildiğinden çok alan kullanabiliyordu
- PostgreSQL 13’te deduplication etkin olduğunda yinelenen değer yalnızca bir kez saklanır; bu da çok tekrar içeren indekslerin boyutunu ciddi biçimde etkileyebilir
- PostgreSQL 13’te
deduplicate_items = ON varsayılandır; önceki sürümlerden yükseltme yapıldıysa tam etkiyi almak için indekslerin REINDEX ile yeniden düzenlenmesi gerekir
- 1 milyon satırlık örnekte, çok fazla tekrar içeren unique olmayan sütun indeksi deduplication uygulanınca 21 MB’tan 6840 kB’ye düştü; unique sütun indeksi ise 21 MB olarak değişmedi
- Bu örnekte PostgreSQL 13 henüz yeni bir sürümdü ve bulut sağlayıcısı desteklemediği için deduplication ile alan azaltılamadı
Tablo bloat’u ve pg_repack
-
Tablo bloat’unu kaldırmanın kısıtları
- Tablolarda da dead tuple’lar nedeniyle bloat ve parçalanma oluşabilir
- İndekslerden farklı olarak tabloları basitçe yeniden oluşturmak zordur; yeni tablo oluşturma, veri taşıma, senkronizasyon, indekslerin, kısıtların ve referential constraint’lerin yeniden oluşturulması gerekir
VACUUM FULL table_name, bloat’un ve dead tuple’ların kapladığı alanı geri kazanabilir; ancak tablo lock’u gerektirir- Tabloyu yeniden oluşturma ve
VACUUM FULL, sırasıyla büyük geliştirme çalışmasına veya downtime’a yol açabilir
-
pg_repack kullanımı
- pg_repack, tabloları ve indeksleri downtime olmadan yeniden düzenlemek için kullanılan bir PostgreSQL eklentisidir
- Kullanmak için
CREATE EXTENSION pg_repack; ile eklenti oluşturulur ve konsolda pg_repack -k --table table_name db_name biçiminde çalıştırılır
pg_repack yeni bir tablo oluşturur, kaynak veriyi yüklerken yeni verilerle senkronize eder; ardından indeksleri de yeniden düzenler ve sonunda iki tabloyu değiştirir- Production’da uygularken bazı kısıtlar vardır
- Yeniden düzenlenecek tablo ve indeks boyutu kadar yaklaşık ek depolama alanı gerekir
- Repack başarısız olursa veya elle durdurulursa ara nesneler kalabilir ve manuel temizlik gerekebilir
- Depolama alanı zaten yetersizse ek alan ihtiyacı nedeniyle uygun olmayabilir; bu nedenle boş alan izlenmeli ve önceden plan yapılmalıdır
Beklenmedik keşif: PostgreSQL NULL’ları da indeksliyor
- Genel tekniklerle kullanılmayan indeksleri kaldırma ve tablo/indeks bloat temizliği tamamlandıktan sonra bile azaltılabilecek alan kalmıştı
- En büyük tablolardan biri işlem verilerini tutuyordu; ödeme sonrası kullanıcının iptal edip iade aldığı durumlar da kaydediliyordu
- İşlem tablosunda hem satın alan kullanıcı hem de iptal eden kullanıcı için foreign key vardı ve her alan için B-Tree indeks tanımlanmıştı
- Satın alan kullanıcıda
NOT NULL kısıtı olduğundan tüm satırlarda değer vardı
- İptal eden kullanıcı nullable’dı ve iptal nadir olduğundan değerlerin çoğu NULL’dı
- İptal eden kullanıcı indeksi, satın alan kullanıcı indeksinden çok daha küçük bekleniyordu; ancak gerçek boyutu aynıydı
- Oracle’da NULLs are not indexed olarak öğrenilmişti; PostgreSQL’de ise NULL değerler de indekslenir
- Mevcut tam indeks şöyleydi
CREATE INDEX transaction_cancelled_by_ix
ON transactions(cancelled_by_user_id);
- NULL değerleri hariç tutan partial index’e çevirerek hipotez doğrulandı
DROP INDEX transaction_cancelled_by_ix;
CREATE INDEX transaction_cancelled_by_part_ix
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;
- Yeniden indeksleme sonrası tam indeks boyutu 769 MB idi ve değerlerin %99’undan fazlası NULL’dı
- NULL’ları hariç tutan partial index 5 MB’ın altındaydı; bu da indeksin %99’dan fazlasının azaltıldığı anlamına geldi
- Tablo istatistikleri sıfırlanıp bir süre gözlemlendiğinde yeni indeksin eski indeks gibi kullanıldığı görüldü; performans düşüşü olmadan 760 MB’tan fazla kullanılmayan indexed tuple kaldırıldı
Partial index adaylarını bulma
- Bir partial index’ten iyi sonuç alındıktan sonra benzer indeksleri bulmak için
pg_stats.null_frac değeri yüksek sütunlara ait indeksler arandı
null_frac, PostgreSQL’in ilgili sütun değerleri içinde tahmin ettiği NULL oranıdır- Aday arama sorgusu şu koşulları uyguladı
- Primary key partial olamayacağı için hariç tutuldu
- Zaten partial index olanlar hariç tutuldu
- Composite index’ler hariç tutuldu
- Yalnızca 10 MB’tan büyük indeksler dahil edildi
- Örnek sonuçta
tx_cancelled_by_ix 1418 MB ve null_frac %96,15 olduğundan yaklaşık 1363 MB tasarruf potansiyeli olan bir aday olarak göründü
- Tüm NULL’ları indeksten hariç tutmak her zaman avantajlı değildir
IS NULL koşuluyla NULL arayan sorgular, NULL değerlerine yönelik indeks avantajından yararlanabilir- Yalnızca NULL olmayan değerler değil, neredeyse hiç sorgulanmayan başka değerler de partial index ile hariç tutulabilir
- Başlıktaki 20 GB, primary veritabanında yaklaşık 10 GB azaltılınca replica’da da benzer depolama alanının birlikte azalması sayesinde mümkün oldu
Django ORM’de partial index’e geçiş
-
nullable ForeignKey ve örtük indeks
- Django,
db_index=False açıkça belirtilmezse models.ForeignKey alanı için örtük olarak B-Tree indeksi oluşturur
- İptal eden kullanıcıyı saklayan nullable
ForeignKey üzerinde hiçbir ayar yapılmazsa Django tam indeks oluşturur
- Partial index oluşturmak için FK alanında
db_index=False belirtilmeli ve Meta.indexes içine koşullu models.Index eklenmelidir
class Transaction(models.Model):
cancelled_by_user = models.ForeignKey(
to=User,
null=True,
on_delete=models.CASCADE,
db_index=False,
)
class Meta:
indexes = (
models.Index(
fields=('cancelled_by_user_id', ),
name='%(class_name)s_cancelled_by_part_ix',
condition=Q(cancelled_by_user_id__isnull=False),
),
)
- nullable foreign key’ler partial index adayı olmaya uygundur
- Örtük özelliğin fark edilmeden indeks oluşturmasını engellemek için, foreign key’lerde
db_index değerinin her zaman açıkça belirtilmesini zorunlu kılan bir Django check’i oluşturuldu
-
Downtime olmadan tam indeksi partial index’e çevirmek
- Tam indeksi partial index ile değiştirirken downtime veya performans düşüşünden kaçınmak için adım adım ilerlemek gerekir
- İlgili Django modeli değiştirilip tam indeks partial index’e çevrilir; ancak Django’nun oluşturduğu migration olduğu gibi çalıştırılmaz
- Bu migration FK kısıtını devre dışı bırakıp mevcut tam indeksi drop ettikten sonra yeni partial index’i oluşturabilir; bu da downtime’a ve performans düşüşüne neden olabilir
./manage.py sqlmigrate ile SQL üretilir, yalnızca CREATE INDEX çıkarılıp CONCURRENTLY olacak şekilde düzenlenir ve veritabanında manuel çalıştırılır- Mevcut tam indeks hâlâ durduğu için, partial index oluşturulurken sorgular eski indeksi kullanmaya devam edebilir
- Yeni partial index’in kullanıldığını doğrulamak için tam indeksin istatistik sayaçları
pg_stat_reset_single_table_counters(<full index oid>) ile sıfırlanır
- Sonrasında
pg_stat_all_indexes içindeki idx_scan, idx_tup_read, idx_tup_fetch izlenerek tam indeks ve partial index kullanımı karşılaştırılır
- Partial index’in kullanıldığına karar verilirse tam indeks drop edilir ve gerçekten kazanılacak depolama alanı doğrulanır
- Veritabanı durumu model durumuyla eşleştikten sonra
./manage.py migrate --fake ile Django migration’ı çalıştırılmış olarak kaydedilir
- Dev, QA, staging gibi downtime kaygısının daha az olduğu ortamlarda Django migration’ı normal şekilde çalışır ve tam indeks partial index ile değiştirilir
Sonuç
- Disk, depolama parametreleri ve ayar optimizasyonlarıyla performans ve alan iyileştirmesinde bir sınır vardır
- Son iyileştirme alanı veritabanı nesnelerinin kendisinde, bu örnekte ise indeks tanımını değiştirmekteydi
- Uygulanan akış üç adıma indirgenebilir
- Kullanılmayan indeksleri kaldırma
- Tabloları ve indeksleri repack etme; mümkünse B-Tree deduplication’ı etkinleştirme
- Yalnızca gerekli değerleri indekslemek için partial index kullanma
- Bu tür temizlik, ek depolama provision etmeden önce birkaç gün daha zaman kazandırabilecek pratik bir seçenek olabilir
1 yorum
Hacker News yorumları
Depolama alanı bol olsa bile indeksleri küçük tutmak iyidir. Böylece sıcak sete daha fazla veri sığabilir.
Ancak TB ölçeğinde veritabanı işleten biri açısından “sadece daha fazla depolama eklemek” de her zaman geçerli bir seçenektir. Özellikle bulut dışındaysanız daha da geçerli; kendi donanımınızda kurumsal NVMe SSD’ler yaklaşık $80/TB, DDR4 RAM ise yaklaşık $1.20/GB seviyesinde. 4 saatlik mühendislik maliyetini kabaca $1000 varsayarsak 800GB RAM ya da 12TB depolama alınabilir.
Sadece 1 yıllık haftalık yedekler bile 1TB’ı 50TB’a çıkarır; çevrimdışı depolama olsa da durum aynıdır. Bizim şirkette de canlı DB’ye depolama eklemek kolaydı ve bunu yıllarca yaptık, ancak çok TB’lık yedekleri taşımak giderek zahmetli hale geldiği için mühendislik maliyeti gerektirse bile üretim verisini azaltmamız gereken bir noktadayız.
Tek seferlik durumlarda mevcut altyapı boş raf alanı veya yedek PCIe yuvası gibi maliyetleri absorbe edebilir, ancak bu tür amortisman küçük ölçekte iyi çalışır. Ayrıca sistem büyüdükçe yönetim maliyetleri ve gecikme artışı nedeniyle cihaz başına performans kaçınılmaz olarak düşer. Sistemi sürekli büyüterek çözmeye çalışırsanız bütün sistem giderek hantallaşır ve sonunda işletilemez hale gelebilir. Buna karşılık gereken sistem kaynaklarını azaltan çözümler, birim kaynak başına performansı artırır ve başlı başına daha kaliteli bir varlık yaratır.
Bugün bir optimizasyonla 1TB tasarruf etseniz, birkaç yıl sonra 2TB tasarruf ediyor olabilirsiniz; buna benzer birkaç karar bile tek haneli katlardan daha büyük fark yaratabilir. Daha büyük ölçekte bu özellikle böyledir ve sonuçta her zaman bir denge kurmak gerekir.
“Donanım fırlatıp atmak” bazen akıllıca bir seçenek olabilir, ancak çoğu durumda bugün 128GB RAM’li bir sunucuyu yarın $1K’ya 1TB RAM’e çıkarmanın zor olduğunu düşünüyorum.
Ancak tek seferlik harcama değil aylık maliyet olduğu için hesap biraz daha karmaşıklaşır. Elbette veritabanı sabit bir hızda büyüyorsa, şirket içi tek seferlik provizyonlama da fiilen aylık maliyete yakın hale gelir.
Yazıda PostgreSQL 13’te olup kullanılan 12’de olmayan B-tree deduplication’dan bahsedilirken, bazı foreign key indeks değerlerinin büyük çoğunluğunun NULL olduğu söyleniyor.
Bu durumda B-tree deduplication’ın yardımcı olup olmayacağını merak ediyorum. PostgreSQL 13 belgelerindeki 63.4.2’ye bakınca öyle olacak gibi görünüyor: “B-Tree deduplication is just as effective with ‘duplicates’ that contain a NULL value...” Elbette yazıda uygulanan partial index kadar etkili olmazdı ama ilginç.
https://www.postgresql.org/docs/13/btree-implementation.html
https://news.ycombinator.com/item?id=25989467
Bu yazı, asıl konu olan partial index “keşfi”nin yanı sıra PostgreSQL’de alanın verimsiz kullanılmasından endişe edildiğinde bilinmesi iyi olacak teknikleri genel olarak toparladığı için faydalıydı.
Ancak bir startup ya da erken aşama şirketseniz, bu tür depolama sorunlarında boyut optimizasyonuna kafa yormaktansa daha fazla disk alanı ekleme seçeneğinin neredeyse her zaman daha iyi olduğunu hatırlamakta fayda var. Geliştiriciler pahalı, disk ucuz.
Eskiden hızlı büyüyen bir startup’ta çalışırken kuruculardan biri sürekli tek bir sunucuyu yükseltmekte ısrar ediyordu; yedeklilik ve backup vardı ama mimari basit olduğu için yönetmesi, debug etmesi ve kurtarması kolaydı. Şirketin ilk 5 yılında tüm sistem tek bir sunucuda çalışırken yine de üstel büyüdü ve dünya çapında milyonlarca kullanıcıya hizmet verdi. Bunu gördükten sonra, yalnızca gerektiğinde ve en basit, en doğrudan yolla yükseltme yapmak gerektiğine ikna oldum.
Değerlerin çoğunun NULL olduğu durumlarda olduğu gibi kullanım senaryosuyla açıkça örtüşen partial index kullanmak doğru modellemeye yakındır; bunu erken optimizasyon ya da geliştirici zamanını boşa harcama olarak görmemek gerekir.
Benzer bağlamda bu script’ler epey yardımcı olmuştu.
https://github.com/NikolayS/postgres_dba
Büyük bir tablodaki sütun sırasını yeniden düzenleyerek depolama alanının yaklaşık %10’unu, yani yaklaşık 100GB’ı geri kazanabildik.
Geçen hafta yalnızca
reindexvepg_repackile 600GB’lık DB’de 200GB yer açtım. En kötü tabloda veri 17GB iken indeksler 142GB’tı; yeniden indekslemeden sonra 21GB’a düştü.Bu tabloda indeksleme aşırıydı; birden fazla sütun kombinasyonu için çeşitli indeksler vardı. Devasa indeksin nedenlerinden biri veri dağılımı gibi görünüyor. 2015’ten beri sürekli insert, update ve delete olmuştu; eski verilerin silinme olasılığı daha yüksek olduğu için son yılların verisi daha fazlaydı ama hâlâ yaklaşık %0.1’i 2015 verisiydi. Bu uzun kuyruklu, çarpık dağılımın vacuum’ın indeks şişmesini ele almasını zorlaştırmış olabileceğini düşünüyorum.
O dönemde de tartışılmıştı: An unexpected find that freed 20GB of unused index space in PostgreSQL - https://news.ycombinator.com/item?id=25988871 - Şubat 2021, 78 yorum
Kullanılmayan indeksleri, optimizasyon fırsatlarını ve yüksek gecikmeli sorguları bulmak için pganalyze.com şiddetle öneriliyor
Gerçekten de “tek tuhaf numarayla 20 GB tasarruf” sözü abartı değilmiş. Eski Oracle / SQL Server sahalarında neredeyse hiç duymadığım PostgreSQL’e özgü garip indeks biçimleri kullanmak istediklerine dair çok istek alıyorum; ama NULL değerlerin de indekslendiğini bilmiyordum
İlk katıldığım büyük ölçekli projede, veri kümesi büyüdükçe işlerin neden yavaşladığını ekip anlayamamıştı
İndekslerde kayıt başına ekleme süresi log(n)’dir. Test DB’sinde 1000 kayıt varken sayı 65k’ya yaklaştığında ekleme süresi %60 artar (2^10’a karşı 2^16). Başarı her şeyi yavaşlatır ve sunucu yükseltmelerinin de bir sınırı vardır. İş biriminin istediği muğlak bir özellik yüzünden birkaç indeks daha eklerseniz, bu kez süre iki katına çıkar
Yüz milyonlarca kaydı olan ve tablo/koleksiyon başına 40’tan fazla indeks içeren birçok DB de yönetiyorum
Bir B-tree sayfası içinde ikili arama 100 çevrimden az sürer. 100 milyon kayıt üzerinde B-tree araması da hâlâ mikrosaniye mertebesinde olmalı; bunun üzerindeki ikili arama da yüzlerce nanosaniye olmasa bile mikrosaniye düzeyinde kalır
Bir ya da birkaç değerin çok yaygın olduğu durumlarda da bu ilke geçerli mi? Örneğin bir tamsayı kolonunun %90’ı 0 ise
where val = 0koşulunun satırların %90’ını bırakacağını bilirse, indeks yerine tablo taramayı seçebilir