Партиционирование таблиц в mySQL

[BaHbka]

спасибо за статью. мне в новом проекте как раз пригодится Ваш материал

[progit]

Офигенная весчь!

P.S. Надо больше читать маны…

[happybyte]

Молодцы. До этого приходилось к подобным трюкам прибегать разбивая данные на таблицы ручками :) Хотя при использовании других движков типа SQLite все еще руками приходится разбивать.

[Joka]

большое спасибо, теперь оптимизировать будет легче.

[Joka]

я бы на вашем месте перенес бы в блог Mysql

[sasha_tinkoff]

Пора уже отходить от mysql как просто от тупо списка таблиц с тупой выборкой данных, а относиться к ней более серьезно. Триггеры, процедуры, целостность, ограничения. Теперь вот партицирование.
Айс!

[ferrari]

множество из того, что вы перечислили недоступно на хостинге в силу безопасности, а если у вас на один проект — один(свой) сервер, то конечно, давно пора к этому относиться серьезно.

[alexxz]

Партицирование есть только на MyISAM, а слова «целостность», «ограничения» а также ещё много других СУБДшных слов отношения к MyISAM не имеют.

[youngest]

А вот не согласен. Партицирование отлично работает с innodb.

[skachko]

MyISAM и InnoBD — всё-таки разные вещи.

[Joka]

не правда, партицирование есть и в иннодб еще как минимум

из мануала

This example shows how to create a table that is partitioned by hash into 6 partitions and which uses the InnoDB storage engine:

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;

[alexxz]

Прошу прощения, действительно ошибся 8(

[django]

Пока это всё только в начале пути и не стоить плясать раньше времени. С этими новшествами постоянно появляются проблемы, так что говорить о полноценной и удобной поддержке транзакций, процедур, целостности, etc ещё рано. Им ещё обкатываться и обкатываться.

[voituk]

Этим «новшествам» в MySQL уже года так 2.5 (точно помню что пробовал партишининг еще на старой работе, с какой ушел 2.5 года назад)
Также как и триггеры с хранимыми процедурами.
Так что новшества далеко не новы, и уже успешно обкатаны.
Потому статья в контексте MySQL слегка «баянистая», зато хорошо расписаны pros & cons самого подхода.

[django]

Обкатаны то обкатаны, только до нормального удобства им ещё далеко. Сходите в гугл например по запросу mysql transaction problem, вместо transaction можно подставить что душе угодно из списка и поймёте что радоваться пока особо нечему. Вопросы сыплются пачками, как сделать это, как сделать то, особенно от людей не понаслышке знакомых например с mssql.

[voituk]

Обилие вопросов от людей «знакомых с MSSQL» в топиках о MySQL совершенно ни о чем не говорит.
Разве что о растущей популярности последнего.

И если для MSSQL-щиков и Oracle-истов что-то работает не так как они привыкли, это отнюдь не говорит о проблемах в MySQL. Это говорит о том, что в MySQL это ввиду разных причин это попросту иначе.

[dazed]

Наконец-то это произошло в MySQL!!! Теперь можно будет на нем тянуть еще большие объемы:)

[rrromka]

Что значит «наконец-то»? Релиз MySQL 5.1 вышел почти год назад.

[dazed]

Просто даже не подозревал, что ввели такую вещь как партиционирование, думал опять по мелочам что-то добавили.

[youngest]

Именно по этому я и написал этот пост ;)

[halkfild]

да, мануалы полезнейшая вещь :)

спасибо за статью, а то я в одном проекте уже начал проектировать ручные механизмы такого партиционирования. Теперь можно потратить время на доработку полезного функционала.

[kurokikaze]

Большое спасибо, думаю пригодится :)

[merlin-vrn]

А я раньше извращался с MERGE…

[dex7er]

Да, это конечно очень поможет при больших объемах БД. Спасибо.

[andrew_tch]

По хешам… да на отдельные диски… да мои 5 миллионов строк…
Пора ORM под 5.1 портировать, спасибо!

[relec]

эх, везет же, всего 5 миллионов строк…

[greyhard]

все это хорошо.
а как быть с автоматическим разбиением? или каждый новый месяц создавать новую таблицу? (

[youngest]

ну мне не жалко было в своем проекте прописать еще 20 строчек на 2 года вперед. А в целом есть операции переразбития партиций или изменения логики.

насколько я знаю, некоторые ребята создают партиции каждые сутки по крону

[greyhard]

ну это же хак ), как я понял по мануалам и форуму мускуль, им уже описали идею авторазбития, может скоро это и повится )

[Goodkat]

при перепартицировании каждые сутки по крону — строки переносятся из одного раздела в другой? или просто создаётся новый раздел для новых, с этого момента, данных?

[TWINc]

Вау! Сча как нарежу...)

[ignar]

Большое спасибо, не знал :)

[roskov]

А есть какие-нибудь конкретные данные? Например, 5 млн записей без партиционирования и с этим страшным словом :) Интересно какой прирост производительности.

[youngest]

dev.mysql.com/tech-resources/articles/performance-partitioning.html

[roskov]

Ох, спасибо. Результаты впечатляют.

