Säästke PostgreSQL-is suurte mahtude pealt sentigi

poolt tõstatatud suurte andmevoogude salvestamise teema jätkamine eelmine artikkel partitsioonide kohta, selles vaatleme võimalusi, kuidas saate vähendage salvestatava "füüsilist" suurust PostgreSQL-is ja nende mõju serveri jõudlusele.

Me räägime TOAST seaded ja andmete joondamine. "Keskmiselt" ei säästa need meetodid liiga palju ressursse, kuid ilma rakenduse koodi üldse muutmata.

Meie kogemus osutus aga selles osas väga viljakaks, kuna oma olemuselt on peaaegu iga seire salvestamine enamasti ainult lisatavad salvestatud andmete osas. Ja kui te ei tea, kuidas õpetada andmebaasi selle asemel kettale kirjutama 200MB / s poole vähem - palun all kass.

Suurandmete väikesed saladused

Tööprofiili järgi meie teenus, lendavad nad regulaarselt tema juurde pesadest tekstipaketid.

Ja sellest ajast VLSI kompleksmille andmebaasi me jälgime on mitmekomponendiline toode, millel on keerulised andmestruktuurid, seejärel päringud maksimaalse jõudluse saavutamiseks osutuvad üsna selliseks "mitmeköiteline" keeruka algoritmilise loogikaga. Nii et iga üksiku päringu eksemplari või sellest tuleneva täitmisplaani maht meieni jõudvas logis osutub “keskmiselt” üsna suureks.

Vaatame ühe tabeli struktuuri, kuhu kirjutame “toored” - see tähendab, et siin on logikirje algtekst:

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

Tüüpiline märk (muidugi juba jaotatud, nii et see on jaotise mall), kus kõige olulisem on tekst. Mõnikord üsna mahukas.

Tuletage meelde, et PG ühe kirje "füüsiline" suurus ei saa hõivata rohkem kui ühe lehekülje andmeid, kuid "loogiline" suurus on täiesti erinev küsimus. Mahulise väärtuse (varchar/text/bytea) väljale kirjutamiseks kasutage TOAST tehnoloogia:

PostgreSQL kasutab fikseeritud lehe suurust (tavaliselt 8 KB) ja ei luba kortereid hõlmata mitut lehekülge. Seetõttu on väga suurte väljaväärtuste otsene salvestamine võimatu. Selle piirangu ületamiseks tihendatakse suured välja väärtused ja/või jagatakse mitme füüsilise rea vahel. See juhtub kasutajale märkamatult ja sellel on vähe mõju enamikule serverikoodidele. Seda meetodit tuntakse kui TOAST...

Tegelikult iga "potentsiaalselt suurte" väljadega tabeli puhul automaatselt luuakse paaritatud tabel koos viilutamisega iga "suur" kirje 2KB segmentides:

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

See tähendab, et kui peame kirjutama "suure" väärtusega stringi data, siis toimub tõeline salvestus mitte ainult põhilauale ja selle PK-le, vaid ka TOASTile ja selle PK-le.

TOAST mõju vähendamine

Kuid enamik meie rekordeid pole ikka veel nii suured, peaks mahtuma 8KB - Kuidas ma saan selle pealt raha säästa?

Siin tuleb see atribuut meile appi STORAGE tabeli veerus:

• LAIENDATUD võimaldab nii tihendamist kui ka eraldi salvestamist. See standardne valik enamiku TOASTiga ühilduvate andmetüüpide jaoks. Esmalt proovib see tihendada ja salvestab selle siis tabelist väljapoole, kui rida on ikka liiga suur.
• PEAMINE võimaldab tihendamist, kuid mitte eraldi salvestamist. (Tegelikult tehakse selliste veergude jaoks siiski eraldi salvestus, kuid ainult viimase abinõuna, kui pole muud võimalust stringi kahandamiseks nii, et see lehele mahuks.)

Tegelikult on see just see, mida me teksti jaoks vajame - suruge see nii palju kui võimalik kokku ja kui see üldse ei sobi, pange see TOAST sisse. Seda saab teha otse käigu pealt ühe käsuga:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Kuidas hinnata mõju

Kuna andmevoog muutub iga päev, ei saa me võrrelda absoluutarvusid, vaid suhtelises mõttes väiksem osa Panime selle TOAST-i kirja – seda parem. Kuid siin on oht - mida suurem on iga üksiku kirje "füüsiline" maht, seda "laiemaks" muutub register, sest peame katma rohkem lehekülgi andmeid.

Jagu enne muudatusi:

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

Jagu pärast muudatusi:

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

Tegelikult me hakkas TOASTile kirjutama 2 korda harvemini, mis laadis maha mitte ainult ketta, vaid ka protsessori:

Märgin ära, et oleme muutunud väiksemaks ka ketta “lugemises”, mitte ainult “kirjutamises” – kuna kirjet tabelisse sisestades peame “lugema” ka osa iga indeksi puust, et määrata see. tulevane positsioon neis.

Kes suudab PostgreSQL 11-ga hästi elada

Pärast PG11-le värskendamist otsustasime jätkata TOASTi "häälestamist" ja märkasime, et alates sellest versioonist sai parameeter häälestamiseks kättesaadavaks toast_tuple_target:

TOAST-i töötlemiskood käivitub ainult siis, kui tabelisse salvestatav rea väärtus on suurem kui TOAST_TUPLE_THRESHOLD baiti (tavaliselt 2 KB). TOAST-kood tihendab ja/või teisaldab välja väärtusi tabelist seni, kuni rea väärtus on väiksem kui TOAST_TUPLE_TARGET bait (muutuv väärtus, tavaliselt ka 2 KB) või suurust ei saa vähendada.

Otsustasime, et andmed, mis meil tavaliselt on, on kas "väga lühikesed" või "väga pikad", seega otsustasime piirduda minimaalse võimaliku väärtusega:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Vaatame, kuidas uued sätted mõjutasid ketta laadimist pärast ümberseadistamist:

Pole paha! Keskmine järjekord kettale on vähenenud umbes 1.5 korda ja ketas "hõivatud" on 20 protsenti! Aga võib-olla mõjutas see protsessorit kuidagi?

Vähemalt hullemaks ei läinud. Kuigi on raske hinnata, kas isegi sellised mahud ei suuda keskmist protsessori koormust kõrgemale tõsta 5%.

Tingimuste kohti muutes muutub summa...!

Nagu teate, säästab sent rubla ja meie salvestusmahtude juures on see umbes 10TB/kuus isegi väike optimeerimine võib anda head kasumit. Seetõttu pöörasime tähelepanu meie andmete füüsilisele struktuurile – kuidas täpselt "virnastatud" väljad kirje sees iga tabeli.

Sest kuna andmete joondamine see on otse edasi mõjutab saadud helitugevust:

Paljud arhitektuurid pakuvad andmete joondamist masina sõnade piiridel. Näiteks 32-bitises x86 süsteemis joondatakse täisarvud (täisarvu tüüp, 4 baiti) 4-baidise sõnapiiri järgi, nagu ka kahekordse täpsusega ujukomaarvud (topelttäpsusega ujukoma, 8 baiti). Ja 64-bitises süsteemis joondatakse topeltväärtused 8-baidiste sõnapiiridega. See on veel üks kokkusobimatuse põhjus.

Joondamise tõttu sõltub tabelirea suurus väljade järjestusest. Tavaliselt pole see mõju eriti märgatav, kuid mõnel juhul võib see kaasa tuua märkimisväärse suuruse suurenemise. Näiteks kui segate char(1) ja täisarvu välju, kulub nende vahel tavaliselt 3 baiti.

Alustame sünteetiliste mudelitega:

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

Kust tuli esimesel juhul paar lisabaiti? See on lihtne - 2-baidine smallint joondatud 4-baidise piiriga enne järgmist välja ja kui see on viimane, pole midagi ega vaja joondada.

Teoreetiliselt on kõik hästi ja võid väljad ümber paigutada nii, nagu sulle meeldib. Kontrollime seda reaalsetel andmetel ühe tabeli näitel, mille igapäevane osa võtab enda alla 10-15 GB.

Esialgne struktuur:

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)

Jaotis pärast veerujärjekorra muutmist - täpselt samad väljad, lihtsalt erinev järjekord:

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)

Sektsiooni kogumaht määratakse "faktide" arvu järgi ja see sõltub ainult välistest protsessidest, seega jagame kuhja suuruse (pg_relation_size) selles olevate kirjete arvu järgi – see tähendab, et saame tegelikult salvestatud kirje keskmine suurus:

Miinus 6% mahust, Suurepärane!

Kuid kõik pole muidugi nii roosiline - lõppude lõpuks indeksites me väljade järjekorda muuta ei saaja seetõttu "üldiselt" (pg_total_relation_size) ...

...ikka siin ka säästetud 1.5%ühtki koodirida muutmata. Jah, jah!

Märgin, et ülaltoodud väljade paigutuse variant ei ole see, et see oleks kõige optimaalsem. Sest te ei soovi esteetilistel põhjustel mõnda põlluplokki "rebida" - näiteks paari (pack, recno), mis on selle tabeli PK.

Üldiselt on väljade "minimaalse" paigutuse määramine üsna lihtne "toore jõu" ülesanne. Seetõttu saate oma andmetest veelgi paremaid tulemusi kui meie omad – proovige järele!

Allikas: www.habr.com