Эканомім капейчыну на вялікіх аб'ёмах у PostgreSQL

Працягваючы тэму запісу вялікіх патокаў дадзеных, паднятую папярэднім артыкулам пра секцыянаванне, у гэтай разгледзім спосабы, якімі можна паменшыць «фізічны» памер захоўваемага у 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) выкарыстоўваецца тэхналогія TOAST:

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 павінны б укладвацца - як бы на гэтым зэканоміць?..

Тут нам на дапамогу прыходзіць атрыбут. STORAGE у слупка табліцы:

• EXTENDED дапускае як сціск, так і асобнае захоўванне. Гэта стандартны варыянт для большасці тыпаў даных, сумяшчальных з TOAST. Спачатку адбываецца спроба выканаць сціск, затым - захаванне па-за табліцай, калі радок усё яшчэ занадта вялікая.
• ГАЛОЎНАЯ дапускае сціск, але не асобнае захоўванне. (Фактычна, асобнае захоўванне, тым не менш, будзе выканана для такіх слупкоў, але толькі як крайняя мера, калі няма іншага спосабу паменшыць радок так, каб яна змяшчалася на старонцы.)

Фактычна, гэта роўна тое, што нам трэба для тэксту. максімальна сціснуць, і ўжо калі зусім ніяк не ўлезла - вынесці ў 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 у 2 разы радзей, што разгрузіла не толькі дыск, але і CPU:

Заўважу, што мы яшчэ і "чытаць" дыск сталі менш, не толькі "пісаць" - паколькі пры ўстаўцы запісу ў нейкую табліцу даводзіцца "вычытваць" яшчэ і частка дрэва кожнага з індэксаў, каб вызначыць яе будучую пазіцыю ў іх.

Каму на PostgreSQL 11 жыць добра

Пасля абнаўлення да PG11 мы вырашылі працягнуць «цюнінг» TOAST і звярнулі ўвагу, што пачынаючы з гэтай версіі стаў даступны для наладкі параметр toast_tuple_target:

Код апрацоўкі 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
(
-- Унаследована 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)

Секцыя пасля змены парадку слупкоў - роўна тыя ж палі, толькі парадак іншы:

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)

Агульны аб'ём секцыі вызначаецца колькасцю фактаў і залежыць толькі ад знешніх працэсаў, таму падзелім памер heap (pg_relation_size) на колькасць запісаў у ёй - гэта значыць атрымаем сярэдні памер рэальнага захоўваемага запісу:

Мінус 6% аб'ёму, выдатна!

Але ўсё, вядома, не настолькі вясёлкава - бо у індэксах-то парадак палёў мы змяніць не можам, а таму «ў цэлым» (pg_total_relation_size) ...

… усё ж і тут зэканомілі 1.5%, не змяніўшы ні радкі кода. Такі так!

Заўважу, што прыведзены вышэй варыянт расстаноўкі палёў - не факт, што самы аптымальны. Таму што некаторыя блокі палёў не хочацца «разрываць» ужо па эстэтычных меркаваннях - напрыклад, пару (pack, recno), Якая з'яўляецца PK для гэтай табліцы.

У цэлым жа, вызначэнне "мінімальнай" расстаноўкі палёў - гэта дастаткова простая "пераборная" задача. Таму вы можаце на сваіх дадзеных атрымаць вынікі нават лепш, чым у нас - паспрабуйце!

Крыніца: habr.com