Boloka penny ho li-volume tse kholo ho PostgreSQL

Ho tsoela pele ka sehlooho sa ho rekota melaetsa e meholo ea data e phahamisitsoeng ke sengoloa se fetileng mabapi le ho arola, ho sena re tla sheba litsela tseo u ka li etsang fokotsa boholo ba "nama" bo bolokoang ho PostgreSQL, le phello ea bona ts'ebetsong ea seva.

Re tla bua ka Litlhophiso tsa TOAST le ho hokahanya ha data. "Ka karolelano," mekhoa ena e ke ke ea boloka lisebelisoa tse ngata haholo, empa ntle le ho fetola khoutu ea kopo ho hang.

Leha ho le joalo, phihlelo ea rona e ile ea hlahisa litholoana haholo tabeng ena, kaha ho boloka hoo e ka bang leha e le efe ea ho beha leihlo ka tlhaho ea eona ke haholo setlama-feela mabapi le data e rekotiloeng. 'Me haeba u ntse u ipotsa hore na u ka ruta database joang ho ngolla disk ho e-na le hoo 200MB / s halofo e ngata - ka kopo tlasa katse.

Liphiri tse nyane tsa data e kholo

Ka profil ea mosebetsi tshebeletso ya rona, ba lula ba fofela ho eena ba e-tsoa makhulong liphutheloana tsa mongolo.

Mme ho tloha VLSI e rarahanengeo database ea eona eo re e behang leihlo e le sehlahisoa se nang le likarolo tse ngata se nang le libopeho tse rarahaneng tsa data, ebe re botsa lipotso bakeng sa tshebetso e phahameng ho etsahala joalo "meqolo e mengata" e nang le logic e rarahaneng ea algorithmic. Kahoo palo ea ketsahalo e 'ngoe le e 'ngoe ea kopo kapa moralo oa ts'ebetso o hlahang ho logi e tlang ho rona e fetoha "ka karolelano" e kholo haholo.

Ha re shebeng sebopeho sa e 'ngoe ea litafole tseo re ngolang "data e tala" ho tsona - ke hore, sena ke sengoloa sa mantlha ho tsoa ho kenyelletso ea log:

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

Letšoao le tloaelehileng (le seng le arotsoe, ho hlakile, kahoo sena ke template ea karolo), moo ntho ea bohlokoa ka ho fetisisa e leng mongolo. Ka linako tse ling haholo voluminous.

Hopola hore boholo ba "'mele" ba rekoto e le' ngoe ho PG ha bo khone ho nka leqephe le fetang le le leng la data, empa boholo ba "bohlale" ke taba e fapaneng ka ho felletseng. Ho ngola boleng ba volumetric (varchar/text/bytea) tšimong, sebelisa Theknoloji ea TOAST:

PostgreSQL e sebelisa boholo ba leqephe le tsitsitseng (hangata 8 KB), 'me ha e lumelle li-tuples ho bala maqephe a mangata. Ka hona, ho ke ke ha khoneha ho boloka ka ho toba litekanyetso tse kholo haholo tsa tšimo. Ho hlola moeli ona, litekanyetso tse kholo tsa tšimo lia hatelloa le / kapa li aroloa ka mela e mengata ea 'mele. Sena se etsahala se sa lemohuoe ke mosebelisi mme se na le tšusumetso e nyane ho khoutu e ngata ea seva. Mokhoa ona o tsejoa e le TOAST...

Ha e le hantle, bakeng sa tafole e 'ngoe le e' ngoe e nang le masimo a "mohlomong a maholo", ka ho iketsa tafole e kopantsoeng le "slicing" e boptjoa rekoto e 'ngoe le e' ngoe e "kholo" likarolong tsa 2KB:

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

Ke hore, haeba re tlameha ho ngola khoele e nang le boleng "e kholo". data, joale rekoto ea sebele e tla etsahala eseng feela ho tafole e kholo le PK ea eona, empa hape le ho TOAST le PK ea eona.

