ProHoster > Blog > Administrim > Kurseni qindarka në vëllime të mëdha në PostgreSQL

Kurseni qindarka në vëllime të mëdha në PostgreSQL

Duke vazhdojmë temën e regjistrimit të flukseve të mëdha të të dhënave, e ngritur në artikullin e mëparshëm mbi seksionimin, në këtë do të shqyrtojmë mënyrat me të cilat mund të ulet "masa fizike" e të dhënave të ruajtura në PostgreSQL, dhe ndikimi i tyre në performancën e serverit.

BĂ«het fjalĂ« pĂ«r konfigurimet TOAST dhe-alignimin e tĂ« dhĂ«nave. "NĂ« mesatarĂ«" kĂ«to metoda do tĂ« kursejnĂ« jo shumĂ« burime, por — krejtĂ«sisht pa modifikimin e Kodit tĂ« aplikacionit.

Kurseni qindarka në vëllime të mëdha në PostgreSQL
MegjithatĂ«, pĂ«rvoja jonĂ« ka rezultuar mjaft produktive nĂ« kĂ«tĂ« drejtim, pasi depozita e pothuajse çdo monitorimi nga natyra e saj Ă«shtĂ« pjesĂ«risht append-only nga pikĂ«pamja e tĂ« dhĂ«nave tĂ« regjistruara. Dhe nĂ«se ju intereson se si mund tĂ« mĂ«soni bazĂ«n pĂ«r tĂ« shkruar nĂ« disk nĂ« vend tĂ« 200MB/s gjysmĂ« mĂ« pak — ju lutemi ndiqni mĂ« poshtĂ«.

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

Në përputhje me profilin e punës të shërbimit tonë, ai rregullisht merr nga log-et paketa teksti.

Dhe pasi kompleksi SBIS, tĂ« cilat Baza tĂ« DhĂ«nash ne monitorojmĂ« — Ă«shtĂ« njĂ« produkt me shumĂ« komponente me struktura tĂ« komplikuara tĂ« tĂ« dhĂ«nave, atĂ«herĂ« edhe kĂ«rkesat pĂ«r tĂ« arritur performancĂ«n maksimale po rezultojnĂ« mjaft "multivolume" me logjikĂ« algoritmike tĂ« komplikuar. Pra, edhe volumi i çdo instancĂ« tĂ« veçantĂ« tĂ« kĂ«rkesĂ«s ose planit rezultues tĂ« ekzekutimit nĂ« log-un qĂ« vjen tek ne Ă«shtĂ« "nĂ« mesatarĂ«" mjaft i madh.

Le tĂ« shohim strukturĂ«n e njĂ«rit nga tabelat, nĂ« tĂ« cilĂ«n ne shkruajmĂ« tĂ« dhĂ«nat "e papĂ«rpunuara" — domethĂ«nĂ« kĂ«tu Ă«shtĂ« teksti 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Ă« (e seksionuar pa dyshim, prandaj kjo Ă«shtĂ« njĂ« model seksioni), ku mĂ« e rĂ«ndĂ«sishmja Ă«shtë—tekstin. NdonjĂ«herĂ« mjaft voluminoze.

Le tĂ« kujtojmĂ« se "masa fizike" e njĂ« regjistrimi nĂ« PG nuk mund tĂ« zĂ«rĂ« mĂ« shumĂ« se njĂ« faqe tĂ« dhĂ«nash, por "masa logjike" — Ă«shtĂ« njĂ« çështje krejt tjetĂ«r. PĂ«r tĂ« shkruar njĂ« vlerĂ« voluminoze nĂ« fushĂ« (varchar/text/bytea) pĂ«rdoret teknologjia TOAST:

PostgreSQL përdor një madhësi page fikse (zakonisht 8 KB) dhe nuk lejon që tuple të zënë më shumë se një faqe. Prandaj, ruajtja e vlerave 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 u vënë re nga përdoruesi dhe ndikon minimalisht në pjesën më të madhe të kodit të serverit. Ky metodë njihet si TOAST 


Në fakt, për çdo tabelë me «fusha potencialisht të mëdha» automatikisht krijohet një tabelë përkatëse me «copëza» të çdo regjistrimi «të madh» me segmente prej 2KB:

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

Domethënë, nëse na duhet të shkruajmë një rresht me një vlerë «të madhe» data, regjistrimi real do të ndodhi jo vetëm në tabelën kryesore dhe PK-në e saj, por edhe në TOAST dhe PK-në e tij.

Të zvogëlojmë ndikimin e TOAST

Por shumica e regjistrimeve tona nuk janĂ« kaq tĂ« mĂ«dha, nĂ« 8KB duhet tĂ« pĂ«rfshihen — si mund ta kursejmĂ« kĂ«tĂ«?..

