🥇 Nfipamọ Penny lẹwa kan lori awọn ipele nla ni PostgreSQL

Tesiwaju koko-ọrọ ti gbigbasilẹ awọn ṣiṣan data nla ti o dide nipasẹ ti tẹlẹ article nipa ipin, ninu eyi a yoo wo awọn ọna ti o le ṣe din "ti ara" iwọn ti awọn ti o ti fipamọ ni PostgreSQL, ati ipa wọn lori iṣẹ olupin.

A yoo soro nipa Eto TOAST ati titete data. “Ni apapọ,” awọn ọna wọnyi kii yoo ṣafipamọ ọpọlọpọ awọn orisun, ṣugbọn laisi iyipada koodu ohun elo rara.

Sibẹsibẹ, iriri wa ti jade lati jẹ iṣelọpọ pupọ ni ọran yii, nitori ibi ipamọ ti fere eyikeyi ibojuwo nipasẹ iseda rẹ jẹ julọ append-nikan ni awọn ofin ti o ti gbasilẹ data. Ati pe ti o ba n iyalẹnu bi o ṣe le kọ data data lati kọ si disk dipo 200MB / s idaji bi Elo - jọwọ labẹ o nran.

Awọn aṣiri kekere ti data nla

Nipa profaili iṣẹ iṣẹ wa, wọ́n máa ń fò lọ sí ọ̀dọ̀ rẹ̀ látìgbàdégbà ọrọ jo.

Ati lati igba naa VLSI ekadata rẹ ti a ṣe atẹle jẹ ọja eroja pupọ pẹlu awọn ẹya data idiju, lẹhinna awọn ibeere fun o pọju išẹ tan jade oyimbo bi yi "olona-iwọn" pẹlu eka algorithmic kannaa. Nitorinaa iwọn ti apẹẹrẹ kọọkan ti ibeere kan tabi ero ipaniyan abajade ninu log ti o wa si wa ni “ni apapọ” tobi pupọ.

Jẹ ki a wo eto ti ọkan ninu awọn tabili sinu eyiti a kọ data “aise” - iyẹn ni, eyi ni ọrọ atilẹba lati titẹ sii log:

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

Aami aṣoju (ti a ti pin tẹlẹ, nitorinaa, eyi jẹ awoṣe apakan), nibiti ohun pataki julọ jẹ ọrọ naa. Nigba miran oyimbo voluminous.

Ranti pe iwọn “ti ara” ti igbasilẹ kan ninu PG ko le gba diẹ sii ju oju-iwe kan ti data lọ, ṣugbọn iwọn “logbon” jẹ ọrọ ti o yatọ patapata. Lati kọ iye iwọn didun kan (varchar/ọrọ/bytea) si aaye kan, lo TOAST ọna ẹrọ:

PostgreSQL nlo iwọn oju-iwe ti o wa titi (eyiti o jẹ 8 KB), ati pe ko gba awọn tuple laaye lati tan awọn oju-iwe pupọ. Nitorinaa, ko ṣee ṣe lati tọju awọn iye aaye ti o tobi pupọ taara. Lati bori aropin yii, awọn iye aaye nla jẹ fisinuirindigbindigbin ati/tabi pin kọja awọn laini ti ara lọpọlọpọ. Eyi ṣẹlẹ laisi akiyesi nipasẹ olumulo ati pe o ni ipa diẹ lori koodu olupin pupọ julọ. Ọna yii ni a mọ si TOAST...

Ni otitọ, fun gbogbo tabili pẹlu awọn aaye “o pọju nla”, laifọwọyi tabili ti a so pọ pẹlu “pipẹ” ti ṣẹda igbasilẹ “nla” kọọkan ni awọn apakan 2KB:

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

Iyẹn ni, ti a ba ni lati kọ okun kan pẹlu iye “nla” kan data, lẹhinna igbasilẹ gidi yoo waye kii ṣe si tabili akọkọ nikan ati PK rẹ, ṣugbọn tun si TOAST ati PK rẹ.

Idinku ipa TOAST

Ṣugbọn pupọ julọ awọn igbasilẹ wa ko tun tobi ju, yẹ ki o dada sinu 8KB - Bawo ni MO ṣe le ṣafipamọ owo lori eyi? ..

