Okoa senti kwa idadi kubwa katika PostgreSQL

Kuendeleza mada ya kurekodi mitiririko mikubwa ya data iliyokuzwa na makala iliyopita kuhusu partitioning, katika hili tutaangalia njia ambazo unaweza kupunguza ukubwa wa "kimwili" wa kuhifadhiwa katika PostgreSQL, na athari zao kwenye utendaji wa seva.

Tutazungumzia Mipangilio ya TOAST na upatanishi wa data. "Kwa wastani," njia hizi hazitahifadhi rasilimali nyingi, lakini bila kurekebisha msimbo wa maombi kabisa.

Hata hivyo, uzoefu wetu uligeuka kuwa na tija sana katika suala hili, kwani uhifadhi wa karibu ufuatiliaji wowote kwa asili yake ni mara nyingi nyongeza-tu kwa mujibu wa data iliyorekodiwa. Na ikiwa unashangaa jinsi unaweza kufundisha hifadhidata kuandika kwa diski badala yake 200MB / s nusu - tafadhali chini ya paka.

Siri ndogo za data kubwa

Kwa wasifu wa kazi huduma zetu, wao mara kwa mara kuruka kwake kutoka lairs vifurushi vya maandishi.

Na tangu VLSI tataambao hifadhidata yake tunayofuatilia ni bidhaa yenye vipengele vingi na miundo changamano ya data, kisha maswali kwa utendaji wa juu kugeuka kabisa kama hii "Juzuu nyingi" yenye mantiki changamano ya algorithmic. Kwa hivyo kiasi cha kila tukio la mtu binafsi la ombi au mpango wa utekelezaji unaotokana katika logi unaokuja kwetu unageuka kuwa "kwa wastani" kubwa kabisa.

Wacha tuangalie muundo wa moja ya jedwali ambalo tunaandika data "mbichi" - ambayo ni, hapa kuna maandishi asilia kutoka kwa ingizo la logi:

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

Ishara ya kawaida (tayari imegawanywa, bila shaka, hivyo hii ni template ya sehemu), ambapo jambo muhimu zaidi ni maandishi. Wakati mwingine voluminous kabisa.

Kumbuka kwamba saizi ya "kimwili" ya rekodi moja katika PG haiwezi kuchukua zaidi ya ukurasa mmoja wa data, lakini saizi ya "mantiki" ni jambo tofauti kabisa. Ili kuandika thamani ya volumetric (varchar/text/bytea) kwenye uwanja, tumia Teknolojia ya TOAST:

PostgreSQL hutumia saizi isiyobadilika ya ukurasa (kwa kawaida KB 8), na hairuhusu nakala kujumuisha kurasa nyingi. Kwa hiyo, haiwezekani kuhifadhi moja kwa moja maadili makubwa sana ya shamba. Ili kuondokana na kizuizi hiki, thamani kubwa za sehemu hubanwa na/au kugawanywa katika mistari mingi halisi. Hili hufanyika bila kutambuliwa na mtumiaji na lina athari kidogo kwenye nambari nyingi za seva. Njia hii inajulikana kama TOAST...

Kwa kweli, kwa kila jedwali iliyo na sehemu "zinazowezekana", kiotomatiki meza ya paired na "slicing" imeundwa kila rekodi "kubwa" katika sehemu za 2KB:

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

Hiyo ni, ikiwa tunapaswa kuandika kamba yenye thamani "kubwa". data, basi rekodi halisi itatokea sio tu kwa meza kuu na PK yake, lakini pia kwa TOAST na PK yake.

Kupunguza ushawishi wa TOAST

Lakini rekodi zetu nyingi bado sio kubwa, inapaswa kutoshea katika 8KB - Ninawezaje kuokoa pesa kwenye hii? ..

Hapa ndipo sifa inakuja kwa msaada wetu STORAGE kwenye safu ya jedwali:

• Iliyoongezwa inaruhusu compression wote na hifadhi tofauti. Hii chaguo la kawaida kwa aina nyingi za data zinazotii TOAST. Inajaribu kwanza kufanya ukandamizaji, kisha huihifadhi nje ya meza ikiwa safu bado ni kubwa sana.
• MAIN inaruhusu compression lakini si hifadhi tofauti. (Kwa kweli, hifadhi tofauti bado itafanywa kwa safu wima kama hizo, lakini tu kama njia ya mwisho, wakati hakuna njia nyingine ya kupunguza kamba ili iweze kutoshea kwenye ukurasa.)

Kwa kweli, hii ndiyo hasa tunayohitaji kwa maandishi - itapunguza iwezekanavyo, na ikiwa haifai kabisa, kuiweka kwenye TOAST. Hii inaweza kufanywa moja kwa moja kwenye kuruka, kwa amri moja:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Jinsi ya kutathmini athari

Kwa kuwa mtiririko wa data hubadilika kila siku, hatuwezi kulinganisha nambari kamili, lakini kwa masharti ya jamaa sehemu ndogo Tuliandika kwa TOAST - bora zaidi. Lakini kuna hatari hapa - kadiri kiwango cha "kimwili" cha kila rekodi kinavyoongezeka, faharisi "pana" inakuwa, kwa sababu lazima tufunike kurasa zaidi za data.

Sehemu kabla ya mabadiliko:

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

Sehemu baada ya mabadiliko:

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

Kwa kweli, sisi alianza kuandika kwa TOAST mara 2 chini ya mara kwa mara, ambayo haikupakua diski tu, bali pia CPU:

