ProHoster > Blogg > Administration > Sparar en slant pÄ stora volymer i PostgreSQL

Sparar en slant pÄ stora volymer i PostgreSQL

FortsÀtter Àmnet att spela in stora datastreamar som lyftes i den tidigare artikeln om partitionering, hÀr kommer vi att undersöka sÀtt att minska den "fysiska" storleken pÄ den lagrade i PostgreSQL, och deras pÄverkan pÄ serverprestanda.

Vi kommer att prata om TOAST-instĂ€llningar och datajustering. "I genomsnitt" kommer dessa metoder att spara inte sĂ€rskilt mycket resurser, men — helt utan att modifiera applikationskoden.

Sparar en slant pÄ stora volymer i PostgreSQL
Dock har vĂ„r erfarenhet varit mycket produktiv pĂ„ detta omrĂ„de, eftersom lagringen av nĂ€stan vilken övervakning som helst i sin natur Ă€r största delen append-only ur synvinkel av de skrivna data. Och om du Ă€r intresserad av hur man kan fĂ„ databasen att skriva till disk istĂ€llet för 200MB/s halverat — vĂ€nligen lĂ€s vidare.

SmÄ hemligheter om stora data

Genom arbetsprofilen för vÄr tjÀnst, fÄr den regelbundet in frÄn loggarna textpaket.

Och eftersom komplexa SBIS, vars databaser vi övervakar, — Ă€r en flerkomponentprodukt med komplexa datastukturer, sĂ„ blir Ă€ven förfrĂ„gningarna för att uppnĂ„ maximal prestanda ganska "multivolymer" med komplex algoritmisk logik. SĂ„ Ă€ven volymen av varje enskild instans av en förfrĂ„gan eller den resulterande körplanen i loggen vi fĂ„r Ă€r "i genomsnitt" ganska stor.

LĂ„t oss titta pĂ„ strukturen av en av tabellerna dĂ€r vi skriver "rĂ„data" — det vill sĂ€ga den ursprungliga texten direkt frĂ„n loggposten:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- sektionens nyckel
    date
, data -- det viktigaste
    text
, PRIMARY KEY(pack, recno)
);

En typisk sĂ„dan tabell (redan partitionerad, sĂ„klart, dĂ€rför Ă€r detta — en sektionmall), dĂ€r det viktigaste Ă€ræ–‡æœŹ. Ibland ganska omfattande.

Kom ihĂ„g att den "fysiska" storleken pĂ„ en post i PG inte kan överstiga en databasida, men den "logiska" storleken — Ă€r nĂ„got helt annat. För att skriva ett stort vĂ€rde i ett fĂ€lt (varchar/text/bytea) anvĂ€nds TOAST-teknologin:

PostgreSQL anvĂ€nder en fast sidstorlek (vanligtvis 8 KB) och tillĂ„ter inte att tupler upptar flera sidor. DĂ€rför Ă€r det inte möjligt att direkt lagra mycket stora fĂ€ltvĂ€rden. För att övervinna denna begrĂ€nsning komprimeras och/eller delas stora fĂ€ltvĂ€rden upp i flera fysiska rader. Detta sker osynligt för anvĂ€ndaren och pĂ„verkar för det mesta serverns kod marginellt. Denna metod kallas TOAST 


Faktiskt, för varje tabell med "potentiellt stora" fÀlt skapas automatiskt en par-tabell med "skivor" av varje "stor" post i segment om 2KB:

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

Det vill sÀga om vi behöver skriva en rad med ett "stort" vÀrde data, sÄ kommer den verkliga posten att ske inte bara i den primÀra tabellen och dess PK, utan Àven i TOAST och dess PK.

Minska pÄverkan av TOAST

Men de flesta poster Ă€r inte sĂ„ stora, i 8KB bör de fĂ„ plats — hur kan vi spara pĂ„ detta?..

HÀr kommer hjÀlp av attributet STORAGE hos tabellens kolumn:

  • EXTENDED tillĂ„ter bĂ„de komprimering och separat lagring. Detta Ă€r den standardalternativ för de flesta datatyper som Ă€r kompatibla med TOAST. Först görs ett försök att komprimera, sedan - lagras utanför tabellen, om raden fortfarande Ă€r för stor.
  • MAIN tillĂ„ter komprimering men inte separat lagring. (Faktiskt, separat lagring kommer att utföras för sĂ„dana kolumner, men endast som en sista Ă„tgĂ€rd, nĂ€r det inte finns nĂ„got annat sĂ€tt att minska raden sĂ„ att den fĂ„r plats pĂ„ sidan.)