Eyi ni ibi ti abuda naa wa si iranlọwọ wa STORAGE ni iwe tabili:

TILE faye gba mejeeji funmorawon ati lọtọ ipamọ. Eyi boṣewa aṣayan fun ọpọlọpọ awọn iru data ifaramọ TOAST. O akọkọ igbiyanju lati ṣe funmorawon, ki o si tọjú o ita awọn tabili ti o ba ti kana jẹ tun tobi ju.

MỌ́ faye gba funmorawon sugbon ko lọtọ ipamọ. (Ni otitọ, ibi ipamọ lọtọ yoo tun ṣee ṣe fun iru awọn ọwọn, ṣugbọn nikan bi ohun asegbeyin ti, nigbati ko ba si ọna miiran lati dinku okun naa ki o baamu lori oju-iwe naa.)

Ni otitọ, eyi ni deede ohun ti a nilo fun ọrọ naa - compress o bi o ti ṣee, ati ti o ba ti o ko ba wo dada ni gbogbo, fi o ni TOAST. Eyi le ṣee ṣe taara lori fifo, pẹlu aṣẹ kan:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Bii o ṣe le ṣe iṣiro ipa naa

Niwọn bi sisan data n yipada lojoojumọ, a ko le ṣe afiwe awọn nọmba pipe, ṣugbọn ni awọn ofin ibatan kere pin A kọ si isalẹ ni TOAST - pupọ dara julọ. Ṣugbọn eewu kan wa nibi - iwọn “ti ara” ti o tobi julọ ti igbasilẹ kọọkan, “fifẹ” atọka naa di, nitori a ni lati bo awọn oju-iwe data diẹ sii.

Abala ṣaaju awọn ayipada:

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

Abala lẹhin awọn ayipada:

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

Ni otitọ, awa bẹrẹ kikọ si TOAST ni igba 2 kere si nigbagbogbo, eyiti kii ṣe disk nikan, ṣugbọn Sipiyu naa:

Emi yoo ṣe akiyesi pe a tun ti dinku ni “kika” disk naa, kii ṣe “kikọ” nikan - nitori nigba fifi igbasilẹ sii sinu tabili, a tun ni lati “ka” apakan igi ti atọka kọọkan lati le pinnu rẹ. ojo iwaju ipo ninu wọn.

Tani o le gbe daradara lori PostgreSQL 11

Lẹhin mimu dojuiwọn si PG11, a pinnu lati tẹsiwaju “tuntun” TOAST ati ṣe akiyesi pe bẹrẹ lati ẹya yii paramita naa wa fun titunṣe toast_tuple_target:

Awọn koodu sisẹ TOAST ina nikan nigbati iye kana lati wa ni ipamọ ninu tabili tobi ju TOAST_TUPLE_THRESHOLD awọn baiti (nigbagbogbo 2 KB). Koodu TOAST naa yoo rọpọ ati/tabi gbe awọn iye aaye kuro ninu tabili titi ti iye ila yoo kere ju TOAST_TUPLE_TARGET awọn baiti (iye iyipada, tun nigbagbogbo 2 KB) tabi iwọn ko le dinku.

A pinnu pe data ti a nigbagbogbo ni boya “kukuru pupọ” tabi “gun pupọ”, nitorinaa a pinnu lati fi opin si ara wa si iye to ṣeeṣe ti o kere julọ:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Jẹ ki a wo bii awọn eto tuntun ṣe kan ikojọpọ disk lẹhin atunto:

Ko buru! Apapọ ti isinyi si disk ti dinku to 1.5 igba, ati disk "nšišẹ" ni 20 ogorun! Sugbon boya yi bakan fowo Sipiyu?

Ni o kere o ko gba eyikeyi buru. Botilẹjẹpe, o ṣoro lati ṣe idajọ ti paapaa iru awọn ipele ko tun le gbe fifuye apapọ Sipiyu ga 5%.

Nipa yiyipada awọn aaye ti awọn ofin, apao ... awọn ayipada!

Bii o ṣe mọ, penny kan ṣafipamọ ruble kan, ati pẹlu awọn iwọn ipamọ wa o fẹrẹ to 10TB / osù ani diẹ ti o dara ju le fun kan ti o dara èrè. Nitorinaa, a san ifojusi si eto ti ara ti data wa - bawo ni deede Awọn aaye "tolera" inu igbasilẹ naa kọọkan ninu awọn tabili.

