Fortsætter emnet optagelse af store datastrømme rejst af , i dette vil vi se på de måder, du kan reducere den "fysiske" størrelse af det lagrede i PostgreSQL, og deres indflydelse på serverens ydeevne.
Vi taler om TOAST-indstillinger og datajustering. "I gennemsnit vil disse metoder ikke spare for mange ressourcer, men uden at ændre applikationskoden overhovedet.
Men vores erfaring viste sig at være meget produktiv i denne henseende, da opbevaring af næsten enhver overvågning i sin natur er for det meste kun vedhæfte hvad angår registrerede data. Og hvis du undrer dig over, hvordan du i stedet kan lære databasen at skrive til disk 200MB / s halvt så meget - venligst under kat.
Big datas små hemmeligheder
Efter jobprofil , flyver de jævnligt til ham fra hulerne tekstpakker.
Og siden hvis database vi overvåger er et multi-komponent produkt med komplekse datastrukturer, derefter forespørgsler for maksimal ydeevne blive ganske sådan her . Så mængden af hver enkelt forekomst af en anmodning eller den resulterende eksekveringsplan i loggen, der kommer til os, viser sig at være "i gennemsnit" ret stor.
Lad os se på strukturen af en af tabellerne, som vi skriver "rå" data i - det vil sige, her er den originale tekst fra logposten:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ключ секции
date
, data -- самое главное
text
, PRIMARY KEY(pack, recno)
);Et typisk skilt (allerede sektioneret, selvfølgelig, så dette er en sektionsskabelon), hvor det vigtigste er teksten. Nogle gange ret omfangsrig.
Husk, at den "fysiske" størrelse af en post i en PG ikke kan optage mere end én side med data, men den "logiske" størrelse er en helt anden sag. For at skrive en volumetrisk værdi (varchar/text/bytea) til et felt, brug :
PostgreSQL bruger en fast sidestørrelse (typisk 8 KB), og tillader ikke tupler at spænde over flere sider. Derfor er det umuligt direkte at lagre meget store feltværdier. For at overvinde denne begrænsning bliver store feltværdier komprimeret og/eller opdelt på tværs af flere fysiske linjer. Dette sker ubemærket af brugeren og har ringe indflydelse på det meste af serverkoden. Denne metode er kendt som TOAST...
Faktisk automatisk for hvert bord med "potentielt store" felter hver "stor" post i 2KB segmenter:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Det vil sige, hvis vi skal skrive en streng med en "stor" værdi data, så vil den rigtige optagelse finde sted ikke kun til hovedbordet og dets PK, men også til TOAST og dets PK.
Reducerer TOAST indflydelse
Men de fleste af vores rekorder er stadig ikke så store, skal passe ind i 8KB - Hvordan kan jeg spare penge på dette?..
Det er her, egenskaben kommer os til hjælp ved tabelkolonnen:
- UDVIDET tillader både komprimering og separat opbevaring. Det her standard mulighed for de fleste TOAST-kompatible datatyper. Den forsøger først at udføre komprimering og gemmer den derefter uden for tabellen, hvis rækken stadig er for stor.
- MAIN tillader komprimering, men ikke separat lagring. (Faktisk vil separat lagring stadig blive udført for sådanne kolonner, men kun som en sidste udvej, når der ikke er nogen anden måde at krympe strengen på, så den passer på siden.)
Faktisk er det præcis, hvad vi har brug for til teksten - komprimer den så meget som muligt, og hvis den slet ikke passer, så læg den i TOAST. Dette kan gøres direkte med én kommando:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Hvordan man vurderer effekten
Da datastrømmen ændrer sig hver dag, kan vi ikke sammenligne absolutte tal, men i relative termer mindre andel Vi skrev det ned i TOAST - så meget desto bedre. Men der er en fare her - jo større det "fysiske" volumen af hver enkelt post er, jo "bredere" bliver indekset, fordi vi skal dække flere sider med data.
Afsnit før ændringer:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Afsnit efter ændringer:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Faktisk vi begyndte at skrive til TOAST 2 gange sjældnere, som fjernede ikke kun disken, men også CPU'en:
Jeg vil bemærke, at vi også er blevet mindre til at "læse" disken, ikke kun "skrive" - da vi, når vi indsætter en post i en tabel, også skal "læse" en del af træet i hvert indeks for at bestemme dens fremtidig stilling i dem.
Hvem kan leve godt på PostgreSQL 11
Efter opdatering til PG11 besluttede vi at fortsætte med at "tune" TOAST og bemærkede, at fra denne version af parameteren :
TOAST-behandlingskoden udløses kun, når rækkeværdien, der skal gemmes i tabellen, er større end TOAST_TUPLE_THRESHOLD bytes (normalt 2 KB). TOAST-koden vil komprimere og/eller flytte feltværdier ud af tabellen, indtil rækkeværdien bliver mindre end TOAST_TUPLE_TARGET bytes (variabel værdi, også normalt 2 KB), eller størrelsen ikke kan reduceres.
Vi besluttede, at de data, vi normalt har, enten er "meget korte" eller "meget lange", så vi besluttede at begrænse os til den mindst mulige værdi:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Lad os se, hvordan de nye indstillinger påvirkede diskindlæsning efter omkonfiguration:
Ikke dårligt! Gennemsnit køen til disken er blevet mindre cirka 1.5 gange, og disken "optaget" er 20 procent! Men måske har dette på en eller anden måde påvirket CPU'en?
Det blev i hvert fald ikke værre. Selvom det er svært at bedømme, om selv sådanne mængder stadig ikke kan hæve den gennemsnitlige CPU-belastning højere 5%.
Ved at ændre vilkårenes steder ændres summen...!
Som du ved, sparer en krone en rubel, og med vores lagervolumener handler det om 10TB/måned selv en lille optimering kan give et godt overskud. Derfor var vi opmærksomme på den fysiske struktur af vores data – hvordan præcist "stablede" felter inde i posten hver af tabellerne.
Fordi pga dette er ligetil :
Mange arkitekturer giver datajustering på maskinordsgrænser. På et 32-bit x86-system vil heltal (heltalstype, 4 bytes) f.eks. blive justeret på en 4-byte ordgrænse, og det samme gælder dobbelte præcisionsflydende decimaltal (dobbelt præcision flydende komma, 8 bytes). Og på et 64-bit system vil dobbelte værdier blive justeret til 8-byte ordgrænser. Dette er endnu en grund til inkompatibilitet.
På grund af justering afhænger størrelsen af en tabelrække af rækkefølgen af felterne. Normalt er denne effekt ikke særlig mærkbar, men i nogle tilfælde kan det føre til en betydelig stigning i størrelsen. Hvis du for eksempel blander char(1) og heltalsfelter, vil der typisk være 3 bytes spildt mellem dem.
Lad os starte med syntetiske 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 байтHvor kom et par ekstra bytes fra i det første tilfælde? Det er simpelt - 2-byte smallint justeret på 4-byte grænse før det næste felt, og når det er det sidste, er der intet og ingen grund til at justere.
I teorien er alt fint, og du kan omarrangere felterne, som du vil. Lad os tjekke det på rigtige data ved at bruge eksemplet på en af tabellerne, hvis daglige sektion optager 10-15 GB.
Oprindelig 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)Sektion efter ændring af kolonnerækkefølge - præcis samme felter, bare forskellig rækkefølge:
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)
Sektionens samlede volumen bestemmes af antallet af "fakta" og afhænger kun af eksterne processer, så lad os dividere størrelsen af bunken (pg_relation_size) ved antallet af poster i det - det vil sige, vi får den gennemsnitlige størrelse af den faktiske lagrede post:
Minus 6 % volumen, Store!
Men alt er selvfølgelig ikke så rosenrødt - trods alt, i indekser kan vi ikke ændre rækkefølgen af felter, og derfor "generelt" (pg_total_relation_size) ...
...her også stadig sparet 1.5 %uden at ændre en enkelt kodelinje. Ja, ja!
Jeg bemærker, at ovenstående mulighed for at arrangere felter ikke er det faktum, at det er den mest optimale. Fordi du ikke ønsker at "rive" nogle blokke af felter af æstetiske årsager - for eksempel et par (pack, recno), som er PK for denne tabel.
Generelt er det en ret simpel "brute force"-opgave at bestemme "minimums"-arrangementet af felter. Derfor kan du få endnu bedre resultater ud af dine data end vores - prøv det!
Kilde: www.habr.com