E mālama i kahi peni ma nā puke nui ma PostgreSQL

Ke hoʻomau nei i ke kumuhana o ka hoʻopaʻa ʻana i nā kahawai ʻikepili nui i hāpai ʻia e ʻatikala mua e pili ana i ka ʻāpana, ma keia e nana kakou i na ala e hiki ai ia oe e ho'ēmi i ka nui "kino" o ka mea mālama i PostgreSQL, a me kā lākou hopena i ka hana o ka server.

E kamaʻilio mākou e pili ana ʻO nā hoʻonohonoho TOAST a me ka hoʻoponopono ʻikepili. "Ma ka awelika," ʻaʻole e mālama kēia mau ʻano i nā kumuwaiwai he nui, akā me ka ʻole o ka hoʻololi ʻana i ke code noi.

Eia nō naʻe, ua lilo kā mākou ʻike i mea waiwai loa i kēia ʻano, no ka mea, ʻo ka mālama ʻana o kahi kokoke i nā nānā ʻana ma kona ʻano ka nui o ka hoopili-wale e pili ana i ka ʻikepili i hoʻopaʻa ʻia. A inā ʻoe e noʻonoʻo pehea e hiki ai iā ʻoe ke aʻo i ka waihona e kākau i kahi disk 200MB / s hapalua ka nui - e ʻoluʻolu ma lalo o ka pōpoki.

Nā mea huna liʻiliʻi o ka ʻikepili nui

Ma ka moʻolelo hana kā mākou lawelawe, lele mau lakou ia ia mai na lua pūʻolo kikokikona.

A mai ia manawa VLSI paʻakikīnona ka waihona a mākou e nānā nei he huahana multi-component me nā hoʻonohonoho ʻikepili paʻakikī, a laila nīnau no ka hana nui loa e like me keia "nui-nui" me ka loina algorithmic paʻakikī. No laila, ʻo ka nui o kēlā me kēia hihia o kahi noi a i ʻole ka hopena o ka hoʻolālā hoʻokō i loko o ka log e hiki mai ana iā mākou e lilo i "ma awelika" nui loa.

E nānā kākou i ke ʻano o kekahi o nā papa a mākou e kākau ai i ka ʻikepili "raw" - ʻo ia hoʻi, eia ka kikokikona kumu mai ka log log:

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

ʻO kahi hōʻailona maʻamau (ua pau i ka ʻāpana, ʻoiaʻiʻo, no laila he ʻāpana ʻāpana kēia), kahi o ka mea nui ka kikokikona. I kekahi manawa he voluminous.

E hoʻomanaʻo ʻaʻole hiki i ka nui "kino" o hoʻokahi moʻolelo i loko o kahi PG ke noho ʻoi aku ma mua o hoʻokahi ʻaoʻao o ka ʻikepili, akā ʻo ka nui "logical" he mea ʻokoʻa loa. No ke kākau ʻana i kahi waiwai volumetric (varchar/text/bytea) i kahi kahua, e hoʻohana ʻenehana TOAST:

Hoʻohana ʻo PostgreSQL i ka nui o ka ʻaoʻao paʻa (maʻamau 8 KB), ʻaʻole ia e ʻae i nā tuple e hoʻonui i nā ʻaoʻao he nui. No laila, ʻaʻole hiki ke mālama pono i nā waiwai kahua nui loa. No ka lanakila ʻana i kēia palena, hoʻopili ʻia nā koina kahua nui a / a i ʻole hoʻokaʻawale i nā laina kino. Hana ʻia kēia me ka ʻike ʻole ʻia e ka mea hoʻohana a he liʻiliʻi ka hopena i ka hapa nui o nā code server. Ua kapa ʻia kēia ʻano hana ʻo TOAST...

ʻO ka ʻoiaʻiʻo, no kēlā me kēia pākaukau me nā māla "hiki i ka nui", maʻalahi hana ʻia kahi papa ʻaina me ka "slicing". kēlā me kēia moʻolelo "nui" ma nā ʻāpana 2KB:

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

ʻO ia, inā pono mākou e kākau i kahi kaula me ka waiwai "nui". data, a laila e hana ʻia ka hoʻopaʻa ʻana maoli ʻaʻole wale i ka papaʻaina nui a me kāna PK, akā i TOAST a me kāna PK.

E hoemi ana i ka mana TOAST

Akā ʻaʻole nui ka hapa nui o kā mākou mau moʻolelo, pono e komo i 8KB - Pehea e hiki ai iaʻu ke mālama i ke kālā ma kēia?..