Nitagundua kuwa pia tumekuwa ndogo katika "kusoma" diski, sio "kuandika" tu - kwani wakati wa kuingiza rekodi kwenye jedwali, lazima pia "tusome" sehemu ya mti wa kila faharisi ili kuamua nafasi ya baadaye ndani yao.

Nani anaweza kuishi vizuri kwenye PostgreSQL 11

Baada ya kusasisha hadi PG11, tuliamua kuendelea "kurekebisha" TOAST na tukagundua kuwa kuanzia toleo hili kigezo kilipatikana kwa kurekebisha. toast_tuple_target:

Msimbo wa uchakataji wa TOAST huwaka tu wakati thamani ya safu mlalo itakayohifadhiwa kwenye jedwali ni kubwa kuliko baiti TOAST_TUPLE_THRESHOLD (kwa kawaida KB 2). Msimbo wa TOAST utabana na/au kuhamisha thamani za sehemu kutoka kwenye jedwali hadi thamani ya safu mlalo iwe chini ya baiti TOAST_TUPLE_TARGET (thamani inayobadilika, pia kwa kawaida 2 KB) au saizi haiwezi kupunguzwa.

Tuliamua kuwa data tuliyo nayo kwa kawaida ni "fupi sana" au "ndefu sana", kwa hivyo tuliamua kujiwekea kikomo cha thamani ya chini iwezekanavyo:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Wacha tuone jinsi mipangilio mipya iliathiri upakiaji wa diski baada ya kusanidi upya:

Sio mbaya! Wastani foleni kwenye diski imepungua takriban mara 1.5, na disk "busy" ni asilimia 20! Lakini labda hii kwa namna fulani iliathiri CPU?

Angalau haikuwa mbaya zaidi. Ingawa, ni ngumu kuhukumu ikiwa hata idadi kama hiyo bado haiwezi kuinua wastani wa mzigo wa CPU juu 5%.

Kwa kubadilisha maeneo ya masharti, jumla... inabadilika!

Kama unavyojua, senti huokoa ruble, na kwa viwango vyetu vya uhifadhi ni karibu 10TB/mwezi hata optimization kidogo inaweza kutoa faida nzuri. Kwa hiyo, tulizingatia muundo wa kimwili wa data yetu - jinsi gani hasa sehemu "zilizopangwa" ndani ya rekodi kila moja ya meza.

Kwa sababu kwa sababu upatanishi wa data hii ni moja kwa moja huathiri kiasi kinachosababisha:

Usanifu mwingi hutoa upatanishi wa data kwenye mipaka ya maneno ya mashine. Kwa mfano, kwenye mfumo wa 32-bit x86, nambari kamili (aina kamili, baiti 4) zitapangwa kwenye mpaka wa maneno wa baiti 4, kama vile nambari za uhakika zinazoelea zitakavyokuwa maradufu (hatua inayoelea ya usahihi mara mbili, baiti 8). Na kwenye mfumo wa 64-bit, maadili mara mbili yatalinganishwa na mipaka ya maneno ya 8-byte. Hii ni sababu nyingine ya kutokubaliana.

Kwa sababu ya mpangilio, saizi ya safu ya meza inategemea mpangilio wa uwanja. Kawaida athari hii haionekani sana, lakini katika baadhi ya matukio inaweza kusababisha ongezeko kubwa la ukubwa. Kwa mfano, ukichanganya char(1) na sehemu kamili, kwa kawaida kutakuwa na baiti 3 zinazopotea kati yao.

Wacha tuanze na mifano ya syntetisk:

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

Je, baiti kadhaa za ziada zilitoka wapi katika kesi ya kwanza? Ni rahisi - Nukta ndogo ya baiti 2 ikiwa imepangiliwa kwenye mpaka wa baiti 4 kabla ya uwanja unaofuata, na wakati ni wa mwisho, hakuna kitu na hakuna haja ya kupatanisha.

Kwa nadharia, kila kitu ni sawa na unaweza kupanga upya mashamba kama unavyopenda. Hebu tuangalie kwenye data halisi kwa kutumia mfano wa moja ya meza, sehemu ya kila siku ambayo inachukua 10-15GB.

Muundo wa awali:

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)

Sehemu baada ya kubadilisha mpangilio wa safu - haswa nyanja sawa, mpangilio tofauti tu:

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)

Kiasi cha jumla cha sehemu imedhamiriwa na idadi ya "ukweli" na inategemea tu michakato ya nje, kwa hivyo wacha tugawanye saizi ya lundo (pg_relation_size) kwa idadi ya rekodi ndani yake - yaani, tunapata ukubwa wa wastani wa rekodi halisi iliyohifadhiwa:

Toa sauti ya 6%., Kubwa!

Lakini kila kitu, kwa kweli, sio nzuri sana - baada ya yote, katika faharisi hatuwezi kubadilisha mpangilio wa sehemu, na kwa hivyo "kwa ujumla" (pg_total_relation_size) ...

... bado nipo hapa imeokoa 1.5%bila kubadilisha safu moja ya nambari. Ndiyo ndiyo!

Ninagundua kuwa chaguo hapo juu la kupanga shamba sio ukweli kwamba ndio bora zaidi. Kwa sababu hutaki "kurarua" baadhi ya sehemu kwa sababu za urembo - kwa mfano, wanandoa (pack, recno), ambayo ni PK ya jedwali hili.

Kwa ujumla, kuamua mpangilio wa "kiwango cha chini" wa uwanja ni kazi rahisi ya "nguvu kali". Kwa hivyo, unaweza kupata matokeo bora zaidi kutoka kwa data yako kuliko yetu - jaribu!

Chanzo: mapenzi.com