Ušetřete penny na velkých objemech v PostgreSQL

Pokračování tématu záznamu velkých datových toků, které vyvolal předchozí článek o rozdělení, v tomto se podíváme na způsoby, jakými můžete snížit „fyzickou“ velikost uložených v PostgreSQL a jejich vliv na výkon serveru.

Budeme mluvit o Nastavení TOAST a zarovnání dat. „V průměru“ tyto metody neušetří příliš mnoho zdrojů, ale bez úpravy kódu aplikace.

Naše zkušenosti se však v tomto ohledu ukázaly jako velmi produktivní, protože ukládání téměř jakéhokoli monitorování je ze své podstaty většinou pouze append-only z hlediska zaznamenaných dat. A pokud vás zajímá, jak můžete naučit databázi místo toho zapisovat na disk 200MB / s o polovinu méně - prosím pod kat.

Malá tajemství velkých dat

Podle pracovního profilu naše služba, pravidelně k němu létají z pelíšků textové balíčky.

A od té doby komplex VLSIjehož databáze sledujeme je vícesložkový produkt se složitými datovými strukturami, dále pak dotazy pro maximální výkon dopadnout docela takhle „multi-volume“ se složitou algoritmickou logikou. Takže objem každé jednotlivé instance požadavku nebo výsledného plánu realizace v protokolu, který k nám přichází, se ukazuje být „v průměru“ poměrně velký.

Podívejme se na strukturu jedné z tabulek, do které zapisujeme „raw“ data – tedy zde je původní text ze záznamu logu:

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

Typický znak (již samozřejmě rozdělený, takže se jedná o šablonu oddílu), kde je nejdůležitější text. Někdy docela objemné.

Připomeňme, že „fyzická“ velikost jednoho záznamu v PG nemůže zabírat více než jednu stránku dat, ale „logická“ velikost je úplně jiná věc. Chcete-li zapsat objemovou hodnotu (varchar/text/bytea) do pole, použijte Technologie TOAST:

PostgreSQL používá pevnou velikost stránky (typicky 8 KB) a neumožňuje n-ticím zabírat více stránek. Proto je nemožné přímo ukládat velmi velké hodnoty polí. K překonání tohoto omezení jsou velké hodnoty pole komprimovány a/nebo rozděleny do více fyzických linek. To se děje bez povšimnutí uživatele a má malý dopad na většinu kódu serveru. Tato metoda je známá jako TOAST...

Ve skutečnosti pro každou tabulku s "potenciálně velkými" poli automaticky je vytvořena spárovaná tabulka s „krájením“. každý „velký“ záznam v 2KB segmentech:

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

Tedy pokud musíme napsat řetězec s „velkou“ hodnotou data, pak dojde ke skutečnému záznamu nejen na hlavní stůl a jeho PK, ale i na TOAST a jeho PK.

Snížení vlivu TOASTu

Ale většina našich záznamů stále není tak velká, by se měl vejít do 8 kB - Jak na tom mohu ušetřit?...

Zde nám tento atribut přichází na pomoc STORAGE ve sloupci tabulky:

• ROZŠÍŘENÁ umožňuje kompresi i samostatné ukládání. Tento standardní verze pro většinu datových typů kompatibilních s TOAST. Nejprve se pokusí provést kompresi, a pokud je řádek stále příliš velký, uloží jej mimo tabulku.
• HLAVNÍ umožňuje kompresi, ale ne oddělené ukládání. (Ve skutečnosti bude pro takové sloupce stále prováděno samostatné ukládání, ale pouze jako poslední možnost, když neexistuje žádný jiný způsob, jak zmenšit řetězec tak, aby se vešel na stránku.)

Ve skutečnosti je to přesně to, co pro text potřebujeme - co nejvíce jej zmáčkněte, a pokud se vůbec nevejde, vložte jej do TOASTU. To lze provést přímo za běhu jedním příkazem:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Jak vyhodnotit účinek

Jelikož se datový tok mění každý den, nemůžeme porovnávat absolutní čísla, ale relativní čísla menší podíl Zapsali jsme to do TOASTU – tím lépe. Zde však existuje nebezpečí – čím větší je „fyzický“ objem každého jednotlivého záznamu, tím „širší“ se index stává, protože musíme pokrýt více stránek dat.

část před změnami:

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

část po změnách:

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

Ve skutečnosti my začal psát na TOAST 2krát méně často, který vyložil nejen disk, ale i CPU:

Podotýkám, že jsme se také zmenšili ve „čtení“ disku, nejen ve „zápisu“ – protože při vkládání záznamu do tabulky musíme také „číst“ část stromu každého indexu, abychom určili jeho budoucí postavení v nich.

Kdo může dobře žít na PostgreSQL 11

Po aktualizaci na PG11 jsme se rozhodli pokračovat v „ladění“ TOAST a všimli jsme si, že počínaje touto verzí byl parametr k dispozici pro ladění toast_tuple_target:

Kód zpracování TOAST se spustí pouze tehdy, když je hodnota řádku, která má být uložena v tabulce, větší než TOAST_TUPLE_THRESHOLD bajtů (obvykle 2 KB). Kód TOAST bude komprimovat a/nebo přesouvat hodnoty polí z tabulky, dokud nebude hodnota řádku menší než TOAST_TUPLE_TARGET bajtů (proměnná hodnota, také obvykle 2 KB) nebo velikost nebude možné zmenšit.

Rozhodli jsme se, že data, která obvykle máme, jsou buď „velmi krátká“ nebo „velmi dlouhá“, takže jsme se rozhodli omezit na minimální možnou hodnotu:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Podívejme se, jak nová nastavení ovlivnila načítání disku po rekonfiguraci:

Není špatné! Průměrný fronta na disk se snížila přibližně 1.5krát a disk je „zaneprázdněn“ z 20 procent! Ale možná to nějak ovlivnilo CPU?

Alespoň se to nezhoršilo. Je však těžké posoudit, zda ani takové objemy stále nemohou zvýšit průměrné zatížení CPU 5%.

Změnou míst pojmů se součet... mění!

Jak víte, cent ušetří rubl a s našimi skladovacími objemy je to o tom 10 TB/měsíc i malá optimalizace může přinést dobrý zisk. Proto jsme věnovali pozornost fyzické struktuře našich dat – jak přesně „skládaných“ polí uvnitř záznamu každý ze stolů.

Protože kvůli zarovnání dat to je přímo vpřed ovlivňuje výsledný objem:

Mnoho architektur poskytuje zarovnání dat na hranicích strojových slov. Například na 32bitovém systému x86 budou celá čísla (typ celého čísla, 4 bajty) zarovnána na hranici slova se 4 bajty, stejně jako čísla s plovoucí desetinnou čárkou s dvojitou přesností (s plovoucí desetinnou čárkou s dvojitou přesností, 8 bajtů). A na 64bitovém systému budou dvojité hodnoty zarovnány s 8bajtovými hranicemi slov. To je další důvod nekompatibility.

Kvůli zarovnání závisí velikost řádku tabulky na pořadí polí. Obvykle tento efekt není příliš patrný, ale v některých případech může vést k výraznému zvětšení velikosti. Pokud například smícháte char(1) a celočíselná pole, obvykle mezi nimi budou 3 bajty.

Začněme syntetickými modely:

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

Odkud se v prvním případě vzalo pár bajtů navíc? Je to jednoduché - 2bajtový smallint zarovnaný na 4bajtové hranici před dalším polem, a když je to poslední, není nic a není potřeba zarovnávat.

Teoreticky je vše v pořádku a pole si můžete přeskládat, jak chcete. Pojďme si to ověřit na reálných datech na příkladu jedné z tabulek, jejíž denní část zabírá 10-15GB.

Počáteční 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)

Sekce po změně pořadí sloupců - přesně stejná pole, jen jiné pořadí:

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)

Celkový objem sekce je dán počtem „faktů“ a závisí pouze na externích procesech, takže rozdělme velikost haldy (pg_relation_size) počtem záznamů v něm - tedy dostaneme průměrná velikost aktuálně uloženého záznamu:

Mínus 6 % objemu, Skvělý!

Ale všechno samozřejmě není tak růžové - koneckonců, v indexech nemůžeme měnit pořadí polí, a tedy „obecně“ (pg_total_relation_size) ...

...ještě tady taky ušetřeno 1.5 %bez změny jediného řádku kódu. Ano ano!

Podotýkám, že výše uvedená možnost uspořádání polí není tím, že je nejoptimálnější. Protože nechcete „trhat“ některé bloky polí z estetických důvodů - například pár (pack, recno), což je PK pro tuto tabulku.

Obecně je určení „minimálního“ uspořádání polí poměrně jednoduchý úkol „hrubé síly“. Proto můžete ze svých dat získat ještě lepší výsledky než naše – zkuste to!

Zdroj: www.habr.com