Faktiskt Ă€r detta precis vad vi behöver för text — komprimera maximalt, och om det verkligen inte gĂ„r in — flytta till TOAST. Detta kan göras direkt "i farten", med ett kommando:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Hur utvÀrdera effekten

Eftersom datamĂ€ngden förĂ€ndras varje dag kan vi inte jĂ€mföra absoluta siffror, men i relativa termer Ă€r ju ju mindre andel vi skriver till TOAST — desto bĂ€ttre. Men hĂ€r finns en risk – ju mer "fysisk" volym varje enskild post har, desto "bredare" blir indexet, eftersom det mĂ„ste tĂ€cka ett större antal datakoder.

Sektion före Àndringar:

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

Sektion efter Àndringar:

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

Faktiskt har vi börjat skriva till TOAST dubbelt sÄ sÀllan, vilket lett till att bÄde disken och CPU:n har blivit avlastade:

Sparar en slant pÄ stora volymer i PostgreSQL
Sparar en slant pÄ stora volymer i PostgreSQL
Jag vill pĂ„minna om att vi ocksĂ„ har börjat "lĂ€sa" disken mindre, inte bara "skriva" — eftersom nĂ€r en post ska infogas i en tabell mĂ„ste en del av trĂ€det för varje index "lĂ€sa" för att bestĂ€mma dess framtida position.

Vem mÄr bra av PostgreSQL 11

Efter uppgraderingen till PG11 beslöt vi att fortsÀtta "justeringen" av TOAST och noterade att en instÀllning blev tillgÀnglig för konfiguration frÄn och med denna version. toast_tuple_target:

Koden för TOAST-processingen aktiveras endast nÀr storleken pÄ strÀngvÀrdet som ska lagras i tabellen överskrider TOAST_TUPLE_THRESHOLD byte (vanligtvis 2 KB). TOAST-koden kommer att komprimera och/eller flytta fÀltvÀrden utanför tabellen tills strÀngvÀrdet Àr mindre Àn TOAST_TUPLE_TARGET byte (en variabel storlek, oftast ocksÄ 2 KB) eller tills volymreducering inte lÀngre Àr möjlig.

Vi kom fram till att vÄra data vanligtvis antingen Àr "mycket korta" eller "vÀldigt lÄnga", sÄ vi beslutade att hÄlla oss till det minimala möjliga vÀrdet:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

LÄt oss se hur de nya instÀllningarna pÄverkade diskbelastningen efter omkonfigureringen:

Sparar en slant pÄ stora volymer i PostgreSQL
Inte illa! Den genomsnittliga diskköen minskade ungefÀr med 1,5 gÄnger, och "belÀggningen" pÄ disken minskade med cirka 20%! Men kanske det pÄverkar CPU:n pÄ nÄgot sÀtt?

Sparar en slant pÄ stora volymer i PostgreSQL
I alla fall blev det definitivt inte sĂ€mre. Även om det Ă€r svĂ„rt att avgöra, eftersom dessa volymer Ă€ndĂ„ inte kan höja den genomsnittliga CPU-belastningen över 5%.

Byter man platser pÄ summorna... förÀndras summan!

Som bekant sparar en kĂ„k en rubel, och med vĂ„ra lagringsvolymer pĂ„ omkring 10TB/mĂ„nad kan Ă€ven en liten optimering ge ganska bra vinster. DĂ€rför uppmĂ€rksammade vi den fysiska strukturen av vĂ„ra data — hur exakt "fĂ€lten" ligger inom varje post i varje tabell.

Eftersom pÄ grund av datajustering detta direkt pÄverkar den resulterande volymen:

MÄnga arkitekturer förutsÀtter datajustering vid maskinordningsgrÀnser. Till exempel, pÄ ett 32-bitarssystem x86 kommer heltal (typ integer, som tar 4 byte) att justeras enligt 4-byteordningsgrÀnser, precis som flyttal med dubbel precision (typ double precision, 8 byte). Och pÄ ett 64-bitarssystem kommer double-vÀrden att justeras enligt 8-byteordningsgrÀnser. Det hÀr Àr ytterligare en orsak till inkompatibilitet.

