Spar en krone på store volumer i PostgreSQL

Fortsetter temaet opptak av store datastrømmer reist av forrige artikkel om partisjonering, i dette vil vi se på måtene du kan redusere den "fysiske" størrelsen på det lagrede i PostgreSQL, og deres innvirkning på serverytelsen.

Vi skal snakke om TOAST-innstillinger og datajustering. "I gjennomsnitt" vil disse metodene ikke spare for mange ressurser, men uten å endre applikasjonskoden i det hele tatt.

Imidlertid viste vår erfaring seg å være veldig produktiv i denne forbindelse, siden lagring av nesten enhver overvåking i sin natur er stort sett bare vedlegg når det gjelder registrerte data. Og hvis du lurer på hvordan du kan lære databasen å skrive til disk i stedet 200MB / s halvparten så mye - vær så snill under katt.

Big datas små hemmeligheter

Etter jobbprofil vår service, flyr de jevnlig til ham fra hulene tekstpakker.

Og siden VLSI-komplekshvis database vi overvåker er et flerkomponentprodukt med komplekse datastrukturer, deretter spørringer for maksimal ytelse bli ganske slik "flervolum" med kompleks algoritmisk logikk. Så volumet av hver enkelt forekomst av en forespørsel eller den resulterende utførelsesplanen i loggen som kommer til oss viser seg å være "i gjennomsnitt" ganske stor.

La oss se på strukturen til en av tabellene der vi skriver "rå" data - det vil si her er den originale teksten fra loggoppføringen:

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 seksjonert, selvfølgelig, så dette er en seksjonsmal), hvor det viktigste er teksten. Noen ganger ganske omfangsrik.

Husk at den "fysiske" størrelsen på en post i en PG ikke kan oppta mer enn én side med data, men den "logiske" størrelsen er en helt annen sak. For å skrive en volumetrisk verdi (varchar/text/bytea) til et felt, bruk TOAST-teknologi:

PostgreSQL bruker en fast sidestørrelse (vanligvis 8 KB), og tillater ikke tuples å spenne over flere sider. Derfor er det umulig å lagre veldig store feltverdier direkte. For å overvinne denne begrensningen blir store feltverdier komprimert og/eller delt over flere fysiske linjer. Dette skjer ubemerket av brukeren og har liten innvirkning på de fleste serverkoder. Denne metoden er kjent som TOAST...

Faktisk, for hvert bord med "potensielt store" felt, automatisk en sammenkoblet tabell med "slicing" opprettes hver "store" post i 2KB segmenter:

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

Det vil si hvis vi må skrive en streng med en "stor" verdi data, så vil det virkelige opptaket skje ikke bare til hovedbordet og dets PK, men også til TOAST og dets PK.

Reduserer TOAST-innflytelse

Men de fleste av platene våre er fortsatt ikke så store, skal passe inn i 8KB – Hvordan kan jeg spare penger på dette?

Det er her egenskapen kommer oss til hjelp STORAGE ved tabellkolonnen:

• UTVIDET tillater både komprimering og separat lagring. Dette standard alternativ for de fleste TOAST-kompatible datatyper. Den prøver først å utføre komprimering, og lagrer den deretter utenfor tabellen hvis raden fortsatt er for stor.
• MAIN tillater komprimering, men ikke separat lagring. (Faktisk vil separat lagring fortsatt utføres for slike kolonner, men bare som en siste utvei, når det ikke er noen annen måte å krympe strengen slik at den passer på siden.)

Faktisk er dette akkurat det vi trenger for teksten - komprimer den så mye som mulig, og hvis den ikke passer i det hele tatt, legg den i TOAST. Dette kan gjøres direkte med én kommando:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Hvordan vurdere effekten

Siden dataflyten endres hver dag, kan vi ikke sammenligne absolutte tall, men i relative termer mindre andel Vi skrev det ned i TOAST - så mye desto bedre. Men det er en fare her - jo større det "fysiske" volumet til hver enkelt post er, jo "bredere" blir indeksen, fordi vi må dekke flere sider med data.

Seksjon før endringer:

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

Seksjon etter endringer:

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

Faktisk, vi begynte å skrive til TOAST 2 ganger sjeldnere, som avlastet ikke bare disken, men også CPU:

Jeg vil merke at vi også har blitt mindre når det gjelder å "lese" disken, ikke bare "skrive" - ​​siden når vi setter inn en post i en tabell, må vi også "lese" en del av treet til hver indeks for å bestemme dens fremtidig posisjon i dem.

Hvem kan leve godt på PostgreSQL 11

Etter å ha oppdatert til PG11 bestemte vi oss for å fortsette å "tune" TOAST og la merke til at fra denne versjonen ble parameteren tilgjengelig for tuning toast_tuple_target:

TOAST-behandlingskoden utløses bare når radverdien som skal lagres i tabellen er større enn TOAST_TUPLE_THRESHOLD byte (vanligvis 2 KB). TOAST-koden vil komprimere og/eller flytte feltverdier ut av tabellen til radverdien blir mindre enn TOAST_TUPLE_TARGET byte (variabel verdi, også vanligvis 2 KB) eller størrelsen ikke kan reduseres.

Vi bestemte oss for at dataene vi vanligvis har er enten "veldig korte" eller "svært lange", så vi bestemte oss for å begrense oss til minimum mulig verdi:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

La oss se hvordan de nye innstillingene påvirket disklasting etter omkonfigurering:

Ikke verst! Gjennomsnitt køen til disken har blitt mindre ca. 1.5 ganger, og disken "opptatt" er 20 prosent! Men kanskje dette påvirket CPU'en på en eller annen måte?

Det ble i hvert fall ikke verre. Selv om det er vanskelig å bedømme om selv slike volumer fortsatt ikke kan øke den gjennomsnittlige CPU-belastningen høyere 5%.

Ved å endre plassering av vilkårene endres summen...!

Som du vet sparer en krone en rubel, og med våre lagringsvolumer handler det om 10TB/mnd selv litt optimalisering kan gi god fortjeneste. Derfor tok vi hensyn til den fysiske strukturen til dataene våre – hvordan nøyaktig "stablede" felt inne i posten hver av tabellene.

På grunn av datajustering dette er rett frem påvirker det resulterende volumet:

Mange arkitekturer gir datajustering på maskinordgrenser. For eksempel, på et 32-bits x86-system, vil heltall (heltallstype, 4 byte) bli justert på en 4-byte ordgrense, og det samme vil doble presisjonsflyttall (dobbel presisjon flytekomma, 8 byte). Og på et 64-bitssystem vil doble verdier bli justert til 8-byte ordgrenser. Dette er en annen grunn til inkompatibilitet.

På grunn av justering avhenger størrelsen på en tabellrad av rekkefølgen på feltene. Vanligvis er denne effekten ikke veldig merkbar, men i noen tilfeller kan den føre til en betydelig økning i størrelsen. For eksempel, hvis du blander char(1) og heltallsfelt, vil det vanligvis være 3 byte bortkastet mellom dem.

La oss 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 byte fra i det første tilfellet? Det er enkelt - 2-byte smallint justert på 4-byte grense før neste felt, og når det er det siste, er det ingenting og ingen grunn til å justere.

I teorien er alt bra, og du kan omorganisere feltene som du vil. La oss sjekke det på ekte data ved å bruke eksemplet på en av tabellene, hvor den daglige delen opptar 10-15 GB.

Opprinnelig 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)

Utsnitt etter endring av kolonnerekkefølge - nøyaktig samme felt, bare annen rekkefø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)

Det totale volumet av seksjonen bestemmes av antall "fakta" og avhenger bare av eksterne prosesser, så la oss dele størrelsen på haugen (pg_relation_size) etter antall poster i den - det vil si at vi får gjennomsnittlig størrelse på faktisk lagret post:

Minus 6 % volum, Flott!

Men alt er selvfølgelig ikke så rosenrødt - tross alt, i indekser kan vi ikke endre rekkefølgen på feltene, og derfor "generelt" (pg_total_relation_size) ...

...fortsatt her også spart 1.5 %uten å endre en eneste kodelinje. Ja, ja!

Jeg legger merke til at alternativet ovenfor for å arrangere felt ikke er det faktum at det er det mest optimale. Fordi du ikke vil "rive" noen blokker med felt av estetiske grunner - for eksempel et par (pack, recno), som er PK for denne tabellen.

Generelt er det å bestemme "minimum" arrangementet av felt en ganske enkel "brute force"-oppgave. Derfor kan du få enda bedre resultater fra dine data enn våre – prøv det!

Kilde: www.habr.com