Ho fokotsa tšusumetso ea TOAST

Empa boholo ba lirekoto tsa rona ha li kholo hakaalo, e lokela ho lekana ho 8KB - Nka boloka chelete joang ho see? ..

Mona ke moo tšobotsi e re thusang STORAGE kholomong ea tafole:

• TTELISITSOE lumella ka bobeli compression le ka thoko polokelo. Sena khetho e tloaelehileng bakeng sa mefuta e mengata ea data e lumellanang le TOAST. E qala ho leka ho etsa compression, ebe e e boloka ka ntle ho tafole haeba mola o ntse o le moholo haholo.
• SEHOLO e lumella ho hatella empa e se polokelo e arohaneng. (Ebile, polokelo e arohaneng e ntse e tla etsoa bakeng sa likholomo tse joalo, empa feela joalo ka khetho ea ho qetela, ha ho se na tsela e ’ngoe ea ho honyetsa khoele hore e kene leqepheng.)

Ha e le hantle, sena ke sona seo re se hlokang bakeng sa temana - e hatelle ka hohle kamoo ho ka khonehang, 'me haeba e sa lumellane ho hang, e behe ho TOAST. Sena se ka etsoa ka kotloloho ho fofa, ka taelo e le 'ngoe:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Mokhoa oa ho hlahloba phello

Kaha phallo ea data e fetoha letsatsi le leng le le leng, re ke ke ra bapisa linomoro tse felletseng, empa ka maemo a lekanyelitsoeng karolo e nyane Re e ngotse ka TOAST - e betere haholo. Empa ho na le kotsi mona - ha palo ea "'mele" e kholoanyane ea rekoto e' ngoe le e 'ngoe, index ea "bophara" e fetoha, hobane re tlameha ho koahela maqephe a mangata a data.

Karolo pele ho liphetoho:

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

Karolo ka mora liphetoho:

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

Ha e le hantle, re o qalile ho ngolla TOAST makhetlo a 2 hangata, e sa kang ea laolla disk feela, empa hape le CPU:

Ke tla hlokomela hore re se re le banyane ho "bala" disk, eseng feela "ho ngola" - kaha ha re kenya tlaleho tafoleng, re boetse re tlameha ho "bala" karolo ea sefate sa index ka 'ngoe ho fumana hore na boemo ba kamoso ho tsona.

Ke mang ea ka phelang hantle ho PostgreSQL 11

Kamora ho ntlafatsa ho PG11, re nkile qeto ea ho tsoela pele ho "tuning" TOAST mme ra hlokomela hore ho qala ka mofuta ona parameter e fumaneha bakeng sa tokiso. toast_tuple_target:

TOAST process code codes e tuka feela ha boleng ba mola o tla bolokoa tafoleng o le kholo ho feta TOAST_TUPLE_THRESHOLD byte (hangata 2 KB). Khoutu ea TOAST e tla hatella le/kapa e tlose boleng ba tšimo ho tsoa tafoleng ho fihlela boleng ba mola bo fetoha ka tlase ho TOAST_TUPLE_TARGET byte (boleng bo feto-fetohang, hangata 2 KB) kapa boholo bo ke ke ba fokotsoa.

Re nkile qeto ea hore data eo hangata re nang le eona e ka ba "e khuts'oane haholo" kapa "e telele haholo", kahoo re nkile qeto ea ho ipehela bonyane boleng bo ka khonehang:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Ha re boneng hore na litlhophiso tse ncha li amme joang ho kenya disk kamora ho hlophisoa bocha:

Ha e mpe! Karolelano mokoloko oa disk o fokotsehile hoo e ka bang ka makhetlo a 1.5, 'me disk e "phathahane" ke karolo ea 20 lekholong! Empa mohlomong sena se amme CPU ka tsela e itseng?

Bonyane ha ea ka ea mpefala le ho feta. Leha ho le joalo, ho thata ho ahlola haeba le meqolo e joalo e ntse e sa khone ho phahamisa mojaro o tloaelehileng oa CPU holimo 5%.

