Nastavljajući temu snimanja velikih tokova podataka pokrenutu od , u ovom ćemo se osvrnuti na načine na koje možete smanjiti “fizičku” veličinu pohranjenog u PostgreSQL, i njihov uticaj na performanse servera.
Pričaćemo o tome TOAST postavke i poravnanje podataka. “U prosjeku”, ove metode neće uštedjeti previše resursa, ali bez modifikacije koda aplikacije.
Međutim, naše iskustvo se pokazalo vrlo produktivnim u tom pogledu, budući da je skladištenje gotovo svakog praćenja po svojoj prirodi 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 , redovno mu lete iz jazbine tekstualni paketi.
I od tada čija baza podataka pratimo je višekomponentni proizvod sa složenim strukturama podataka, zatim upitima za maksimalne performanse ispadne sasvim ovako . Dakle, obim svake pojedinačne instance zahtjeva ili rezultirajućeg plana izvršenja u dnevniku koji nam dolazi ispada “u prosjeku” prilično velik.
Pogledajmo strukturu jedne od tabela u koju upisujemo "sirove" podatke - to jest, evo originalnog teksta 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ć podijeljen, tako da je ovo šablon odjeljka), gdje je najvažniji tekst. Ponekad prilično obiman.
Podsjetimo 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 :
PostgreSQL koristi fiksnu veličinu stranice (obično 8 KB) i ne dozvoljava da se torke prostiru na više stranica. Stoga je nemoguće direktno pohraniti vrlo velike vrijednosti polja. Da bi se prevladalo ovo ograničenje, velike vrijednosti polja se komprimiraju i/ili dijele na više fizičkih linija. Ovo se dešava neprimećeno od strane korisnika i ima mali uticaj na većinu serverskog koda. Ova metoda je poznata kao TOAST...
Zapravo, za svaku tabelu sa "potencijalno velikim" poljima, automatski svaki "veliki" zapis u segmentima od 2KB:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Odnosno, ako moramo napisati string sa “velikom” vrijednošću data, tada će doći do pravog snimanja ne samo na glavni sto i njegov PK, već i na TOAST i njegov PK.
Smanjenje uticaja TOAST-a
Ali većina naših rekorda još uvijek nije tako velika, treba da stane u 8KB - Kako da uštedim na ovome?..
Tu nam atribut priskače u pomoć u koloni tabele:
- PROŠIRENO omogućava i kompresiju i odvojeno skladištenje. Ovo standardna opcija za većinu tipova podataka usklađenih s TOAST-om. Prvo pokušava izvršiti kompresiju, a zatim ga pohranjuje izvan tablice ako je red i dalje prevelik.
- HAND dozvoljava kompresiju, ali ne i zasebno skladištenje. (Zapravo, odvojeno skladištenje će se i dalje obavljati za takve stupce, ali samo kao poslednje sredstvo, 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 što je više moguće, a ako vam nikako ne odgovara, stavite ga u TOAST. Ovo se može uraditi direktno u hodu, jednom komandom:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Kako proceniti efekat
Budući da se tok podataka mijenja svaki dan, ne možemo upoređivati apsolutne brojeve, već relativne manji udio Zapisali smo to u TOAST - tim bolje. Ali tu postoji opasnost - što je veći “fizički” obim svakog pojedinačnog 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%)U stvari, mi počeo pisati u TOAST 2 puta rjeđe, koji je istovario ne samo disk, već i CPU:
Napomenuću da smo postali manji i u „čitanju“ diska, a ne samo u „pisanju“ – budući da prilikom umetanja zapisa u tabelu moramo „čitati“ i deo stabla svakog indeksa da bismo odredili njegov budući položaj u njima.
Ko može dobro da živi na PostgreSQL 11
Nakon ažuriranja na PG11, odlučili smo nastaviti "podešavanje" TOAST-a i primijetili smo da počevši od ove verzije parametar :
Kod za obradu TOAST se pokreće samo kada je vrijednost reda koja se pohranjuje u tablicu veća od TOAST_TUPLE_THRESHOLD bajtova (obično 2 KB). TOAST kod će komprimirati i/ili premjestiti vrijednosti polja iz tabele sve dok vrijednost reda ne postane manja od TOAST_TUPLE_TARGET bajtova (vrijednost promjenljive, također obično 2 KB) ili 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 minimalnu 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 na disku 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 da li čak i takve količine još uvijek ne mogu povećati prosječno opterećenje CPU-a 5%.
Promjenom mjesta pojmova, zbir... se mijenja!
Kao što znate, jedan peni štedi rublju, a s našim količinama skladištenja to je otprilike 10TB/mjesečno čak i mala optimizacija može dati dobar profit. Stoga smo obratili pažnju na fizičku strukturu naših podataka – kako točno „naslagana“ polja unutar zapisa svaka od tabela.
Jer zbog ovo je pravo naprijed :
Mnoge arhitekture obezbeđuju poravnanje podataka na granicama mašinske reči. Na primjer, na 32-bitnom x86 sistemu, cijeli brojevi (tip cjelobrojnog, 4 bajta) će biti poravnati na granici riječi od 4 bajta, kao i brojevi s pomičnim zarezom dvostruke preciznosti (pokretni zarez dvostruke preciznosti, 8 bajtova). A na 64-bitnom sistemu, dvostruke vrijednosti će biti usklađene sa granicama riječi od 8 bajta. Ovo je još jedan razlog za nekompatibilnost.
Zbog poravnanja, veličina reda tabele zavisi od redosleda 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 char(1) i integer polja, 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 je došlo nekoliko dodatnih bajtova u prvom slučaju? jednostavno je - 2-bajtni smallint poravnat na 4-bajtnoj granici prije sljedećeg polja, a kada je zadnje, nema ničega i nema potrebe za poravnavanjem.
U teoriji, sve je u redu i možete preurediti polja kako želite. Provjerimo to na stvarnim podacima na primjeru jedne od tabela, čiji dnevni dio 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 redoslijeda kolona - tačno ista polja, samo drugačiji redosled:
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 sekcije određen je brojem "činjenica" i ovisi samo o vanjskim procesima, pa podijelimo veličinu hrpe (pg_relation_size) po broju zapisa u njemu - odnosno dobijamo prosječna veličina stvarno pohranjenog zapisa:
Minus 6% zapremine, Odlično!
Ali sve, naravno, nije tako ružičasto - uostalom, u indeksima ne možemo promijeniti redoslijed polja, a samim tim i "općenito" (pg_total_relation_size) ...
...još uvijek ovdje ušteđeno 1.5%bez promjene nijednog reda koda. Da da!
Napominjem da gornja opcija za uređenje polja nije činjenica da je najoptimalnija. Zato što ne želite da "pocepate" neke blokove polja iz estetskih razloga - na primjer, par (pack, recno), što je PK za ovu tabelu.
Općenito, određivanje "minimalnog" rasporeda polja je prilično jednostavan zadatak "grube sile". Stoga možete dobiti još bolje rezultate od vaših podataka od naših - isprobajte!
izvor: www.habr.com