Bespaar 'n sent op groot volumes in PostgreSQL

Voortsetting van die onderwerp van die opneem van groot datastrome wat deur vorige artikel oor partisionering, hierin sal ons kyk na die maniere waarop jy kan verminder die "fisiese" grootte van die gestoor in PostgreSQL, en hul impak op bedienerprestasie.

Ons sal praat oor TOAST instellings en data belyning. "Gemiddeld" sal hierdie metodes nie te veel hulpbronne bespaar nie, maar sonder om die toepassingskode enigsins te verander.

Ons ervaring blyk egter baie produktief te wees in hierdie verband, aangesien die berging van byna enige monitering deur die aard daarvan is meestal slegs byvoeging in terme van aangetekende data. En as jy wonder hoe jy die databasis kan leer om eerder na skyf te skryf 200MB / s half soveel - asseblief onder kat.

Klein geheime van groot data

Volgens posprofiel ons diens, vlieg hulle gereeld van die laer af na hom toe tekspakkette.

En sedertdien VLSI komplekswie se databasis ons monitor is 'n multi-komponent produk met komplekse data strukture, dan navrae vir maksimum prestasie draai nogal so uit "multi-volume" met komplekse algoritmiese logika. Die volume van elke individuele geval van 'n versoek of die gevolglike uitvoeringsplan in die logboek wat na ons toe kom, blyk dus "gemiddeld" redelik groot te wees.

Kom ons kyk na die struktuur van een van die tabelle waarin ons "rou" data skryf - dit wil sê, hier is die oorspronklike teks van die loginskrywing:

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

'n Tipiese teken (natuurlik reeds gedeel, so dit is 'n afdelingsjabloon), waar die belangrikste ding die teks is. Soms nogal lywig.

Onthou dat die "fisiese" grootte van een rekord in 'n PG nie meer as een bladsy data kan beslaan nie, maar die "logiese" grootte is 'n heeltemal ander saak. Om 'n volumetriese waarde (varchar/text/bytea) in 'n veld te skryf, gebruik TOAST tegnologie:

PostgreSQL gebruik 'n vaste bladsygrootte (gewoonlik 8 KB), en laat nie toe dat tuples oor verskeie bladsye strek nie. Daarom is dit onmoontlik om baie groot veldwaardes direk te stoor. Om hierdie beperking te oorkom, word groot veldwaardes saamgepers en/of oor verskeie fisiese lyne verdeel. Dit gebeur ongemerk deur die gebruiker en het min impak op die meeste bedienerkode. Hierdie metode staan ​​bekend as TOAST...

Trouens, vir elke tafel met "potensieel groot" velde, outomaties 'n gepaarde tabel met "slicing" word geskep elke "groot" rekord in 2KB segmente:

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

Dit wil sê as ons 'n string met 'n "groot" waarde moet skryf data, dan sal die regte opname plaasvind nie net na die hooftafel en sy PK nie, maar ook na TOAST en sy PK.

Verminder TOAST invloed

Maar die meeste van ons rekords is steeds nie so groot nie, moet by 8KB pas - Hoe kan ek geld hierop spaar?

Dit is waar die eienskap ons te hulp kom STORAGE by die tabelkolom:

• UITGEBREIDE laat beide kompressie en aparte berging toe. Hierdie standaard opsie vir die meeste TOAST-geskikte datatipes. Dit probeer eers om kompressie uit te voer, en stoor dit dan buite die tabel as die ry nog te groot is.
• HOOF laat kompressie toe, maar nie aparte berging nie. (Trouens, aparte berging sal steeds vir sulke kolomme uitgevoer word, maar slegs as 'n laaste uitweg, wanneer daar geen ander manier is om die tou te krimp sodat dit op die bladsy pas nie.)

Trouens, dit is presies wat ons nodig het vir die teks - druk dit so veel as moontlik saam, en as dit glad nie pas nie, sit dit in TOAST. Dit kan direk op die vlieg gedoen word, met een opdrag:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Hoe om die effek te evalueer

Aangesien die datavloei elke dag verander, kan ons nie absolute getalle vergelyk nie, maar in relatiewe terme kleiner aandeel Ons het dit in TOAST neergeskryf - soveel te beter. Maar hier is 'n gevaar - hoe groter die "fisiese" volume van elke individuele rekord, hoe "breer" word die indeks, want ons moet meer bladsye met data dek.

Afdeling voor veranderinge:

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

Afdeling na veranderinge:

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

Trouens, ons het 2 keer minder gereeld aan TOAST begin skryf, wat nie net die skyf afgelaai het nie, maar ook die SVE:

Ek sal daarop let dat ons ook kleiner geword het in die "lees" van die skyf, nie net "skryf" nie - aangesien wanneer ons 'n rekord in 'n tabel invoeg, ons ook 'n deel van die boom van elke indeks moet "lees" om die toekomstige posisie in hulle.

Wie kan goed leef op PostgreSQL 11

Nadat ons na PG11 opgedateer het, het ons besluit om voort te gaan om TOAST te “tuning” en het opgemerk dat vanaf hierdie weergawe die parameter toast_tuple_target:

Die TOAST-verwerkingskode word slegs geaktiveer wanneer die rywaarde wat in die tabel gestoor moet word, groter is as TOAST_TUPLE_THRESHOLD grepe (gewoonlik 2 KB). Die TOAST-kode sal veldwaardes uit die tabel saamdruk en/of skuif totdat die rywaarde minder as TOAST_TUPLE_TARGET-grepe word (veranderlike waarde, ook gewoonlik 2 KB) of die grootte nie verminder kan word nie.

Ons het besluit dat die data wat ons gewoonlik het óf "baie kort" óf "baie lank" is, en daarom het ons besluit om onsself tot die minimum moontlike waarde te beperk:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Kom ons kyk hoe die nuwe instellings skyflaai na herkonfigurasie beïnvloed het:

Nie sleg nie! Gemiddeld die tou na die skyf het afgeneem ongeveer 1.5 keer, en die skyf "besig" is 20 persent! Maar miskien het dit die SVE op een of ander manier beïnvloed?

Dit het darem nie erger geword nie. Alhoewel dit moeilik is om te oordeel of selfs sulke volumes steeds nie die gemiddelde SVE-lading hoër kan verhoog nie 5%.

Deur die plekke van die terme te verander, verander die som...!

Soos u weet, spaar 'n sent 'n roebel, en met ons bergingsvolumes gaan dit oor 10TB/maand selfs 'n bietjie optimalisering kan 'n goeie wins gee. Daarom het ons aandag gegee aan die fisiese struktuur van ons data – hoe presies "gestapelde" velde binne die rekord elk van die tabelle.

Omdat as gevolg van data belyning dit is reguit vorentoe beïnvloed die resulterende volume:

Baie argitekture verskaf databelyning op masjienwoordgrense. Byvoorbeeld, op 'n 32-bis x86-stelsel, sal heelgetalle (heelgetal tipe, 4 grepe) in lyn gebring word op 'n 4-grepe woordgrens, net soos dubbel presisie swewende punt getalle (dubbel presisie swewende punt, 8 grepe). En op 'n 64-bis-stelsel sal dubbelwaardes in lyn gebring word met 8-grepe woordgrense. Dit is nog 'n rede vir onverenigbaarheid.

As gevolg van belyning hang die grootte van 'n tabelry af van die volgorde van die velde. Gewoonlik is hierdie effek nie baie opvallend nie, maar in sommige gevalle kan dit lei tot 'n aansienlike toename in grootte. Byvoorbeeld, as jy char(1) en heelgetalvelde meng, sal daar tipies 3 grepe tussen hulle vermors word.

Kom ons begin met sintetiese modelle:

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

Waar het 'n paar ekstra grepe vandaan gekom in die eerste geval? Dis eenvoudig - 2-grepe smallint belyn op 4-grepe grens voor die volgende veld, en wanneer dit die laaste een is, is daar niks en hoef nie in lyn te kom nie.

In teorie is alles goed en jy kan die velde herrangskik soos jy wil. Kom ons kyk dit op regte data deur die voorbeeld van een van die tabelle te gebruik, waarvan die daaglikse afdeling 10-15 GB beslaan.

Aanvanklike struktuur:

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)

Afdeling na die verandering van kolomvolgorde - presies dieselfde velde, net verskillende volgorde:

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)

Die totale volume van die afdeling word bepaal deur die aantal "feite" en hang slegs af van eksterne prosesse, so kom ons verdeel die grootte van die hoop (pg_relation_size) volgens die aantal rekords daarin - dit wil sê, ons kry gemiddelde grootte van werklike gestoor rekord:

Minus 6% volume, puik!

Maar alles is natuurlik nie so rooskleurig nie - immers, in indekse kan ons nie die volgorde van velde verander nie, en daarom "in die algemeen" (pg_total_relation_size) ...

... ook nog hier 1.5% bespaarsonder om 'n enkele reël kode te verander. Ja ja!

Ek let daarop dat die bogenoemde opsie vir die rangskikking van velde nie die feit is dat dit die mees optimale is nie. Omdat jy om estetiese redes nie 'n paar blokke velde wil "skeur" nie - byvoorbeeld 'n paartjie (pack, recno), wat die PK vir hierdie tabel is.

Oor die algemeen is die bepaling van die "minimum" rangskikking van velde 'n redelik eenvoudige "brute force" taak. Daarom kan jy selfs beter resultate uit jou data kry as ons s'n - probeer dit!

Bron: will.com