PostgreSQL'de büyük hacimlerde bir kuruş tasarruf edin

tarafından ortaya çıkan büyük veri akışlarının kaydedilmesi konusuna devam edilmesi bölümleme hakkında önceki makale, bu konuda şunları yapabileceğiniz yollara bakacağız: depolananın “fiziksel” boyutunu azaltın PostgreSQL'de ve bunların sunucu performansı üzerindeki etkileri.

hakkında konuşacağız TOAST ayarları ve veri hizalama. "Ortalama olarak" bu yöntemler çok fazla kaynak tasarrufu sağlamaz, ancak uygulama kodunu hiçbir şekilde değiştirmez.

Ancak, doğası gereği hemen hemen her izlemenin depolanması gerektiğinden deneyimlerimiz bu konuda oldukça verimli oldu. çoğunlukla yalnızca ekleme Kaydedilen veriler açısından. Ve bunun yerine veritabanına diske yazmayı nasıl öğretebileceğinizi merak ediyorsanız 200MB / sn yarısı kadar - lütfen kedinin altında.

Büyük verinin küçük sırları

İş profiline göre servisimizinlerinden düzenli olarak ona uçuyorlar metin paketleri.

Dan beri VLSI kompleksiveritabanını izlediğimiz, karmaşık veri yapılarına sahip çok bileşenli bir ürün, ardından sorgular maksimum performans için oldukça böyle ortaya çıktı Karmaşık algoritmik mantığa sahip “çok ciltli”. Dolayısıyla, bize gelen günlükteki bir isteğin her bir örneğinin veya bunun sonucunda ortaya çıkan yürütme planının hacmi "ortalama olarak" oldukça büyük çıkıyor.

"Ham" verileri yazdığımız tablolardan birinin yapısına bakalım - yani, günlük girişindeki orijinal metin:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

En önemli şeyin metin olduğu tipik bir işaret (tabii ki zaten bölümlere ayrılmıştır, dolayısıyla bu bir bölüm şablonudur). Bazen oldukça hacimli.

Bir PG'deki bir kaydın "fiziksel" boyutunun bir sayfadan fazla veriyi işgal edemeyeceğini, ancak "mantıksal" boyutun tamamen farklı bir konu olduğunu hatırlayın. Bir alana hacimsel bir değer (varchar/text/bytea) yazmak için şunu kullanın: TOAST teknolojisi:

PostgreSQL sabit bir sayfa boyutu kullanır (genellikle 8 KB) ve demetlerin birden fazla sayfaya yayılmasına izin vermez. Bu nedenle çok büyük alan değerlerinin doğrudan depolanması mümkün değildir. Bu sınırlamanın üstesinden gelmek için büyük alan değerleri sıkıştırılır ve/veya birden fazla fiziksel satıra bölünür. Bu, kullanıcı tarafından fark edilmeden gerçekleşir ve çoğu sunucu kodu üzerinde çok az etkisi olur. Bu yönteme TOAST adı veriliyor...

Aslında, "potansiyel olarak büyük" alanlara sahip her tablo için otomatik olarak “dilimleme” özelliğine sahip eşleştirilmiş bir tablo oluşturulur 2KB segmentlerdeki her "büyük" kayıt:

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

Yani "büyük" değere sahip bir dize yazmamız gerekiyorsa data, o zaman gerçek kayıt gerçekleşecektir sadece ana masaya ve PK'sına değil, aynı zamanda TOAST ve PK'sına da.

TOAST etkisini azaltmak

Ancak kayıtlarımızın çoğu hâlâ o kadar büyük değil. 8KB'ye sığmalı - Bundan nasıl tasarruf edebilirim?..

Bu özelliğin yardımımıza geldiği yer burasıdır STORAGE tablo sütununda:

• GENİŞLETİLMİŞ hem sıkıştırmaya hem de ayrı depolamaya izin verir. Bu standart seçenek çoğu TOAST uyumlu veri türü için. İlk önce sıkıştırmayı gerçekleştirmeye çalışır, ardından satır hala çok büyükse bunu tablonun dışında saklar.
• ANA sıkıştırmaya izin verir ancak ayrı depolamaya izin vermez. (Aslında bu tür sütunlar için ayrı depolama yine de gerçekleştirilecektir, ancak yalnızca Son çare olarak, dizeyi sayfaya sığacak şekilde küçültmenin başka yolu olmadığında.)

Aslında metin için tam olarak ihtiyacımız olan şey bu - mümkün olduğu kadar sıkıştırın ve hiç sığmazsa TOAST'a koyun. Bu, tek bir komutla doğrudan anında yapılabilir:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Etki nasıl değerlendirilir?

Veri akışı her gün değiştiği için mutlak sayıları karşılaştıramayız ancak göreceli olarak karşılaştırabiliriz. daha küçük pay Bunu TOAST'a yazdık - çok daha iyi. Ancak burada bir tehlike var; her bir kaydın "fiziksel" hacmi ne kadar büyük olursa, indeks de o kadar "geniş" olur çünkü daha fazla veri sayfasını kapsamamız gerekir.

bölüm değişikliklerden önce:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

bölüm değişikliklerden sonra:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

Aslında biz TOAST'a 2 kat daha az yazmaya başladım, yalnızca diski değil aynı zamanda CPU'yu da boşalttı:

Sadece "yazma" değil, diski "okuma" konusunda da küçüldüğümüzü not edeceğim - çünkü bir tabloya bir kayıt eklerken, aynı zamanda her indeksin ağacının bir kısmını da "okumamız" gerekir. onların gelecekteki konumu.

PostgreSQL 11'de kimler iyi yaşayabilir?

PG11'e güncelleme yaptıktan sonra, TOAST'ı "ayarlamaya" devam etmeye karar verdik ve bu sürümden itibaren parametrenin ayarlama için uygun hale geldiğini fark ettik. toast_tuple_target:

TOAST işleme kodu yalnızca tabloda depolanacak satır değeri TOAST_TUPLE_THRESHOLD bayttan (genellikle 2 KB) büyük olduğunda tetiklenir. TOAST kodu, satır değeri TOAST_TUPLE_TARGET bayttan (değişken değer, ayrıca genellikle 2 KB) küçük olana veya boyut azaltılamayana kadar alan değerlerini sıkıştıracak ve/veya tablonun dışına taşıyacaktır.

Genellikle sahip olduğumuz verilerin ya "çok kısa" ya da "çok uzun" olduğuna karar verdik ve bu nedenle kendimizi mümkün olan minimum değerle sınırlamaya karar verdik:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Yeni ayarların yeniden yapılandırma sonrasında disk yüklemeyi nasıl etkilediğini görelim:

Fena değil! Ortalama diske giden kuyruk azaldı yaklaşık 1.5 kat ve disk yüzde 20 “meşgul”! Ama belki bu bir şekilde CPU'yu etkilemiştir?

En azından daha da kötüleşmedi. Bununla birlikte, bu tür hacimlerin bile ortalama CPU yükünü daha yükseğe çıkaramayacağına karar vermek zordur. 5%.

Terimlerin yerleri değiştirildiğinde toplam değişir!

Bildiğiniz gibi, bir kuruş bir ruble tasarruf etmenizi sağlar ve depolama hacimlerimizle bu yaklaşık 10TB/ay küçük bir optimizasyon bile iyi bir kazanç sağlayabilir. Bu nedenle verilerimizin fiziksel yapısına - tam olarak nasıl Kaydın içindeki “yığılmış” alanlar tabloların her biri.

Çünkü yüzünden veri hizalama bu doğrudan ileri ortaya çıkan hacmi etkiler:

Birçok mimari, makine sözcük sınırlarında veri hizalaması sağlar. Örneğin, 32 bitlik bir x86 sisteminde, tamsayılar (tamsayı türü, 4 bayt), çift duyarlıklı kayan nokta sayıları (çift duyarlıklı kayan nokta, 4 bayt) gibi 8 baytlık bir sözcük sınırı üzerinde hizalanacaktır. Ve 64 bitlik bir sistemde, çift değerler 8 baytlık sözcük sınırlarına göre hizalanacaktır. Bu uyumsuzluğun başka bir nedenidir.

Hizalama nedeniyle tablo satırının boyutu alanların sırasına bağlıdır. Genellikle bu etki çok belirgin değildir, ancak bazı durumlarda boyutta önemli bir artışa yol açabilir. Örneğin, char(1) ile integer alanlarını karıştırırsanız, genellikle aralarında 3 bayt israf olur.

Sentetik modellerle başlayalım:

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байт

İlk durumda birkaç ekstra bayt nereden geldi? Basit - 2 baytlık küçük nokta, 4 baytlık sınıra hizalanmış bir sonraki alandan önce ve sonuncu olduğunda hiçbir şey yoktur ve hizalamaya gerek yoktur.

Teorik olarak her şey yolunda ve alanları istediğiniz gibi yeniden düzenleyebilirsiniz. Günlük bölümü 10-15 GB'ı kaplayan tablolardan birinin örneğini kullanarak bunu gerçek veriler üzerinde kontrol edelim.

Başlangıç ​​yapısı:

CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

Sütun sırasını değiştirdikten sonraki bölüm - tam olarak aynı alanlar, yalnızca farklı sıra:

CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan:  dt date NOT NULL,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)

Bölümün toplam hacmi "gerçeklerin" sayısına göre belirlenir ve yalnızca dış süreçlere bağlıdır, bu nedenle yığının boyutunu bölelim (pg_relation_size) içindeki kayıt sayısına göre - yani şunu elde ederiz: gerçek saklanan kaydın ortalama boyutu:

Eksi %6 hacim, Harika!

Ama elbette her şey o kadar pembe değil - sonuçta, indekslerde alanların sırasını değiştiremeyizve dolayısıyla “genel olarak” (pg_total_relation_size) ...

...hâlâ buradayım %1.5 tasarruf edilditek bir kod satırını değiştirmeden. Evet evet!

Alanları düzenlemek için yukarıdaki seçeneğin en uygun olanı olmadığını belirtmek isterim. Çünkü estetik nedenlerden dolayı bazı alan bloklarını "yırtmak" istemezsiniz - örneğin birkaç tane (pack, recno), bu tablonun PK'sidir.

Genel olarak alanların "minimum" düzenini belirlemek oldukça basit bir "kaba kuvvet" görevidir. Bu nedenle, verilerinizden bizimkinden daha iyi sonuçlar alabilirsiniz; deneyin!

Kaynak: habr.com