Arbedwch geiniog ar gyfeintiau mawr yn PostgreSQL

Parhau â'r pwnc o gofnodi ffrydiau data mawr a godwyd gan erthygl flaenorol am rannu, yn hyn byddwn yn edrych ar y ffyrdd y gallwch chi lleihau maint “corfforol” y storfa yn PostgreSQL, a'u heffaith ar berfformiad gweinyddwyr.

Byddwn yn siarad am Gosodiadau TOAST ac aliniad data. “Ar gyfartaledd,” ni fydd y dulliau hyn yn arbed gormod o adnoddau, ond heb addasu cod y cais o gwbl.

Fodd bynnag, trodd ein profiad yn gynhyrchiol iawn yn hyn o beth, gan fod storio bron unrhyw fonitro yn ôl ei natur atodiad yn unig yn bennaf o ran data a gofnodwyd. Ac os ydych chi'n pendroni sut y gallwch chi ddysgu'r gronfa ddata i ysgrifennu i ddisg yn lle hynny 200MB / s hanner cymaint - os gwelwch yn dda dan cath.

Cyfrinachau bach o ddata mawr

Yn ôl proffil swydd ein gwasanaeth, maent yn ehedeg ato yn rheolaidd o'r llaciau pecynnau testun.

Ac ers hynny VLSI cymhlethy mae eu cronfa ddata rydym yn ei monitro yn gynnyrch aml-gydran gyda strwythurau data cymhleth, yna ymholiadau ar gyfer y perfformiad mwyaf posibl troi allan yn eithaf fel hyn “aml-gyfrol” gyda rhesymeg algorithmig cymhleth. Felly mae cyfaint pob achos unigol o gais neu'r cynllun gweithredu dilynol yn y log a ddaw atom yn troi allan i fod yn eithaf mawr “ar gyfartaledd”.

Edrychwn ar strwythur un o'r tablau yr ydym yn ysgrifennu data "crai" iddynt - hynny yw, dyma'r testun gwreiddiol o'r cofnod log:

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

Arwydd nodweddiadol (eisoes mewn adrannau, wrth gwrs, felly templed adran yw hwn), lle mai'r peth pwysicaf yw'r testun. Weithiau yn eithaf swmpus.

Dwyn i gof na all maint “corfforol” un cofnod mewn PG feddiannu mwy nag un dudalen o ddata, ond mae maint “rhesymegol” yn fater hollol wahanol. I ysgrifennu gwerth cyfeintiol (varchar/testun/byte) i faes, defnyddiwch Technoleg TOAST:

Mae PostgreSQL yn defnyddio maint tudalen sefydlog (8 KB fel arfer), ac nid yw'n caniatáu i tuples rychwantu tudalennau lluosog. Felly, mae'n amhosibl storio gwerthoedd maes mawr iawn yn uniongyrchol. Er mwyn goresgyn y cyfyngiad hwn, mae gwerthoedd maes mawr yn cael eu cywasgu a/neu eu rhannu ar draws llinellau ffisegol lluosog. Mae hyn yn digwydd heb i'r defnyddiwr sylwi arno ac nid yw'n cael fawr o effaith ar y rhan fwyaf o god gweinyddwyr. Gelwir y dull hwn yn TOAST ...

Mewn gwirionedd, ar gyfer pob bwrdd gyda chaeau "a allai fod yn fawr", yn awtomatig mae bwrdd pâr gyda “sleisio” yn cael ei greu pob cofnod “mawr” mewn segmentau 2KB:

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

Hynny yw, os oes rhaid i ni ysgrifennu llinyn gyda gwerth “mawr”. data, yna bydd y recordiad go iawn yn digwydd nid yn unig i'r prif fwrdd a'i PK, ond hefyd i TOAST a'i PK.

Lleihau dylanwad TOAST

Ond nid yw'r rhan fwyaf o'n cofnodion mor fawr â hynny o hyd, Dylai ffitio i mewn i 8KB - Sut alla i arbed arian ar hyn? ..

