Uštedite novčić na velikim količinama u PostgreSQL-u

Nastavljajući temu snimanja velikih tokova podataka koju je pokrenuo prethodni članak o particioniranju, u ovom ćemo pogledati načine na koje možete smanjiti "fizičku" veličinu pohranjenog u PostgreSQL-u i njihov utjecaj na performanse poslužitelja.

Razgovarat ćemo o TOAST postavke i usklađivanje podataka. “U prosjeku,” ove metode neće uštedjeti previše resursa, ali bez ikakve izmjene koda aplikacije.

Međutim, pokazalo se da je naše iskustvo vrlo produktivno u tom pogledu, jer pohranjivanje gotovo svakog praćenja po svojoj prirodi jest uglavnom samo za dodavanje u smislu snimljenih podataka. A ako se pitate kako možete naučiti bazu podataka da umjesto toga piše na disk 200MB / s upola manje - molim pod kat.

Male tajne velikih podataka

Po profilu posla našu uslugu, redovito mu lete iz jazbina tekstualni paketi.

I od VLSI kompleksčiju bazu podataka pratimo je višekomponentni proizvod sa složenim strukturama podataka, zatim upitima za maksimalne performanse ispasti baš ovako “multi-volume” sa složenom algoritamskom logikom. Dakle, obujam svake pojedinačne instance zahtjeva ili rezultirajućeg plana izvršenja u dnevniku koji nam dolazi "u prosjeku" je prilično velik.

Pogledajmo strukturu jedne od tablica u koju upisujemo “sirove” podatke - to jest, ovdje je izvorni tekst iz unosa u dnevnik:

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

Tipičan znak (naravno već razdijeljen, pa je ovo predložak odjeljka), gdje je najvažniji tekst. Ponekad prilično voluminozan.

Podsjetimo se da "fizička" veličina jednog zapisa u PG-u ne može zauzeti više od jedne stranice podataka, ali "logička" veličina je sasvim druga stvar. Za pisanje volumetrijske vrijednosti (varchar/text/bytea) u polje, koristite TOAST tehnologija:

PostgreSQL koristi fiksnu veličinu stranice (obično 8 KB) i ne dopušta torkama da obuhvaćaju više stranica. Stoga je nemoguće izravno pohraniti vrlo velike vrijednosti polja. Kako bi se prevladalo ovo ograničenje, velike vrijednosti polja komprimiraju se i/ili dijele na više fizičkih linija. To se događa nezapaženo od strane korisnika i ima mali utjecaj na većinu poslužiteljskog koda. Ova metoda je poznata kao TAST...

Zapravo, za svaku tablicu s "potencijalno velikim" poljima, automatski stvara se uparena tablica s "slicingom". svaki "veliki" zapis u segmentima od 2 KB:

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

To jest, ako moramo napisati niz s "velikom" vrijednošću data, tada će se dogoditi pravo snimanje ne samo za glavni stol i njegov PK, već i za TOAST i njegov PK.

Smanjenje utjecaja TOAST-a

Ali većina naših zapisa još uvijek nije tako velika, treba stati u 8KB - Kako mogu uštedjeti novac na ovome?..

Tu nam u pomoć dolazi atribut STORAGE u stupcu tablice:

• PROŠIRENO omogućuje i kompresiju i odvojeno pohranjivanje. Ovaj standardna opcija za većinu tipova podataka kompatibilnih s TOAST-om. Prvo pokušava izvršiti kompresiju, a zatim ga pohranjuje izvan tablice ako je red još uvijek prevelik.
• GLAVNI omogućuje kompresiju, ali ne i odvojeno pohranjivanje. (Zapravo, zasebno pohranjivanje će se i dalje izvoditi za takve stupce, ali samo u krajnjem slučaju, kada ne postoji drugi način da se niz smanji tako da stane na stranicu.)

Zapravo, to je upravo ono što nam treba za tekst - stisnite ga koliko god je moguće, a ako nikako ne stane, stavite ga u TAST. To se može učiniti izravno u hodu, jednom naredbom:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Kako procijeniti učinak

Budući da se tijek podataka mijenja svaki dan, ne možemo uspoređivati ​​apsolutne brojke, već relativne manji udio Zapisali smo to u TOAST - tim bolje. Ali ovdje postoji opasnost - što je veći "fizički" volumen svakog pojedinog zapisa, indeks postaje "širi", jer moramo pokriti više stranica podataka.

Odjeljak prije promjena:

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

Odjeljak nakon promjena:

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

Zapravo, mi počeo pisati u TOAST 2 puta rjeđe, koji je rasteretio ne samo disk, već i CPU:

