Комментарии 84
спасибо за статью. мне в новом проекте как раз пригодится Ваш материал
Офигенная весчь!
P.S. Надо больше читать маны…
P.S. Надо больше читать маны…
большое спасибо, теперь оптимизировать будет легче.
Пора уже отходить от mysql как просто от тупо списка таблиц с тупой выборкой данных, а относиться к ней более серьезно. Триггеры, процедуры, целостность, ограничения. Теперь вот партицирование.
Айс!
Айс!
множество из того, что вы перечислили недоступно на хостинге в силу безопасности, а если у вас на один проект — один(свой) сервер, то конечно, давно пора к этому относиться серьезно.
Партицирование есть только на MyISAM, а слова «целостность», «ограничения» а также ещё много других СУБДшных слов отношения к MyISAM не имеют.
А вот не согласен. Партицирование отлично работает с innodb.
не правда, партицирование есть и в иннодб еще как минимум
из мануала
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;
из мануала
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;
Прошу прощения, действительно ошибся 8(
Пока это всё только в начале пути и не стоить плясать раньше времени. С этими новшествами постоянно появляются проблемы, так что говорить о полноценной и удобной поддержке транзакций, процедур, целостности, etc ещё рано. Им ещё обкатываться и обкатываться.
Этим «новшествам» в MySQL уже года так 2.5 (точно помню что пробовал партишининг еще на старой работе, с какой ушел 2.5 года назад)
Также как и триггеры с хранимыми процедурами.
Так что новшества далеко не новы, и уже успешно обкатаны.
Потому статья в контексте MySQL слегка «баянистая», зато хорошо расписаны pros & cons самого подхода.
Также как и триггеры с хранимыми процедурами.
Так что новшества далеко не новы, и уже успешно обкатаны.
Потому статья в контексте MySQL слегка «баянистая», зато хорошо расписаны pros & cons самого подхода.
Обкатаны то обкатаны, только до нормального удобства им ещё далеко. Сходите в гугл например по запросу mysql transaction problem, вместо transaction можно подставить что душе угодно из списка и поймёте что радоваться пока особо нечему. Вопросы сыплются пачками, как сделать это, как сделать то, особенно от людей не понаслышке знакомых например с mssql.
Обилие вопросов от людей «знакомых с MSSQL» в топиках о MySQL совершенно ни о чем не говорит.
Разве что о растущей популярности последнего.
И если для MSSQL-щиков и Oracle-истов что-то работает не так как они привыкли, это отнюдь не говорит о проблемах в MySQL. Это говорит о том, что в MySQL это ввиду разных причин это попросту иначе.
Разве что о растущей популярности последнего.
И если для MSSQL-щиков и Oracle-истов что-то работает не так как они привыкли, это отнюдь не говорит о проблемах в MySQL. Это говорит о том, что в MySQL это ввиду разных причин это попросту иначе.
Наконец-то это произошло в MySQL!!! Теперь можно будет на нем тянуть еще большие объемы:)
да, мануалы полезнейшая вещь :)
спасибо за статью, а то я в одном проекте уже начал проектировать ручные механизмы такого партиционирования. Теперь можно потратить время на доработку полезного функционала.
спасибо за статью, а то я в одном проекте уже начал проектировать ручные механизмы такого партиционирования. Теперь можно потратить время на доработку полезного функционала.
НЛО прилетело и опубликовало эту надпись здесь
Большое спасибо, думаю пригодится :)
А я раньше извращался с MERGE…
Да, это конечно очень поможет при больших объемах БД. Спасибо.
По хешам… да на отдельные диски… да мои 5 миллионов строк…
Пора ORM под 5.1 портировать, спасибо!
Пора ORM под 5.1 портировать, спасибо!
все это хорошо.
а как быть с автоматическим разбиением? или каждый новый месяц создавать новую таблицу? (
а как быть с автоматическим разбиением? или каждый новый месяц создавать новую таблицу? (
Вау! Сча как нарежу...)
Большое спасибо, не знал :)
А есть какие-нибудь конкретные данные? Например, 5 млн записей без партиционирования и с этим страшным словом :) Интересно какой прирост производительности.
Спасибо. Долго руки не доходят узнать все особенности 5.1, до сих пор все вручную делаю.
мат-часть рулит!
От оно как оказывается. Давно хотел что то подобное с логами сделать. Да все как то башем и перлом в дамп, а тут такая красота.
Ещё более интерснее была бы возможность распределения таблиц по разным серверам. Но пока это видимо не планируется.
Partitioning в контексте баз данных принято на русский переводить, как секционирование.
Слово партиционирование жутко неестественное, сильно режет слух.
Слово партиционирование жутко неестественное, сильно режет слух.
Где это принято? Общей нормы языка по данному вопросу нет (браузер/броузер) поэтому кто-то говорит секционирования, но лично я чаще всегда слышу партицирование. Хотя в яндексе конечно больше секционирования.
Слова «партиция» и «партицирование» можно считать сленгом. Оно удобнее за счет того, что в названии сразу слышится действие, команда, которой можно сделать это самое действие. А в книжках, действительно, обычно используют слова «секция» и «секционирование».
По моему опыту если используются partitions то запросы обязательно надо прогонять через EXPLAIN PARTITIONS… Иначе легко вместо секции получить full-scan.
1. Можно при помощи ALTER сделать партицирование?
2. Как сделать порции по квартально, так что бы при наступлении нового квартала создавалась партиция?
2. Как сделать порции по квартально, так что бы при наступлении нового квартала создавалась партиция?
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') )
);
насколько я видел обычно для этого делают несложную атомарную процедуру.
создать новую таблицу — перенести в нее данные — удалить старую — переименовать новую
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') )
);
НЛО прилетело и опубликовало эту надпись здесь
Мы видим, что при выполнении этого запроса работа будет идти исключительно с «подтаблицей» p_2008.— и это странно, потому что в запросе используется 2009-й год и партиция должна быть p_2009.
НЛО прилетело и опубликовало эту надпись здесь
Было в анонсах 5.1 на хабре
Ой как нравится мне когда мускульщики радуются хранимым процедурам и прочему =) Секционирование это да это молодцы =) но попрежнему плохо юзабельная СУБД =( и бизнес-логику унутрь БД не упихать =(
Ой как нравится мне когда мускульщики радуются хранимым процедурам и прочему =) Секционирование это да это молодцы =) но попрежнему плохо юзабельная СУБД =( и бизнес-логику унутрь БД не упихать =(
ну такое :)
Такое впечатление что заплатка на заплатке
есть небольшие фразы мелким текстом типа «Beginning with MySQL 5.1.12, the stored functions and procedures are not permitted in partitioning „
что сводит на нет весь бонус от их использования.
это как в триггере который не может менять таблицу которая его породила :)
Такое впечатление что заплатка на заплатке
есть небольшие фразы мелким текстом типа «Beginning with MySQL 5.1.12, the stored functions and procedures are not permitted in partitioning „
что сводит на нет весь бонус от их использования.
это как в триггере который не может менять таблицу которая его породила :)
а есть ли ограничение на кол-во разбиений? например, я хочу разбить таблицу по пользователям
Спасибо за действительно полезную публикацию — жму вашу мужественную руку.
Появился вопрос — можно ли пропивать партицирование, примерно таким образом:
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 дней%)
)
Примерно такой подход возможен в реализации?
Если попытаться, к примеру, число дней прошедших с публикации проставлять через триггеры, а в таблице просто проставить статичные значения, будут ли данные по завершению работы триггера перемещаться из одной подтаблицы в другую?
Появился вопрос — можно ли пропивать партицирование, примерно таким образом:
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 дней%)
)
Примерно такой подход возможен в реализации?
Если попытаться, к примеру, число дней прошедших с публикации проставлять через триггеры, а в таблице просто проставить статичные значения, будут ли данные по завершению работы триггера перемещаться из одной подтаблицы в другую?
Такой подход я боюсь невозможен.
Но как вариант можно каждые 10 дней выполнять
Но как вариант можно каждые 10 дней выполнять
ALTER TABLE members REORGANIZE PARTITION p0 INTO…
А как обстоит дело с добавлением новый секций в существующую таблицу?
Это можно делать через alter?
Это можно делать через alter?
Более того, ускорение достигается даже в случае выполнения запросов, затрагивающих все данные во всех партициях — ведь в этом случае сначала происходит первичная «обработка» таблиц по меньше, потом данные объединяются и производятся финальные вычисления.
По-моему далеко не всегда это будет быстрее. Насколько я понимаю, индексы строятся тоже для отдельной партиции таблицы. Получается, что при объединении результатов выборок из нескольких партиций дальше mySQL будет искать уже по временной таблице без индексов.
С другой стороны, это ускорит выборку, если никаких дальнейших действий с выбранными из партиций данных производить уже не надо.
Я работаю с Oracle, поэтому немного не в курсе, но любопытно. Поддерживает ли mySQL субпартицирование — разбиение партиции на более мелкие подпартиции? И возможно ли создание локальных индексов (по одной из партиций)?
1. да, субпартицирование поддерживается (правда лично я никогда не понимал зачем :)
2. индексы ВСЕГДА строятся по каждой из партиций отдельно.
2. индексы ВСЕГДА строятся по каждой из партиций отдельно.
Спасибо, все это очень вкусненько :-)
Субпартиции нужны для того же, что и партиции — разбивка большой таблицы на более мелкие части для удобства хранения и поиска в ней. Например, у нас почти все таблицы разбиваются на партиции по дате (месяц/день), при этом каждая четвертая еще и субпартицируется по региону.
Субпартиции нужны для того же, что и партиции — разбивка большой таблицы на более мелкие части для удобства хранения и поиска в ней. Например, у нас почти все таблицы разбиваются на партиции по дате (месяц/день), при этом каждая четвертая еще и субпартицируется по региону.
Вопрос в догонку: есть ли какие-то готовые средства для определения какой диапозон рядов является наиболее часто запрашиваемым с целью равномерного распределения нагрузки между серверами?
спасибо за информацию, чую пора переходить на 5.1
НЛО прилетело и опубликовало эту надпись здесь
НЛО прилетело и опубликовало эту надпись здесь
Кто может знает насчет partitioning при использовании FOREIGN KEY?
При попытке разбить таблицу мне выдает:
1506. Foreight key is not yet supported in conjunction with partitioning
… когда же появится?
При попытке разбить таблицу мне выдает:
1506. Foreight key is not yet supported in conjunction with partitioning
… когда же появится?
Есть значительное ограничение, что если в таблице есть уникальные ключи, в том числе и PRIMARY, то каждый из этих ключей должен включать в себя все столбцы, по которым производится разбиение.
Например, у нас есть форум и есть таблица сообщений, где есть поля post_id и topic_id, где post_id — уникальный код сообщения, а topic_id — ссылка на тему форума, в котором содержится это сообщение, то разбить таблицу по полю topic_id не получится. Потому что она не входит в уникальный ключ post_id. А если сделать ключ (post_id, topic_id), то выйдет так, что может быть один и тот же код сообщения в разных темах форума. И поле post_id утратит уникальность.
Ещё одно важное ограничение — нельзя использовать FULLTEXT индекс.
P. S. Написано по горячим следам после того, как я хотел ускорить работу форума с 1,6 млн. сообщений.
Например, у нас есть форум и есть таблица сообщений, где есть поля post_id и topic_id, где post_id — уникальный код сообщения, а topic_id — ссылка на тему форума, в котором содержится это сообщение, то разбить таблицу по полю topic_id не получится. Потому что она не входит в уникальный ключ post_id. А если сделать ключ (post_id, topic_id), то выйдет так, что может быть один и тот же код сообщения в разных темах форума. И поле post_id утратит уникальность.
Ещё одно важное ограничение — нельзя использовать FULLTEXT индекс.
P. S. Написано по горячим следам после того, как я хотел ускорить работу форума с 1,6 млн. сообщений.
могу ошибаться, но там могут быть проблемы из-за теперешней ситуации с мускулом. то есть на сколько я понял из информации на оф. сайте там есть что-то связанное с расширенными возможностями этой субд при покупке что-ли поддержки или какой-то лицензии. так же мне так показалось, что есть довольно сильные различия в различных сборках mysql, потому что дома на одной сборке работали партишены, на сервере, буквально на предыдущей не особо работали.
так же стоит хорошенько взвесить уровень разбиения и какой именно тип разбиения выбрать, ибо может сильно сказаться в обратную сторону производительности (как-то провел неделю, исследуя различные способы разбиения на части таблицу в 150млн записей).
так же стоит хорошенько взвесить уровень разбиения и какой именно тип разбиения выбрать, ибо может сильно сказаться в обратную сторону производительности (как-то провел неделю, исследуя различные способы разбиения на части таблицу в 150млн записей).
Не хватает ещё слов «при партицировании insert/update проиходит быстрее» так как перестраиваются малые индексы в подтаблицах а не один большой индекс на всю таблицу.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Партиционирование таблиц в mySQL