ʻO kēia kahi e hiki mai ai ke ʻano e kōkua iā mākou STORAGE ma ke kolamu papa:

• LāʻI ʻLANA ʻae i ka hoʻopaʻa ʻana a me ka waiho ʻokoʻa. ʻO kēia koho maʻamau no ka hapa nui o nā ʻano ʻikepili hoʻokō TOAST. Ho'āʻo mua ʻo ia e hana i ka hoʻoemi, a laila mālama ʻia ma waho o ka pākaukau inā ʻoi aku ka nui o ka lālani.
• MAINI ʻae i ka hoʻopaʻa ʻana akā ʻaʻole mālama kaʻawale. (ʻOiaʻiʻo, e hoʻokō ʻia ka mālama ʻokoʻa no kēlā mau kolamu, akā wale nō ma ke ano he hana hope loa, inā ʻaʻohe ala ʻē aʻe e hōʻemi ai i ke kaula i kūpono i ka ʻaoʻao.)

ʻOiaʻiʻo, ʻo kēia ka mea e pono ai mākou no ka kikokikona - e hoʻopili i ka mea hiki, a inā ʻaʻole kūpono, e hoʻokomo i loko o TOAST. Hiki ke hana pololei i ka lele, me hoʻokahi kauoha:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Pehea e loiloi ai i ka hopena

Ma muli o ka loli ʻana o ka ʻikepili i kēlā me kēia lā, ʻaʻole hiki iā mākou ke hoʻohālikelike i nā helu piha, akā ma nā huaʻōlelo pili mahele liilii Ua kākau mākou ma TOAST - ʻoi aku ka maikaʻi. Akā aia kekahi pilikia ma ʻaneʻi - ʻoi aku ka nui o ka leo "kino" o kēlā me kēia moʻolelo, ʻoi aku ka "ākea" o ka papa kuhikuhi, no ka mea pono mākou e uhi i nā ʻaoʻao o ka ʻikepili.

Paukū ma mua o nā hoʻololi:

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

Paukū ma hope o nā loli:

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

ʻOiaʻiʻo, mākou ua hoʻomaka e kākau iā TOAST 2 manawa i emi pinepine, ka mea i wehe ʻole i ka disk wale nō, akā ʻo ka CPU pū kekahi:

E hoʻomaopopo wau ua liʻiliʻi mākou i ka "heluhelu" i ka disk, ʻaʻole wale ka "kākau" - no ka mea i ka wā e hoʻokomo ai i kahi moʻolelo i loko o ka papaʻaina, pono mākou e "heluhelu" i kahi ʻāpana o ka lāʻau o kēlā me kēia index i mea e hoʻoholo ai i kāna. kūlana e hiki mai ana i loko o lākou.

ʻO wai ka mea hiki ke ola maikaʻi ma PostgreSQL 11

Ma hope o ka hoʻonui ʻana iā PG11, ua hoʻoholo mākou e hoʻomau i ka "tuning" TOAST a ʻike i ka hoʻomaka ʻana mai kēia mana ua loaʻa ka ʻāpana no ke kani ʻana. toast_tuple_target:

Ke ahi wale nei ka code processing TOAST inā ʻoi aku ka nui o ka waiwai o ka lālani e mālama ʻia ma ka pākaukau ma mua o TOAST_TUPLE_THRESHOLD bytes (maʻa mau 2 KB). Na ke code TOAST e kaomi a/a i ʻole e hoʻoneʻe i nā koina kahua mai ka papaʻaina a hiki i ka emi ʻana o ka waiwai o ka lālani ma mua o TOAST_TUPLE_TARGET bytes (waiwai hoʻololi, ʻo ia hoʻi he 2 KB) a ʻaʻole hiki ke hoʻemi ʻia ka nui.

Ua hoʻoholo mākou he "pōkole loa" a i ʻole "lōʻihi loa" ka ʻikepili i loaʻa iā mākou, no laila ua hoʻoholo mākou e kaupalena iā mākou iho i ka waiwai liʻiliʻi loa:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

E ʻike kākou pehea i pili ai nā ʻōnaehana hou i ka hoʻouka ʻana o ka disk ma hope o ka hoʻonohonoho hou ʻana:

ʻaʻole maikaʻi ʻole! Awelika ua emi ka pila i ka diski ma kahi o 1.5 manawa, a ʻo ka diski "ʻoihana" he 20 pakeneka! Akā ua pili paha kēia i ka CPU?

ʻAʻole i ʻoi aku ka ʻino. ʻOiai, paʻakikī ke hoʻoholo inā ʻaʻole hiki i kēlā mau puke ke hoʻokiʻekiʻe i ka awelika CPU 5%.

