Iffranka penny fuq volumi kbar f'PostgreSQL

It-tkomplija tas-suġġett tar-reġistrazzjoni ta 'flussi ta' data kbar imqajma minn artikolu preċedenti dwar il-qsim, f'dan se nħarsu lejn il-modi li bihom tista ' tnaqqas id-daqs "fiżiku" tal-maħżuna f'PostgreSQL, u l-impatt tagħhom fuq il-prestazzjoni tas-server.

Aħna ser nitkellmu dwar Settings TOAST u allinjament tad-dejta. "Bħala medja," dawn il-metodi mhux se jiffrankaw wisq riżorsi, iżda mingħajr ma jimmodifikaw il-kodiċi tal-applikazzjoni xejn.

Madankollu, l-esperjenza tagħna rriżulta li kienet produttiva ħafna f'dan ir-rigward, peress li l-ħażna ta 'kważi kull monitoraġġ min-natura tagħha hija l-aktar append-only f'termini ta' data rreġistrata. U jekk qed tistaqsi kif tista 'tgħallem id-database biex tikteb fuq disk minflok 200MB / s nofs kemm - jekk jogħġbok taħt qattus.

Sigrieti żgħar tal-big data

Skont il-profil tax-xogħol is-servizz tagħna, huma regolarment itiru lejh mill-lairs pakketti ta' test.

U minn dakinhar kumpless VLSIli l-bażi tad-data li nissorveljaw hija prodott b'ħafna komponenti bi strutturi ta 'dejta kumplessi, imbagħad mistoqsijiet għall-prestazzjoni massima tirriżulta pjuttost bħal dan "multi-volum" b'loġika algoritmika kumplessa. Allura l-volum ta 'kull istanza individwali ta' talba jew il-pjan ta 'eżekuzzjoni li jirriżulta fil-ġurnal li jiġi għandna jirriżulta li huwa "bħala medja" pjuttost kbir.

Ejja nħarsu lejn l-istruttura ta 'waħda mit-tabelli li fiha niktbu dejta "mhux maħduma" - jiġifieri, hawnhekk huwa t-test oriġinali mid-dħul tal-ġurnal:

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