Dyma lle mae'r nodwedd yn dod i'n cymorth STORAGE yng ngholofn y bwrdd:

• ESTYNEDIG yn caniatáu cywasgu a storio ar wahân. hwn opsiwn safonol ar gyfer y rhan fwyaf o fathau o ddata sy'n cydymffurfio â TOAST. Yn gyntaf mae'n ceisio perfformio cywasgu, yna'n ei storio y tu allan i'r bwrdd os yw'r rhes yn dal yn rhy fawr.
• PRIF yn caniatáu cywasgu ond nid storio ar wahân. (Mewn gwirionedd, bydd storio ar wahân yn dal i gael ei berfformio ar gyfer colofnau o'r fath, ond dim ond fel dewis olaf, pan nad oes unrhyw ffordd arall i grebachu'r llinyn fel ei fod yn ffitio ar y dudalen.)

Mewn gwirionedd, dyma'n union sydd ei angen arnom ar gyfer y testun - cywasgu cymaint â phosibl, ac os nad yw'n ffitio o gwbl, rhowch ef yn TOAST. Gellir gwneud hyn yn uniongyrchol ar y hedfan, gydag un gorchymyn:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Sut i werthuso'r effaith

Gan fod llif y data yn newid bob dydd, ni allwn gymharu niferoedd absoliwt, ond mewn termau cymharol cyfran lai Fe wnaethon ni ei ysgrifennu i lawr yn TOAST - gorau oll. Ond mae perygl yma – po fwyaf yw cyfaint “corfforol” pob cofnod unigol, yr “ehangaf” y daw’r mynegai, oherwydd mae’n rhaid i ni orchuddio mwy o dudalennau o ddata.

Adran cyn newidiadau:

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

Adran ar ôl newidiadau:

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

Yn wir, ni dechrau ysgrifennu at TOAST 2 gwaith yn llai aml, a ddadlwythodd nid yn unig y ddisg, ond hefyd y CPU:

Nodaf ein bod hefyd wedi mynd yn llai o ran “darllen” y ddisg, nid yn unig “ysgrifennu” - oherwydd wrth fewnosod cofnod mewn tabl, mae'n rhaid i ni hefyd “ddarllen” rhan o goeden pob mynegai er mwyn pennu ei sefyllfa yn y dyfodol ynddynt.

Pwy all fyw yn dda ar PostgreSQL 11

Ar ôl diweddaru i PG11, fe wnaethom benderfynu parhau i “diwnio” TOAST a sylwi bod y paramedr yn dechrau o'r fersiwn hwn toast_tuple_target:

Mae'r cod prosesu TOAST ond yn tanio pan fydd gwerth y rhes i'w storio yn y tabl yn fwy na TOAST_TUPLE_THRESHOLD beit (2 KB fel arfer). Bydd y cod TOAST yn cywasgu a/neu'n symud gwerthoedd maes allan o'r tabl nes bod gwerth y rhes yn dod yn llai na TOAST_TUPLE_TARGET beit (gwerth amrywiol, hefyd fel arfer 2 KB) neu ni ellir lleihau'r maint.

Fe wnaethom benderfynu bod y data sydd gennym fel arfer naill ai’n “fyr iawn” neu’n “hir iawn”, felly fe benderfynon ni gyfyngu ein hunain i’r gwerth lleiaf posibl:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Gadewch i ni weld sut yr effeithiodd y gosodiadau newydd ar lwytho disg ar ôl ad-drefnu:

Ddim yn ddrwg! Cyfartaledd mae'r ciw i'r ddisg wedi lleihau tua 1.5 gwaith, ac mae'r ddisg “prysur” yn 20 y cant! Ond efallai bod hyn rywsut wedi effeithio ar y CPU?