PÄ grund av justeringen beror storleken pÄ tabellraden pÄ ordningen av fÀlten. Vanligtvis Àr denna effekt inte mycket mÀrkbar, men i vissa fall kan den leda till en betydande ökning av storleken. Till exempel, om man blandar fÀlten av typen char(1) och integer, kommer det vanligtvis att gÄ förlorade 3 byte mellan dem.

LÄt oss börja med syntetiska modeller:

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 byte

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 byte

VarifrĂ„n kom de extra 2 byten i det första fallet? Det Ă€r enkelt — 2-byte smallint justeras till en 4-byte grĂ€ns före nĂ€sta fĂ€lt, och nĂ€r det stĂ„r sist behöver man inte justera nĂ„got.

I teorin Àr allt bra och man kan flytta fÀlten hur som helst. LÄt oss kontrollera pÄ verkliga data som ett exempel frÄn en av tabellerna vars dagliga sektion tar upp 10-15GB.

Den ursprungliga strukturen:

CREATE TABLE public.plan_20190220
(
-- Ärvt frĂ„n tabell plan:  pack uuid NOT NULL,
-- Ärvt frĂ„n tabell plan:  recno smallint NOT NULL,
-- Ärvt frĂ„n tabell plan:  host uuid,
-- Ärvt frĂ„n tabell plan:  ts timestamp with time zone,
-- Ärvt frĂ„n tabell plan:  exectime numeric(32,3),
-- Ärvt frĂ„n tabell plan:  duration numeric(32,3),
-- Ärvt frĂ„n tabell plan:  bufint bigint,
-- Ärvt frĂ„n tabell plan:  bufmem bigint,
-- Ärvt frĂ„n tabell plan:  bufdsk bigint,
-- Ärvt frĂ„n tabell plan:  apn uuid,
-- Ärvt frĂ„n tabell plan:  ptr uuid,
-- Ärvt frĂ„n tabell 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)

Sektionen efter att kolumnerna har bytt ordning — precis samma fĂ€lt, bara en annan ordning:

CREATE TABLE public.plan_20190221
(
-- Ärvt frĂ„n tabell plan:  dt date NOT NULL,
-- Ärvt frĂ„n tabell plan:  ts timestamp with time zone,
-- Ärvt frĂ„n tabell plan:  pack uuid NOT NULL,
-- Ärvt frĂ„n tabell plan:  recno smallint NOT NULL,
-- Ärvt frĂ„n tabell plan:  host uuid,
-- Ärvt frĂ„n tabell plan:  apn uuid,
-- Ärvt frĂ„n tabell plan:  ptr uuid,
-- Ärvt frĂ„n tabell plan:  bufint bigint,
-- Ärvt frĂ„n tabell plan:  bufmem bigint,
-- Ärvt frĂ„n tabell plan:  bufdsk bigint,
-- Ärvt frĂ„n tabell plan:  exectime numeric(32,3),
-- Ärvt frĂ„n tabell 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)

Den totala volymen av sektionen bestĂ€ms av antalet "fakta" och beror endast pĂ„ externa processer, sĂ„ lĂ„t oss dela storleken pĂ„ heap (pg_relation_size) pĂ„ antalet poster i den — sĂ„ vi fĂ„r den genomsnittliga storleken pĂ„ en verklig lagrad post:

Sparar en slant pÄ stora volymer i PostgreSQL
Minus 6% volym, bra!

Men allt Ă€r givetvis inte sĂ„ positivt — för i indexen kan vi inte Ă€ndra ordningen pĂ„ fĂ€lten, och dĂ€rför "i stort" (pg_total_relation_size)


Sparar en slant pÄ stora volymer i PostgreSQL

 ÀndÄ hÀr sparade vi 1.5%, utan att Àndra en enda kodrad. Precis sÄ!

Sparar en slant pÄ stora volymer i PostgreSQL

Jag pĂ„pekar att den ovanstĂ„ende arrangemanget av fĂ€lten — inte nödvĂ€ndigtvis den mest optimala. Eftersom vissa fĂ€ltblock vill man inte "bryta" av estetiska skĂ€l — till exempel paret (pack, recno), som Ă€r PK för denna tabell.

I stort sett Ă€r definitionen av den "minimala" arrangemanget av fĂ€lt en ganska enkel "brute force"-uppgift. SĂ„ ni kan pĂ„ era data fĂ„ resultat till och med bĂ€ttre Ă€n vĂ„ra — ge det ett försök!

KĂ€lla: habr.com

Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar đŸ”„ Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar | ProHoster