Economisiți un ban pe volume mari în PostgreSQL

Continuând subiectul înregistrării fluxurilor mari de date generate de articolul anterior despre partiționare, în aceasta ne vom uita la modalitățile în care puteți reduce dimensiunea „fizică” a stocului în PostgreSQL și impactul acestora asupra performanței serverului.

Vom vorbi despre Setări TOAST și alinierea datelor. „În medie”, aceste metode nu vor economisi prea multe resurse, dar fără a modifica deloc codul aplicației.

Cu toate acestea, experiența noastră s-a dovedit a fi foarte productivă în acest sens, deoarece stocarea aproape oricărei monitorizări prin natura sa este de cele mai multe ori numai anexă din punct de vedere al datelor înregistrate. Și dacă vă întrebați cum puteți învăța baza de date să scrie pe disc 200MB / s jumătate - vă rog sub cat.

Micile secrete ale datelor mari

După profilul postului serviciul nostru, ei zboară regulat către el din bârloguri pachete de text.

Și de atunci complex VLSIa cărui bază de date o monitorizăm este un produs multicomponent cu structuri complexe de date, apoi interogări pentru performanță maximă iese cam asa „multi-volum” cu o logică algoritmică complexă. Deci volumul fiecărei instanțe individuale a unei solicitări sau planul de execuție rezultat din jurnalul care ne vine se dovedește a fi „în medie” destul de mare.

Să ne uităm la structura unuia dintre tabelele în care scriem date „brute” - adică aici este textul original din intrarea în jurnal:

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

Un semn tipic (deja secționat, desigur, deci acesta este un șablon de secțiune), unde cel mai important lucru este textul. Uneori destul de voluminos.

Amintiți-vă că dimensiunea „fizică” a unei înregistrări dintr-un PG nu poate ocupa mai mult de o pagină de date, dar dimensiunea „logică” este o chestiune complet diferită. Pentru a scrie o valoare volumetrică (varchar/text/bytea) într-un câmp, utilizați Tehnologia TOAST:

PostgreSQL folosește o dimensiune fixă ​​a paginii (de obicei 8 KB) și nu permite tuplurilor să se întinde pe mai multe pagini. Prin urmare, este imposibil să stocați direct valori de câmp foarte mari. Pentru a depăși această limitare, valorile câmpurilor mari sunt comprimate și/sau împărțite pe mai multe linii fizice. Acest lucru se întâmplă neobservat de utilizator și are un impact redus asupra majorității codului de server. Această metodă este cunoscută sub numele de TOAST...

De fapt, pentru fiecare tabel cu câmpuri „potențial mari”, automat este creat un tabel asociat cu „slicing”. fiecare înregistrare „mare” în segmente de 2 KB:

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

Adică dacă trebuie să scriem un șir cu o valoare „mare”. data, atunci va avea loc înregistrarea reală nu numai la masa principală și PK-ul său, ci și la TOAST și PK-ul său.

Reducerea influenței TOAST

Dar majoritatea înregistrărilor noastre încă nu sunt atât de mari, ar trebui să se încadreze în 8KB - Cum pot economisi bani pe asta?...

Aici ne vine în ajutor atributul STORAGE la coloana tabelului:

• EXTENDED permite atât compresia, cât și stocarea separată. Acest opțiune standard pentru majoritatea tipurilor de date conforme cu TOAST. Mai întâi încearcă să efectueze compresie, apoi o stochează în afara tabelului dacă rândul este încă prea mare.
• PRINCIPAL permite comprimarea, dar nu stocarea separată. (De fapt, stocarea separată va fi în continuare efectuată pentru astfel de coloane, dar numai ca ultimă soluţie, când nu există altă modalitate de a micșora șirul astfel încât să se potrivească pe pagină.)

De fapt, asta este exact ceea ce avem nevoie pentru text - comprimă-l cât mai mult, iar dacă nu se potrivește deloc, pune-l în TOAST. Acest lucru se poate face direct din mers, cu o singură comandă:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Cum se evaluează efectul

Deoarece fluxul de date se schimbă în fiecare zi, nu putem compara numere absolute, ci în termeni relativi cotă mai mică Am notat-o ​​în TOAST - cu atât mai bine. Dar există un pericol aici - cu cât volumul „fizic” al fiecărei înregistrări individuale este mai mare, cu atât indexul devine „mai larg”, deoarece trebuie să acoperim mai multe pagini de date.

Secțiune înainte de modificări:

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

Secțiune dupa modificari:

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

De fapt, noi a început să scrie pentru TOAST de 2 ori mai rar, care a descărcat nu numai discul, ci și procesorul:

Voi observa că am devenit, de asemenea, mai mici în „citirea” discului, nu doar „scrierea” - deoarece atunci când introducem o înregistrare într-un tabel, trebuie să „citim” și o parte din arborele fiecărui index pentru a-i determina. poziţia viitoare în ele.

Cine poate trăi bine pe PostgreSQL 11

După actualizarea la PG11, am decis să continuăm „tuning” TOAST și am observat că pornind de la această versiune parametrul toast_tuple_target:

Codul de procesare TOAST se declanșează numai atunci când valoarea rândului care trebuie stocată în tabel este mai mare decât TOAST_TUPLE_THRESHOLD octeți (de obicei 2 KB). Codul TOAST va comprima și/sau muta valorile câmpurilor din tabel până când valoarea rândului devine mai mică de TOAST_TUPLE_TARGET octeți (valoare variabilă, de asemenea, de obicei 2 KB) sau dimensiunea nu poate fi redusă.

Am decis că datele pe care le avem de obicei sunt fie „foarte scurte”, fie „foarte lungi”, așa că am decis să ne limităm la valoarea minimă posibilă:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Să vedem cum noile setări au afectat încărcarea discului după reconfigurare:

Nu-i rău! In medie coada pe disc a scăzut de aproximativ 1.5 ori, iar discul „ocupat” este de 20 la sută! Dar poate că asta a afectat cumva procesorul?

Cel puțin nu a devenit mai rău. Deși, este dificil de judecat dacă chiar și astfel de volume încă nu pot crește sarcina medie a procesorului 5%.

Schimbând locurile termenilor, suma... se schimbă!

După cum știți, un ban economisește o rublă, iar cu volumele noastre de stocare este vorba 10TB/lună chiar și puțină optimizare poate oferi un profit bun. Prin urmare, am acordat atenție structurii fizice a datelor noastre - cum anume câmpuri „stivuite” în interiorul înregistrării fiecare dintre tabele.

Pentru că din cauza alinierea datelor asta este direct afectează volumul rezultat:

Multe arhitecturi oferă alinierea datelor pe limitele cuvintelor mașinii. De exemplu, pe un sistem x32 pe 86 de biți, numerele întregi (tip întreg, 4 octeți) vor fi aliniate pe o limită de cuvânt de 4 octeți, la fel ca numerele cu virgulă mobilă cu precizie dublă (virgulă mobilă cu precizie dublă, 8 octeți). Și pe un sistem pe 64 de biți, valorile duble vor fi aliniate la limitele cuvintelor de 8 octeți. Acesta este un alt motiv de incompatibilitate.

Datorită alinierii, dimensiunea unui rând de tabel depinde de ordinea câmpurilor. De obicei, acest efect nu este foarte vizibil, dar în unele cazuri poate duce la o creștere semnificativă a dimensiunii. De exemplu, dacă amestecați câmpuri char(1) și întregi, de obicei vor fi irosiți 3 octeți între ele.

Să începem cu modelele sintetice:

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

De unde au venit câțiva octeți suplimentari în primul caz? E simplu - Smallint de 2 octeți aliniat la limita de 4 octeți înainte de următorul câmp, iar când este ultimul, nu este nimic și nu este nevoie să se alinieze.

În teorie, totul este în regulă și poți rearanja câmpurile după cum vrei. Să verificăm datele reale folosind exemplul unuia dintre tabele, a cărui secțiune zilnică ocupă 10-15 GB.

Structura initiala:

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)

Secțiune după schimbarea ordinii coloanelor - exact aceleași câmpuri, doar ordine diferită:

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)

Volumul total al secțiunii este determinat de numărul de „fapte” și depinde numai de procesele externe, așa că să împărțim dimensiunea mormanului (pg_relation_size) după numărul de înregistrări din el - adică obținem dimensiunea medie a înregistrării reale stocate:

Minus 6% volum, Grozav!

Dar totul, desigur, nu este atât de roz - la urma urmei, în indexuri nu putem schimba ordinea câmpurilorși, prin urmare, „în general” (pg_total_relation_size) ...

... tot aici a economisit 1.5%fără a schimba o singură linie de cod. Da, da!

Observ că opțiunea de mai sus pentru aranjarea câmpurilor nu este faptul că este cea mai optimă. Pentru că nu doriți să „smulgeți” unele blocuri de câmpuri din motive estetice - de exemplu, câteva (pack, recno), care este PK pentru acest tabel.

În general, determinarea aranjamentului „minim” a câmpurilor este o sarcină de „forță brută” destul de simplă. Prin urmare, puteți obține rezultate și mai bune din datele dvs. decât ale noastre - încercați!

Sursa: www.habr.com