Nitori nitori ti titete data eyi ni taara siwaju yoo ni ipa lori iwọn didun abajade:

Ọpọlọpọ awọn faaji pese titete data lori awọn aala ọrọ ẹrọ. Fun apẹẹrẹ, lori eto x32 86-bit, awọn nọmba (oriṣi odidi, awọn baiti 4) yoo wa ni ibamu lori aala ọrọ 4-baiti, gẹgẹbi yoo ṣe ilọpo meji awọn nọmba oju omi lilefoofo (ojuami lilefoofo oju omi ilọpo meji, awọn baiti 8). Ati lori eto 64-bit, awọn iye meji yoo wa ni ibamu si awọn aala ọrọ 8-baiti. Eyi jẹ idi miiran fun incompatibility.

Nitori titete, iwọn laini tabili kan da lori aṣẹ ti awọn aaye. Nigbagbogbo ipa yii kii ṣe akiyesi pupọ, ṣugbọn ni awọn igba miiran o le ja si ilosoke pataki ni iwọn. Fun apẹẹrẹ, ti o ba dapọ char(1) ati awọn aaye odidi, igbagbogbo awọn baiti mẹta yoo jẹ sofo laarin wọn.

Jẹ ki a bẹrẹ pẹlu awọn awoṣe sintetiki:

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

Nibo ni a tọkọtaya ti afikun baiti wa lati ni akọkọ nla? O rọrun - 2-baiti smallint deedee lori 4-baiti ala ṣaaju aaye ti o tẹle, ati nigbati o jẹ ti o kẹhin, ko si nkankan ati pe ko si ye lati ṣe deede.

Ni imọran, ohun gbogbo dara ati pe o le tunto awọn aaye bi o ṣe fẹ. Jẹ ki a ṣayẹwo rẹ lori data gidi ni lilo apẹẹrẹ ti ọkan ninu awọn tabili, apakan ojoojumọ ti eyiti o gba 10-15GB.

Ilana akọkọ:

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)

Apakan lẹhin iyipada aṣẹ ọwọn - gangan kanna oko, o kan yatọ si ibere:

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)

Iwọn apapọ ti apakan jẹ ipinnu nipasẹ nọmba awọn “awọn otitọ” ati da lori awọn ilana ita nikan, nitorinaa jẹ ki a pin iwọn ti okiti naa (pg_relation_size) nipasẹ nọmba awọn igbasilẹ ti o wa ninu rẹ - eyini ni, a gba apapọ iwọn ti gangan ti o ti fipamọ igbasilẹ:

Iyokuro 6% iwọn didun, Nla!

Ṣugbọn ohun gbogbo, nitorinaa, kii ṣe rosy pupọ - lẹhinna, ni awọn atọka a ko le yi awọn ibere ti awọn aayeati nitori naa “ni gbogbogbo” (pg_total_relation_size)…

... tun wa nibi ti o ti fipamọ 1.5%lai yi pada kan nikan ila ti koodu. Bẹẹni, bẹẹni!

Mo ṣe akiyesi pe aṣayan ti o wa loke fun siseto awọn aaye kii ṣe otitọ pe o jẹ aipe julọ. Nitoripe o ko fẹ lati “ya” diẹ ninu awọn bulọọki ti awọn aaye fun awọn idi ẹwa - fun apẹẹrẹ, tọkọtaya kan (pack, recno), eyi ti o jẹ PK fun yi tabili.

Ni gbogbogbo, ṣiṣe ipinnu eto “kere” ti awọn aaye jẹ iṣẹ-ṣiṣe “agbara iro” ti o rọrun. Nitorinaa, o le gba awọn abajade to dara paapaa lati data rẹ ju tiwa lọ - gbiyanju!

orisun: www.habr.com

Fi Penny kan pamọ sori awọn iwọn nla ni PostgreSQL

Awọn aṣiri kekere ti data nla

Idinku ipa TOAST

Bii o ṣe le ṣe iṣiro ipa naa

Tani o le gbe daradara lori PostgreSQL 11

Nipa yiyipada awọn aaye ti awọn ofin, apao ... awọn ayipada!

Fi ọrọìwòye kun Fagilee esi