ProHoster >> Blog >> Administrimi >> Kursim të parave për sasi të mëdha në PostgreSQL

Kursim të parave për sasi të mëdha në PostgreSQL

Duke vazhdimin e temës së regjistrimit të flukseve të mëdha të të dhënave artikulli i mëparshëm mbi ndarjen, në këtë do të shqyrtojmë mënyrat me të cilat mund të reduktosh "përmasën fizike" të të dhënave të ruajtura në PostgreSQL, dhe ndikimin e saj në performancën e serverit.

Do të flasim për caktimet TOAST dhe rregullimin e të dhënave. "Në mesatare", këto metoda do të lejojnë kursimin e jo shumë burimeve, por pa modifikime në kodin e aplikacionit.

Kursim të parave për sasi të mëdha në PostgreSQL
Megjithatë, përvoja jonë ka rezultuar shumë produktive në këtë fushë, pasi ruajtja e pothuajse çdo monitorimi për natyrën e saj është në shumicë të rasteve append-only për sa i përket të dhënave të shkruara. Dhe nëse je kurioz, si mund ta mësosh bazën të shkruajë në disk në vend të 200MB/s gjysmë më pak - të lutem, vazhdo më tej.

Sekretet e vogla të të dhënave të mëdha

Sipas profilit të punës të shërbimit tonë, ai përballonte rregullisht nga logët paqet tekstuale.

Dhe përsa i përket kompleksit SBIS, të cilat DB-të tona monitorojnë, - është një produkt me shumë komponentë me struktura të ndërlikuara të të dhënave, kështu që për të arritur performancën maksimale kanë rezultuar të jenë gjithashtu "multi-volume" me logjikë algjebrike të ndërlikuar. Pra, volumi i çdo ekzemplar të veçantë të kërkesës ose përfundimit të planit të ekzekutimit në logun që na vjen është "në mesatare" mjaft i madh.

Le të shohim strukturën e një nga tabelat, në të cilën ne shkruajmë "të dhëna të papërpunuara" - do të thotë pikërisht tekstin origjinal nga regjistrimi i logut:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- çelësi i seksionit
    date
, data -- më e rëndësishmja
    text
, PRIMARY KEY(pack, recno)
);

Një tabelë tipike e tillë (sigurisht e ndarë, prandaj kjo është - një shabllon seksioni), ku më e rëndësishmja është - teksti. Ndonjëherë mjafton shumë voluminoz.

Le të kujtojmë se "përmasat fizike" e një regjistrimi në PG nuk mund të zë më shumë se një faqe të dhënash, por "përmasat logjike" janë një çështje tjetër. Për të regjistruar një vlerë të madhe në fushë (varchar/text/bytea) përdoret teknologjia TOAST:

PostgreSQL përdor një madhësi fikse faqe (zakonisht 8 KB), dhe nuk lejon që të dhënat të zënë më shumë se një faqe. Prandaj, të ruash vlera shumë të mëdha të fushave nuk është e mundur. Për të kapërcyer këtë kufizim, vlerat e mëdha të fushave kompresohen dhe/ose ndahen në disa rreshta fizikë. Kjo ndodh pa e vënë re për përdoruesin dhe ndikon pak në shumicën e kodit të serverit. Ky metod quhet TOAST 


Realistikisht, për çdo tabelë me "fusha potencialisht të mëdha" automatikisht krijohet një tabelë marrëse me "copëzimin" e çdo regjistrimi "të madh" në seksione prej 2KB:

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

Pra, nëse na duhen të regjistrojmë një rresht me një "vlerë të madhe" data, regjistrimi real do të ndodhi jo vetëm në tabelën kryesore dhe PK e saj, por edhe në TOAST dhe PK e tij.

Ulimi i ndikimit të TOAST

Por shumica e regjistrimeve tona megjithatë nuk janë kaq të mëdha, në 8KB duhet të ndihmohen - si të kursejmë për këtë?..

