Pokračovanie v téme zaznamenávania veľkých dátových tokov, ktorú nastolil , v tomto sa pozrieme na spôsoby, akými môžete zmenšiť „fyzickú“ veľkosť uložených v PostgreSQL a ich vplyv na výkon servera.
Porozprávame sa o Nastavenia TOAST a zarovnanie údajov. „V priemere“ tieto metódy neušetria príliš veľa zdrojov, ale bez úpravy kódu aplikácie.
Naša skúsenosť sa však v tomto ohľade ukázala ako veľmi produktívna, keďže ukladanie takmer akéhokoľvek monitorovania svojou povahou je väčšinou len priložené z hľadiska zaznamenaných údajov. A ak vás zaujíma, ako môžete namiesto toho naučiť databázu zapisovať na disk 200MB / s o polovicu menej - prosím pod kat.
Malé tajomstvá veľkých dát
Podľa pracovného profilu , pravidelne k nemu prilietajú z pelechov textové balíky.
A odkedy ktorej databázu sledujeme je viaczložkový produkt s komplexnými dátovými štruktúrami, následne dotazmi pre maximálny výkon dopadnúť celkom takto . Takže objem každej jednotlivej inštancie požiadavky alebo výsledného plánu vykonávania v protokole, ktorý k nám prichádza, sa ukazuje byť „v priemere“ dosť veľký.
Pozrime sa na štruktúru jednej z tabuliek, do ktorej zapisujeme „surové“ údaje – to znamená, že tu je pôvodný text zo záznamu protokolu:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ключ секции
date
, data -- самое главное
text
, PRIMARY KEY(pack, recno)
);Typický znak (samozrejme už rozsekaný, takže ide o predlohu sekcie), kde je najdôležitejší text. Niekedy dosť objemné.
Pripomeňme, že „fyzická“ veľkosť jedného záznamu v PG nemôže zaberať viac ako jednu stranu údajov, ale „logická“ veľkosť je úplne iná záležitosť. Ak chcete zapísať objemovú hodnotu (varchar/text/bytea) do poľa, použite :
PostgreSQL používa pevnú veľkosť stránky (zvyčajne 8 KB) a neumožňuje, aby n-tice zaberali viacero stránok. Preto nie je možné priamo ukladať veľmi veľké hodnoty polí. Na prekonanie tohto obmedzenia sú veľké hodnoty polí komprimované a/alebo rozdelené na viacero fyzických riadkov. Stáva sa to bez povšimnutia používateľa a má malý vplyv na väčšinu serverového kódu. Táto metóda je známa ako TOAST...
V skutočnosti pre každú tabuľku s „potenciálne veľkými“ poliami automaticky každý „veľký“ záznam v 2KB segmentoch:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Teda ak musíme napísať reťazec s „veľkou“ hodnotou data, potom sa uskutoční skutočné nahrávanie nielen na hlavný stôl a jeho PK, ale aj na TOAST a jeho PK.
Zníženie vplyvu TOAST
Ale väčšina našich záznamov stále nie je taká veľká, by sa mal zmestiť do 8 kB - Ako na tom môžem ušetriť?...
Tu nám príde na pomoc atribút v stĺpci tabuľky:
- PREDĹŽENÁ umožňuje kompresiu aj samostatné ukladanie. Toto štandardná možnosť pre väčšinu typov údajov kompatibilných s TOAST. Najprv sa pokúsi vykonať kompresiu a potom ju uloží mimo tabuľky, ak je riadok stále príliš veľký.
- HLAVNÁ umožňuje kompresiu, ale nie oddelené ukladanie. (V skutočnosti sa pre takéto stĺpce bude stále vykonávať samostatné ukladanie, ale iba ako posledná možnosť, keď neexistuje iný spôsob, ako zmenšiť reťazec tak, aby sa zmestil na stránku.)
V skutočnosti je to presne to, čo potrebujeme pre text - komprimujte ho čo najviac a ak sa vôbec nezmestí, vložte ho do TOASTU. Dá sa to urobiť priamo za behu jedným príkazom:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Ako vyhodnotiť účinok
Keďže dátový tok sa mení každý deň, nemôžeme porovnávať absolútne čísla, ale relatívne menší podiel Zapísali sme si to do TOASTu – tým lepšie. Je tu však nebezpečenstvo – čím väčší je „fyzický“ objem každého jednotlivého záznamu, tým „širší“ sa index stáva, pretože musíme pokryť viac strán údajov.
časť pred zmenami:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
časť po zmenách:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)V skutočnosti my začal písať TOAST 2 krát menej často, ktorý vyložil nielen disk, ale aj CPU:
Podotýkam, že sme sa zmenšili aj pri „čítaní“ disku, nielen pri „zápise“ – keďže pri vkladaní záznamu do tabuľky musíme „čítať“ aj časť stromu každého indexu, aby sme určili jeho budúce postavenie v nich.
Kto môže dobre žiť na PostgreSQL 11
Po aktualizácii na PG11 sme sa rozhodli pokračovať v „ladení“ TOAST a všimli sme si, že od tejto verzie je parameter dostupný na ladenie :
Kód spracovania TOAST sa spustí iba vtedy, keď je hodnota riadka, ktorá sa má uložiť do tabuľky, väčšia ako TOAST_TUPLE_THRESHOLD bajtov (zvyčajne 2 KB). Kód TOAST bude komprimovať a/alebo presúvať hodnoty polí z tabuľky, kým hodnota riadka nebude menšia ako TOAST_TUPLE_TARGET bajtov (premenná hodnota, zvyčajne tiež 2 KB) alebo sa veľkosť nedá zmenšiť.
Rozhodli sme sa, že údaje, ktoré zvyčajne máme, sú buď „veľmi krátke“ alebo „veľmi dlhé“, preto sme sa rozhodli obmedziť na minimálnu možnú hodnotu:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Pozrime sa, ako nové nastavenia ovplyvnili načítanie disku po rekonfigurácii:
Nie zlé! Priemerná front na disk sa zmenšil približne 1.5-krát a disk je „zaneprázdnený“ na 20 percent! Ale možno to nejako ovplyvnilo CPU?
Aspoň sa to nezhoršilo. Je však ťažké posúdiť, či ani takéto objemy stále nedokážu zvýšiť priemernú záťaž CPU 5%.
Zmenou miest pojmov sa mení súčet...!
Ako viete, cent ušetrí rubeľ a pri našich objemoch úložiska je to o tom 10 TB/mesiac aj malá optimalizácia môže priniesť dobrý zisk. Preto sme venovali pozornosť fyzickej štruktúre našich dát – ako presne „naskladané“ polia vo vnútri záznamu každý zo stolov.
Pretože kvôli toto je priamočiare :
Mnohé architektúry poskytujú zarovnanie údajov na hraniciach strojových slov. Napríklad v 32-bitovom systéme x86 budú celé čísla (typ celého čísla, 4 bajty) zarovnané na 4-bajtovej hranici slova, rovnako ako čísla s pohyblivou rádovou čiarkou s dvojitou presnosťou (s pohyblivou rádovou čiarkou s dvojitou presnosťou, 8 bajtov). A na 64-bitovom systéme budú dvojité hodnoty zarovnané s 8-bajtovými hranicami slov. To je ďalší dôvod nekompatibility.
Kvôli zarovnaniu závisí veľkosť riadka tabuľky od poradia polí. Zvyčajne tento efekt nie je príliš nápadný, ale v niektorých prípadoch môže viesť k výraznému zvýšeniu veľkosti. Ak napríklad zmiešate polia typu char(1) a celočíselné, zvyčajne sa medzi nimi stratia 3 bajty.
Začnime syntetickými modelmi:
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 байтOdkiaľ sa v prvom prípade vzalo pár bajtov navyše? Je to jednoduché - 2-bajtový smallint zarovnaný na 4-bajtovej hranici pred ďalším poľom a keď je to posledné, nie je potrebné nič a nie je potrebné zarovnávať.
Teoreticky je všetko v poriadku a polia si môžete preusporiadať, ako chcete. Overme si to na reálnych dátach na príklade jednej z tabuliek, ktorej denná časť zaberá 10-15GB.
Počiatočná štruktúra:
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)Sekcia po zmene poradia stĺpcov - presne rovnaké polia, len iné poradie:
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 sekcie je určený počtom „faktov“ a závisí len od externých procesov, takže rozdeľme veľkosť haldy (pg_relation_size) počtom záznamov v ňom - teda dostaneme priemerná veľkosť aktuálne uloženého záznamu:
Mínus 6% objemu, Skvelé!
Ale všetko, samozrejme, nie je také ružové - koniec koncov, v indexoch nemôžeme meniť poradie polí, a teda „všeobecne“ (pg_total_relation_size) ...
...ešte aj tu ušetrilo 1.5%bez zmeny jediného riadku kódu. Áno áno!
Poznamenávam, že vyššie uvedená možnosť usporiadania polí nie je skutočnosťou, že je najoptimálnejšia. Pretože nechcete „trhať“ niektoré bloky polí z estetických dôvodov - napríklad pár (pack, recno), čo je PK pre túto tabuľku.
Vo všeobecnosti je určenie „minimálneho“ usporiadania polí pomerne jednoduchou úlohou „hrubej sily“. Preto môžete zo svojich údajov získať ešte lepšie výsledky ako tie naše – skúste to!
Zdroj: hab.com