Prihranite peni pri velikih količinah v PostgreSQL

Nadaljevanje teme snemanja velikih podatkovnih tokov, ki jo je postavil prejšnji članek o particioniranju, v tem si bomo ogledali načine, na katere lahko zmanjšajte "fizično" velikost shranjenega v PostgreSQL in njihov vpliv na delovanje strežnika.

Pogovarjali se bomo o TOAST nastavitve in usklajevanje podatkov. "V povprečju" te metode ne bodo prihranile preveč virov, vendar brez spreminjanja kode aplikacije.

Vendar so se naše izkušnje v zvezi s tem izkazale za zelo produktivne, saj je shranjevanje skoraj vsakega spremljanja po svoji naravi večinoma samo za dodajanje v smislu zapisanih podatkov. In če se sprašujete, kako lahko bazo podatkov naučite, da namesto tega piše na disk 200MB / s pol manj - prosim pod kat.

Male skrivnosti velikih podatkov

Po profilu delovnega mesta naše storitve, k njemu redno priletavajo iz brlogov besedilni paketi.

In od takrat VLSI komplekskaterih baza podatkov spremljamo, je večkomponentni izdelek s kompleksnimi podatkovnimi strukturami, nato pa poizvedbe za maksimalno zmogljivost izpade takole »več zvezkov« s kompleksno algoritemsko logiko. Tako se obseg vsake posamezne instance zahteve ali posledično izvedbenega načrta v dnevniku, ki pride do nas, izkaže za »v povprečju« precej velik.

Poglejmo strukturo ene od tabel, v katero pišemo "surove" podatke - to je, tukaj je izvirno besedilo iz vnosa v dnevnik:

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

Tipičen znak (seveda že razdeljen, torej predloga razdelka), kjer je najpomembnejše besedilo. Včasih precej obsežna.

Spomnimo se, da "fizična" velikost enega zapisa v PG ne more zavzeti več kot ene strani podatkov, "logična" velikost pa je povsem druga stvar. Če želite vpisati volumetrično vrednost (varchar/text/bytea) v polje, uporabite Tehnologija TOAST:

PostgreSQL uporablja fiksno velikost strani (običajno 8 KB) in ne dovoljuje, da bi se tuple raztezale na več straneh. Zato je nemogoče neposredno shraniti zelo velike vrednosti polj. Za premagovanje te omejitve so velike vrednosti polj stisnjene in/ali razdeljene na več fizičnih linij. To se zgodi neopaženo za uporabnika in ima majhen vpliv na večino kode strežnika. Ta metoda je znana kot TOAST...

Pravzaprav za vsako tabelo z "potencialno velikimi" polji samodejno ustvarjena je seznanjena tabela z "rezanjem". vsak "velik" zapis v 2KB segmentih:

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

To je, če moramo napisati niz z "veliko" vrednostjo data, potem bo prišlo do pravega snemanja ne le za glavno mizo in njen PK, ampak tudi za TOAST in njen PK.

Zmanjšanje vpliva TOAST

Toda večina naših rekordov še vedno ni tako velika, naj ustreza 8KB - Kako lahko pri tem prihranim?..

Tu nam na pomoč priskoči atribut STORAGE v stolpcu tabele:

• RAZŠIRJENO omogoča stiskanje in ločeno shranjevanje. to standardna možnost za večino tipov podatkov, skladnih s TOAST. Najprej poskuša izvesti stiskanje, nato pa shrani izven tabele, če je vrstica še vedno prevelika.
• GLAVNI omogoča stiskanje, ne pa ločenega shranjevanja. (Pravzaprav se bo za take stolpce še vedno izvajalo ločeno shranjevanje, vendar le v skrajnem primeru, ko ni drugega načina za skrčenje niza, tako da se prilega strani.)

Pravzaprav je to točno tisto, kar potrebujemo za besedilo - stisnite ga čim bolj, in če se sploh ne prilega, ga dajte v TOAST. To je mogoče storiti neposredno z enim ukazom:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Kako oceniti učinek

Ker se tok podatkov spreminja vsak dan, ne moremo primerjati absolutnih številk, ampak relativno manjši delež Zapisali smo v ZDRAVLJICO – toliko bolje. A tu obstaja nevarnost – večji kot je »fizični« obseg vsakega posameznega zapisa, »širši« postane indeks, saj moramo pokriti več strani s podatki.

Oddelek pred spremembami:

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

Oddelek po spremembah:

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

Pravzaprav mi začel pisati TOAST 2-krat manj pogosto, ki je razbremenil ne samo disk, ampak tudi CPE:

Opozoril bom, da smo postali manjši tudi pri "branju" diska, ne le pri "pisanju" - saj moramo pri vstavljanju zapisa v tabelo "prebrati" tudi del drevesa vsakega indeksa, da ugotovimo njegov prihodnji položaj v njih.

Kdo lahko dobro živi na PostgreSQL 11

Po posodobitvi na PG11 smo se odločili nadaljevati »uglaševanje« TOAST in opazili, da je od te različice naprej parameter postal na voljo za uglaševanje toast_tuple_target:

Koda za obdelavo TOAST se sproži le, če je vrednost vrstice, ki naj se shrani v tabeli, večja od TOAST_TUPLE_THRESHOLD bajtov (običajno 2 KB). Koda TOAST bo stisnila in/ali premaknila vrednosti polj iz tabele, dokler vrednost vrstice ne postane manjša od TOAST_TUPLE_TARGET bajtov (vrednost spremenljivke, običajno tudi 2 KB) ali pa velikosti ni mogoče zmanjšati.

Odločili smo se, da so podatki, ki jih običajno imamo, "zelo kratki" ali "zelo dolgi", zato smo se odločili, da se omejimo na najmanjšo možno vrednost:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Poglejmo, kako so nove nastavitve vplivale na nalaganje diska po ponovni konfiguraciji:

Ni slabo! Povprečje čakalna vrsta na disku se je zmanjšala približno 1.5-krat, disk pa je "zaseden" 20 odstotkov! Mogoče pa je to nekako vplivalo na CPE?

Vsaj hujšega ni bilo. Čeprav je težko oceniti, ali tudi takšne količine še vedno ne morejo povečati povprečne obremenitve procesorja 5%.

Z menjavo mest členov se spremeni vsota...!

Kot veste, peni prihrani rubelj, in z našimi količinami skladiščenja je to približno 10TB/mesec že majhna optimizacija lahko prinese dober dobiček. Zato smo bili pozorni na fizično strukturo naših podatkov – kako natančno »naložena« polja znotraj zapisa vsako od tabel.

Ker zaradi usklajevanje podatkov to je naravnost naprej vpliva na nastalo glasnost:

Mnoge arhitekture zagotavljajo poravnavo podatkov na mejah strojnih besed. Na primer, v 32-bitnem sistemu x86 bodo cela števila (vrsta celega števila, 4 bajti) poravnana na 4-bajtni besedni meji, prav tako števila s plavajočo vejico z dvojno natančnostjo (plavajoča vejica z dvojno natančnostjo, 8 bajtov). V 64-bitnem sistemu pa bodo dvojne vrednosti poravnane z 8-bajtnimi besednimi mejami. To je še en razlog za nezdružljivost.

Zaradi poravnave je velikost vrstice tabele odvisna od vrstnega reda polj. Običajno ta učinek ni zelo opazen, vendar lahko v nekaterih primerih povzroči znatno povečanje velikosti. Na primer, če pomešate polja char(1) in celih števil, bodo med njimi običajno izgubljeni 3 bajci.

Začnimo s sintetičnimi modeli:

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

Od kod par dodatnih bajtov v prvem primeru? Preprosto je - 2-bajtni smallint poravnan na 4-bajtno mejo pred naslednjim poljem, in ko je zadnje, ni ničesar in ni treba poravnati.

V teoriji je vse v redu in polja lahko poljubno razporejate. Preverimo to na resničnih podatkih na primeru ene od tabel, katere dnevni del zavzema 10-15 GB.

Začetna struktura:

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)

Razdelek po spremembi vrstnega reda stolpcev - natančno ista polja, le drugačen vrstni red:

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)

Skupna prostornina odseka je določena s številom "dejstev" in je odvisna samo od zunanjih procesov, zato razdelimo velikost kupa (pg_relation_size) po številu zapisov v njem - to je, dobimo povprečna velikost dejansko shranjenega zapisa:

Minus 6% volumna, Super!

A vse seveda ni tako rožnato - navsezadnje v indeksih ne moremo spremeniti vrstnega reda polj, torej "na splošno" (pg_total_relation_size) ...

...še vedno tukaj prihranjeno 1.5 %ne da bi spremenili eno vrstico kode. ja, ja!

Opažam, da zgornja možnost za ureditev polj ni dejstvo, da je najbolj optimalna. Ker ne želite "raztrgati" nekaterih blokov polj iz estetskih razlogov - na primer nekaj (pack, recno), ki je PK za to tabelo.

Na splošno je določitev "minimalne" razporeditve polj dokaj preprosta "surova" naloga. Zato lahko iz svojih podatkov dobite še boljše rezultate kot naši – poskusite!

Vir: www.habr.com