что такое партиционирование таблиц
ez code
Партиционирование таблиц в MySql
Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям.
Разбиение таблицы на разделы очень полезно, если таблица содержит большое количество данных. Разбиение ускорит выборку и запись в таблицу. Вот некоторые преимущества партиционирования:
Типы Разбиения
В MySql доступны следующие типы разделения:
Примеры
RANGE Partitioning:
Таблица, которая разбита на разделы диапазоном, разбита на разделы таким способом, которым каждый раздел содержит строки, для которых значение выражения выделения разделов находится внутри данного диапазона. Диапазоны должны быть непрерывны, но не перекрываться и определены, используя оператор VALUES LESS THAN
В этой схеме выделения разделов все строки, соответствующие записям, занимающим номера от 1 до 5, сохранены в разделе p0, от 6 до 10 в p1 и т. д.
LIST Partitioning:
Как в выделении разделов RANGE, каждый раздел должен быть явно определен. Главное различие в том, что в выделении разделов списка, каждый раздел определен и выбран основываясь на членстве значения столбца в одном наборе значений списков.
HASH Partitioning:
Выделение разделов HASH используется прежде всего, чтобы гарантировать четкое распределение данных среди предопределенного числа разделов. Например, следующая инструкция создает таблицу, которая использует хэширование на столбце store_id и разделена на 4 раздела:
KEY Partitioning:
Выделение разделов ключом подобно выделению разделов хэшем за исключением того, что выделение разделов хэшем использует определяемое пользователем выражение, а хэш-функция для выделения разделов ключом обеспечена MySQL.
MySQL и партицирование
Прочитав статью вспомнил об одном очень специфичном заказчике и системе по сбору статистики по событиям. На дворе 21 век и я знаю о наличии ClickHouse, но вот заказчик не хочет менять БД (причина мне непонятна и не известна, религия, наверное, не позволяет), да и пусть будет так, я его несколько раз предупреждал о последствиях. Когда станет медленно совсем, осознает проблему.
Суть проблемы
Но речь не об этом. В общем, прочитав статью я вспомнил об этом проекте и решил попробовать интегрировать партицирование в таблицу с 7 000 000 записями. На prod стэнде там уже намного больше записей.
Также в проекте использовался шардинг, который, по большому счету там лишний. Нет смысла в такого рода системе делать шардинг да еще и по времени (на каждый месяц своя таблица).
В общем вариантов, на самом деле было немного, как делить данные, и был выбран самый очевидный: добавить в таблицу колонку dYm (date Year month), так как в таблицу и так уже пишется время, то сделать этого не составило труда. Правда с определенной оговоркой, так как на сервере мало памяти, то пришлось пересоздать таблицу и импортировать данные в новую таблицу, предварительно добавив нужное поле.
Создание таблицы с партициями (часть полей убрал):
Как было описано в статье, что я привел изначально, плюсы такого деления очевидны:
Почитав статью решение по оптимизации также напрашивается само собой: нам в запросе нужно использовать поиск через between по уникальному ключу. В итоге, если в приложении все запросы заменить на такие:
то мы получим очень хороший explain:
SIMPLE event_list_test p201709,p201710 range PRIMARY,time PRIMARY 8 NULL 145875 11.11 Using where
Чего же мы добились?
А добились мы следующего:
Партиционирование таблиц в mySQL
Начиная с версии 5.1 mySQL поддерживает горизонтальное партицирование таблиц. Что это такое? Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям.. На нижнем уровне для myISAM таблиц, это физически разные файлы, по 3 на каждую партицию (описание таблицы, файл индексов, файл данных). Для innoDB таблиц в конфигурации по умолчанию – разные пространства таблиц в файлах innoDB (не забываем, что innoDB позволяет настраивать индивидуальные хранилища на уровне баз данных или даже конкретных таблиц).
CREATE TABLE orders_range (
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = MYISAM
PARTITION BY RANGE( YEAR(order_date) ) (
PARTITION p_old VALUES LESS THAN(2008),
PARTITION p_2008 VALUES LESS THAN(2009),
PARTITION p_2009 VALUES LESS THAN(MAXVALUE)
);
Что мы получаем? Первая «таблица» будет хранить данные за «архивный» период, до 2008го года, вторая — за 2008й год, и «третья» — все остальное.
Самое вкусное — запросы при этом совершенно не надо переписывать/оптимизировать:
select * from orders_range where order_date=’2009-08-01′;
И вот что при этом происходит:
Мы видим, что при выполнении этого запроса работа будет идти исключительно с «подтаблицей» p_2008.
Более того, ускорение достигается даже в случае выполнения запросов, затрагивающих все данные во всех партициях — ведь в этом случае сначала происходит первичная «обработка» таблиц по меньше, потом данные объединяются и производятся финальные вычисления. Так вот как раз «первые» этапы, в данном случае будут происходить гораздо быстрее.
Какие еще есть преимущества?
Главным преимуществом я бы назвал тот факт, что партиция с «оперативными» данными (т.е. последними, по которым наиболее часто происходит выборка) имеют минимальный размер, и как следствие, могут постоянно находится в оперативной памяти.
Если у вас есть таблица логов, в которую непрерывно идет запись и жесткие диски не успевают, а ставить рейд вам не позволяет религия, вы можете настроить партиционирование по хеш-функции, и указать по одной партиции на каждый доступный вам жесткий диск. В таком случае, новые данные будут равномерно писаться на все жесткие диски.
Какие способы «разделения» данных предоставляет mySQL?
По диапазону значений
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30)
);
По точному списку значений
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20)
)
Зачем, спросите вы? Разбивать на партиции необходимо либо исходя из соображений оптимизации выборки (что чаще) либо исходя из соображений оптимизации записи (реже). Соответственно, идеальный вариант — это когда вы разбиваете таблицу на максимально возможное количество партиций так, что бы 90% всех выборок происходило в пределах одной партиции. И если у вас сложная логика выборки (например, объекты расположенные в северных кварталах города, ID которых идут в разнобой) то иногда есть смысл перечислять их принудительно.
PARTITION BY HASH(store_id)
PARTITIONS 4;
Вы никак не управляете партицированием, просто указываете, по какому полю строить хеш и сколько «подтаблиц» создавать. Зачем? Гораздо быстрее происходит выборка по указанному полю. В некоторых случаях позволяет достигнуть «равномерного разброса» и ускорения записи данных.
Почти то же самое что и HASH, но более логично — по ключу.
PARTITION BY KEY(s1)
PARTITIONS 10;
Т.е. выборка по указанному ключевому полю происходит максимально эффективно.
Но тут так же следует определиться со способом партицирования. Хорошо подходит для счетчика посетителей, когда его логин является единственным идентификатором, по которому необходимо выбирать все остальные данные.
Нет вертикального партицирования. Это когда разные столбцы (поля) находятся в разных «подтаблицах». Поскольку иногда это бывает полезно, вы можете достичь этого самостоятельно, пусть даже не так прозрачно: разделить таблицу на две, связав их по первичному ключу. Если вам совсем хочется красоты — можете дополнительно создать по ним VIEW, например для того что бы не переписывать старые части кода.
Зачем это делать? Например, в таблице, где у вас в основном числа и даты, есть одно поле VARCHAR (255) для комментариев, которое используется на порядок реже чем остальные поля. В случае если его вынести в другую таблицу, то мы получим фиксированный размер строки (mySQL сможет совершенно точно вычислять позицию нужной строки по индексу в файле данных). Таблица станет более устойчивой к сбоям в случае внештатных ситуаций (опять же, из-за фиксированного размера строки). Ну и существенно уменьшится сам размер таблицы.
И заканчивая статью приведу пример более «реального» партицирования таблиц — помесячно. Так как LIST/RANGE принимают только целочисленные значения, то надо немного исхитрится:
PARTITION BY RANGE( TO_DAYS(order_date) ) (
PARTITION y2009m1 VALUES LESS THAN( TO_DAYS(‘2009-02-01’) ),
PARTITION y2009m2 VALUES LESS THAN( TO_DAYS(‘2009-03-01’) ),
PARTITION y2009m3 VALUES LESS THAN( TO_DAYS(‘2009-04-01’) )
);
PS: В mysql всегда приходится немного «исхитриться», так что скучно с ней не будет никогда, а мы в свою очередь никогда не останемся без работы 🙂
Partitioned Tables and Indexes
SQL Server поддерживает секционирование таблиц и индексов. Данные секционированных таблиц и индексов делятся на блоки, которые могут распределяться между несколькими файловыми группами в базе данных. Данные секционируются горизонтально, поэтому группы строк сопоставляются с отдельными секциями. Все секции одного индекса или таблицы должны находиться в одной и той же базе данных. Таблица или индекс рассматриваются как единая логическая сущность при выполнении над данными запросов или обновлений. До версии SQL Server 2016 (13.x); SP1 секционированные таблицы и индексы были доступны не в каждом выпуске SQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL Server, см. в статье Возможности, поддерживаемые различными выпусками SQL Server 2016.
SQL Server поддерживает по умолчанию до 15 000 секций. В версиях, предшествующих SQL Server 2012 (11.x), количество секций по умолчанию было равно 1000.
Преимущества секционирования
Секционирование больших таблиц или индексов может дать следующие преимущества в управляемости и производительности.
Это позволяет быстро и эффективно переносить подмножества данных и обращаться к ним, сохраняя при этом целостность набора данных. Например, такая операция, как загрузка данных из OLTP в систему OLAP, выполняется за секунды, а не за минуты и часы, как в случае несекционированных данных.
Операции обслуживания можно выполнять быстрее с одной или несколькими секциями. Операции более эффективны, так как выполняются только с поднаборами данных, а не со всей таблицей. Например, можно сжать данные в одну или несколько секций или перестроить одну или несколько секций индекса.
Можно повысить скорость выполнения запросов в зависимости от запросов, которые часто выполняются в вашей конфигурации оборудования. Например, оптимизатор запросов может быстрее выполнять запросы на эквисоединение двух и более секционированных таблиц, если столбцы секционирования те же, что и столбцы для объединения таблиц. См. подробнее о запросах.
В процессе сортировки данных для операций ввода-вывода в SQL Server сначала проводится сортировка данных по секциям. Для ускорения сортировки данных рекомендуется распределить файлы данных в секциях по нескольким жестким дискам, создав RAID. Таким образом, несмотря на сортировку данных по секциям, SQL Server сможет одновременно осуществлять доступ ко всем жестким дискам каждой секции.
Кроме того, можно повысить производительность, применяя блокировки на уровне секций, а не всей таблицы. Это может уменьшить количество конфликтов блокировок для таблицы. Чтобы снизить состязание блокировок с помощью применения укрупнения блокировок к секциям, задайте для параметра LOCK_ESCALATION инструкции ALTER TABLE значение AUTO.
Секции таблицы или индекса можно разместить в одной ( PRIMARY ) или нескольких файловых группах. При работе с многоуровневым хранилищем использование нескольких файловых групп позволяет назначать определенные секции определенным уровням хранилища. Все прочие преимущества секционирования применяются независимо от количества используемых файловых групп или размещения секций в определенных файловых группах.
Компоненты и основные понятия
Следующие термины относятся к секционированию таблиц и индексов.
Функция секционирования
Схема секционирования
Объект базы данных, который сопоставляет секции функции секционирования набору файловых групп. Главная причина, по которой секции разделяются по разным файловым группам, заключается в необходимости независимого резервного копирования этих секций, поскольку оно всегда выполняется отдельно для каждой из файловых групп.
В База данных SQL Azure поддерживаются только первичные файловые группы.
Столбец секционирования
Выровненный индекс
Индекс, созданный на основе той же схемы секционирования, что и соответствующая таблица. Когда таблица и ее индексы выровнены, SQL Server может при обслуживании структуры секционирования как таблицы, так и индексов быстро и эффективно переключать секции. Для выравнивания с базовой таблицей индексу необязательно использовать функцию секционирования с тем же именем. Тем не менее функции секционирования индекса и базовой таблицы не должны существенно различаться, то есть:
Секционирование кластеризованных индексов
При секционировании кластеризованного индекса столбец секционирования должен содержаться в ключе кластеризации. При секционировании неуникального кластеризованного индекса, если столбец секционирования не указан явно в ключе кластеризации, SQL Server по умолчанию добавляет столбец секционирования в список ключей кластеризованного индекса. Если кластеризованный индекс является уникальным, для него следует явным образом задать наличие столбца секционирования в ключе кластеризованного индекса. Дополнительные сведения о кластеризованных индексах и архитектуре индексов см. в разделе Правила проектирования кластеризованного индекса.
Секционирование некластеризованных индексов
При секционировании уникального некластеризованного индекса столбец секционирования должен содержаться в ключе индекса. При секционировании неуникального некластеризованного индекса SQL Server по умолчанию добавляет столбец секционирования как неключевой (включенный) столбец индекса, чтобы обеспечить выравнивание индекса с базовой таблицей. Если столбец секционирования уже присутствует в индексе, SQL Server его не добавляет. Дополнительные сведения о некластеризованных индексах и архитектуре индексов см. в разделе Рекомендации по созданию некластеризованных индексов.
Невыровненный индекс
Индекс, секционированный независимо от соответствующей таблицы. Т. е. индекс имеет другую схему секционирования или находится не в той файловой группе, где находится базовая таблица. Создание невыровненного секционированного индекса может быть полезно в следующих случаях:
Устранение секций
Процесс, в ходе которого оптимизатор запросов обращается только к определенным секциям в соответствии с фильтром запроса.
Рекомендации по производительности
Более высокое новое максимальное количество секций (15 000) влияет на память, операции с секционированными индексами, команды DBCC и запросы. В этом разделе показано, как влияет на производительность создание более 1 000 секций и как обойти проблемы. Увеличение максимального количества секций до 15 000 позволяет дольше хранить данные. Однако рекомендуется хранить данные ровно столько времени, сколько требуется, и поддерживать баланс между производительностью и количеством секций.
Рекомендации относительно процессорных ядер и числа секций
Чтобы добиться максимальной производительности с помощью параллельных операций, рекомендуется, чтобы число секций и процессорных ядер совпадало, но не превышало 64 (это максимальное число параллельных процессоров, которые SQL Server может использовать).
Использование памяти и рекомендации
При большом количестве используемых секций рекомендуется использовать ОЗУ не менее 16 ГБ. Если у системы недостаточно памяти, возможен сбой инструкций языка обработки данных (DML), инструкций языка описания данных (DDL) и других операций из-за нехватки памяти. В системах с ОЗУ 16 ГБ и большим количеством процессов, интенсивно использующих память, возможны сбои операций, работающих на большом количестве секций, из-за нехватки памяти. Поэтому чем больше у вас памяти сверх 16 МБ, тем меньше вероятность проблем с производительностью и памятью.
Ограничения оперативной памяти могут повлиять на производительность SQL Server при построении секционированного индекса и даже на саму возможность его построения. Такое случается, например, когда индекс не выровнен со своей базовой таблицей или со своим кластеризованным индексом, если такой существует в таблице. В этом случае может оказаться полезным увеличить параметр конфигурации сервера index create memory. Дополнительные сведения см. в статье Настройка параметра конфигурации сервера index create memory.
Операции с секционированными индексами
Создание и перестройка невыровненных индексов для таблицы, количество секций в которой превышает 1000, возможны, но не поддерживаются. Это может привести к снижению производительности или чрезмерному потреблению памяти во время таких операций.
Создание и перестройка выровненных индексов может занимать больше времени по мере увеличения количества секций. Не рекомендуется выполнять одновременно несколько команд создания и перестройки индекса, так как возможны проблемы с производительностью и памятью.
При сортировке, выполняемой при построении секционированных индексов, SQL Server сначала создает для каждой секции по одной таблице сортировки. Затем либо в соответствующей файловой группе каждой секции, либо в tempdb, если задан параметр индекса SORT_IN_TEMPDB, производится построение таблиц сортировки. Для всех таблиц сортировки требуется минимальный объем оперативной памяти. При построении секционированного индекса, выровненного со своей базовой таблицей, таблицы сортировки создаются по одной за раз, экономно расходуя оперативную память. Однако при построении невыровненного секционированного индекса таблицы сортировки создаются одновременно. В результате необходим достаточный объем оперативной памяти, чтобы параллельно их обрабатывать. Чем больше число секций, тем больше требуется оперативной памяти. Для каждой из секций размер таблицы сортировки составляет не менее 40 страниц, по 8 килобайт каждая. Например, для невыровненного секционированного индекса, разбитого на 100 секций, потребуется объем оперативной памяти для одновременной сортировки 4 000 страниц (40*100). Если такой объем памяти доступен, операция создания будет выполнена успешно, но может пострадать производительность. Если же такой объем памяти недоступен, операция построения завершится ошибкой. Для выровненного секционированного индекса, разбитого на 100 секций, для сортировки потребуется всего 40 страниц, поскольку сортировки осуществляются не одновременно.
Как для выровненных, так и для невыровненных индексов может потребоваться больший объем оперативной памяти, если SQL Server применяет степени параллелизма для выполнения данной операции на многопроцессорном компьютере. Чем больше степень параллелизма, тем больше требуется оперативной памяти. Например, если для SQL Server определена степень параллелизма 4, то невыровненному секционированному индексу, содержащему 100 секций, потребуется такой объем памяти, чтобы четыре процессора могли одновременно отсортировать по 4 000 страниц, то есть 16 000 страниц. Если секционированный индекс выровнен, требования оперативной памяти снижаются до 40 страниц для каждого из четырех процессоров, то есть 160 страниц (4*40). С помощью параметра индекса MAXDOP можно вручную снизить степень параллелизма.
Команды DBCC
При большем количестве секций выполнение команд DBCC может занимать больше времени по мере увеличения количества секций.
Запросы
Запросы, использующие функцию устранения секций, могут иметь сопоставимую или более высокую производительность с большим числом секций. Запросы, не использующие функцию устранения секций, могут занимать больше времени по мере увеличения количества секций.
Запросы, в которых используются такие операторы, как TOP или MAX/MIN, в столбцах, отличных от столбца секционирования, могут столкнуться со снижением производительности при секционировании, поскольку вычисляться должны все секции.
При частом выполнении запросов на эквивалентное соединение двух и более секционированных таблиц, их секционированные столбцы должны совпадать со столбцами, по которым производится соединение. Дополнительно: таблицы или их индексы должны быть упорядочены. Это означает, что в них используется либо общая именованная функция секционирования, либо разные, но дающие одинаковый результат. То есть:
Дополнительные сведения об обработке секций при обработке запросов см. в статье Секционированные таблицы и индексы.
Изменения в поведении при статистических вычислениях во время операций с секционированным индексом
Связанные задачи
| Задания | Раздел |
|---|---|
| Описано, как создать функции секционирования и схемы секционирования и применить их к таблице или индексу. | Создание секционированных таблиц и индексов |
См. также
Следующие публикации по стратегиям секционированных таблиц и индексов и примеры внедрения могут оказаться полезными.
Партицирование таблиц в PostgreSQL: чек-лист для старта
Часто возникает проблема: одна из таблиц в базе данных сильно выросла и время выполнения запросов к этой таблице увеличилось. Одним из вариантов решения такой проблемы в PostgreSQL является партицирование. В статье затронем не только техническую реализацию, но и опишем этапы подготовки к партицированию.
Представим, что у нас есть батон хлеба. Порежем его на части. Каждый отрезанный кусочек — часть целого батона, но не сам батон. То есть мы поделили целое на части — это и есть партицирование. Батон как целое соответствует таблице, а кусочки батона как части — партициям этой таблицы.
Заметим, что кусочки батона не равны между собой: одни тоньше, другие толще, у одних корочки нет только с одной стороны, у других — с двух сторон и так далее. Так же и с партициями: они могут содержать разное количество строк, а значит и размер на диске будет разным. Стоит отметить, что таблица партицируется построчно.
Таблицу, которую партицируют, называют мастер-таблицей. Партиция имеет связь с мастер-таблицей и представляет собой обычную таблицу, то есть к ней можно обращаться точно так же, как к самой обычной таблице: SELECT, INSERT (если не нарушает ограничений, накладываемых на партицию), UPDATE, DELETE. Допустимы операции обслуживания (VACUUM, ANALYZE), а также операции по изменению схемы таблицы (ALTER), правда, с некоторыми ограничения (подробнее см. официальную документацию).
Забегая вперёд, отметим, что при партицировании через наследование нет необходимости переписывать код приложения: обращения на чтение и запись выполняются к мастер-таблице. PostgreSQL самостоятельно определяет, к каким партициям следует обратиться. В то же время, если есть такая необходимость и приложение поддерживает подобную логику работы, можно напрямую обращаться к конкретным партициям.
Какие проблемы может решить партицирование?
ускорение выборки данных;
ускорение вставки данных;
упрощение удаления старых данных;
упрощение обслуживания таблицы.
Следует помнить, что партицирование — не панацея. Как и с любым другим инструментом, его применение не означает автоматически, что, например, проблема ускорения выборки или вставки данных будет решена. Результат сильно зависит от структуры таблицы, используемых индексов, критерия партицирования, размера партиций и прочих условий.
Стандартный процесс при удалении старых данных: выполняем DELETE FROM по условию, а затем запускаем полную вакуумизацию (VACUUM FULL) с простоем (про полный вакуум без простоя см. pg_repack). Обе операции затратны по времени и нагрузке на сервер БД и слабо контролируемы: нельзя понять прогресс выполняемой операции. В случае партицирования (при условии, что партицирование выполнено по полю created_at либо аналогичному) удаление старых данных занимает 2-3 секунды и выполняется через удаление соответствующей партиции с помощью DROP TABLE.
В случае упрощения обслуживания тот же полный вакуум гораздо быстрее отработает на сумме всех партиций, чем на одной большой таблице того же размера.
В каких случаях партицирование не поможет либо никак не повлияет?
время создания бэкапа;
время восстановления из бэкапа;
В первых двух случаях время практически не изменится, так как выполняется полный перебор данных. В последнем случае: был 1 млрд строк, после партицирования имеем тот же 1 млрд. То есть занимаемое место на диске будет практически тем же.
Некоторые ограничения и возможные проблемы по итогам партицирования
партицируемая таблица должна быть достаточно большого размера; согласно документации, рекомендуется партицировать в случае, если таблица превосходит размер физической памяти;
на партицируемую таблицу нельзя ссылаться через FOREIGN KEYS (можно, начиная с PostgreSQL 12); при этом обратное (партицируемая таблица ссылается на другие) допустимо;
в некоторых случаях партицирование может ухудшить производительность на операциях чтения и записи; как указывалось выше, итоги партицирования сильно зависят от многих условий;
в идеале запрос будет выполняться против одной партиции, но в худшем случае — затронет все партиции и, в зависимости от настроек PostgreSQL, увеличит время выполнения запроса, как в предыдущем пункте.
Виды партицирования
Декларативное партицирование появилось в PostgreSQL 10. Является встроенным (built-in) и наиболее производительным решением по партицированию, но имеет существенные ограничения:
требует изначально создать таблицу, готовую к партицированию;
нельзя партицировать уже существующую таблицу через ALTER TABLE;
при добавлении и удалении партиций будет простой в работе таблицы из-за ACCESS EXCLUSIVE LOCK (начиная с PostgreSQL 12, режим блокировки более щадящий: можно использовать SELECT, но только без FOR UPDATE/SHARE);
имеет ряд других ограничений по сравнению с другим типом партицирования.
Партицирование через наследование является более гибким решением:
можно партицировать уже существующую таблицу;
нет даунтайма при добавлении и удалении партиций;
можно задать любой произвольный критерий партицирования (об этом ниже);
возможно множественное наследование (наследование схем более чем одной таблицы);
в конце концов партицирование можно безболезненно отменить.
Зачастую о партицировании задумываются, когда таблица сильно разрослась, поэтому подробнее остановимся на партицировании через наследование.
Процедура партицирования через наследование
1. Создаём таблицы-партиции с использованием ключевого слова INHERITS:
Указываем уникальное имя таблицы-партиции. Желательно в имени кратно указать критерий (условие) партицирования. Задаём критерий партицирования для каждой партиции через CHECK. Обратите внимание, условия для партиций должны быть уникальны, вставляемая строка данных должна удовлетворять условию только одной партиции. Поэтому в данном примере нижняя граница условия имеет знак нестрогого неравенства, а верхняя граница — строгого.
Если планируется разнести имеющиеся в мастер-таблице данные по партициям, то в данном шаге создаём столько партиций, сколько необходимо, чтобы покрыть ими текущие данные плюс некоторое количество партиций для будущих данных на 2-3 месяца вперёд. Иначе создаём партиции только для будущих данных.
2. Добавляем индексы, такие же, как в мастер-таблице:
3. Создаём функцию, обеспечивающую партицирование:
Цель данной функции — определение партиции, в которую будет вставлена новая строка, на основе заданных условий партицирования. Если такое условие найдено не будет, функция сгенерирует ошибку о невозможности выполнения подобной операции.
4. Подключаем функцию к мастер-таблице:
Ключевой шаг. Триггер обеспечивает вставку данных в правильную партицию. При выборке данных PostgreSQL самостоятельно определяет, из каких партиций брать данные.
5. Разносим данные из мастер-таблицы по партициям:
Итерационно по условию WHERE удаляем данные из мастер-таблицы и удалённые данные возвращаем (RETURNING) в качестве ответа на запрос DELETE FROM. Эти данные используем для вставки в нужную партицию. Обратите внимание на ключевое слово ONLY в операции удаления: без него запрос отработает не только по мастер-таблице, но и по всем партициям. Данное ключевое слово может быть полезно и при выборке данных ТОЛЬКО из мастер-таблицы, и при прочих операциях с мастер-таблицей, если не хотим, чтобы были затронуты партиции.
Если планируется полностью удалить данные из мастер-таблицы без переноса, то пропускаем данный шаг.
6. Очищаем мастер-таблицу
Здесь снова используем ключевое слово ONLY, таким образом данные в партициях затронуты не будут. В процессе работы запроса для мастер-таблицы будут созданы новые пустые дата-файлы и пустые файлы индексов, прежние файлы мастер-таблицы будут удалены. В данном шаге можно было бы использовать полный вакуум, однако, в отличие от TRUNCATE операция полного вакуума выполняет проверку на существование данных, что даже на формально пустой таблице может вызвать значительный простой.
Отлично, партицирование выполнено, всё работает.
Проходит три месяца, новые данные перестают писаться в таблицу. Всё потому, что мы забыли добавить в крон добавление новых партиций. Быстро лечим кроном и переписываем функцию (добавляем условия проверки). Через год у нас уже 100 партиций, и функция разрослась до неимоверных размеров. Обслуживать такую таблицу со временем стало сложнее. Давайте не будем так делать и вместо этого заставим PostgreSQL самостоятельно решать подобные проблемы.
Объединим первые три шага процедуры в один. В итоге получим следующую процедуру:
1. Создаём расширенную функцию, обеспечивающую партицирование с автоматическим созданием партиций:
В данном шаге на основе входных данных определяем имя партиции, к которой должны относится новые данные, и условия принадлежности к этой партиции (первые три строки блока BEGIN). Проверяем существование такой партиции (TO_REGCLASS), и если её нет — создаём соответствующую партицию и индексы к ней (EXECUTE FORMAT … CREATE TABLE, ALTER TABLE, CREATE INDEX). В конце вставляем новые данные в правильную партицию. Соответственно, если партиция существует, то просто вставляем в неё новые данные.
2. Подключаем функцию к мастер-таблице.
3. Разносим данные из мастер-таблицы по партициям.
4. Очищаем мастер-таблицу.
Таким образом,
вместо 6 шагов процедура сократилась до 4;
нет необходимости в периодическом ручном, либо полуавтоматическом (через cron) создании партиций;
функция существенно упростилась (да, всё так: 100 if’ов для каждой партиции против одного if’а на проверку существования партиции — это значительное упрощение);
изменение функции требуется только при изменении схемы мастер-таблицы.
Может показаться, что функция усложнилась, появились переменные, выполняются дополнительные шаги по идентификации имени и созданию партиции — это же всё очень медленно. Но, во-первых, партиции создаются не каждую секунду, эта операция фактически выполняется раз в месяц (в случае помесячного разбиения). Во-вторых, как далее будет видно на примерах, даже вставка данных легко может быть ускорена, несмотря на дополнительные накладные расходы.
На этом можно было бы закончить статью, ведь с технической точки зрения мы всё сделали: создали функцию, прикрутили её к таблице, при необходимости перенесли данные. Однако возникает вопрос: по какому критерию мы партицировали таблицу? Как определить этот критерий? Почему выбрали шаг в один месяц? Что ещё мы не учли?
Давайте разберёмся с этими вопросами!
Собираем чек-лист партицирования
Итак, вы решили выполнить партицирование таблицы и, вероятно, таким образом хотите решить какую-то проблему. Какой итоговый результат вы ожидаете от партицирования? Почему вы выбрали для решения этой проблемы партицирование? Рассматривали ли вы другие варианты решения? Может быть стоит выполнить рефакторинг кода? Или на сервере БД поставить более мощное железо? Или просто в таблице не хватает индекса? Иными словами, есть ли у вас цель? Без цели выполнение партицирования чревато простоями и ухудшением производительности.
Если у вас есть цель — решаемая проблема, если прочие варианты решений были рассмотрены и отвергнуты по какой-либо причине, тогда можно попробовать реализовать партицирование. Повторюсь, партицирование — не панацея, и никто не даст никаких гарантий, что оно поможет.
Далее нужно определить самый часто встречающийся запрос на чтение данных из партицируемой таблицы (в некоторых случаях требуется найти самый тяжёлый запрос). В этом могут помочь разработчики.
В запросе смотрим, по какому условию выполняется выборка. Если в условии выборка выполняется по одному полю, то это поле — единственный кандидат в критерии партицирования. Если же полей в условии несколько, то либо пытаемся определить победителя, просмотрев топ запросов, либо все поля в условии считаем кандидатами в критерии партицирования. В любом спорном случае проверяем, какое из полей более всего согласуется с поставленной целью.
Например, в самом часто встречающемся запросе выборка выполняется по полям id и created_at. Топ запросов не выявил победителя. Если цель партицирования — упростить удаление старых данных, тогда в качестве критерия партицирования выбираем поле created_at. Если же цель — ускорить выборку данных, — скорее всего, поле id будет лучшим выбором.
Однако, есть и третий вариант: сделать комбинированный критерий из нескольких полей. Но, несмотря на то, что партицирование через наследование позволяет реализовать подобный сценарий, такого варианта следует опасаться.
Во-первых, это значительно усложняет код функции, обеспечивающей партицирование.
Во-вторых, это неизбежно ведёт к возрастанию накладных расходов при вставке данных и увеличивает время на выполнение операций вставки.
В-третьих, общая производительность на чтение данных из таблицы после такого партицирования скорее всего значительно снизится. Но повторюсь, результат сильно зависит от структуры таблицы, используемых индексов, критерия партицирования, размера партиций и прочих условий. Вполне возможно, в вашем случае вариант с комбинированным критерием будет наиболее оптимальным решением.
Следует помнить, что запросы, в которых выборка выполняется не по критерию партицирования, будут обращаться ко всем партициям и, следовательно, будут выполняться медленнее, чем без партицирования.
Определившись с критерием партицирования, выбираем размер партиции. Причём, размер — это не только байты, килобайты и прочее, это может быть:
количество строк в партиции;
периоды времени хранения информации (день, неделя, месяц, год);
диапазон идентификаторов записей в таблице (например, 1 млн id на партицию);
иные варианты, которые считаете приемлемыми для оценки размера партиции.
Иногда вместо размера партиции говорят о шаге партицирования: партицировали таблицу с шагом 1 месяц, 10 млн id, 100 млн строк и т.п.
В простом случае в выборе размера партиции могут помочь самый частый запрос и критерий партицирования. Например, самый частый запрос делает выборку за последний месяц, а критерий партицирования — поле created_at. Тогда размером партиции можно выбрать период времени в 1 календарный месяц.
В более сложных случаях, ответ не так очевиден. Например, в случае если критерий партицирования — поле id, то какой размер будет оптимальным: 1 млн id, 10 млн, 100 млн, 1 млрд? Если есть сомнения, выберите несколько возможных вариантов. Желательно ограничить количество таких вариантов числом 5.
Оптимальный размер партиции 5-20ГБ, но в вашем случае может быть и другой размер.
Когда вы оценили размер партиции и остановились на одном из вариантов, добавьте к нему ещё два: с бОльшим и мЕньшим шагом. Например, при партицировании с шагом в 1 месяц добавляем варианты с шагом 1 неделя и с шагом 2 месяца.
Подобьём промежуточный итог
имеется некая проблема, которую пытаемся решить;
найден самый часто выполняющийся запрос на чтение (либо топ запросов);
определен один или несколько критериев партиционирования;
подобраны несколько вариантов шага партиционирования для каждого из критериев.
Итого имеем N гипотез (К критериев x Ш шагов) партиционирования таблицы.
Теперь для каждой гипотезы нужно подготовить:
SQL-запрос на создание функции, обеспечивающей партицирование;
SQL-запрос (одинаковый для всех гипотез) на добавление триггера к таблице;
скрипт по переносу данных из мастер-таблицы по партициям;
для случая отката изменений запросы на удаление функции и триггера, а также скрипт обратного переноса данных.
Переходим к тестированию
Ни в коем случае не пропускайте и не игнорируйте данный этап: именно здесь мы должны подтвердить или опровергнуть наши гипотезы. Оценивать результаты тестирования следует с точки зрения поставленной цели. Если цель — ускорение выборки данных, то гипотезы, в которых происходит замедление выборки, точно не подходят, так как не согласуются с целью. Если же цель — упрощение удаления данных, то сам факт партицирования реализует поставленную цель. Однако, в этом случае стоит подобрать такой размер партиции, чтобы замедление выборки было минимальным.
Выполнять тестирование следует либо на таком же железе, как и на продакшн-базе, либо на максимально приближенном по характеристикам. В крайнем случае, если продакшн не нагружен, можно развернуть тестируемую базу рядом. Не следует тестировать на более мощном железе: при успешном тестировании реализация партицирования в проде может привести к отрицательным результатам.
Как тестировать? Разворачиваем из бэкапа либо всю базу, либо только партицируемую таблицу со связанными таблицами. Добавляем функцию и триггер. С помощью подготовленного скрипта переносим данные из мастер-таблицы по партициям. Прогоняем пачку топ запросов, замеряем время выполнения (в этом поможет поможет команда \timing), смотрим план выполнения, сравниваем результаты с продом.
Если тестирование показало отрицательные результаты, стоит пересмотреть критерий партицирования и шаг партицирования. Может быть, вы поставили слишком много целей: ускорить всё и вся и чтобы проще было удалять старые данные. Достигнуть таких целей возможно, но подобная комбинация схемы таблицы и логики работы приложения (а значит и структура запросов) встречается крайне редко.
Нашли расхождение, исправили, повторно протестировали, получили успешные результаты — отлично, проверяем работу скриптов отката изменений и переходим к реализации на проде. В ином случае, к сожалению, партицирование не решает поставленной задачи, необходимо найти иное решение.
Итого, получаем следующий чек-лист по партицированию:
находим самый часто встречающийся запрос;
определяем критерий партицирования;
подбираем размер партиции;
готовим скрипты для рассматриваемых гипотез:
функцию, обеспечивающую партицирование;
скрипт переноса данных;
скрипты отката изменений;
выполняем тестирование гипотез;
выбираем подходящую гипотезу по итогам тестирования;
делаем бэкап продуктивных данных;
партицируем таблицу на проде.
Пара слов о параметрах СУБД, которые влияют на партицирование
Наиболее критичные из них:
параметр constraint_exclusion — должен быть включен, иначе план выполнения запроса будет сформирован неоптимально: операции выборки будут затрагивать все партиции без учёта налагаемых ограничений на партиции (PostgreSQL «забудет» о критерии партицирования);
параметр max_parallel_workers_per_gather, отвечающий за максимальное количество воркеров, используемых для сборки результатов поиска от разных воркеров (при выполнении операции Gather или Gather Merge); в некоторых случаях может существенно ускорить работу запросов, в других — привести к деградации производительности всей СУБД.
В следующей статье расскажу на примерах, как нам помогло партицирование в решении задач в Skyeng.