O leiaf nid oedd yn gwaethygu o gwbl. Er, mae'n anodd barnu a yw hyd yn oed cyfeintiau o'r fath yn dal i fethu codi'r llwyth CPU cyfartalog yn uwch 5%.

Trwy newid lleoedd y termau, mae'r swm... yn newid!

Fel y gwyddoch, mae ceiniog yn arbed rwbl, a chyda'n cyfeintiau storio mae'n ymwneud 10TB y mis gall hyd yn oed ychydig o optimeiddio roi elw da. Felly, fe wnaethom dalu sylw i strwythur ffisegol ein data - sut yn union meysydd “pentyrru” y tu mewn i'r cofnod pob un o'r byrddau.

Oherwydd oherwydd aliniad data mae hyn yn syml yn effeithio ar y cyfaint canlyniadol:

Mae llawer o saernïaeth yn darparu aliniad data ar ffiniau geiriau peiriant. Er enghraifft, ar system 32-did x86, bydd cyfanrifau (math cyfanrif, 4 beit) yn cael eu halinio ar ffin gair 4-beit, yn ogystal â rhifau pwynt arnawf trachywiredd dwbl (pwynt arnawf trachywiredd dwbl, 8 beit). Ac ar system 64-bit, bydd gwerthoedd dwbl yn cyd-fynd â ffiniau geiriau 8-beit. Dyma reswm arall dros anghydnawsedd.

Oherwydd aliniad, mae maint rhes tabl yn dibynnu ar drefn y caeau. Fel arfer nid yw'r effaith hon yn amlwg iawn, ond mewn rhai achosion gall arwain at gynnydd sylweddol mewn maint. Er enghraifft, os byddwch yn cymysgu torgoch(1) a chaeau cyfanrif, fel arfer bydd 3 beit yn cael eu gwastraffu rhyngddynt.

Gadewch i ni ddechrau gyda modelau synthetig:

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

O ble daeth cwpl o beit ychwanegol yn yr achos cyntaf? Mae'n syml - Smallint 2-beit wedi'i alinio ar ffin 4-beit cyn y cae nesaf, a phan fydd yn un olaf, nid oes dim ac nid oes angen alinio.

Mewn egwyddor, mae popeth yn iawn a gallwch aildrefnu'r caeau fel y dymunwch. Gadewch i ni ei wirio ar ddata go iawn gan ddefnyddio enghraifft un o'r tablau, y mae ei adran ddyddiol yn llenwi 10-15GB.

Strwythur cychwynnol:

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)

Adran ar ôl newid trefn y golofn - yn union yr un meysydd, dim ond trefn wahanol:

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)

Mae cyfanswm cyfaint yr adran yn cael ei bennu gan nifer y “ffeithiau” ac mae'n dibynnu ar brosesau allanol yn unig, felly gadewch i ni rannu maint y domen (pg_relation_size) wrth nifer y cofnodion sydd ynddo — hyny yw, cawn maint cyfartalog y cofnod storio gwirioneddol:

Llai cyfaint 6%., Gwych!

Ond nid yw popeth, wrth gwrs, mor rosy - wedi'r cyfan, mewn mynegeion ni allwn newid trefn y meysydd, ac felly “yn gyffredinol” (pg_total_relation_size) ...

...dal yma hefyd arbed 1.5%heb newid un llinell o god. Ie, ie!

Sylwaf nad yr opsiwn uchod ar gyfer trefnu meysydd yw'r ffaith mai dyma'r un mwyaf optimaidd. Oherwydd nad ydych chi eisiau “rhwygo” rhai blociau o gaeau am resymau esthetig - er enghraifft, cwpl (pack, recno), sef y PK ar gyfer y tabl hwn.

Yn gyffredinol, mae pennu trefniant “lleiafswm” meysydd yn dasg “grym creulon” eithaf syml. Felly, gallwch gael canlyniadau gwell fyth o'ch data na'n data ni - rhowch gynnig arni!

Ffynhonnell: hab.com