Këtu na vjen në ndihmë atribute STORAGE në kolonën e tabelës:

  • EXTENDED lejon si kompresimin, ashtu dhe ruajtjen e veçantĂ«. Kjo Ă«shtĂ« opsioni standart pĂ«r shumicĂ«n e llojeve tĂ« tĂ« dhĂ«nave, tĂ« pĂ«rshtatshme me TOAST. Fillimisht ndodh njĂ« pĂ«rpjekje pĂ«r tĂ« bĂ«rĂ« kompresimin, pastaj - ruajtja jashtĂ« tabelĂ«s, nĂ«se rreshti Ă«shtĂ« ende tepĂ«r i madh.
  • MAIN lejon kompresimin, por jo ruajtjen e veçantĂ«. (NĂ« fakt, ruajtja e veçantĂ«, megjithatĂ«, do tĂ« realizohet pĂ«r kĂ«to kolona, por vetĂ«m si njĂ« masĂ« ekstreme, kur nuk ka mĂ«nyrĂ« tjetĂ«r pĂ«r tĂ« reduktuar rreshtin sa pĂ«r tĂ« pĂ«rfshirĂ« nĂ« faqe.)

Në fakt, kjo është pikërisht ajo që na nevojitet për tekstin - maksimalisht të kompresojmë, dhe nëse ashtu zgjidhim të shkojmë në TOAST.Kjo mund të bëhet menjëherë "në flakë", me një komandë:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Si ta vlerësojmë efektin

Duke qenë se çdo ditë fluksi i të dhënave ndryshon, ne nuk mund të krahasojmë numrat absolut, por në të relativët, sa më të vogla pjesë ne regjistruam në TOAST - aq më mirë. Por këtu ka rrezikun - sa më i madh të jetë "vëllimi fizik" i çdo regjistrimi të veçantë, aq "më i gjerë" bëhet indeksi, pasi duhet të mbulojë më shumë faqe të dhënash.

Sekcioni para ndryshimeve:

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

Sekcioni pas ndryshimeve:

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

Në fakt, ne filluam të shkruajmë në TOAST dy herë më rrallë, që lehtësuar jo vetëm diskun, por edhe CPU-në:

Kursim të parave për sasi të mëdha në PostgreSQL
Kursim të parave për sasi të mëdha në PostgreSQL
Dua të theksoj se kemi filluar të "lexojmë" disku më pak, jo vetëm "shkruajmë" - pasi gjatë futur të një regjistrimi në një tabelë, na duhet të "lexojmë" gjithashtu pjesë të pemës së çdo indeksi për të përcaktuar pozitat e së ardhmes në to.

Kush e ka mirë të jetojë në PostgreSQL 11

Pas update në PG11, ne vendosëm të vazhdojmë me "tunimin" TOAST dhe vëmë re se nga këto versione është bërë e mundur të përshtaten parametrat. toast_tuple_target:

Kodi i përpunimit TOAST aktivizohet vetëm kur vlera e rreshtit, e cila duhet të ruhet në tabelë, është më e madhe se TOAST_TUPLE_THRESHOLD byte (zakonisht është 2 KB). Kodi TOAST do të kompresojë dhe/ose do të nxjerrë vlerat e fushave jashtë tabelës deri sa vlera e rreshtit të bëhet më e vogël se TOAST_TUPLE_TARGET byte (një vlerë variable, gjithashtu zakonisht 2 KB) apo deri sa të jetë e pamundur të reduktohet volumi.

Vendosëm se të dhënat tona zakonisht janë ose "shumë të shkurtra" ose "shumë të gjata", kështu që vendosëm të kufizojmë vlerën minimale të mundshme:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Le të shohim se si ndryshimet e reja ndikuar në ngarkimin e diskut pas rimodelimit:

Kursim të parave për sasi të mëdha në PostgreSQL
Jo keq! Mesatarja e radhĂ«s nĂ« disk u reduktua pothuajse 1.5 herĂ«, dhe "shfrytĂ«zimi" i diskut — 20%! Por ndoshta ka ndikuar ndonjĂ« gjĂ« nĂ« CPU?

