Sábháil pingin ar líon mór i PostgreSQL

Leanúint ar aghaidh leis an topaic a bhaineann le sruthanna móra sonraí a d’ardaigh alt roimhe seo faoi dheighilt, leis seo féachfaimid ar na bealaí inar féidir leat laghdú ar an "fisiciúil" méid an stóráilte in PostgreSQL, agus a dtionchar ar fheidhmíocht an fhreastalaí.

Beidh muid ag caint faoi Socruithe TOAST agus ailíniú sonraí. “Ar an meán,” ní shábhálfaidh na modhanna seo an iomarca acmhainní, ach gan an cód feidhmchláir a mhodhnú ar chor ar bith.

Mar sin féin, d'éirigh lenár dtaithí a bheith an-táirgiúil maidir leis seo, ós rud é go stóráiltear beagnach aon mhonatóireacht dá nádúr den chuid is mó aguisín-amháin i dtéarmaí sonraí taifeadta. Agus má tá tú ag smaoineamh conas is féidir leat an bunachar sonraí a mhúineadh chun scríobh chuig diosca ina ionad 200MB / s leath an oiread - le do thoil faoi cat.

Rúin bheaga sonraí móra

De réir phróifíl poist ár seirbhís, eitilt siad go rialta chuige ó na lairs pacáistí téacs.

Agus ó shin Coimpléasc VLSIar táirge il-chomhpháirte é a mbunachar sonraí a ndéanaimid monatóireacht air le struchtúir sonraí casta, agus ansin fiosrúcháin le haghaidh feidhmíochta uasta cas amach go leor mar seo “il-imleabhar” le loighic chasta algartamaíoch. Mar sin dealraíonn sé go bhfuil toirt gach ásc aonair iarratais nó an plean forghníomhaithe dá bharr sa loga a thagann chugainn “ar an meán” sách mór.

Breathnaímid ar struchtúr ceann de na táblaí ina scríobhaimid sonraí “amh” - is é sin, seo é an buntéacs ón logáil isteach:

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

Comhartha tipiciúil (roinnte cheana féin, ar ndóigh, mar sin tá sé seo le teimpléad alt), áit a bhfuil an rud is tábhachtaí an téacs. Uaireanta toirt go leor.

Thabhairt chun cuimhne nach féidir le méid “fisiciúil” taifead amháin i PG níos mó ná leathanach amháin sonraí a áitiú, ach is ábhar difriúil ar fad é an méid “loighciúil”. Chun luach toirtmhéadrach (varchar/téacs/beart) a scríobh chuig réimse, úsáid Teicneolaíocht TOAST:

Úsáideann PostgreSQL méid leathanach seasta (8 KB go hiondúil), agus ní ligeann sé do thuplaí dul thar leathanaigh iolracha. Mar sin, tá sé dodhéanta luachanna réimse an-mhór a stóráil go díreach. Chun an teorannú seo a shárú, déantar luachanna réimse móra a chomhbhrú agus/nó a roinnt thar línte fisiceacha iolracha. Tarlaíonn sé seo nach dtugtar an t-úsáideoir faoi deara agus is beag tionchar a bhíonn aige ar an gcuid is mó de chód an fhreastalaí. Tugtar TOAST ar an modh seo...

Go deimhin, le haghaidh gach tábla le réimsí "a d'fhéadfadh a bheith mór", go huathoibríoch cruthaítear tábla péireáilte le “slisniú”. gach taifead “mór” i míreanna 2KB:

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

Is é sin, má tá orainn teaghrán a scríobh le luach “mór”. data, ansin beidh an taifeadadh fíor tarlú ní hamháin don phríomhbhord agus a PK, ach freisin do TOAST agus a PK.

Tionchar TOAST a laghdú

Ach níl an chuid is mó dár dtaifid chomh mór sin fós, ba chóir go luífeadh sé isteach i 8KB - Conas is féidir liom airgead a shábháil ar seo? ..