Napomenut ću da smo također postali manji u “čitanju” diska, ne samo u “pisanju” - budući da prilikom umetanja zapisa u tablicu moramo “pročitati” i dio stabla svakog indeksa kako bismo odredili njegov budući položaj u njima.

Tko može dobro živjeti na PostgreSQL 11

Nakon ažuriranja na PG11, odlučili smo nastaviti "podešavati" TOAST i primijetili smo da je počevši od ove verzije parametar postao dostupan za ugađanje toast_tuple_target:

Kod za obradu TOAST aktivira se samo kada je vrijednost retka koja se sprema u tablicu veća od TOAST_TUPLE_THRESHOLD bajtova (obično 2 KB). TOAST kod će komprimirati i/ili premjestiti vrijednosti polja iz tablice sve dok vrijednost retka ne postane manja od TOAST_TUPLE_TARGET bajtova (vrijednost varijable, također obično 2 KB) ili dok se veličina ne može smanjiti.

Odlučili smo da su podaci koje obično imamo ili "vrlo kratki" ili "vrlo dugi", pa smo se odlučili ograničiti na najmanju moguću vrijednost:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Pogledajmo kako su nove postavke utjecale na učitavanje diska nakon rekonfiguracije:

Nije loše! Prosjek red čekanja na disk se smanjio otprilike 1.5 puta, a disk je “zauzet” 20 posto! Ali možda je to nekako utjecalo na CPU?

Barem nije bilo gore. Iako je teško procijeniti ne mogu li čak ni takve količine povećati prosječno opterećenje CPU-a 5%.

Promjenom mjesta pojmova mijenja se zbroj...!

Kao što znate, novčić štedi rublju, a s našim volumenom pohrane to je otprilike 10TB/mjesečno čak i mala optimizacija može dati dobar profit. Stoga smo obratili pozornost na fizičku strukturu naših podataka - kako točno “naslagana” polja unutar zapisa svaki od stolova.

Jer zbog usklađivanje podataka ovo je ravno naprijed utječe na rezultirajući volumen:

Mnoge arhitekture omogućuju usklađivanje podataka na granicama riječi stroja. Na primjer, na 32-bitnom x86 sustavu, cijeli brojevi (tip cijelog broja, 4 bajta) bit će poravnati na granici riječi od 4 bajta, kao i brojevi s pomičnim zarezom dvostruke preciznosti (pomični zarez dvostruke preciznosti, 8 bajtova). A na 64-bitnom sustavu dvostruke vrijednosti bit će usklađene s granicama riječi od 8 bajtova. Ovo je još jedan razlog nekompatibilnosti.

Zbog poravnanja, veličina retka tablice ovisi o redoslijedu polja. Obično ovaj učinak nije jako primjetan, ali u nekim slučajevima može dovesti do značajnog povećanja veličine. Na primjer, ako pomiješate polja char(1) i integer, između njih će obično biti izgubljena 3 bajta.

Počnimo sa sintetičkim modelima:

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 байт

Odakle par dodatnih bajtova u prvom slučaju? Jednostavno je - 2-bajtna mala inta poravnata na granici od 4 bajta prije sljedećeg polja, a kada je zadnje, nema ništa i nema potrebe za poravnavanjem.

U teoriji je sve u redu i možete preuređivati ​​polja kako želite. Provjerimo to na stvarnim podacima na primjeru jedne od tablica, čiji dnevni odjeljak zauzima 10-15 GB.

Početna struktura:

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)

Odjeljak nakon promjene reda stupaca - točno ista polja, samo drugačiji redoslijed:

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)

Ukupni volumen odjeljka određen je brojem "činjenica" i ovisi samo o vanjskim procesima, pa podijelimo veličinu hrpe (pg_relation_size) po broju zapisa u njemu - tj. dobivamo prosječna veličina stvarno pohranjenog zapisa:

Minus 6% volumena, Sjajno!

Ali sve, naravno, nije tako ružičasto - uostalom, u indeksima ne možemo promijeniti redoslijed polja, a time i "općenito" (pg_total_relation_size) ...

...još uvijek ovdje ušteđeno 1.5%bez promjene ijedne linije koda. Da da!

Napominjem da gornja opcija za uređenje polja nije činjenica da je najoptimalnija. Zato što ne želite "kidati" neke blokove polja iz estetskih razloga - na primjer, par (pack, recno), što je PK za ovu tablicu.

Općenito, određivanje "minimalnog" rasporeda polja je prilično jednostavan zadatak "grube sile". Stoga iz svojih podataka možete dobiti još bolje rezultate od naših – pokušajte!

Izvor: www.habr.com