Kursim të parave për sasi të mëdha në PostgreSQL
Të paktën, ndonjëherë nuk u përkeqësua. Megjithatë, është e vështirë të gjykohet, ndoshta sepse e tilla sasi gjithashtu nuk mund të ngrejë mesataren e ngarkesës së CPU-së lart. 5%.

Ndryshimi i vendit të termave
 e ndryshon rezultatin!

Siç dihet, njĂ« qindarkĂ« ruan njĂ« lek, dhe me volumet tona tĂ« ruajtjes rreth 10TB/muaj edhe njĂ« optimizim i vogĂ«l mund tĂ« sjellĂ« njĂ« fitim tĂ« mirĂ«. Prandaj, ne e drejtuam vĂ«mendjen tonĂ« drejt strukturĂ«s fizike tĂ« tĂ« dhĂ«nave tona — si konkretisht "janĂ« vendosur" fushat brenda regjistrimeve e çdo tabele.

Për shkak se për përputhjen e të dhënave kjo ndikon drejtpërdrejt në volumin rezultant.:

Shumë arkitektura parashikojnë përputhjen e të dhënave sipas kufijve të fjalëve makinerike. Për shembull, në një sistem 32-bit x86, numrat e tërë (tipi integer, zë 4 byte) do të përputhen sipas kufirit të fjalëve 4-byte, ashtu si numrat me pikë të lëvizshme të dyfishtë (tipi double precision, 8 byte). Ndërsa në një sistem 64-bit, vlerat e double do të përputhen sipas kufirit të fjalëve 8-byte. Kjo është një arsye tjetër për papajtueshmëri.

Për shkak të përputhjes, madhësia e rreshtit të tabelës varet nga rendi i vendosjes së fushave. Zakonisht, ky efekt nuk vërehet shumë, por në disa raste mund të çojë në rritje të rëndësishme të madhësisë. Për shembull, nëse vendosen fushat e tipave char(1) dhe integer përzier, zakonisht do të humbet 3 byte të panevojshme ndërmjet tyre.

Le të fillojmë me modele sintetike:

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 byte

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 byte

Nga ka dy byte tĂ« tepĂ«rta nĂ« rastin e parĂ«? E gjithĂ« Ă«shtĂ« e thjeshtĂ« — smallint 2-byte pĂ«rshtatet sipas kufirit 4-byte para fushĂ«s sĂ« ardhshme, kurse kur Ă«shtĂ« fushĂ« e fundit — nuk ka asgjĂ« pĂ«r t'u pĂ«rshtatur dhe as nuk ka nevojĂ«.

NĂ« teori — gjithçka Ă«shtĂ« mirĂ« dhe fushat mund tĂ« rregullohen si tĂ« duash. Le tĂ« kontrollojmĂ« mbi tĂ« dhĂ«na reale duke marrĂ« si shembull njĂ« nga tabelat, ku seksioni ditor zĂ« 10-15GB.

Struktura origjinale:

CREATE TABLE public.plan_20190220
(
-- Trashëguar nga tabela plan:  pack uuid NOT NULL,
-- Trashëguar nga tabela plan:  recno smallint NOT NULL,
-- Trashëguar nga tabela plan:  host uuid,
-- Trashëguar nga tabela plan:  ts timestamp with time zone,
-- Trashëguar nga tabela plan:  exectime numeric(32,3),
-- Trashëguar nga tabela plan:  duration numeric(32,3),
-- Trashëguar nga tabela plan:  bufint bigint,
-- Trashëguar nga tabela plan:  bufmem bigint,
-- Trashëguar nga tabela plan:  bufdsk bigint,
-- Trashëguar nga tabela plan:  apn uuid,
-- Trashëguar nga tabela plan:  ptr uuid,
-- Trashëguar nga tabela 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)

