Sungani ndalama pamagulu akulu mu PostgreSQL

Kupitiliza mutu wa kujambula mitsinje yayikulu ya data yomwe idakwezedwa ndi m'mbuyo nkhani za partitioning, m’nkhaniyi tiona njira zimene mungathere kuchepetsa kukula kwa "thupi" la zosungidwa mu PostgreSQL, ndi zotsatira zake pakugwira ntchito kwa seva.

Tikambirana Zokonda za TOAST ndi kulumikizana kwa data. "Pafupipafupi," njirazi sizingapulumutse zinthu zambiri, koma popanda kusintha kachidindo kogwiritsa ntchito konse.

Komabe, zomwe takumana nazo zidakhala zopindulitsa kwambiri pankhaniyi, popeza kusungirako pafupifupi kuwunika kulikonse ndi chikhalidwe chake zambiri zowonjezera-zokha malinga ndi deta yojambulidwa. Ndipo ngati mukuganiza momwe mungaphunzitsire nkhokwe kuti mulembe ku disk m'malo mwake 200MB / s theka lambiri - chonde pansi pa mphaka.

Zinsinsi zazing'ono za data yayikulu

Mwa mbiri ya ntchito utumiki wathu, nthawi zonse zimawulukira kwa iye kuchokera m'mabwalo zolemba phukusi.

Ndipo kuyambira pamenepo Zithunzi za VLSIomwe nkhokwe yake timawunikira ndi zinthu zambiri zokhala ndi ma data ovuta, kenako mafunso kwa magwiridwe antchito kwambiri kukhala chonchi "Mipikisano voliyumu" yokhala ndi malingaliro ovuta a algorithmic. Chifukwa chake kuchuluka kwa chochitika chilichonse cha pempho kapena dongosolo lokonzekera lomwe limabwera kwa ife limakhala "lalikulu" kwambiri.

Tiyeni tiwone momwe imodzi mwamatebulo omwe timalembamo "yaiwisi" - ndiye kuti, apa pali zolembedwa zoyambirira kuchokera palemba lolemba:

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

Chizindikiro chodziwika bwino (chagawanika kale, ndithudi, kotero ichi ndi template ya gawo), kumene chofunika kwambiri ndi malemba. Nthawi zina voluminous.

Kumbukirani kuti kukula "kwathupi" kwa mbiri imodzi mu PG sikungakhale ndi masamba opitilira imodzi, koma kukula "koyenera" ndi nkhani yosiyana kotheratu. Kuti mulembe mtengo wa volumetric (varchar/text/bytea) kumunda, gwiritsani ntchito Mtengo wa magawo TOAST:

PostgreSQL imagwiritsa ntchito kukula kwa tsamba lokhazikika (nthawi zambiri 8 KB), ndipo siyilola kuti ma tuple azikhala masamba angapo. Choncho, n'zosatheka kusunga mwachindunji zikhalidwe zazikulu zamunda. Kuti athane ndi izi, ziwongola dzanja zazikulu zimapanikizidwa ndi/kapena kugawanika pamizere ingapo yakuthupi. Izi zimachitika mosazindikira ndi wogwiritsa ntchito ndipo zimakhala ndi zotsatira zochepa pa ma code ambiri a seva. Njirayi imadziwika kuti TOAST...

M'malo mwake, patebulo lililonse lomwe lili ndi minda "yomwe ingakhale yayikulu", zokha tebulo lophatikizidwa ndi "slicing" limapangidwa chilichonse "chachikulu" m'magawo a 2KB:

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

Ndiko kuti, ngati tiyenera kulemba chingwe ndi "chachikulu" mtengo data, ndiye kujambula kwenikweni kudzachitika osati patebulo lalikulu ndi PK yake, komanso ku TOAST ndi PK yake.

Kuchepetsa kukopa kwa TOAST

Koma zolemba zathu zambiri sizinali zazikulu, ayenera kukhala 8 KB - Ndingasunge bwanji ndalama pa izi? ..

Apa ndi pamene khalidwe limatithandiza STORAGE pagawo la tebulo:

• ZOCHITIKA amalola onse psinjika ndi osiyana yosungirako. Izi muyezo njira pamitundu yambiri yogwirizana ndi TOAST. Poyamba imayesa kukakamiza, kenako imayisunga kunja kwa tebulo ngati mzerewo udakali waukulu kwambiri.
• CHIKULU amalola psinjika koma osati osiyana yosungirako. (M'malo mwake, kusungirako kosiyana kudzachitidwabe pazigawo zotere, koma kokha ngati njira yomaliza, pamene palibe njira ina yochepetsera chingwecho kuti chikwane patsamba.)

M'malo mwake, izi ndi zomwe tikufuna palembali - pondani momwe mungathere, ndipo ngati sichikukwanira, ikani mu TOAST. Izi zitha kuchitika molunjika pa ntchentche, ndi lamulo limodzi:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Momwe mungadziwire zotsatira zake

Popeza kusuntha kwa deta kumasintha tsiku ndi tsiku, sitingathe kuyerekeza manambala athunthu, koma molingana gawo laling'ono Tinalemba mu TOAST - zabwino kwambiri. Koma pali chiwopsezo apa - kuchuluka kwa "thupi" kwa mbiri iliyonse, "kufalikira" kwa index kumakhala, chifukwa tiyenera kuphimba masamba ochulukirapo.

Gawo zisanasinthe:

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

Gawo pambuyo pa kusintha:

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

Ndipotu ife adayamba kulemba ku TOAST 2 kucheperako, yomwe sinatulutse diski yokha, komanso CPU:

Ndizindikira kuti takhalanso ochepa pa "kuwerenga" disk, osati "kulemba" kokha - popeza poyika zolemba patebulo, tiyeneranso "kuwerenga" gawo la mtengo wa index iliyonse kuti tidziwe malo amtsogolo mwa iwo.

Ndani angakhale bwino pa PostgreSQL 11

Titasinthira ku PG11, tidaganiza zopitiliza "kukonza" TOAST ndipo tidawona kuti kuyambira pamtunduwu mawonekedwe toast_tuple_target:

Khodi ya TOAST imangoyaka ngati mizere yoti isungidwe patebulo ili yayikulu kuposa TOAST_TUPLE_THRESHOLD byte (nthawi zambiri 2 KB). Khodi ya TOAST ipanikiza ndi/kapena kusuntha makonda kuchokera patebulo mpaka mtengo ukhale wotsika kuposa TOAST_TUPLE_TARGET mabayiti (mtengo wosinthika, nthawi zambiri 2 KB) kapena kukula kwake sikungachepe.

Tinaganiza kuti deta yomwe timakhala nayo nthawi zambiri imakhala "yaifupi kwambiri" kapena "yautali kwambiri", choncho tinaganiza zochepetsera mtengo wocheperako:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Tiyeni tiwone momwe zosintha zatsopano zidakhudzira kutsitsa kwa disk mutatha kukonzanso:

Osayipa kwenikweni! Avereji mzere wopita ku diski wachepa pafupifupi 1.5 nthawi, ndi litayamba "wotanganidwa" ndi 20 peresenti! Koma mwina izi zidakhudzanso CPU?

Osachepera sizinaipire. Ngakhale, ndizovuta kuweruza ngati ngakhale ma voliyumu otere sangathe kukweza kuchuluka kwa CPU 5%.

Posintha malo a mawu, kuchuluka ... kusintha!

Monga mukudziwira, khobiri limasunga ruble, ndipo ndi ma voliyumu athu osungira ndi pafupi 10TB / pamwezi ngakhale kukhathamiritsa pang'ono kungapereke phindu labwino. Chifukwa chake, tidalabadira kapangidwe kathu ka deta - momwemo ndendende "zambirimbiri" minda mkati mwa mbiri lililonse la matebulo.

Chifukwa chifukwa kulumikizana kwa data izi ndi zolunjika kutsogolo zimakhudza kuchuluka kwake:

Zomangamanga zambiri zimapereka kulumikizana kwa data pamalire a mawu a makina. Mwachitsanzo, pamakina a 32-bit x86, manambala (mtundu wamtundu uliwonse, ma byte 4) adzayanjanitsidwa pamalire a mawu a 4-byte, monga momwe zidzakhalire manambala amalo oyandama olondola kawiri (malo oyandama olondola kawiri, ma byte 8). Ndipo pamakina a 64-bit, zikhalidwe ziwiri zizigwirizana ndi malire a mawu a 8-byte. Ichi ndi chifukwa china chosagwirizana.

Chifukwa cha kuyanjanitsa, kukula kwa mzere wa tebulo kumadalira dongosolo la minda. Nthawi zambiri izi sizikuwoneka bwino, koma nthawi zina zimatha kukulitsa kukula kwake. Mwachitsanzo, ngati mutasakaniza magawo a char(1) ndi ma integer, padzakhala ma byte atatu omwe atayidwa pakati pawo.

Tiyeni tiyambe ndi zitsanzo zopangira:

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

Kodi ma byte angapo owonjezera adachokera kuti koyambirira? Ndi zophweka - 2-byte yaying'ono yolumikizidwa pamalire a 4-byte pamaso pa munda wotsatira, ndipo pamene uli wotsiriza, palibe ndipo palibe chifukwa chogwirizanitsa.

M'malingaliro, zonse zili bwino ndipo mutha kukonzanso minda momwe mukufunira. Tiyeni tiyang'ane pa data yeniyeni pogwiritsa ntchito chitsanzo cha tebulo limodzi, gawo la tsiku ndi tsiku lomwe limakhala 10-15GB.

Kapangidwe koyambirira:

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)

Gawo pambuyo posintha dongosolo lazambiri - ndendende minda yomweyo, dongosolo losiyana:

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)

Kuchuluka kwa gawoli kumatsimikiziridwa ndi chiwerengero cha "zoona" ndipo zimangotengera njira zakunja, kotero tiyeni tigawane kukula kwa mulu (pg_relation_size) ndi chiwerengero cha zolemba momwemo - ndiko kuti, timapeza kukula kwapakati pa mbiri yosungidwa:

Kuchotsa 6% voliyumu, Zabwino!

Koma zonse, ndithudi, sizili zabwino - pambuyo pake, mu indexes sitingathe kusintha dongosolo la minda, ndipo chifukwa chake "zambiri" (pg_total_relation_size) ...

...ndikali panonso yasungidwa 1.5%popanda kusintha mzere umodzi wa code. Inde, inde!

Ndikuwona kuti njira yomwe ili pamwambapa yokonzekera minda sikuti ndiyomwe ili yabwino kwambiri. Chifukwa simukufuna "kung'amba" magawo ena pazifukwa zokongola - mwachitsanzo, angapo (pack, recno), yomwe ndi PK ya tebulo ili.

Mwambiri, kuzindikira makonzedwe a "minimum" a minda ndi ntchito yosavuta "brute force". Chifukwa chake, mutha kupeza zotsatira zabwinoko kuchokera ku data yanu kuposa yathu - yesani!

Source: www.habr.com