[MgDuke]

Для SQL Server:
faiz.kera.la/2009/08/02/does-partitioning-improve-performance-for-sql-tables/

[goran]

Спасибо. Долго руки не доходят узнать все особенности 5.1, до сих пор все вручную делаю.

[DIDJER]

мат-часть рулит!

[death_claw]

От оно как оказывается. Давно хотел что то подобное с логами сделать. Да все как то башем и перлом в дамп, а тут такая красота.

[stal]

Ещё более интерснее была бы возможность распределения таблиц по разным серверам. Но пока это видимо не планируется.

[alekciy]

Поддерживаю. Партицирование конечно штука хорошая, но разнос по разным нодам было бы более заманчиво. Ну видимо предполагается, что это будет делаться средствами proxy.

[DmitryKoterov]

Разнос по разным нодам, кажется, называется «шардинг». И шардинг имеет гораздо более широкую применимость в нагруженных проектах, чем партицирование. Мне кажется, что партицирование — оно только для логов и полезно…

[maovrn]

Партицирование полезно для любой большой таблицы.

[amima]

Partitioning в контексте баз данных принято на русский переводить, как секционирование.
Слово партиционирование жутко неестественное, сильно режет слух.

[alekciy]

Где это принято? Общей нормы языка по данному вопросу нет (браузер/броузер) поэтому кто-то говорит секционирования, но лично я чаще всегда слышу партицирование. Хотя в яндексе конечно больше секционирования.

[maovrn]

Слова «партиция» и «партицирование» можно считать сленгом. Оно удобнее за счет того, что в названии сразу слышится действие, команда, которой можно сделать это самое действие. А в книжках, действительно, обычно используют слова «секция» и «секционирование».

[kyb27]

По моему опыту если используются partitions то запросы обязательно надо прогонять через EXPLAIN PARTITIONS… Иначе легко вместо секции получить full-scan.

[eudo]

1. Можно при помощи ALTER сделать партицирование?
2. Как сделать порции по квартально, так что бы при наступлении нового квартала создавалась партиция?

[youngest]

1. сложный вопрос

насколько я видел обычно для этого делают несложную атомарную процедуру.
создать новую таблицу — перенести в нее данные — удалить старую — переименовать новую

2. например так

PARTITION BY LIST(YEAR(order_date) * 100 + QUARTER(order_date)) (
PARTITION y2005q3 VALUES IN(200503),
PARTITION y2005q4 VALUES IN(200504),
PARTITION y2006q1 VALUES IN(200601),
…

или так

PARTITION BY RANGE( TO_DAYS(order_date) ) (
PARTITION y2009q1 VALUES LESS THAN( TO_DAYS('2009-03-01') ),
PARTITION y2009q2 VALUES LESS THAN( TO_DAYS('2009-06-01') ),
PARTITION y2009q3 VALUES LESS THAN( TO_DAYS('2009-09-01') )
);

[eudo]

А если построить хеш по ГОД+НОМЕР_КВАРТАЛА, и указать PARTITIONS 20? (на 5 лет должно хватить)

[eugenius_nsk]

Мы видим, что при выполнении этого запроса работа будет идти исключительно с «подтаблицей» p_2008.

— и это странно, потому что в запросе используется 2009-й год и партиция должна быть p_2009.

[youngest]

Опечатка в запросе, исправил

[dezconnect]

Было в анонсах 5.1 на хабре
Ой как нравится мне когда мускульщики радуются хранимым процедурам и прочему =) Секционирование это да это молодцы =) но попрежнему плохо юзабельная СУБД =( и бизнес-логику унутрь БД не упихать =(

[youngest]

ну такое :)

Такое впечатление что заплатка на заплатке

есть небольшие фразы мелким текстом типа «Beginning with MySQL 5.1.12, the stored functions and procedures are not permitted in partitioning „

что сводит на нет весь бонус от их использования.

это как в триггере который не может менять таблицу которая его породила :)

[Irokez]

а есть ли ограничение на кол-во разбиений? например, я хочу разбить таблицу по пользователям

[youngest]

Не совсем понятно что вы имели ввиду под своим вопросом.

если максимально допустимое количество партиций — то 1024

Maximum number of partitions. The maximum number of partitions possible for a given table is 1024. This includes subpartitions.

[Irokez]

да, это и имел в виду, спасибо

[ZiNTeR]

Спасибо за действительно полезную публикацию — жму вашу мужественную руку.
Появился вопрос — можно ли пропивать партицирование, примерно таким образом:

PARTITION BY RANGE( TO_DAYS(%Current_date%)-TO_DAYS(order_date) ) (
PARTITION new VALUES LESS THAN( %10 дней% ),
PARTITION older VALUES LESS THAN( %20 дней%),
PARTITION oldest VALUES LESS THAN( %30 дней%)
)

Примерно такой подход возможен в реализации?
Если попытаться, к примеру, число дней прошедших с публикации проставлять через триггеры, а в таблице просто проставить статичные значения, будут ли данные по завершению работы триггера перемещаться из одной подтаблицы в другую?

[youngest]

Такой подход я боюсь невозможен.

