Заощаджуємо копієчку на великих обсягах у PostgreSQL

Продовжуючи тему запису великих потоків даних, підняту попередньою статтею про секціонування, у цій розглянемо способи, якими можна зменшити «фізичний» розмір збереженого у PostgreSQL, та їх вплив на продуктивність сервера.

Йтиметься про налаштування TOAST та вирівнювання даних. «У середньому» ці способи дозволять заощадити не надто багато ресурсів, зате взагалі без модифікації коду програми.

Однак, наш досвід виявився досить продуктивним у цьому плані, оскільки сховище майже будь-якого моніторингу за своєю природою є. здебільшого append-only з точки зору даних, що записуються. І якщо вам цікаво, як можна навчити базу писати на диск замість 200MB / с удвічі менше – прошу під кат.

Маленькі секрети великих даних

За профілем роботи нашого сервісу, йому регулярно прилітають із ліг текстові пакети.

А оскільки комплекс НВІС, чиї БД ми моніторимо, це багатокомпонентний продукт зі складними структурами даних, то й запити для досягнення максимальної продуктивності виходять цілком такими «багатотомниками» зі складною алгоритмічною логікою. Так що й обсяг кожного окремого екземпляра запиту або результуючого плану виконання в лозі, що надходить до нас, виявляється «в середньому» досить великим.

Давайте подивимося на структуру однієї з таблиць, в яку ми пишемо «сирі» дані — тобто прямо оригінальний текст із запису лога:

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 у стовпця таблиці:

• ПОВЕРНЕНІ допускає як стиск, і окреме зберігання. Це стандартний варіант для більшості типів даних, сумісних із 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