Duke vazhdimin e temës së regjistrimit të flukseve të mëdha të të dhënave , 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.
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 , ai përballonte rregullisht nga logët paqet tekstuale.
Dhe përsa i përket , 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 . 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 :
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 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 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ë:
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. :
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:
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?
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 kjo ndikon drejtpërdrejt :
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 byteNga 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
(
-- Унаследована 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) 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.:
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)…
… megjithatë këtu kurse kemi arritur të kursen 1.5%, pa ndryshuar asnjë rresht kodi. Po, kështu është!
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
