Экономим копеечку на больших объемах в 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. Сначала происходит попытка выполнить сжатие, затем — сохранение вне таблицы, если строка всё ещё слишком велика.
• 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 в 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