Fortsätter ämnet för att spela in stora dataströmmar väckt av , i detta kommer vi att titta på de sätt som du kan minska den "fysiska" storleken på det lagrade i PostgreSQL, och deras inverkan på serverns prestanda.
Vi ska prata om TOAST-inställningar och datajustering. "I genomsnitt" kommer dessa metoder inte att spara för många resurser, men utan att modifiera applikationskoden alls.
Men vår erfarenhet visade sig vara mycket produktiv i detta avseende, eftersom lagring av nästan all övervakning till sin natur är mestadels bara tillägg när det gäller registrerade uppgifter. Och om du undrar hur du kan lära databasen att skriva till disk istället 200MB / s hälften så mycket - snälla under katt.
Big datas små hemligheter
Efter jobbprofil , de flyger regelbundet till honom från lyorna textpaket.
Och sedan vars databas vi övervakar är en flerkomponentsprodukt med komplexa datastrukturer, sedan frågor för maximal prestanda bli ganska så här . Så volymen för varje enskild instans av en begäran eller den resulterande exekveringsplanen i loggen som kommer till oss visar sig vara "i genomsnitt" ganska stor.
Låt oss titta på strukturen för en av tabellerna som vi skriver "rå" data i - det vill säga här är den ursprungliga texten från loggposten:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ключ секции
date
, data -- самое главное
text
, PRIMARY KEY(pack, recno)
);En typisk skylt (redan sektionerad förstås, så detta är en avsnittsmall), där det viktigaste är texten. Ibland ganska voluminös.
Kom ihåg att den "fysiska" storleken på en post i en PG inte kan uppta mer än en sida med data, men den "logiska" storleken är en helt annan sak. För att skriva ett volymetriskt värde (varchar/text/bytea) till ett fält, använd :
PostgreSQL använder en fast sidstorlek (vanligtvis 8 KB), och tillåter inte tupler att sträcka sig över flera sidor. Därför är det omöjligt att direkt lagra mycket stora fältvärden. För att övervinna denna begränsning komprimeras och/eller delas stora fältvärden över flera fysiska linjer. Detta händer obemärkt av användaren och har liten inverkan på de flesta serverkoder. Denna metod är känd som TOAST...
Faktum är att för varje bord med "potentiellt stora" fält, automatiskt varje "stor" post i 2KB-segment:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Det vill säga om vi måste skriva en sträng med ett "stort" värde data, då kommer den riktiga inspelningen att ske inte bara till huvudbordet och dess PK, utan även till TOAST och dess PK.
Minska TOAST inflytande
Men de flesta av våra skivor är fortfarande inte så stora, bör passa in i 8KB - Hur kan jag spara pengar på detta?
Det är här attributet kommer till vår hjälp i tabellkolumnen:
- UTÖKAD tillåter både komprimering och separat lagring. Detta standardalternativ för de flesta TOAST-kompatibla datatyper. Den försöker först utföra komprimering och lagrar den sedan utanför tabellen om raden fortfarande är för stor.
- HUVUDSAKLIG tillåter komprimering men inte separat lagring. (Faktum är att separat lagring fortfarande kommer att utföras för sådana kolumner, men bara som en sista utväg, när det inte finns något annat sätt att krympa strängen så att den passar på sidan.)
Det är faktiskt precis vad vi behöver för texten - komprimera den så mycket som möjligt, och om den inte passar alls, lägg den i TOAST. Detta kan göras direkt i farten, med ett kommando:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Hur man utvärderar effekten
Eftersom dataflödet förändras varje dag kan vi inte jämföra absoluta tal, utan i relativa termer mindre andel Vi skrev ner det i TOAST - så mycket desto bättre. Men det finns en fara här - ju större den "fysiska" volymen av varje enskild post är, desto "bredare" blir indexet, eftersom vi måste täcka fler sidor med data.
avsnitt före ändringar:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
avsnitt efter ändringar:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Faktum är att vi började skriva till TOAST 2 gånger mer sällan, som avlastade inte bara disken utan även CPU:n:
Jag kommer att notera att vi också har blivit mindre när det gäller att "läsa" skivan, inte bara "skriva" - eftersom när vi infogar en post i en tabell måste vi också "läsa" en del av trädet i varje index för att bestämma dess framtida position i dem.
Vem kan leva bra på PostgreSQL 11
Efter att ha uppdaterat till PG11 bestämde vi oss för att fortsätta "justera" TOAST och märkte att från och med den här versionen blev parametern tillgänglig för inställning :
TOAST-bearbetningskoden aktiveras endast när radvärdet som ska lagras i tabellen är större än TOAST_TUPLE_THRESHOLD byte (vanligtvis 2 KB). TOAST-koden kommer att komprimera och/eller flytta fältvärden ut ur tabellen tills radvärdet blir mindre än TOAST_TUPLE_TARGET byte (variabelt värde, vanligtvis också 2 KB) eller så kan storleken inte minskas.
Vi bestämde oss för att den data vi vanligtvis har är antingen "mycket kort" eller "mycket lång", så vi bestämde oss för att begränsa oss till det minsta möjliga värdet:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Låt oss se hur de nya inställningarna påverkade diskladdningen efter omkonfigurering:
Inte dåligt! Genomsnitt kön till disken har minskat cirka 1.5 gånger, och disken "upptagen" är 20 procent! Men kanske detta påverkade CPU:n på något sätt?
Det blev åtminstone inte värre. Även om det är svårt att bedöma om ens sådana volymer fortfarande inte kan höja den genomsnittliga CPU-belastningen högre 5%.
Genom att ändra termernas plats ändras summan...!
Som ni vet sparar en slant en rubel, och med våra lagringsvolymer handlar det om 10TB/månad även lite optimering kan ge en bra vinst. Därför ägnade vi uppmärksamhet åt den fysiska strukturen av vår data - hur exakt "staplade" fält i posten var och en av tabellerna.
På grund av det här är rakt fram :
Många arkitekturer tillhandahåller datajustering på maskinordsgränser. Till exempel, på ett 32-bitars x86-system, kommer heltal (heltalstyp, 4 byte) att justeras på en 4-byte ordgräns, liksom dubbla precisionsflytande tal (dubbel precision flyttal, 8 byte). Och på ett 64-bitarssystem kommer dubbla värden att anpassas till 8-byte ordgränser. Detta är ytterligare ett skäl till inkompatibilitet.
På grund av justering beror storleken på en tabellrad på fältens ordning. Vanligtvis är denna effekt inte särskilt märkbar, men i vissa fall kan det leda till en betydande ökning av storleken. Till exempel, om du blandar char(1) och heltalsfält, kommer det vanligtvis att finnas 3 byte bortkastade 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 байт
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байтVar kom ett par extra byte ifrån i det första fallet? Det är enkelt - 2-byte smallint justerad på 4-byte gräns före nästa fält, och när det är det sista, finns det inget och inget behov av att justera.
I teorin är allt bra och du kan ordna om fälten som du vill. Låt oss kontrollera det på riktiga data med hjälp av exemplet på en av tabellerna, vars dagliga sektion upptar 10-15 GB.
Initial struktur:
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)Avsnitt efter ändrad kolumnordning - exakt samma fält, bara olika ordning:
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)
Den totala volymen av sektionen bestäms av antalet "fakta" och beror bara på externa processer, så låt oss dela upp storleken på högen (pg_relation_size) av antalet poster i den - det vill säga vi får genomsnittlig storlek på faktisk lagrad post:
Minus 6% volym, Bra!
Men allt är förstås inte så rosenrött - trots allt, i index kan vi inte ändra ordningen på fälten, och därför "i allmänhet" (pg_total_relation_size) ...
...här också sparat 1.5 %utan att ändra en enda kodrad. Jaja!
Jag noterar att alternativet ovan för att ordna fält inte är det faktum att det är det mest optimala. Eftersom du inte vill "riva" några block av fält av estetiska skäl - till exempel ett par (pack, recno), vilket är PK för denna tabell.
I allmänhet är det en ganska enkel "brute force"-uppgift att bestämma det "minsta" arrangemanget av fält. Därför kan du få ännu bättre resultat av din data än vår – prova det!
Källa: will.com