Bespaar een cent op grote volumes in PostgreSQL

Voortbordurend op het onderwerp van het opnemen van grote datastromen, aangedragen door vorig artikel over partitioneren, hierin kijken we naar de manieren waarop u dat kunt doen verklein de “fysieke” omvang van de opgeslagen bestanden in PostgreSQL, en hun impact op de serverprestaties.

We zullen erover praten TOAST-instellingen en gegevensuitlijning. “Gemiddeld zullen deze methoden niet al te veel bronnen besparen, maar zonder dat de applicatiecode überhaupt wordt gewijzigd.

Onze ervaring bleek in dit opzicht echter zeer productief, aangezien de opslag van vrijwel elke monitoring van nature dat is meestal alleen als bijlage als het gaat om geregistreerde gegevens. En als u zich afvraagt ​​hoe u de database kunt leren om naar schijf te schrijven 200MB / s half zoveel - graag onder cat.

Kleine geheimen van big data

Per functieprofiel onze service, ze vliegen regelmatig vanuit de schuilplaatsen naar hem toe tekstpakketten.

En sindsdien VLSI-complexwiens database we monitoren is een uit meerdere componenten bestaand product met complexe datastructuren en vervolgens queries voor maximale prestaties komen ongeveer zo uit “multi-volume” met complexe algoritmische logica. Het volume van elke individuele instantie van een verzoek of het resulterende uitvoeringsplan in het logboek dat naar ons toekomt, blijkt dus “gemiddeld” behoorlijk groot te zijn.

Laten we eens kijken naar de structuur van een van de tabellen waarin we 'onbewerkte' gegevens schrijven - dat wil zeggen, hier is de originele tekst uit het logboekitem:

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

Een typisch teken (uiteraard al in secties verdeeld, dus dit is een sectiesjabloon), waarbij de tekst het belangrijkste is. Soms behoorlijk volumineus.

Bedenk dat de “fysieke” grootte van één record in een PG niet meer dan één pagina met gegevens kan beslaan, maar de “logische” grootte is een heel andere zaak. Gebruik om een ​​volumetrische waarde (varchar/text/bytea) naar een veld te schrijven TOAST-technologie:

PostgreSQL gebruikt een vaste paginagrootte (doorgaans 8 KB) en staat niet toe dat tupels meerdere pagina's beslaan. Daarom is het onmogelijk om zeer grote veldwaarden direct op te slaan. Om deze beperking te ondervangen worden grote veldwaarden gecomprimeerd en/of gesplitst over meerdere fysieke lijnen. Dit gebeurt onopgemerkt door de gebruiker en heeft weinig impact op de meeste servercode. Deze methode staat bekend als TOAST...

In feite automatisch voor elke tabel met "potentieel grote" velden er wordt een gepaarde tabel met “slicing” gemaakt elk “groot” record in segmenten van 2 KB:

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

Dat wil zeggen, als we een string met een “grote” waarde moeten schrijven data, dan vindt de echte opname plaats niet alleen naar de hoofdtabel en zijn PK, maar ook naar TOAST en zijn PK.

TOAST-invloed verminderen

Maar de meeste van onze platen zijn nog steeds niet zo groot, zou in 8 KB moeten passen - Hoe kan ik hierop geld besparen?

Dit is waar het kenmerk ons ​​te hulp komt STORAGE in de tabelkolom:

• UITGEBREID maakt zowel compressie als afzonderlijke opslag mogelijk. Dit standaard optie voor de meeste TOAST-compatibele gegevenstypen. Het probeert eerst compressie uit te voeren en slaat het vervolgens buiten de tabel op als de rij nog steeds te groot is.
• MAIN staat compressie toe, maar geen afzonderlijke opslag. (In feite zal voor dergelijke kolommen nog steeds afzonderlijke opslag worden uitgevoerd, maar alleen als laatste redmiddel, wanneer er geen andere manier is om de tekenreeks te verkleinen zodat deze op de pagina past.)

In feite is dit precies wat we nodig hebben voor de tekst: comprimeer het zo veel mogelijk, en als het helemaal niet past, plaats het dan in TOAST. Dit kan direct worden gedaan, met één commando:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Hoe het effect te evalueren

Omdat de datastroom elke dag verandert, kunnen we geen absolute cijfers vergelijken, maar in relatieve termen kleiner aandeel We hebben het opgeschreven in TOAST - des te beter. Maar er schuilt hier een gevaar: hoe groter het ‘fysieke’ volume van elk afzonderlijk record, hoe ‘breder’ de index wordt, omdat we meer pagina’s met gegevens moeten bestrijken.

Секция vóór veranderingen:

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

Секция na veranderingen:

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

Sterker nog, wij begon 2 keer minder vaak naar TOAST te schrijven, die niet alleen de schijf, maar ook de CPU heeft leeggemaakt:

Ik zal opmerken dat we ook kleiner zijn geworden in het “lezen” van de schijf, en niet alleen in het “schrijven” - aangezien we bij het invoegen van een record in een tabel ook een deel van de boom van elke index moeten “lezen” om de waarde ervan te bepalen toekomstige positie daarin.

Wie kan goed leven op PostgreSQL 11

Na het updaten naar PG11 besloten we door te gaan met het “tunen” van TOAST en merkten dat vanaf deze versie de parameter beschikbaar kwam voor tuning toast_tuple_target:

De TOAST-verwerkingscode wordt alleen geactiveerd als de rijwaarde die in de tabel moet worden opgeslagen groter is dan TOAST_TUPLE_THRESHOLD bytes (meestal 2 KB). De TOAST-code comprimeert en/of verplaatst veldwaarden uit de tabel totdat de rijwaarde kleiner wordt dan TOAST_TUPLE_TARGET bytes (variabele waarde, meestal ook 2 KB) of de grootte niet kan worden verkleind.

We hebben besloten dat de gegevens die we gewoonlijk hebben ‘zeer kort’ of ‘zeer lang’ zijn, dus hebben we besloten ons te beperken tot de minimaal mogelijke waarde:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Laten we eens kijken hoe de nieuwe instellingen het laden van de schijf beïnvloedden na herconfiguratie:

Niet slecht! Gemiddeld de wachtrij naar de schijf is afgenomen ongeveer 1.5 keer, en de schijf “bezet” is 20 procent! Maar misschien heeft dit op de een of andere manier invloed gehad op de CPU?

Het werd in ieder geval niet erger. Hoewel het moeilijk te beoordelen is of zelfs dergelijke volumes de gemiddelde CPU-belasting nog steeds niet kunnen verhogen 5%.

Door de plaatsen van de termen te veranderen, verandert de som...!

Zoals u weet, bespaart een cent een roebel, en met onze opslagvolumes gaat het om 10TB/maand zelfs een beetje optimalisatie kan een goede winst opleveren. Daarom hebben we aandacht besteed aan de fysieke structuur van onze gegevens – hoe precies “gestapelde” velden in de record elk van de tafels.

Omdat vanwege uitlijning van gegevens dit is eenvoudig beïnvloedt het resulterende volume:

Veel architecturen bieden gegevensuitlijning op machinewoordgrenzen. Op een 32-bits x86-systeem worden gehele getallen (integer-type, 4 bytes) bijvoorbeeld uitgelijnd op een woordgrens van 4 bytes, evenals drijvende-kommagetallen met dubbele precisie (zwevende-kommagetallen met dubbele precisie, 8 bytes). En op een 64-bits systeem worden dubbele waarden uitgelijnd met woordgrenzen van 8 bytes. Dit is een andere reden voor incompatibiliteit.

Door uitlijning is de grootte van een tabelrij afhankelijk van de volgorde van de velden. Meestal is dit effect niet erg merkbaar, maar in sommige gevallen kan het leiden tot een aanzienlijke toename in omvang. Als u bijvoorbeeld char(1)- en integer-velden combineert, zullen er doorgaans 3 bytes tussen deze velden verspild worden.

Laten we beginnen met synthetische modellen:

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 kwamen in het eerste geval een paar extra bytes vandaan? Het is makkelijk - Smallint van 2 bytes uitgelijnd op de grens van 4 bytes vóór het volgende veld, en als het het laatste veld is, is er niets en is het niet nodig om uit te lijnen.

In theorie is alles in orde en kun je de velden herschikken zoals je wilt. Laten we het op echte gegevens controleren aan de hand van het voorbeeld van een van de tabellen, waarvan het dagelijkse gedeelte 10-15 GB beslaat.

Initiële structuur:

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)

Sectie na het wijzigen van de kolomvolgorde - precies dezelfde velden, alleen een andere 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)

Het totale volume van de sectie wordt bepaald door het aantal "feiten" en is alleen afhankelijk van externe processen, dus laten we de grootte van de hoop verdelen (pg_relation_size) door het aantal records erin - dat wil zeggen, we krijgen gemiddelde grootte van daadwerkelijk opgeslagen record:

Min 6% volume, Geweldig!

Maar alles is natuurlijk niet zo rooskleurig - tenslotte in indexen kunnen we de volgorde van velden niet wijzigen, en daarom “in het algemeen” (pg_total_relation_size) ...

...hier ook nog 1.5% bespaardzonder ook maar één regel code te veranderen. Ja, ja!

Ik merk op dat de bovenstaande optie voor het rangschikken van velden niet het feit is dat deze de meest optimale is. Omdat je om esthetische redenen sommige blokken velden niet wilt "scheuren", bijvoorbeeld een paar (pack, recno), wat de PK voor deze tabel is.

Over het algemeen is het bepalen van de ‘minimale’ rangschikking van velden een vrij eenvoudige ‘brute force’-taak. Daarom kunt u nog betere resultaten uit uw gegevens halen dan de onze - probeer het!

Bron: www.habr.com