Sinjal tipiku (diġà maqsum, ovvjament, għalhekk dan huwa mudell ta 'sezzjoni), fejn l-iktar ħaġa importanti hija t-test. Kultant pjuttost voluminuż.

Ifakkar li d-daqs "fiżiku" ta 'rekord wieħed f'PG ma jistax jokkupa aktar minn paġna waħda ta' dejta, iżda d-daqs "loġiku" huwa kwistjoni kompletament differenti. Biex tikteb valur volumetriku (varchar/test/bytea) għal qasam, uża Teknoloġija TOAST:

PostgreSQL juża daqs tal-paġna fiss (tipikament 8 KB), u ma jippermettix li tuples jifirxu fuq bosta paġni. Għalhekk, huwa impossibbli li taħżen direttament valuri tal-kamp kbar ħafna. Biex tingħeleb din il-limitazzjoni, valuri kbar tal-kamp huma kkompressati u/jew maqsuma fuq linji fiżiċi multipli. Dan jiġri mingħajr ma jiġi osservat mill-utent u ftit għandu impatt fuq il-biċċa l-kbira tal-kodiċi tas-server. Dan il-metodu huwa magħruf bħala TOAST...

Fil-fatt, għal kull tabella b'oqsma "potenzjalment kbar", awtomatikament tinħoloq tabella abbinata bi "tqattigħ". kull rekord "kbir" f'segmenti ta' 2KB:

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

Jiġifieri, jekk ikollna niktbu string b'valur "kbir". data, allura r-reġistrazzjoni reali se sseħħ mhux biss għat-tabella prinċipali u l-PK tagħha, iżda wkoll għal TOAST u l-PK tagħha.

Tnaqqis tal-influwenza TOAST

Iżda ħafna mir-rekords tagħna għadhom mhumiex daqshekk kbar, għandhom jidħlu fi 8KB - Kif nista' niffranka l-flus fuq dan?...

Dan huwa fejn l-attribut jiġi għall-għajnuna tagħna STORAGE fil-kolonna tal-mejda:

• ESTENSI jippermetti kemm kompressjoni kif ukoll ħażna separata. Dan għażla standard għall-biċċa l-kbira tat-tipi ta 'dejta konformi TOAST. L-ewwel jipprova jwettaq kompressjoni, imbagħad jaħżen barra t-tabella jekk ir-ringiela tkun għadha kbira wisq.
• PRINĊIPALI jippermetti kompressjoni iżda mhux ħażna separata. (Fil-fatt, ħażna separata xorta se ssir għal kolonni bħal dawn, iżda biss bħala l-aħħar għażla, meta ma jkun hemm l-ebda mod ieħor biex tiċkien is-sekwenza sabiex tidħol fil-paġna.)

Fil-fatt, dan huwa eżattament dak li għandna bżonn għat-test - ikkompressah kemm jista 'jkun, u jekk ma taqbilx xejn, poġġiha f'TOAST. Dan jista 'jsir direttament fuq il-fly, bi kmand wieħed:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Kif tevalwa l-effett

Peress li l-fluss tad-dejta jinbidel kuljum, ma nistgħux inqabblu numri assoluti, iżda f'termini relattivi sehem iżgħar Aħna ktibnieha fi TOAST - tant aħjar. Iżda hawn periklu - aktar ma jkun kbir il-volum "fiżiku" ta 'kull rekord individwali, aktar isir "usa" l-indiċi, għaliex irridu nkopru aktar paġni ta' dejta.

Taqsima qabel il-bidliet:

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

Taqsima wara bidliet:

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

Fil-fatt, aħna bdiet tikteb lil TOAST 2 darbiet inqas spiss, li ħatt mhux biss id-diska, iżda wkoll is-CPU:

Se ninnota li sirna wkoll iżgħar fil-"qari" tad-diska, mhux biss "il-kitba" - peress li meta ddaħħal rekord f'tabella, irridu wkoll "naqraw" parti mis-siġra ta 'kull indiċi sabiex niddeterminawha. pożizzjoni futura fihom.

Min jista 'jgħix tajjeb fuq PostgreSQL 11

Wara li aġġornajna għal PG11, iddeċidejna li nkomplu "tirfina" TOAST u ndunajna li nibda minn din il-verżjoni l-parametru toast_tuple_target:

Il-kodiċi tal-ipproċessar TOAST jispara biss meta l-valur tar-ringiela li għandu jinħażen fit-tabella huwa akbar minn TOAST_TUPLE_THRESHOLD bytes (ġeneralment 2 KB). Il-kodiċi TOAST se jikkompressa u/jew iċċaqlaq il-valuri tal-kamp barra mit-tabella sakemm il-valur tar-ringiela jsir inqas minn TOAST_TUPLE_TARGET bytes (valur varjabbli, normalment ukoll 2 KB) jew id-daqs ma jistax jitnaqqas.

Iddeċidejna li d-dejta li s-soltu jkollna hija jew "qasir ħafna" jew "twila ħafna", għalhekk iddeċidejna li nillimitaw ruħna għall-valur minimu possibbli:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Ejja naraw kif is-settings il-ġodda affettwaw it-tagħbija tad-diska wara r-rikonfigurazzjoni:

Mhux ħażin! Medja il-kju għad-diska naqas madwar 1.5 darbiet, u d-diska "busy" hija 20 fil-mija! Imma forsi dan b'xi mod affettwa s-CPU?

Mill-inqas ma marret għall-agħar. Għalkemm, huwa diffiċli li wieħed jiġġudika jekk anke volumi bħal dawn għadhom ma jistgħux jgħollu t-tagħbija medja tas-CPU ogħla 5%.

Billi tbiddel il-postijiet tat-termini, is-somma... tinbidel!

Kif tafu, Penny jiffranka rublu, u bil-volumi tal-ħażna tagħna huwa dwar 10TB/xahar anke ftit ottimizzazzjoni tista 'tagħti profitt tajjeb. Għalhekk, tajna attenzjoni għall-istruttura fiżika tad-dejta tagħna - kif eżattament oqsma “munzell” ġewwa r-rekord kull waħda mit-tabelli.

Minħabba minħabba allinjament tad-data dan huwa dritt 'il quddiem jaffettwa l-volum li jirriżulta:

Ħafna arkitetturi jipprovdu allinjament tad-dejta fuq il-konfini tal-kliem tal-magni. Pereżempju, fuq sistema x32 86-bit, numri interi (tip interi, 4 bytes) se jkunu allinjati fuq limitu ta 'kelma ta' 4 byte, kif se jirdoppja numri floating point ta 'preċiżjoni (punt floating ta' preċiżjoni doppja, 8 bytes). U fuq sistema ta '64-bit, valuri doppji se jkunu allinjati mal-konfini tal-kliem ta' 8 byte. Din hija raġuni oħra għall-inkompatibilità.