Ka ho fetola libaka tsa mantsoe, kakaretso ... e fetoha!

Joalokaha u tseba, penny e boloka ruble, 'me ka boholo ba rona ba polokelo ke hoo e ka bang 10TB/khoeli esita le ho ntlafatsa hanyenyane ho ka fana ka phaello e ntle. Ka hona, re ile ra ela hloko sebopeho sa 'mele sa data ea rona - hantle hakae masimo a "stacked" ka hare ho rekoto e 'ngoe le e 'ngoe ea litafole.

Hobane ka lebaka la tlhophiso ya data sena se otlolohile pele e ama molumo oa sephetho:

Mehaho e mengata ea meralo e fana ka tatellano ea data ho meeli ea mantsoe a mochini. Ka mohlala, tsamaisong ea 32-bit x86, palo e feletseng (mofuta oa palo, li-byte tse 4) li tla amahanngoa le moeli oa mantsoe a 4-byte, joalo ka ha linomoro tsa lintlha tse phaphametseng li tla habeli (habeli ho nepahala ha floating, 8 byte). 'Me tsamaisong ea 64-bit, litekanyetso tse peli li tla amahanngoa le meeli ea mantsoe a 8-byte. Lena ke lebaka le leng la ho se lumellane.

Ka lebaka la ho tsamaisana, boholo ba mola oa tafole bo itšetlehile ka tatellano ea masimo. Hangata phello ena ha e bonahale haholo, empa maemong a mang e ka lebisa keketseho e kholo ea boholo. Mohlala, ha o kopanya likarolo tsa char(1) le palo e felletseng, hangata ho tla ba le li-byte tse 3 tse senyehileng lipakeng tsa tsona.

Ha re qale ka mehlala ea maiketsetso:

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

Li-byte tse 'maloa tse eketsehileng li tsoa hokae tabeng ea pele? Ho bonolo - 2-byte e nyane e hokahantsoeng moeling oa 4-byte pele ho tšimo e latelang, 'me ha e le ea ho qetela, ha ho letho ebile ha ho hlokahale ho ikamahanya.

Ka khopolo, ntho e 'ngoe le e' ngoe e lokile 'me u ka hlophisa masimo bocha kamoo u ratang. Ha re e hlahlobe ka data ea 'nete re sebelisa mohlala oa e' ngoe ea litafole, karolo ea letsatsi le letsatsi e nang le 10-15GB.

Sebopeho sa pele:

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)

Karolo ka mor'a ho fetola tatellano ea kholomo - hantle masimo a tšoanang, tatellano e fapaneng feela:

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)

Palo eohle ea karolo e khethoa ke palo ea "linnete" mme e itšetlehile feela ka mekhoa ea ka ntle, kahoo a re aroleng boholo ba qubu (pg_relation_size) ka palo ea litlaleho tse ho eona - ke hore, re fumana boholo ba karolelano ea tlaleho ea sebele e bolokiloeng:

Ho tlosa molumo oa 6%., E kholo!

Empa ntho e 'ngoe le e' ngoe, ehlile, ha e monate - ka mor'a tsohle, ka li-index ha re khone ho fetola tatellano ea masimo, ka hona “ka kakaretso” (pg_total_relation_size)…

... ke ntse ke le mona hape e bolokiloe 1.5%ntle le ho fetola mola o le mong oa khoutu. E, ho joalo!

Kea hlokomela hore khetho e kaholimo ea ho hlophisa masimo ha se taba ea hore ke eona e nepahetseng ka ho fetesisa. Hobane ha u batle ho "hahola" libaka tse ling ka mabaka a botle - mohlala, banyalani (pack, recno), e leng PK ea tafole ena.

Ka kakaretso, ho khetha "bonyane" tlhophiso ea masimo ke mosebetsi o bonolo oa "brute force". Ka hona, o ka fumana liphetho tse betere ho tsoa ho data ea hau ho feta ea rona - leka!

Source: www.habr.com