Takarítson meg egy fillért nagy mennyiségű PostgreSQL-ben

által felvetett nagy adatfolyamok rögzítésének témáját folytatva korábbi cikk a particionálásról, ebben megvizsgáljuk, milyen módokon lehet csökkentse a tárolt „fizikai” méretét PostgreSQL-ben, és ezek hatása a szerver teljesítményére.

Majd megbeszéljük TOAST beállítások és adatigazítás. „Átlagosan” ezek a módszerek nem takarítanak meg túl sok erőforrást, de az alkalmazás kódjának módosítása nélkül.

Tapasztalataink azonban e tekintetben igen termékenynek bizonyultak, hiszen természeténél fogva szinte bármilyen monitorozás tárolása az többnyire csak hozzáfűzhető a rögzített adatok tekintetében. És ha kíváncsi arra, hogyan taníthatja meg az adatbázist lemezre írásra 200MB / s fele annyi - kérem a kat.

A nagy adatok kis titkai

Munkaprofil szerint a mi szolgáltatásunk, rendszeresen repülnek hozzá az odúkból szöveges csomagok.

És azóta VLSI komplexamelynek adatbázisát figyeljük egy többkomponensű termék összetett adatstruktúrákkal, majd lekérdezésekkel a maximális teljesítmény érdekében egészen így alakul „többkötetes” összetett algoritmikus logikával. Így a hozzánk érkező naplóban a kérés minden egyes példányának mennyisége vagy az eredményül kapott végrehajtási terv „átlagosan” meglehetősen nagynak bizonyul.

Nézzük meg az egyik tábla szerkezetét, amelybe „nyers” adatokat írunk - vagyis itt van az eredeti szöveg a naplóbejegyzésből:

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

Tipikus tábla (persze már tagolt, tehát ez egy szakaszsablon), ahol a legfontosabb a szöveg. Néha elég terjedelmes.

Emlékezzünk vissza, hogy egy PG-ben egy rekord „fizikai” mérete nem foglalhat el több adatoldalt, de a „logikai” mérete teljesen más kérdés. Ha térfogati értéket (varchar/text/bytea) szeretne írni egy mezőbe, használja a TOAST technológia:

A PostgreSQL rögzített oldalméretet (általában 8 KB) használ, és nem engedi, hogy a sorok több oldalra terjedjenek ki. Ezért lehetetlen nagyon nagy mezőértékeket közvetlenül tárolni. Ennek a korlátozásnak a kiküszöbölése érdekében a nagy mezőértékeket tömörítik és/vagy több fizikai vonalra osztják fel. Ez a felhasználó által észrevétlenül történik, és csekély hatással van a legtöbb szerverkódra. Ez a módszer TOAST néven ismert...

Valójában minden "potenciálisan nagy" mezőket tartalmazó táblához automatikusan párosított táblázat jön létre a „szeleteléssel”. minden „nagy” rekord 2 KB szegmensekben:

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

Vagyis ha „nagy” értékű karakterláncot kell írnunk data, akkor megtörténik az igazi felvétel nem csak a főtáblára és annak PK-jára, hanem a TOAST-ra és annak PK-jára is.

A TOAST hatásának csökkentése

De a legtöbb lemezünk még mindig nem olyan nagy, bele kell férnie a 8KB-ba - Hogyan spórolhatok ezen?...

Itt jön a segítségünkre az attribútum STORAGE a táblázat oszlopában:

• KITERJEDT lehetővé teszi a tömörítést és a külön tárolást is. Ez standard opció a legtöbb TOAST-kompatibilis adattípushoz. Először megpróbálja végrehajtani a tömörítést, majd a táblázaton kívül tárolja, ha a sor még mindig túl nagy.
• FŐ lehetővé teszi a tömörítést, de nem a külön tárolást. (Valójában az ilyen oszlopokhoz továbbra is külön tárolás történik, de csak utolsó lehetőségként, amikor nincs más lehetőség a karakterlánc zsugorítására, hogy elférjen az oldalon.)

Valójában pontosan erre van szükségünk a szöveghez - lehetőleg összenyomkodjuk, és ha egyáltalán nem illik, akkor TOAST-ba tesszük. Ez megtehető közvetlenül menet közben, egyetlen paranccsal:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Hogyan értékeljük a hatást

Mivel az adatfolyam napról napra változik, nem abszolút számokat tudunk összehasonlítani, hanem relatív módon kisebb arányban TOAST-ban írtuk le – annál jobb. De itt van egy veszély - minél nagyobb az egyes rekordok „fizikai” térfogata, annál „szélesebb” lesz az index, mert több oldalnyi adatot kell lefednünk.

Szakasz változások előtt:

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

Szakasz változtatások után:

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

Valójában mi 2-szer ritkábban kezdett el írni a TOAST-nak, amely nemcsak a lemezt, hanem a CPU-t is kirakta:

Megjegyzem, a lemez „olvasásában” is kisebbek lettünk, nem csak „írásban” – hiszen egy rekord táblába történő beillesztésekor az egyes indexek fájának egy részét is „el kell olvasnunk” ahhoz, hogy meghatározzuk a rekordot. jövőbeli pozíciójuk bennük.

Ki tud jól élni a PostgreSQL 11-en?

A PG11-re való frissítés után úgy döntöttünk, hogy folytatjuk a TOAST „hangolását”, és észrevettük, hogy ettől a verziótól kezdve a paraméter elérhetővé vált a hangoláshoz toast_tuple_target:

A TOAST feldolgozókód csak akkor aktiválódik, ha a táblázatban tárolandó sorérték nagyobb, mint TOAST_TUPLE_THRESHOLD bájt (általában 2 KB). A TOAST kód addig tömöríti és/vagy kimozdítja a mező értékeket a táblázatból, amíg a sor értéke kisebb lesz TOAST_TUPLE_TARGET bájtnál (változó érték, általában 2 KB), vagy a méret nem csökkenthető.

Úgy döntöttünk, hogy a rendelkezésünkre álló adatok általában „nagyon rövidek” vagy „nagyon hosszúak”, ezért úgy döntöttünk, hogy a lehető legkisebb értékre szorítkozunk:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Nézzük meg, hogyan befolyásolták az új beállítások a lemez betöltését az újrakonfigurálás után:

Nem rossz! Átlagos csökkent a sor a lemezhez körülbelül 1.5-szer, és a lemez „foglalt” 20 százalék! De lehet, hogy ez valahogy befolyásolta a CPU-t?

Legalább nem lett rosszabb. Bár nehéz megítélni, hogy még ilyen mennyiségek sem tudják-e magasabbra emelni az átlagos CPU-terhelést 5%.

A feltételek helyének megváltoztatásával az összeg... változik!

Mint tudják, egy fillér megtakarít egy rubelt, és a mi tárhelyünkkel kb 10TB/hónap még egy kis optimalizálás is jó profitot hozhat. Ezért odafigyeltünk adataink fizikai felépítésére – hogyan is pontosan „halmozott” mezők a rekordon belül az egyes asztalok.

Mert amiatt adatigazítás ez egyenesen előre szól befolyásolja a kapott hangerőt:

Sok architektúra adatigazítást biztosít a gépi szóhatárokon. Például egy 32 bites x86 rendszeren az egész számok (egész típusú, 4 bájt) egy 4 bájtos szóhatárhoz lesznek igazítva, csakúgy, mint a dupla pontosságú lebegőpontos számok (dupla pontosságú lebegőpontos, 8 bájt). Egy 64 bites rendszeren pedig a dupla értékek a 8 bájtos szóhatárokhoz lesznek igazítva. Ez egy másik oka az összeférhetetlenségnek.

Az igazítás miatt egy táblázat sorának mérete a mezők sorrendjétől függ. Általában ez a hatás nem nagyon észrevehető, de bizonyos esetekben jelentős méretnövekedéshez vezethet. Például, ha keveri a char(1)-et és az egész mezőket, általában 3 bájt veszteségbe kerül közöttük.

Kezdjük a szintetikus modellekkel:

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

Honnan jött pár plusz bájt az első esetben? Ez egyszerű - 2 bájtos smallint a 4 bájtos határon igazítva a következő mező előtt, és amikor az utolsó, akkor nincs semmi és nem kell igazítani.

Elméletileg minden rendben van, és tetszés szerint átrendezheti a mezőket. Nézzük meg valós adatokon az egyik táblázat példáján, amelynek napi része 10-15 GB-ot foglal el.

Kezdeti szerkezet:

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)

Az oszlopsorrend megváltoztatása utáni szakasz - pontosan ugyanazok a mezők, csak más sorrend:

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)

A szakasz teljes térfogatát a „tények” száma határozza meg, és csak külső folyamatoktól függ, ezért osszuk el a kupac méretét (pg_relation_size) a benne lévő rekordok számával – vagyis azt kapjuk a ténylegesen tárolt rekord átlagos mérete:

Mínusz 6% térfogat, Nagy!

De persze nem minden olyan rózsás – elvégre indexekben a mezők sorrendjét nem tudjuk megváltoztatni, és ezért „általában” (pg_total_relation_size) ...

...még itt is 1.5% megtakarítása kód egyetlen sorának megváltoztatása nélkül. Igen igen!

Megjegyzem, hogy a mezők elrendezésének fenti lehetősége nem a legoptimálisabb. Mert esztétikai okokból nem szeretne néhány mezőtömböt – például egy párat – „tépni”. (pack, recno), amely ennek a táblázatnak a PK-ja.

Általánosságban elmondható, hogy a mezők „minimális” elrendezésének meghatározása meglehetősen egyszerű „brute force” feladat. Ezért adataiból még jobb eredményeket érhet el, mint a miénk – próbálja ki!

Forrás: will.com