Продължавайки темата за записването на големи количества данни, повдигната , в тази ще обсъдим методите, чрез които можем да намалим "физическия" размер на съхраняваните в PostgreSQL и тяхното влияние върху производителността на сървъра.
Ще говорим за настройките TOAST и подравняването на данните. "В средата" тези методи ще осигурят икономия на не твърде много ресурси, но — напълно без модифициране на кода на приложението.
Въпреки това, нашият опит се оказа доста продуктивен в това отношение, тъй като хранилището за почти всяко наблюдение по своята същност е по-голямата част append-only от гледна точка на записваните данни. И ако ви интересува как можем да накараме базата данни да пише на диск вместо 200MB/s половина по-малко — моля, последвайте ни.
Малки тайни на големите данни
По профила на работа , му редовно идват от логовете текстови пакети.
А тъй като , чиито БД наблюдаваме, — е многокомпонентен продукт със сложни структури от данни, така и заявките за постигане на максимална производителност излизат напълно такива . Така че обемът на всеки отделен екземпляр на заявка или резултатен план на изпълнение в постъпващия при нас лог се оказва "в средата" доста голям.
Нека да разгледаме структурата на една от таблиците, в която записваме "сурови" данни — тоест точно оригиналния текст от записа на лога:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ключ секции
date
, data -- най-важното
text
, PRIMARY KEY(pack, recno)
);Типичната такава таблица (вече секционирана, разбира се, затова това е шаблон за секцията), където най-важното е — текст. Понякога е достатъчно обширна.
Да припомним, че "физическият" размер на една запис в PG не може да заема повече от една страница данни, но "логическият" размер — е съвсем друго нещо. За да запишем в поле обемна стойност (varchar/text/bytea) се използва :
PostgreSQL използва фиксиран размер на страница (обикновено 8 КБ) и не позволява на кортежите да заемат няколко страници. Затова е невъзможно да се съхраняват много големи стойности на полета. За да се преодолее това ограничение, големите стойности на полета се компресират и/или се разделят на няколко физически реда. Това се случва незабелязано за потребителя и влияе незначително на голяма част от кода на сървъра. Този метод е известен като TOAST …
Всъщност, за всяка таблица с „потенциално големи“ полета автоматично на всяка „голяма“ запис с сегменти по 2KB:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
); Тоест, ако трябва да запишем ред с „голяма“ стойност data, то реалното записване ще се случи не само в основната таблица и нейния PK, но и в TOAST и неговия PK.
Намаляване на влиянието на TOAST
Но повечето записи при нас все пак не са толкова големи, в 8KB би трябвало да се поберат — как да спестим от това?..
Тук на помощ идва атрибутът на колоната на таблицата:
- EXTENDED позволява както компресия, така и отделно съхранение. Това е стандартният вариант за повечето типове данни, съвместими с TOAST. Първо се извършва опит за компресия, след това — запазване извън таблицата, ако редът все още е твърде голям.
- MAIN позволява компресия, но не и отделно съхранение. (Всъщност, отделно съхранение, все пак, ще бъде извършено за такива колони, но само като крайна мярка, когато няма друг начин да се намали реда, така че да се побере на страницата.)
Всъщност, това е точно това, от което имаме нужда за текст — максимално да се компресира, а ако все пак не се побира — да се изнесе в TOAST. Това може да се направи „на лету“, с една команда:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Как да оценим ефекта
Тъй като всеки ден потокът от данни се променя, не можем да сравняваме абсолютни цифри, но в относителните, колкото по-малка част записахме в TOAST — толкова по-добре. Но тук има опасност — колкото по-голям е „физическият“ обем на всяка отделна запись, толкова „по-широк“ става индексът, защото е необходимо да се покрият повече страници с данни.
Секция преди измененията:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Секция след измененията:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Всъщност, ние започнахме да записваме в TOAST два пъти по-рядко, което освободи не само диска, но и CPU:
Забелязвам, че ние също така започнахме да «четем» диска по-малко, а не само да «пишем» — тъй като при вмъкване на запис в някоя таблица се налага да «прочетем» и част от дървото на всеки един индекс, за да определим бъдещата му позиция в тях.
На кого в PostgreSQL 11 му е добре
След актуализацията до PG11 решихме да продължим с «настройките» на TOAST и обърнахме внимание, че от тази версия е достъпен за настройка параметър :
Кодът за обработка на TOAST се задейства само когато стойността на реда, която трябва да бъде запазена в таблицата, е по-голяма от TOAST_TUPLE_THRESHOLD байта (обикновено 2 Кб). Кодът на TOAST ще компресира и/или извежда стойностите от полето извън таблицата, докато стойността на реда не стане по-малка от TOAST_TUPLE_TARGET байта (променлива стойност, обикновено също 2 Кб) или намаляването на обема не стане невъзможно.
Решихме, че данните ни обикновено са или «съвсем къси», или веднага «много дълги», така че решихме да се ограничим до минимално възможната стойност:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Нека видим как новите настройки повлияха на натоварването на диска след пренастройката:
Не е лошо! Средната опашка към диска намаля приблизително 1.5 пъти, а «заетостта» на диска — с 20%! Но може би това е повлияло и на CPU?
Поне по-лошо определено не стана. Въпреки това, трудно е да се съди, ако дори такива обеми не могат да повишат средното натоварване на CPU над 5%.
От преподреждането на членовете на сумата... се променя!
Както знаем, стотинка спестява рубла, и при нашите обеми на съхранение около 10TB/месец дори и малка оптимизация би могла да донесе добри ползи. Затова обърнахме внимание на физическата структура на данните си — как точно «са подредени» полетата вътре в записа на всяка от таблиците.
Защото заради това пряко :
Много архитектури предвиждат изравняване на данните по границите на машинни думи. Например, на 32-битова система x86, цели числа (тип integer, заемащи 4 байта) ще бъдат изравнени по границата на 4-байтови думи, както и числа с плаваща точка двойна точност (тип double precision, 8 байта). А на 64-битова система стойностите double ще бъдат изравнени по границата на 8-байтови думи. Това е още една причина за несъвместимост.
Поради подравняването размерът на таблицата зависи от реда на подреждане на полетата. Обикновено този ефект не е много забележим, но в някои случаи може да доведе до съществено увеличение на размера. Например, ако смешваме полетата от тип char(1) и integer, между тях обикновено ще се загубят 3 байта.
Нека започнем със синтетични модели:
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 байтаОт къде са се взели две излишни байта в първия случай? Всичко е просто — 2-байтовият smallint се подравнява по 4-байтовата граница пред следващото поле, а когато е последен — няма какво да се подравнява и няма причина.
В теорията — всичко е наред и полето може да се пренарежда както пожелаем. Нека да проверим на реални данни, на примера на една от таблиците, чиято дневна секция заема по 10-15GB.
Началната структура:
CREATE TABLE public.plan_20190220
(
-- Наследено от таблицата plan: pack uuid NOT NULL,
-- Наследено от таблицата plan: recno smallint NOT NULL,
-- Наследено от таблицата plan: host uuid,
-- Наследено от таблицата plan: ts timestamp with time zone,
-- Наследено от таблицата plan: exectime numeric(32,3),
-- Наследено от таблицата plan: duration numeric(32,3),
-- Наследено от таблицата plan: bufint bigint,
-- Наследено от таблицата plan: bufmem bigint,
-- Наследено от таблицата plan: bufdsk bigint,
-- Наследено от таблицата plan: apn uuid,
-- Наследено от таблицата plan: ptr uuid,
-- Наследено от таблицата 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)Секция след промяна на реда на колоните — точно същите полета, само редът е различен:
CREATE TABLE public.plan_20190221
(
-- Наследено от таблицата plan: dt date NOT NULL,
-- Наследено от таблицата plan: ts timestamp with time zone,
-- Наследено от таблицата plan: pack uuid NOT NULL,
-- Наследено от таблицата plan: recno smallint NOT NULL,
-- Наследено от таблицата plan: host uuid,
-- Наследено от таблицата plan: apn uuid,
-- Наследено от таблицата plan: ptr uuid,
-- Наследено от таблицата plan: bufint bigint,
-- Наследено от таблицата plan: bufmem bigint,
-- Наследено от таблицата plan: bufdsk bigint,
-- Наследено от таблицата plan: exectime numeric(32,3),
-- Наследено от таблицата 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) Общото количество в секцията се определя от броя на "фактите" и зависи само от външните процеси, затова ще разделим размера на heap (pg_relation_size) на количество записи в нея — тоест получаваме среден размер на реално съхранявана запис:
Минус 6% обем, отлично!
Но всичко, разбира се, не е толкова радостно — нали в индексите порядъкът на полетата не можем да променим, а затова «в общи линии» (pg_total_relation_size)…
… все пак и тук съхранихме 1.5%, без да променим нито един ред код. Наистина!
Забелязвам, че представеният по-горе вариант на подредба на полетата — не е факт, че е най-оптимален. Защото някои блокове полета не искате да «разрушавате» вече на естетически основания — например, двойката (pack, recno), която е PK за тази таблица.
В общи линии, определението за «минимална» подредба на полетата — това е доста проста «переборна» задача. Затова можете с вашите данни да получите резултати дори по-добри от нашите — опитайте!
Източник: habr.com
