Sove yon jounen travay sou gwo komèsan nan PostgreSQL

Kontinye sijè a nan anrejistreman gwo kouran done leve soti vivan pa atik anvan sou patisyon, nan sa a nou pral gade nan fason ou kapab redwi gwosè "fizik" nan ki estoke nan PostgreSQL, ak enpak yo sou pèfòmans sèvè.

Nou pral pale de Anviwònman TOAST ak aliyman done. "An mwayèn," metòd sa yo pa pral sove twòp resous, men san yo pa modifye kòd aplikasyon an ditou.

Sepandan, eksperyans nou an te tounen trè pwodiktif nan sans sa a, depi depo a nan prèske nenpòt siveyans pa nati li yo se sitou ajoute-sèlman an tèm de done anrejistre. Men, si w ap mande ki jan ou ka anseye baz done a ekri sou disk pito 200MB / s mwatye - tanpri anba chat.

Ti sekrè nan gwo done

Pa pwofil travay sèvis nou an, yo regilyèman vole l 'soti nan tanwar yo pakè tèks.

E depi VLSI konplèkski gen baz done nou kontwole se yon pwodwi milti-konpozan ak estrikti done konplèks, Lè sa a, demann pou pèfòmans maksimòm vire byen tankou sa a "milti-volim" ak lojik algoritmik konplèks. Se konsa, volim nan chak egzanp endividyèl nan yon demann oswa plan an ekzekisyon ki kapab lakòz nan boutèy la ki vin jwenn nou vire soti yo dwe "an mwayèn" byen gwo.

Ann gade nan estrikti a nan youn nan tab yo nan ki nou ekri done "kri" - se sa ki, isit la se tèks orijinal la soti nan antre nan boutèy demi lit:

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

Yon siy tipik (deja seksyon, nan kou, kidonk sa a se yon modèl seksyon), kote bagay ki pi enpòtan an se tèks la. Pafwa byen volumineuz.

Sonje byen, gwosè "fizik" yon dosye nan yon PG pa ka okipe plis pase yon paj done, men gwosè "lojik" la se yon bagay konplètman diferan. Pou ekri yon valè volumetrik (varchar/text/bytea) nan yon jaden, sèvi ak TOAST teknoloji:

PostgreSQL sèvi ak yon gwosè paj fiks (tipikman 8 KB), epi li pa pèmèt tuple yo span plizyè paj. Se poutèt sa, li enposib dirèkteman magazen valè jaden gwo anpil. Pou simonte limit sa a, gwo valè jaden yo konprese ak/oswa divize sou plizyè liy fizik. Sa a rive inapèsi pa itilizatè a epi li gen ti enpak sou pifò kòd sèvè. Metòd sa a rele TOAST...

An reyalite, pou chak tab ki gen "potansyèlman gwo" jaden, otomatikman se yon tab pè ak "tranche" kreye chak dosye "gwo" nan segman 2KB:

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

Sa vle di, si nou dwe ekri yon fisèl ki gen yon valè "gwo". data, Lè sa a, anrejistreman reyèl la pral rive pa sèlman nan tab prensipal la ak PK li yo, men tou, TOAST ak PK li yo.

Diminye enfliyans TOAST

Men, pifò nan dosye nou yo toujou pa tèlman gwo, ta dwe anfòm nan 8KB - Kouman mwen ka ekonomize lajan sou sa a?...

Sa a se kote atribi a vini nan èd nou STORAGE nan kolòn tab la:

• PWOLONJE pèmèt tou de konpresyon ak depo separe. Sa a opsyon estanda pou pifò kalite done ki konfòm TOAST. Li premye eseye fè konpresyon, Lè sa a, magazen li deyò tab la si ranje a toujou twò gwo.
• MAIN pèmèt konpresyon men pa separe depo. (An reyalite, depo separe ap toujou fèt pou kolòn sa yo, men sèlman kòm yon dènye rekou, lè pa gen okenn lòt fason pou retresi fisèl la pou li anfòm sou paj la.)

An reyalite, sa a se egzakteman sa nou bezwen pou tèks la - konprese li otank posib, epi si li pa anfòm ditou, mete l nan TOAST. Sa a ka fè dirèkteman sou vole a, ak yon sèl lòd:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Ki jan yo evalye efè a

Depi koule done a chanje chak jou, nou pa ka konpare nimewo absoli, men an tèm relatif pi piti pataje Nou te ekri li nan TOAST - tèlman pi bon. Men, gen yon danje isit la - pi gwo volim "fizik" nan chak dosye endividyèl, se "pi laj" endèks la vin, paske nou dwe kouvri plis paj nan done.

Seksyon anvan chanjman:

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

Seksyon apre chanjman:

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

An reyalite, nou te kòmanse ekri TOAST 2 fwa mwens souvan, ki te dechaje pa sèlman disk la, men tou CPU a:

Mwen pral remake ke nou te vin pi piti tou nan "li" disk la, pa sèlman "ekri" - depi lè w mete yon dosye nan yon tab, nou gen tou "li" yon pati nan pye bwa a nan chak endèks yo nan lòd yo detèmine li. pozisyon nan lavni nan yo.

Ki moun ki ka viv byen sou PostgreSQL 11

Apre mete ajou nan PG11, nou deside kontinye "akor" TOAST ak remake ke apati vèsyon sa a paramèt la te vin disponib pou akor. toast_tuple_target:

Kòd pwosesis TOAST la sèlman lè valè ranje yo dwe estoke nan tablo a pi gwo pase TOAST_TUPLE_THRESHOLD bytes (anjeneral 2 KB). Kòd TOAST la pral konprese ak/oswa deplase valè jaden yo soti nan tab la jiskaske valè ranje a vin mwens pase TOAST_TUPLE_TARGET bytes (valè varyab, tou anjeneral 2 KB) oswa gwosè a pa ka redwi.

Nou deside ke done nou anjeneral genyen yo swa "trè kout" oswa "trè long", kidonk nou deside limite tèt nou nan valè minimòm posib:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Ann wè ki jan nouvo paramèt yo afekte chajman disk apre rekonfigirasyon:

Pa move! Mwayèn keu la nan disk la te diminye apeprè 1.5 fwa, ak disk la "okipe" se 20 pousan! Men, petèt sa a yon jan kanmenm afekte CPU a?

Omwen li pa t vin pi mal. Malgre ke, li difisil pou jije si menm volim sa yo toujou pa ka ogmante chaj CPU mwayèn pi wo 5%.

Lè w chanje kote tèm yo, sòm nan... chanje!

Kòm ou konnen, yon pyès lajan sove yon Ruble, ak komèsan depo nou an li sou 10TB / mwa menm yon ti optimize ka bay yon bon pwofi. Se poutèt sa, nou te peye atansyon sou estrikti fizik done nou an - ki jan egzakteman "anpile" jaden andedan dosye a chak tab yo.

Paske akoz de aliyman done sa a se dwat devan afekte volim ki kapab lakòz:

Anpil achitekti bay aliyman done sou limit mo machin yo. Pou egzanp, sou yon sistèm x32 86-bit, nonm antye yo (kalite nonb antye relatif, 4 byte) yo pral aliyen sou yon fwontyè mo 4-byte, menm jan yo pral doub nimewo presizyon k ap flote (doub presizyon pwen k ap flote, 8 byte). Ak sou yon sistèm 64-bit, valè doub yo pral aliyen ak limit mo 8-byte. Sa a se yon lòt rezon pou enkonpatibilite.

Akòz aliyman, gwosè yon ranje tab depann de lòd jaden yo. Anjeneral efè sa a pa trè aparan, men nan kèk ka li ka mennen nan yon ogmantasyon siyifikatif nan gwosè. Pou egzanp, si ou melanje char (1) ak jaden nonb antye relatif, pral tipikman gen 3 bytes gaspiye ant yo.

Ann kòmanse ak modèl sentetik:

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

Ki kote yon koup de bytes siplemantè soti nan premye ka a? Li senp - Smallint 2-byte ki aliyen sou fwontyè 4-byte anvan pwochen jaden an, epi lè li se youn nan dènye, pa gen anyen epi pa gen okenn bezwen aliman.

Nan teyori, tout bagay anfòm epi ou ka ordonne jaden yo jan ou renmen. Ann tcheke li sou done reyèl lè l sèvi avèk egzanp youn nan tab yo, seksyon an chak jou nan ki okipe 10-15GB.

Estrikti inisyal:

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)

Seksyon apre chanje lòd kolòn - egzakteman menm jaden, jis diferan lòd:

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)

Volim total seksyon an detèmine pa kantite "reyalite" epi li depann sèlman de pwosesis ekstèn, kidonk ann divize gwosè pil la (pg_relation_size) pa kantite dosye ki ladan l - sa vle di, nou jwenn gwosè mwayèn nan dosye aktyèl ki estoke:

Moins 6% volim, Gran!

Men, tout bagay, nan kou, se pa tèlman woz - apre tout, nan endèks nou pa ka chanje lòd jaden yo, ak Se poutèt sa "an jeneral" (pg_total_relation_size) ...

... toujou isit la tou sove 1.5%san yo pa chanje yon sèl liy nan kòd. Wi, wi!

Mwen sonje ke opsyon ki pi wo a pou fè aranjman pou jaden se pa lefèt ke li pi pi bon an. Paske ou pa vle "chire" kèk blòk nan jaden pou rezon ayestetik - pou egzanp, yon koup. (pack, recno), ki se PK pou tab sa a.

An jeneral, detèmine "minimòm" aranjman nan jaden se yon travay "fòs brital" jistis senp. Se poutèt sa, ou ka jwenn menm pi bon rezilta nan done ou pase pa nou - eseye li!

Sous: www.habr.com