Но как вариант можно каждые 10 дней выполнять

[youngest]

ALTER TABLE members REORGANIZE PARTITION p0 INTO…

[youngest]

Блин, залипает клавиатура :(

Вобщем, можно каждые 10 дней реорганизовать партиции под новые фиксированные даты, тогда MySQL самостоятельно пересортирует данные указанным вами образом

Хотя имхо это не совсем корректный подход к использованию данной фичи.

[ZiNTeR]

Чтож, будем делать то что можем и довольствоваться уже немалым — раньше и этой возможности небыло, а как появилась — сразу же изыски гурмана проступать начинают.

[ExH]

А как обстоит дело с добавлением новый секций в существующую таблицу?
Это можно делать через alter?

[youngest]

Да

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8, 12));

[WASD42]

Более того, ускорение достигается даже в случае выполнения запросов, затрагивающих все данные во всех партициях — ведь в этом случае сначала происходит первичная «обработка» таблиц по меньше, потом данные объединяются и производятся финальные вычисления.

По-моему далеко не всегда это будет быстрее. Насколько я понимаю, индексы строятся тоже для отдельной партиции таблицы. Получается, что при объединении результатов выборок из нескольких партиций дальше mySQL будет искать уже по временной таблице без индексов.
С другой стороны, это ускорит выборку, если никаких дальнейших действий с выбранными из партиций данных производить уже не надо.

[youngest]

если у вас идет выборка по всей базе — то она обязательно идет с агрегированием

ускорение в данном случае достигается за счет того что каждую партицию можно по очереди загрузить в оперативную память и все сделать без временных таблиц

выигрыш именно в этом

[maovrn]

Я работаю с Oracle, поэтому немного не в курсе, но любопытно. Поддерживает ли mySQL субпартицирование — разбиение партиции на более мелкие подпартиции? И возможно ли создание локальных индексов (по одной из партиций)?

[youngest]

1. да, субпартицирование поддерживается (правда лично я никогда не понимал зачем :)
2. индексы ВСЕГДА строятся по каждой из партиций отдельно.

[maovrn]

Спасибо, все это очень вкусненько :-)
Субпартиции нужны для того же, что и партиции — разбивка большой таблицы на более мелкие части для удобства хранения и поиска в ней. Например, у нас почти все таблицы разбиваются на партиции по дате (месяц/день), при этом каждая четвертая еще и субпартицируется по региону.

[impass]

Вопрос в догонку: есть ли какие-то готовые средства для определения какой диапозон рядов является наиболее часто запрашиваемым с целью равномерного распределения нагрузки между серверами?

[youngest]

задача не нормально распределить данные, а определить наиболее частые и тяжелые запросы на выборку данных и оптимизировать их

[impass]

Вообще говоря, зависит от задач. :)
А если мне необходимо делать выборку из всей таблицы или бОльшей её части?

[youngest]

но вот я приводил пример — данные о пользователе, в случае если их несколько десятков миллионов.

тогда просто hash от primary ID :)

[l0rda]

спасибо за информацию, чую пора переходить на 5.1

[youngest]

возможность использовать несколько индексов есть и так, если построить по ним составной индекс

вот да, для случая с fulltext составным индексом только через partitioning

[AGvin]

Кто может знает насчет partitioning при использовании FOREIGN KEY?

При попытке разбить таблицу мне выдает:

1506. Foreight key is not yet supported in conjunction with partitioning

… когда же появится?

[adrianov]

Есть значительное ограничение, что если в таблице есть уникальные ключи, в том числе и PRIMARY, то каждый из этих ключей должен включать в себя все столбцы, по которым производится разбиение.

Например, у нас есть форум и есть таблица сообщений, где есть поля post_id и topic_id, где post_id — уникальный код сообщения, а topic_id — ссылка на тему форума, в котором содержится это сообщение, то разбить таблицу по полю topic_id не получится. Потому что она не входит в уникальный ключ post_id. А если сделать ключ (post_id, topic_id), то выйдет так, что может быть один и тот же код сообщения в разных темах форума. И поле post_id утратит уникальность.

Ещё одно важное ограничение — нельзя использовать FULLTEXT индекс.

P. S. Написано по горячим следам после того, как я хотел ускорить работу форума с 1,6 млн. сообщений.

[VelarThind]

могу ошибаться, но там могут быть проблемы из-за теперешней ситуации с мускулом. то есть на сколько я понял из информации на оф. сайте там есть что-то связанное с расширенными возможностями этой субд при покупке что-ли поддержки или какой-то лицензии. так же мне так показалось, что есть довольно сильные различия в различных сборках mysql, потому что дома на одной сборке работали партишены, на сервере, буквально на предыдущей не особо работали.
так же стоит хорошенько взвесить уровень разбиения и какой именно тип разбиения выбрать, ибо может сильно сказаться в обратную сторону производительности (как-то провел неделю, исследуя различные способы разбиения на части таблицу в 150млн записей).

[ohifck]

Не хватает ещё слов «при партицировании insert/update проиходит быстрее» так как перестраиваются малые индексы в подтаблицах а не один большой индекс на всю таблицу.