Sutaupykite centą pirkdami didelius „PostgreSQL“ kiekius

Tęsiant iškeltą didelių duomenų srautų įrašymo temą ankstesnis straipsnis apie skaidymą, čia apžvelgsime būdus, kuriais galite sumažinti „fizinį“ saugomų objektų dydį PostgreSQL ir jų poveikis serverio veikimui.

Pakalbėsime apie TOAST nustatymai ir duomenų derinimas. „Vidutiniškai“ šie metodai nesutaupys per daug išteklių, tačiau visai nekeičiant programos kodo.

Tačiau mūsų patirtis šiuo atžvilgiu buvo labai produktyvi, nes beveik bet koks stebėjimas yra saugomas pagal savo pobūdį dažniausiai tik prideda įrašytų duomenų atžvilgiu. Ir jei jums įdomu, kaip galite išmokyti duomenų bazę rašyti į diską 200MB / s perpus mažiau – prašau po kat.

Mažos didelių duomenų paslaptys

Pagal darbo profilį mūsų paslauga, jie reguliariai skrenda pas jį iš guolio teksto paketai.

Ir nuo tada VLSI kompleksaskurio duomenų bazė stebime, yra kelių komponentų produktas su sudėtingomis duomenų struktūromis, tada užklausomis maksimaliam našumui pasirodo visai taip „Kelių tomų“ su sudėtinga algoritmine logika. Taigi kiekvieno atskiro užklausos egzemplioriaus arba gauto vykdymo plano apimtis mūsų gaunamame žurnale pasirodo „vidutiniškai“ gana didelė.

Pažvelkime į vienos iš lentelių, į kurias rašome „neapdorotus“ duomenis, struktūrą - tai yra, čia yra originalus žurnalo įrašo tekstas:

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

Tipiškas ženklas (žinoma, jau suskirstytas į skyrius, todėl tai yra skyriaus šablonas), kur svarbiausia yra tekstas. Kartais gana didelės apimties.

Prisiminkite, kad „fizinis“ vieno PG įrašo dydis negali užimti daugiau nei vieno duomenų puslapio, tačiau „loginis“ dydis yra visiškai kitas dalykas. Norėdami į lauką įrašyti tūrinę reikšmę (varchar/text/bytea), naudokite TOAST technologija:

PostgreSQL naudoja fiksuotą puslapio dydį (paprastai 8 KB) ir neleidžia eilėms apimti kelių puslapių. Todėl neįmanoma tiesiogiai saugoti labai didelių lauko reikšmių. Norint įveikti šį apribojimą, didelės lauko reikšmės suglaudinamos ir (arba) padalijamos į kelias fizines eilutes. Tai atsitinka nepastebimai vartotojui ir turi mažai įtakos daugumai serverio kodų. Šis metodas žinomas kaip TOAST...

Tiesą sakant, kiekvienai lentelei su „potencialiai dideliais“ laukais, automatiškai sukuriama suporuota lentelė su „pjaustymu“. kiekvienas „didelis“ įrašas 2 KB segmentais:

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

Tai yra, jei turime parašyti eilutę su „didele“ reikšme data, tada įvyks tikrasis įrašas ne tik į pagrindinę lentelę ir jos PK, bet ir į TOAST bei jos PK.

Sumažina TOAST poveikį

Tačiau dauguma mūsų įrašų vis dar nėra tokie dideli, turėtų tilpti į 8KB - Kaip aš galiu sutaupyti pinigų?

Čia mums į pagalbą ateina atributas STORAGE lentelės stulpelyje:

• EXTENDED leidžia suspausti ir atskirai saugoti. Tai standartinis variantas daugumai TOAST suderinamų duomenų tipų. Pirmiausia ji bando suspausti, tada išsaugo ją už lentelės ribų, jei eilutė vis dar per didelė.
• PAGRINDINIS leidžia suspausti, bet ne atskirai saugoti. (Tiesą sakant, tokiems stulpeliams vis tiek bus saugoma atskira saugykla, bet tik kaip paskutinė priemonė, kai nėra kito būdo sutraukti eilutę, kad ji tilptų puslapyje.)

Tiesą sakant, tai yra būtent tai, ko mums reikia tekstui - kiek įmanoma suspausti, o jei visai netelpa, dėti į TOAST. Tai galima padaryti tiesiogiai, naudojant vieną komandą:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Kaip įvertinti poveikį

Kadangi duomenų srautas keičiasi kiekvieną dieną, galime palyginti ne absoliučius skaičius, o santykiniais dydžiais mažesnė dalis Užrašėme tai TOAST – tuo geriau. Tačiau čia yra pavojus - kuo didesnė kiekvieno atskiro įrašo „fizinė“ apimtis, tuo indeksas tampa „platesnis“, nes turime apimti daugiau duomenų puslapių.

skyrius prieš pakeitimus:

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

skyrius po pakeitimų:

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

Tiesą sakant, mes pradėjo rašyti į TOAST 2 kartus rečiau, kuris iškrovė ne tik diską, bet ir procesorių:

Pastebėsiu, kad mes taip pat sumažėjome „skaitydami“ diską, o ne tik „rašydami“ - kadangi įterpdami įrašą į lentelę, turime „perskaityti“ ir kiekvienos rodyklės medžio dalį, kad nustatytume jį. būsimą poziciją jose.

Kas gali gerai gyventi su PostgreSQL 11

Atnaujinę į PG11 nusprendėme tęsti TOAST „tiuningumą“ ir pastebėjome, kad nuo šios versijos parametras tapo prieinamas derinimui toast_tuple_target:

TOAST apdorojimo kodas suaktyvinamas tik tada, kai eilutės reikšmė, kuri turi būti saugoma lentelėje, yra didesnė nei TOAST_TUPLE_THRESHOLD baitų (paprastai 2 KB). TOAST kodas suglaudins ir (arba) perkels laukų reikšmes iš lentelės tol, kol eilutės reikšmė taps mažesnė nei TOAST_TUPLE_TARGET baitai (kintama reikšmė, taip pat paprastai 2 KB) arba dydis nebus sumažintas.

Nusprendėme, kad dažniausiai mūsų turimi duomenys yra „labai trumpi“ arba „labai ilgi“, todėl nusprendėme apsiriboti iki minimalios galimos vertės:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Pažiūrėkime, kaip nauji nustatymai paveikė disko įkėlimą po konfigūravimo:

Neblogai! Vidutinis eilė prie disko sumažėjo maždaug 1.5 karto, o diskas „užimtas“ yra 20 procentų! Bet gal tai kažkaip paveikė CPU?

Bent jau nebuvo blogiau. Nors sunku spręsti, ar net tokie apimtys vis tiek negali padidinti vidutinės procesoriaus apkrovos 5%.

Keičiant terminų vietas, suma... keičiasi!

Kaip žinote, centas sutaupo rublį, o su mūsų saugojimo apimtimis tai yra apie 10TB/mėn net nedidelis optimizavimas gali duoti gerą pelną. Todėl atkreipėme dėmesį į fizinę mūsų duomenų struktūrą – kaip tiksliai „sukrauti“ laukai įrašo viduje kiekviena iš lentelių.

Nes dėl duomenų derinimas tai tiesiai į priekį turi įtakos gaunamam tūriui:

Daugelis architektūrų pateikia duomenų išlygiavimą ant mašininių žodžių ribų. Pavyzdžiui, 32 bitų x86 sistemoje sveikieji skaičiai (sveiko skaičiaus tipas, 4 baitai) bus lygiuojami ant 4 baitų žodžių ribos, kaip ir dvigubo tikslumo slankiojo kablelio skaičiai (dvigubo tikslumo slankusis kablelis, 8 baitai). O 64 bitų sistemoje dvigubos reikšmės bus suderintos su 8 baitų žodžių ribomis. Tai dar viena nesuderinamumo priežastis.

Dėl lygiavimo lentelės eilutės dydis priklauso nuo laukų eilės. Paprastai šis poveikis nėra labai pastebimas, tačiau kai kuriais atvejais jis gali žymiai padidinti dydį. Pavyzdžiui, jei sumaišysite char(1) ir sveikųjų skaičių laukus, tarp jų paprastai bus iššvaistomi 3 baitai.

Pradėkime nuo sintetinių modelių:

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

Iš kur atsirado pora papildomų baitų pirmuoju atveju? Tai paprasta - 2 baitų mažoji dalis sulygiuota ant 4 baitų ribos prieš kitą lauką, o kai jis paskutinis, tai nėra nieko ir nereikia lygiuoti.

Teoriškai viskas gerai ir laukus galima pertvarkyti kaip nori. Patikrinkime tai realiais duomenimis, naudodamiesi vienos iš lentelių pavyzdžiu, kurios kasdienė dalis užima 10–15 GB.

Pradinė struktūra:

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)

Skyrius pakeitus stulpelių tvarką – tiksliai tie patys laukai, tik kita tvarka:

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)

Bendras sekcijos tūris nustatomas pagal „faktų“ skaičių ir priklauso tik nuo išorinių procesų, todėl padalinkime krūvos dydį (pg_relation_size) pagal jame esančių įrašų skaičių – tai yra, gauname vidutinis faktiškai saugomo įrašo dydis:

Minus 6% tūris, Puiku!

Bet viskas, žinoma, nėra taip rožinė - juk indeksuose negalime keisti laukų tvarkos, todėl „apskritai“ (pg_total_relation_size) ...

...vis dar čia sutaupė 1.5 proc.nepakeitus nė vienos kodo eilutės. Taip taip!

Atkreipiu dėmesį, kad aukščiau pateiktas laukų išdėstymo variantas nėra pats optimaliausias. Nes dėl estetinių priežasčių nenorite „plėšyti“ kai kurių laukų blokų - pavyzdžiui, poros (pack, recno), kuris yra šios lentelės PK.

Apskritai „minimalaus“ laukų išdėstymo nustatymas yra gana paprasta „žiaurios jėgos“ užduotis. Todėl iš savo duomenų galite gauti dar geresnių rezultatų nei mūsų – išbandykite!

Šaltinis: www.habr.com