Seo an áit a dtagann an tréith chun ár gcabhair STORAGE ag an gcolún tábla:

• BREIS ligeann sé comhbhrú agus stóráil ar leithligh araon. seo rogha caighdeánach don chuid is mó de na cineálacha sonraí a chomhlíonann TOAST. Déanann sé iarracht comhbhrú a dhéanamh ar dtús, ansin é a stóráil lasmuigh den tábla má tá an tsraith ró-mhór fós.
• PRÍOMH ceadaíonn sé comhbhrú ach ní stóráil ar leithligh. (Go deimhin, déanfar stóráil ar leithligh fós le haghaidh colúin den sórt sin, ach amháin mar rogha dheireanach, nuair nach bhfuil aon bhealach eile ann chun an teaghrán a chrapadh ionas go n-oireann sé ar an leathanach.)

Go deimhin, is é seo go díreach cad a theastaíonn uainn don téacs - comhbhrú an oiread agus is féidir é, agus mura n-oireann sé ar chor ar bith, cuir i TOAST é. Is féidir é seo a dhéanamh go díreach ar an eitilt, le hordú amháin:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Conas an éifeacht a mheas

Ós rud é go n-athraíonn an sreabhadh sonraí gach lá, ní féidir linn dearbhuimhreacha a chur i gcomparáid, ach i dtéarmaí coibhneasta sciar níos lú Scríobhamar síos é in TOAST - is amhlaidh is fearr i bhfad. Ach tá an baol ann anseo - dá mhéad toirt “fisiciúil” gach taifid aonair, is amhlaidh is “leithne” a éiríonn an t-innéacs, toisc go gcaithfimid níos mó leathanach sonraí a chlúdach.

Roinn roimh athruithe:

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

Roinn tar éis athruithe:

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

Go deimhin, táimid thosaigh sé ag scríobh chuig TOAST 2 huaire níos lú, a dhíluchtaigh ní hamháin an diosca, ach freisin an LAP:

Tabharfaidh mé faoi deara go bhfuil muid tar éis éirí níos lú freisin maidir le “léamh” an diosca, ní hamháin “scríbhneoireacht” - ós rud é agus taifead á chur isteach i dtábla, ní mór dúinn freisin cuid de chrann gach innéacs a “léamh” chun a chinneadh a dhéanamh. seasamh sa todhchaí iontu.

Cé atá in ann maireachtáil go maith ar PostgreSQL 11

Tar éis nuashonrú go PG11, bheartaíomar leanúint ar aghaidh ag “tiúnadh” TOAST agus thugamar faoi deara go raibh an paraiméadar ar fáil le haghaidh tiúnadh ón leagan seo. toast_tuple_target:

Ní scaoiltear an cód próiseála TOAST ach amháin nuair a bhíonn luach na rónna atá le stóráil sa tábla níos mó ná TOAST_TUPLE_THRESHOLD bytes (2 KB de ghnáth). Déanfaidh an cód TOAST luachanna réimse a chomhbhrú agus/nó a bhogadh amach as an tábla go dtí go n-éireoidh luach an ró níos lú ná TOAST_TUPLE_TARGET bytes (luach inathraithe, 2 KB de ghnáth) nó ní féidir an méid a laghdú.

Shocraigh muid go mbíonn na sonraí atá againn de ghnáth “an-ghearr” nó “an-fhada”, agus mar sin bheartaíomar sinn féin a theorannú go dtí an luach is lú is féidir:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Feicfimid conas a chuir na socruithe nua isteach ar lódáil diosca tar éis an athchumraithe:

Ní dona! Meán tá laghdú tagtha ar scuaine an diosca thart ar 1.5 uair, agus tá an diosca “gnóthach” 20 faoin gcéad! Ach b'fhéidir go ndeachaigh sé seo i bhfeidhm ar an LAP ar bhealach éigin?