Minħabba l-allinjament, id-daqs ta 'ringiela ta' tabella jiddependi fuq l-ordni tal-oqsma. Normalment dan l-effett ma jkunx notevoli ħafna, iżda f'xi każijiet jista 'jwassal għal żieda sinifikanti fid-daqs. Pereżempju, jekk tħallat char(1) u oqsma integer, tipikament ikun hemm 3 bytes moħlija bejniethom.

Nibdew b'mudelli sintetiċi:

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

Minn fejn ġew koppja ta 'bytes żejda fl-ewwel każ? Huwa sempliċi - 2-byte smallint allinjat fuq il-konfini ta '4-byte qabel il-qasam li jmiss, u meta jkun l-aħħar wieħed, m'hemm xejn u l-ebda ħtieġa li tallinja.

Fit-teorija, kollox huwa tajjeb u tista 'tirranġa l-għelieqi kif tixtieq. Ejja niċċekkjawha fuq data reali billi tuża l-eżempju ta 'waħda mit-tabelli, li s-sezzjoni ta' kuljum tagħha tokkupa 10-15GB.

Struttura inizjali:

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)

Taqsima wara li tinbidel l-ordni tal-kolonna - eżattament istess oqsma, biss ordni differenti:

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)

Il-volum totali tas-sezzjoni huwa determinat min-numru ta '"fatti" u jiddependi biss fuq proċessi esterni, allura ejja naqsmu d-daqs tal-borġ (pg_relation_size) bin-numru ta 'rekords fiha - jiġifieri, aħna nikseb daqs medju tar-rekord attwali maħżun:

Minus 6% volum, Kbir!

Imma kollox, ovvjament, mhuwiex daqshekk ward - wara kollox, fl-indiċi ma nistgħux nibdlu l-ordni tal-oqsma, u għalhekk "b'mod ġenerali" (pg_total_relation_size) ...

...għad hawn ukoll ffrankat 1.5%mingħajr ma tinbidel linja waħda ta 'kodiċi. Iva, iva!

Ninnota li l-għażla ta 'hawn fuq biex tirranġa l-oqsma mhix il-fatt li hija l-aktar ottimali. Minħabba li ma tridx "tiċrit" xi blokki ta 'oqsma għal raġunijiet estetiċi - pereżempju, koppja (pack, recno), li hija l-PK għal din it-tabella.

B'mod ġenerali, id-determinazzjoni tal-arranġament "minimu" tal-oqsma hija kompitu ta '"forza bruta" pjuttost sempliċi. Għalhekk, tista 'tikseb riżultati saħansitra aħjar mid-dejta tiegħek minn tagħna - ipprovah!

Sors: www.habr.com