Seksioni pas ndryshimit tĂ« rendit tĂ« kolonave — janĂ« tĂ« njĂ«jtat fushat, vetĂ«m se rendi Ă«shtĂ« ndryshe. CREATE TABLE public.plan_20190221 ( -- TrashĂ«guar nga tabela plan: dt date NOT NULL, -- TrashĂ«guar nga tabela plan: ts timestamp with time zone, -- TrashĂ«guar nga tabela plan: pack uuid NOT NULL, -- TrashĂ«guar nga tabela plan: recno smallint NOT NULL, -- TrashĂ«guar nga tabela plan: host uuid, -- TrashĂ«guar nga tabela plan: apn uuid, -- TrashĂ«guar nga tabela plan: ptr uuid, -- TrashĂ«guar nga tabela plan: bufint bigint, -- TrashĂ«guar nga tabela plan: bufmem bigint, -- TrashĂ«guar nga tabela plan: bufdsk bigint, -- TrashĂ«guar nga tabela plan: exectime numeric(32,3), -- TrashĂ«guar nga tabela 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):

CREATE TABLE public.plan_20190221
(
-- Trashëguar nga tabela plan:  dt date NOT NULL,
-- Trashëguar nga tabela plan:  ts timestamp me zonën e kohës,
-- Trashëguar nga tabela plan:  pack uuid NOT NULL,
-- Trashëguar nga tabela plan:  recno smallint NOT NULL,
-- Trashëguar nga tabela plan:  host uuid,
-- Trashëguar nga tabela plan:  apn uuid,
-- Trashëguar nga tabela plan:  ptr uuid,
-- Trashëguar nga tabela plan:  bufint bigint,
-- Trashëguar nga tabela plan:  bufmem bigint,
-- Trashëguar nga tabela plan:  bufdsk bigint,
-- Trashëguar nga tabela plan:  exectime numeric(32,3),
-- Trashëguar nga tabela 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)

VĂ«llimi total i seksionit pĂ«rcaktohet nga numri i "fakteve" dhe varet vetĂ«m nga proceset e jashtme, kĂ«shtu qĂ« le tĂ« ndajmĂ« madhĂ«sinĂ« heap (pg_relation_size) me numrin e regjistrave nĂ« tĂ« — pra, tĂ« marrim madhĂ«sinĂ« mesatare tĂ« regjistrit tĂ« ruajtur realisht.:

Kursim të parave për sasi të mëdha në PostgreSQL
Minus 6% e volumit, shkëlqyeshëm!

Por, natyrisht, asgjĂ« nuk Ă«shtĂ« aq rozĂ« — sepse nĂ« indekset rregulli i fushave nuk mund ta ndryshojmĂ«, dhe kĂ«shtu "nĂ« pĂ«rgjithĂ«si" (pg_total_relation_size)


Kursim të parave për sasi të mëdha në PostgreSQL

 megjithatë këtu kurse kemi arritur të kursen 1.5%, pa ndryshuar asnjë rresht kodi. Po, kështu është!

Kursim të parave për sasi të mëdha në PostgreSQL

Dua tĂ« theksoj se varianti i mĂ«sipĂ«rm i pozicionit tĂ« fushave — nuk Ă«shtĂ« domosdoshmĂ«risht optimal. Sepse disa bllokĂ« fushash nuk dua t'i 'ndaj' pĂ«r arsyet estetike — pĂ«r shembull, çiftin (pack, recno), i cili Ă«shtĂ« PK pĂ«r kĂ«tĂ« tabelĂ«.

NĂ« pĂ«rgjithĂ«si, pĂ«rcaktimi i 'pozitivit' minimal tĂ« fushave Ă«shtĂ« njĂ« detyrĂ« mjaft e thjeshtĂ« e 'kĂ«rkimit'. Prandaj, ju mund tĂ« merrni rezultate mĂ« tĂ« mira se tonat nĂ« tĂ« dhĂ«nat tuaja — provoni!

Burimi: habr.com

Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« đŸ”„ Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« | ProHoster