Ar a laghad níor éirigh sé níos measa. Mar sin féin, tá sé deacair a mheas an bhfuil fiú méideanna den sórt sin fós in ann an meán-ualach LAP a ardú níos airde 5%.

Trí áiteanna na dtéarmaí a athrú, athraíonn an tsuim...!

Mar is eol duit, sábhálann pingin rúbal, agus lenár méideanna stórála tá sé thart 10TB in aghaidh na míosa is féidir fiú leas iomlán a bhaint beag a thabhairt brabús maith. Mar sin, thugamar aird ar struchtúr fisiceach ár sonraí - cé chomh díreach réimsí “cruachta” taobh istigh den taifead gach ceann de na táblaí.

Mar gheall ar ailíniú sonraí tá sé seo díreach ar aghaidh tionchar ar an toirt mar thoradh air:

Soláthraíonn go leor ailtireachtaí ailíniú sonraí ar theorainneacha focal meaisín. Mar shampla, ar chóras 32-giotán x86, déanfar slánuimhreacha (cineál slánuimhir, 4 beart) a ailíniú ar theorainn focal 4-beart, chomh maith le huimhreacha cruinne snámhphointe dúbailte (snámhphointe cruinneas dúbailte, 8 beart). Agus ar chóras 64-giotán, déanfar luachanna dúbailte a ailíniú le teorainneacha focal 8-byte. Is cúis eile é seo le neamh-chomhoiriúnacht.

Mar gheall ar ailíniú, braitheann an méid as a chéile tábla ar ord na réimsí. De ghnáth níl an éifeacht seo an-suntasach, ach i gcásanna áirithe is féidir go dtiocfadh méadú suntasach ar mhéid. Mar shampla, má mheascann tú gualaigh(1) agus páirceanna slánuimhir, de ghnáth cuirfear 3 beart amú eatarthu.

Tosaímid le samhlacha sintéiseacha:

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

Cén áit ar tháinig cúpla beart breise sa chéad chás? Tá sé simplí - Sliotán 2-bheart ailínithe ar theorainn 4 beart roimh an chéad réimse eile, agus nuair a bheidh sé an ceann deireanach, níl aon rud agus ní gá a ailíniú.

Go teoiriciúil, tá gach rud go breá agus is féidir leat na réimsí a athshocrú mar is mian leat. Déanaimis é a sheiceáil ar fhíorshonraí ag baint úsáide as an sampla de cheann de na táblaí, a bhfuil an chuid laethúil de 10-15GB.

Struchtúr tosaigh:

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)

Alt tar éis ordú colún a athrú - go díreach réimsí céanna, ach ord difriúil:

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)

Cinntear toirt iomlán na coda ag líon na “fíricí” agus braitheann sé ar phróisis sheachtracha amháin, mar sin déanaimis méid an charn a roinnt (pg_relation_size) de réir líon na dtaifead atá ann - is é sin, a fhaighimid meánmhéid taifead stóráilte iarbhír:

Lúide toirt 6%., Go hiontach!

Ach níl gach rud, ar ndóigh, chomh rosy - tar éis an tsaoil, in innéacsanna ní féidir linn ord na réimsí a athrú, agus mar sin “go ginearálta” (pg_total_relation_size) ...

... anseo fós shábháil 1.5%gan líne amháin de chód a athrú. Sea, tá!

Tugaim faoi deara nach é an rogha thuas maidir le réimsí a shocrú ar an bhfíric gurb é an rogha is fearr. Toisc nach bhfuil tú ag iarraidh roinnt bloic réimsí a “chuimilt” ar chúiseanna aeistéitiúla - mar shampla, cúpla (pack, recno), a bhfuil an PK don tábla seo.

Go ginearálta, is tasc simplí “fórsa brúidiúil” é socrú “íosmhéid” réimsí a chinneadh. Mar sin, is féidir leat torthaí níos fearr fós a fháil ó do shonraí ná mar atá againne - bain triail as!

Foinse: will.com