Ma ka hoʻololiʻana i nā wahi o nā hua'ōlelo, ka huina ... hoʻololi!

E like me kāu e ʻike ai, mālama ka peni i kahi ruble, a me kā mākou waihona waihona e pili ana ia 10TB/mahina hiki i ka optimization liʻiliʻi ke hāʻawi i ka waiwai maikaʻi. No laila, ua nānā mākou i ke ʻano kino o kā mākou ʻikepili - pehea ka pololei "stacked" kahua i loko o ka mooolelo kēlā me kēia papa.

No ka mea, no ka alignment data pololei kēia i mua pili i ka leo hopena:

Hāʻawi nā hale hana he nui i ka alignment ʻikepili ma nā palena huaʻōlelo mīkini. No ka laʻana, ma kahi ʻōnaehana 32-bit x86, e hoʻopili ʻia nā integers (integer type, 4 bytes) ma kahi palena huaʻōlelo 4-byte, e like me ka pālua ʻana i nā helu lana lana pololei (lua ka pololei kiko lana, 8 bytes). A ma kahi ʻōnaehana 64-bit, e hoʻopili ʻia nā waiwai pālua i nā palena huaʻōlelo 8-byte. ʻO kēia kekahi kumu no ka launa ʻole.

Ma muli o ka alignment, pili ka nui o ka lālani papaʻaina i ke ʻano o nā māla. ʻO ka maʻamau, ʻaʻole ʻike nui ʻia kēia hopena, akā i kekahi mau mea hiki ke alakaʻi i ka piʻi nui o ka nui. No ka laʻana, inā hoʻohui ʻoe i ka char(1) a me ka helu integer, ʻoi aku ka maʻamau o 3 bytes i hoʻopau ʻia ma waena o lākou.

E hoʻomaka kākou me nā hiʻohiʻona synthetic:

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

Ma hea i loaʻa mai ai kekahi mau bytes hou i ka hihia mua? He maʻalahi - 2-byte liʻiliʻi liʻiliʻi i kau ʻia ma ka palena 4-byte ma mua o ke kahua aʻe, a ʻo ia ka mea hope loa, ʻaʻohe mea a ʻaʻohe pono e align.

Ma ke kumumanaʻo, maikaʻi nā mea a pau a hiki iā ʻoe ke hoʻonohonoho hou i nā māla e like me kou makemake. E nānā kākou ma ka ʻikepili maoli me ka hoʻohana ʻana i ka laʻana o kekahi o nā papa, ʻo ka ʻāpana o kēlā me kēia lā e noho ana i 10-15GB.

Hoʻokumu mua:

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)

ʻO ka ʻāpana ma hope o ka hoʻololi ʻana i ke kauoha kolamu - pololei nā kahua like, ʻokoʻa ka hoʻonohonoho:

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)

Hoʻoholo ʻia ka nui o ka ʻāpana e ka helu o nā "ʻoiaʻiʻo" a hilinaʻi wale i nā kaʻina o waho, no laila e puʻunaue kākou i ka nui o ka puʻu (pg_relation_size) ma ka helu o nā moʻolelo i loko - ʻo ia hoʻi, loaʻa iā mākou ka nui awelika o ka moʻolelo i mālama ʻia:

Hoʻemi 6% ka nui, Nui!

Akā ʻo nā mea a pau, ʻoiaʻiʻo, ʻaʻole like ka rosy - ma hope o nā mea a pau, i nā index ʻaʻole hiki iā mākou ke hoʻololi i ke ʻano o nā kahua, a no laila "ma ka laulā" (pg_total_relation_size) ...

... ma ʻaneʻi nō hoʻi mālama ʻia 1.5%me ka hoʻololi ʻole i kahi laina code. Ae 'ae!

ʻIke wau ʻo ka koho i luna no ka hoʻonohonoho ʻana i nā mahina ʻaʻole ʻo ia ka mea maikaʻi loa. No ka mea ʻaʻole ʻoe makemake e "haehae" i kekahi mau poloka o nā māla no nā kumu nani - no ka laʻana, he kāne (pack, recno), ʻo ia ka PK no kēia pākaukau.

Ma keʻano holoʻokoʻa, ʻo ka hoʻoholo ʻana i ka hoʻonohonoho ʻana o nā māla he hana "brute force" maʻalahi. No laila, hiki iā ʻoe ke loaʻa nā hopena maikaʻi aʻe mai kāu ʻikepili ma mua o kā mākou - e hoʻāʻo!

Source: www.habr.com