Продолжая тему записи больших потоков данных, поднятую предыдущей статьей про секционирование, в этой рассмотрим способы, которыми можно уменьшить «физический» размер хранимого в PostgreSQL, и их влияние на производительность сервера.
Речь пойдет про настройки TOAST и выравнивание данных. «В среднем» эти способы позволят сэкономить не слишком много ресурсов, зато — вообще без модификации кода приложения.
Однако, наш опыт оказался весьма продуктивным в этом плане, поскольку хранилищ�� почти любого мониторинга по своей природе является большей частью append-only с точки зрения записываемых данных. И если вам интересно, как можно научить базу писать на диск вместо 200MB/s вдвое меньше — прошу под кат.
По профилю работы нашего сервиса, ему регулярно прилетают из логов текстовые пакеты.
А поскольку комплекс СБИС, чьи БД мы мониторим, — это многокомпонентный продукт со сложными структурами данных, то и запросы для достижения максимальной производительности получаются вполне такими «многотомниками» со сложной алгоритмической логикой. Так что и объем каждого отдельного экземпляра запроса или результирующего плана выполнения в поступающем к нам логе оказывается «в среднем» достаточно большим.
Давайте посмотрим на структуру одной из таблиц, в которую мы пишем «сырые» данные — то есть вот прямо оригинальный текст из записи лога:
Типичная такая табличка (уже секционированная, безусловно, поэтому это — шаблон секции), где самое важное — текст. Иногда достаточно объемный.
Вспомним, что «физический» размер одной записи в PG не может занимать больше одной страницы данных, но «логический» размер — совсем другое дело. Чтобы записать в поле объемное значение (varchar/text/bytea) используется технология TOAST:
Фактически, для каждой таблицы с «потенциально большими» полями автоматически создается парная таблица с «нарезкой» каждой «большой» записи сегментами по 2KB:
То есть если нам приходится записывать строку с «большим» значением
Но большинство записей у нас все-таки не так уж и велики, в 8KB должны бы укладываться — как бы на этом сэкономить?..
Тут нам на помощь приходит атрибут
Поскольку каждый день поток данных меняется, мы не можем сравнивать абсолютные цифры, но в относительных чем меньшую долю мы записали в TOAST — тем лучше. Но тут есть опасность — чем больше у нас «физический» объем каждой отдельной записи, тем «шире» становится индекс, потому как приходится покрывать большее количество страниц данных.
Секция до изменений:
Секция после изменений:
Фактически, мы стали писать в TOAST в 2 раза реже, что разгрузило не только диск, но и CPU:
Замечу, что мы еще и «читать» диск стали меньше, не только «писать» — поскольку при вставке записи в какую-то таблицу приходится «вычитывать» еще и часть дерева каждого из индексов, чтобы определить ее будущую позицию в них.
После обновления до PG11 мы решили продолжить «тюнинг» TOAST и обратили внимание, что начиная с этой версии стал доступен для настройки параметр
Давайте посмотрим, как новые настройки сказались на загрузке диска после перенастройки:
Неплохо! Средняя очередь к диску сократилась примерно в 1.5 раза, а «занятость» диска — процентов на 20! Но может, это как-то сказалось на CPU?
По крайней мере, хуже точно не стало. Хотя, сложно судить, если даже такие объемы все равно не могут поднять среднюю загрузку CPU выше 5%.
Как известно, копейка рубль бережет, и при наших объемах хранения порядка 10TB/месяц даже небольшая оптимизация способна дать неплохой профит. Поэтому мы обратили внимание на физическую структуру своих данных — как конкретно «уложены» поля внутри записи каждой из таблиц.
Потому что из-за выравнивания данных это впрямую влияет на результирующий объем:
Давайте начнем с синтетических моделей:
Откуда набежала пара лишних байт в первом случае? Все просто — 2-байтовый smallint выравнивается по 4-байтовой границе перед следующим полем, а когда стоит последним — выравнивать нечего и незачем.
В теории — все хорошо и можно переставлять поля как угодно. Давайте проверим на реальных данных на примере одной из таблиц, суточная секция которой занимает по 10-15GB.
Исходная структура:
Секция после смены порядка столбцов — ровно те же поля, только порядок другой:
Общий объем секции определяется количеством «фактов» и зависит только от внешних процессов, поэтому поделим размер heap (
Минус 6% объема, отлично!
Но все, конечно, не настолько радужно — ведь в индексах-то порядок полей мы изменить не можем, а поэтому «в целом» (
… все-таки и тут сэкономили 1.5%, не изменив ни строчки кода. Таки да!
Замечу, что приведенный выше вариант расстановки полей — не факт, что самый оптимальный. Потому что некоторые блоки полей не хочется «разрывать» уже по эстетическим соображениям — например, пару
В целом же, определение «минимальной» расстановки полей — это достаточно простая «переборная» задача. Поэтому вы можете на своих данных получить результаты даже лучше, чем у нас — попробуйте!
Речь пойдет про настройки 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 у столбца таблицы:Фактически, это ровно то, что нам нужно для текста — максимально сжать, и уж если совсем никак не влезло — вынести в TOAST. Сделать это можно прямо «на лету», одной командой:
- EXTENDED допускает как сжатие, так и отдельное хранение. Это стандартный вариант для большинства типов данных, совместимых с TOAST. Сначала происходит попытка выполнить сжатие, затем — сохранение вне таблицы, если строка всё ещё слишком велика.
- MAIN допускает сжатие, но не отдельное хранение. (Фактически, отдельное хр��нение, тем не менее, будет выполнено для таких столбцов, но лишь как крайняя мера, когда нет другого способа уменьшить строку так, чтобы она помещалась на странице.)
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 для этой таблицы.В целом же, определение «минимальной» расстановки полей — это достаточно простая «переборная» задача. Поэтому вы можете на своих данных получить результаты даже лучше, чем у нас — попробуйте!