Këtu vjen në ndihmë atributi STORAGE i kolonës së tabelës:

  • EXTENDED lejon si kompresimin ashtu edhe ruajtjen e veçantĂ«. Ky Ă«shtĂ« opsioni standard pĂ«r shumicĂ«n e llojeve tĂ« tĂ« dhĂ«nave qĂ« janĂ« tĂ« pĂ«rputhshme me TOAST. Fillimisht bĂ«het njĂ« pĂ«rpjekje pĂ«r tĂ« realizuar kompresimin, pastaj — ruajtja jashtĂ« tabelĂ«s, nĂ«se rreshti Ă«shtĂ« ende shumĂ« i madh.
  • MAIN lejon kompresimin, por jo ruajtjen e veçantĂ«. (NĂ« tĂ« vĂ«rtetĂ«, ruajtja e veçantĂ« do tĂ« kryhet pĂ«r kĂ«to kolona, por vetĂ«m si njĂ« masĂ« ekstreme, kur nuk ka mĂ«nyrĂ« tjetĂ«r pĂ«r tĂ« zvogĂ«luar rreshtin nĂ« mĂ«nyrĂ« qĂ« tĂ« pĂ«rfshihej nĂ« faqe.)

NĂ« tĂ« vĂ«rtetĂ«, kjo Ă«shtĂ« pikĂ«risht ajo qĂ« na nevojitet pĂ«r tekstin — tĂ« kompresojmĂ« sa mĂ« shumĂ« tĂ« jetĂ« e mundur dhe nĂ«se nuk Ă«shtĂ« e mundur — ta transferojmĂ« nĂ« TOAST. Kjo mund tĂ« bĂ«het direkt "nĂ« flakĂ«", me njĂ« komandĂ«:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Si të vlerësojmë efektin

Duke qenĂ« se çdo ditĂ« fluksi i tĂ« dhĂ«nave ndryshon, nuk mund tĂ« krahasojmĂ« numrat absolutĂ«, por nĂ« relative, sa mĂ« pak pjesĂ« e kemi shkruar nĂ« TOAST — aq mĂ« mirĂ«. Por kĂ«tu ka njĂ« rrezik — sa mĂ« shumĂ« tĂ« jetĂ« "sasia fizike" e çdo regjistrimi tĂ« veçantĂ«, aq mĂ« "e gjerĂ«" bĂ«het indeksi, sepse duhet tĂ« mbulojĂ« mĂ« shumĂ« faqe tĂ« dhĂ«nash.

Seksioni para ndryshimeve:

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

Seksioni pas ndryshimeve:

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

Në të vërtetë, ne filluam të shkruajmë në TOAST dy herë më rrallë, e cila jo jo diskun, por po CPU:

Kurseni qindarka në vëllime të mëdha në PostgreSQL
Kurseni qindarka në vëllime të mëdha në PostgreSQL
Dua të theksoj se tani ne po "lexojmë" dhe diskun më pak, jo vetëm "shkruajmë" - sepse kur shtojmë një shënim në ndonjë tabelë, na nevojitet "leximi" gjithashtu i një pjese të pemës për çdo indeks për të përcaktuar pozicionin e saj të ardhshëm në to.

Kujt i përshtatet PostgreSQL 11

Pas përditësimit në PG11 vendosëm të vazhdojmë "tuning" TOAST dhe vëmendëm se që nga kjo version u bë i disponueshëm për konfigurimin e parametrave toast_tuple_target:

Kodi i përpunimit TOAST aktiveohet vetëm kur vlera e rreshtit që duhet të ruhet në tabelë është më e madhe se TOAST_TUPLE_THRESHOLD bajt (zakonisht 2 KB). Kodi TOAST do të kompresojë dhe/ose do të transferojë vlerat e fushës jashtë tabelës derisa vlera e rreshtit të bëhet më e vogël se TOAST_TUPLE_TARGET bajt (vlerë që ndryshon, gjithashtu zakonisht 2 KB) ose do të jetë e pamundur të zvogëlohet.

Ne vendosëm që të dhënat tona zakonisht janë ose "në të vërtetë të shkurtra" ose menjëherë "të gjata", prandaj vendosëm të kufizohemi në vlerën më minimale të mundshme:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Le të shohim se si ndikuan konfigurimet e reja në ngarkesën e diskut pas riparimit:

Kurseni qindarka në vëllime të mëdha në PostgreSQL
Mjaft mirë! Mesatarja e radhës ndaj diskut u reduktua përafërsisht 1.5 herë, dhe "zënia" e diskut - rreth 20%! Por ndoshta, ndonjëherë ka pasur ndikim në CPU?

Kurseni qindarka në vëllime të mëdha në PostgreSQL
Të paktën, nuk ka përmirësuar për keq. Megjithatë, është e vështirë të gjykohet, për shkak se kështu volumet nuk mund të ngjallin mesataren e ngarkesës së CPU mbi 5%.

Ndryshimi i rendit të shuma
 ndryshon!

Siç dihet, një qindarkë ruan një rubel, dhe me volumet tona të ruajtjes për rreth 10TB/muaj madje edhe një optimizim i vogël mund të sillte një profit të mirë. Prandaj, ne e kemi përqendruar vëmendjen tonë në strukturën fizike të të dhënave tona - konkretisht "mbushja" e fushave brenda shënimit të çdo tabele.

Sepse për shkak të rrjeshtimit të të dhënave kjo drejtpërdrejt ndikon në volumet e fundit:

Shumë arkitektura parashikojnë rreshtimin e të dhënave sipas kufijve të fjalëve makinerike. Për shembull, në një sistem 32-bit x86 numerat e plotë (tipi integer, zë 4 bajt) do të rreshtohen në kufirin e fjalëve 4-bajtëshe, ashtu si numrat me pikë të dyfishtë (tipi double precision, 8 bajt). Ndërsa në një sistem 64-bit, vlerat double do të rreshtohen në kufirin e fjalëve 8-bajtëshe. Kjo është një tjetër arsye për moskompatibilitet.

Për shkak të rregullimit, madhësia e rreshtit të tabelës varet nga rendi i vendosjes së fushave. Në përgjithësi, ky efekt nuk është shumë i dukshëm, por në disa raste ai mund të çojë në një rritje të konsiderueshme të madhësisë. Për shembull, nëse fusha të tipit char(1) dhe integer vendosen ndaras, zakonisht do të humbin 3 byte më kot.

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-0000'::uuid
, 0::smallint
));
-- 46 byte

Nga ku erdhĂ«n disa byte tĂ« tepruar nĂ« rastin e parĂ«? E thjeshtĂ« — smallint me 2 byte rregullohet nĂ« kufirin me 4 byte para fushĂ«s tjetĂ«r, ndĂ«rsa kur Ă«shtĂ« e fundit — nuk ka nevojĂ« pĂ«r rregullim.

NĂ« teori — gjithçka Ă«shtĂ« mirĂ« dhe fushat mund tĂ« riorganizohen siç dĂ«shirohen. Le tĂ« kontrollojmĂ« me tĂ« dhĂ«na reale pĂ«r shembullin e njĂ« nga tabelave, sekzioni ditor tĂ« cilit i nevojiten 10-15GB.

Struktura fillestare:

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)

Sekcioni pas ndĂ«rrimit tĂ« rendit tĂ« kolonave — saktĂ«sisht tĂ« njĂ«jtat fusha, vetĂ«m rendi tjetĂ«r:

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)

VĂ«llimi total i sekcionit pĂ«rcaktohet nga numri i "fakteve" dhe varet vetĂ«m nga proceset e jashtme, prandaj le tĂ« ndajmĂ« madhĂ«sinĂ« e heap (pg_relation_size) pĂ«r numrin e shĂ«nimeve nĂ« tĂ« — pra do tĂ« marrim pĂ«rmasĂ«n mesatare tĂ« shĂ«nimit tĂ« ruajtur real:

Kurseni qindarka në vëllime të mëdha në PostgreSQL
Minus 6% të volumit, shkëlqyer!

Por, natyrisht, gjĂ«rat nuk janĂ« aq rozĂ« — sepse nĂ« indekset nuk mund tĂ« ndryshojmĂ« rendin e fushave, dhe prandaj «nĂ« pĂ«rgjithĂ«si» (pg_total_relation_size)


Kurseni qindarka në vëllime të mëdha në PostgreSQL

 megjithatë, edhe këtu kemi kursyer 1.5%, pa ndryshuar asnjë rresht kodi. Po, vërtet!

Kurseni qindarka në vëllime të mëdha në PostgreSQL

Dua tĂ« theksoj se varianti i dhĂ«nĂ« mĂ« lart i renditjes sĂ« fushave — nuk Ă«shtĂ« fakt se Ă«shtĂ« mĂ« optimal. Sepse disa blloqe fushash nuk dua t'i «çaj» pĂ«r arsye estetike — pĂ«r shembull, njĂ« çift (pack, recno), i cili Ă«shtĂ« PK pĂ«r kĂ«tĂ« tabelĂ«.

NĂ« tĂ«rĂ«si, pĂ«rcaktimi i «renditjes minimale» tĂ« fushave — Ă«shtĂ« njĂ« detyrĂ« mjaft e thjeshtĂ« «kĂ«rkuese». Prandaj ju mund tĂ« merrni rezultate even mĂ« tĂ« mira me tĂ« dhĂ«nat tuaja se sa ne — provoni!

Burimi: habr.com

Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS đŸ”„ Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS | ProHoster