Comments 55
А просто создать копию таблицы — не?
копия такой таблицы так будет создаваться в течении многих часов, нагружая сервер, хотя не будет блокировать работу с таблицей вроде как
Ну, по сути, в топике именно такое решение — заблокировать один сервер, чтобы он обновился, потом — второй.
Нет, на момент переливки ничего не блокируется. Работа с первой таблицей как шла так и идет. Можно вообще без лока обойтись — но потеряется ещё немного данных — но впринципе количество незначительное.
Смотря как переливать. Если INSERT INTO (SELECT FROM ...), то исходная таблица (по которой SELECT) залочится на запись на всё время копирования. Какой-нибудь LIMIT 1000 там обязателен.
Ну тогда можно сделать скрипт, START TRANSACTION и пачками…
>> Можно поднять master-slave репликацию, сделать ALTER на slave-сервере а потом поменять их местами.
Самое правильное решение этой «проблемы». Потеря данных в таком случае сведена к минимуму
Самое правильное решение этой «проблемы». Потеря данных в таком случае сведена к минимуму
Немного непонятно место с блокировкой/разблокировкой таблицы, зачем это делать?
чтобы на момент «переливки» данных из одной таблицы в другую, уже перенесенные данные не изменялись и не добавлялись в старой таблице.
Бред, тогда весь топик ни о чем, это вариант в абзаце «В лоб».
почему бред? у вас таблица заблокирована только на запись, на чтение блокировка не ставится, данные доступны для чтения, триггеры отрабатывают и записывают все изменения в другую таблицу из которой потом мы можем подтянуть недостающие изменения
Блокируется ненадолго не несколько секунд только на момент запуска переливки, чтобы запись в лог и переливка началась в один момент, а не получилось например так что началась переливка, обновилось 1000 записей и только потом началась запись в лог.
Если этого не сделать, то до начала копирования уже кто-то может внести изменения, которые отобразятся в users_updated_rows, т.е. мы будем считать, что там что-то изменилось, а эти изменения произошли ещё до начала копирования. Хотя чем это отличается от тех изменений, которые будут в ещё не скопированных полях (которые тоже будут залогированы, но при этом скопированы уже изменёнными) не совсем понятно.
А вообще это классический способ изменения структуры больших таблиц при невозможности организации master-slave репликации
UFO just landed and posted this here
Есть утилита для этого: openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html
Часто у себя используем, очень удобно и нам и пользователям, которые этого даже и не замечают)
А если в таблице есть foreign keys?
ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;
Расскажу, как это мы делаем на Oracle (в таблицах сотни миллионов записей). Возможно, и в MySQL аналогично, но я не уверен, не проверял:
Шаг 1. ALTER TABLE users ADD(last_login INT);
Эта операция выполняется очень быстро, так как изменяется только описание таблицы, данные не изменяются, соответственно ничего не лочится.
Шаг 2. Делается скрипт, который выполняет UPDATE порциями, обрабатывая по N записей за коммит.
UPDATE users SET last_login=0 WHERE id between… AND ...;
Каждый раз лочится только текущие N записей, и, если N не велико, на производительности рабочего проекта это никак не будет заметно. Хотя, в общем, эта операция для всех блоков по N записей и может занять долго времени, на наших данных это обычно занимает несколько часов.
Шаг 3. Вот теперь настало время выполнить ALTER TABLE users MODIFY(last_login DEFAULT 0 NOT NULL);
При этом, так как данные в поле last_login уже существуют, ничего не добавляется, только проверяется наличие данных в этом поле, это относительно быстро.
P.S. Если у кого есть желание, можете проверить на MySQL, было бы интересно посмотреть результат.
Понимаете, какая штука… В MySQL практически любое изменение схемы таблиц производится так:
1. Лочится таблица.
2. Создается ее копия, уже с новой структурой (во временной таблице).
3. Исходная таблица удаляется, новая — переименовывается.
Это происходит даже тогда, когда вы делаете ADD FOREIGN KEY (операция, вообще не изменяющая данный или число/структуру колонок), и даже (о ужас!) когда добавляете новый элемент в ENUM-поле. То же самое и при создании индекса: пересоздается ВСЯ таблица, сколько бы данных в ней ни было. Так устроена InnoDB, и это происходит с самого его создания, а конца-края такому поведению не видно.
В то же время, Oracle и PostgreSQL (наверное, и многие другие СУБД), наоборот, заточены на то, чтобы по минимуму лочить таблицу при ее изменении. Поэтому в них и добавление/удаление колонки, и навершивание внешних ключей и индексов, а также многое другое происходит очень быстро.
Все это имеет громадное значение при интенсивной скорости разработки, когда структура базы данных меняется каждую неделю. Поэтому нередко приходится видеть гроздь таблиц, создававшихся только из-за того, что добавлять новые колонки было дорого/неудобно.
1. Лочится таблица.
2. Создается ее копия, уже с новой структурой (во временной таблице).
3. Исходная таблица удаляется, новая — переименовывается.
Это происходит даже тогда, когда вы делаете ADD FOREIGN KEY (операция, вообще не изменяющая данный или число/структуру колонок), и даже (о ужас!) когда добавляете новый элемент в ENUM-поле. То же самое и при создании индекса: пересоздается ВСЯ таблица, сколько бы данных в ней ни было. Так устроена InnoDB, и это происходит с самого его создания, а конца-края такому поведению не видно.
В то же время, Oracle и PostgreSQL (наверное, и многие другие СУБД), наоборот, заточены на то, чтобы по минимуму лочить таблицу при ее изменении. Поэтому в них и добавление/удаление колонки, и навершивание внешних ключей и индексов, а также многое другое происходит очень быстро.
Все это имеет громадное значение при интенсивной скорости разработки, когда структура базы данных меняется каждую неделю. Поэтому нередко приходится видеть гроздь таблиц, создававшихся только из-за того, что добавлять новые колонки было дорого/неудобно.
Насчет InnoDB — это новая для меня информация, спасибо.
Если не трудно, не могли бы Вы пожалуйста дать ссылки, где можно почитать об этом более подробно? (я сходу не нашел)
Если не трудно, не могли бы Вы пожалуйста дать ссылки, где можно почитать об этом более подробно? (я сходу не нашел)
Вот, только что нашел в блогах: mysqlha.blogspot.com/2008/07/ddl-twice-as-fast.html
Prior to 5.1, most DDL operations to a table (add/drop column, add/drop index) are implemented by copying the table and rebuilding all indexes. MySQL docs are rather quiet about this. The handler interface in 5.1 provides methods that allow this to not be done. Adding an index with the InnoDB 5.1 pluging creates the index without copying the table. Not only is this fast, but the index has less fragmentation because it is created with the output from a sort (InnoDB 5.1 includes merge sort).
July 14, 2008 7:02 AM
Может быть все же в более новых версиях MySQL/InnoDB, созданных после 2008 года, данная проблема была устранена (как минимум с ADD FOREIGN KEY)? Ведь декларировалось же Fast Index Creation в MySQL 5.5/5.6 (или ранее в MySQL 5.1 + InnoDB plugin), и т.д…
Ссылка: dev.mysql.com/doc/refman/5.5/en/innodb-create-index-overview.html
Вы не занимались ли этим вопросом? Или то, что Вы написали выше, касается лишь старых версий MySQL? (Сам я сейчас не могу проверить, нет MySQL под рукой).
Prior to 5.1, most DDL operations to a table (add/drop column, add/drop index) are implemented by copying the table and rebuilding all indexes. MySQL docs are rather quiet about this. The handler interface in 5.1 provides methods that allow this to not be done. Adding an index with the InnoDB 5.1 pluging creates the index without copying the table. Not only is this fast, but the index has less fragmentation because it is created with the output from a sort (InnoDB 5.1 includes merge sort).
July 14, 2008 7:02 AM
Может быть все же в более новых версиях MySQL/InnoDB, созданных после 2008 года, данная проблема была устранена (как минимум с ADD FOREIGN KEY)? Ведь декларировалось же Fast Index Creation в MySQL 5.5/5.6 (или ранее в MySQL 5.1 + InnoDB plugin), и т.д…
Ссылка: dev.mysql.com/doc/refman/5.5/en/innodb-create-index-overview.html
Вы не занимались ли этим вопросом? Или то, что Вы написали выше, касается лишь старых версий MySQL? (Сам я сейчас не могу проверить, нет MySQL под рукой).
> Шаг 1. ALTER TABLE users ADD(last_login INT);
попробовал на большой таблице на MySQL 5.3 — мускул ушел думать. Т.е. скорее всего переливка.
Знаю что начиная с 5.1 ENUM мгновенно добавляет новое значение а модификация существующего и удаление так же долго.
попробовал на большой таблице на MySQL 5.3 — мускул ушел думать. Т.е. скорее всего переливка.
Знаю что начиная с 5.1 ENUM мгновенно добавляет новое значение а модификация существующего и удаление так же долго.
Спасибо за эксперимент.
Интересно было бы повторить это же самое с последним MySQL 5.5. Если будет тот же результат, то это весьма веский и однозначный аргумент не использовать MySQL в больших проектах. В частности, этот аргумент может быть использован при ответе на типичный вопрос заказчика «а почему не MySQL?». А то обычно это довольно сложно объяснить, без конкретных примеров.
Интересно было бы повторить это же самое с последним MySQL 5.5. Если будет тот же результат, то это весьма веский и однозначный аргумент не использовать MySQL в больших проектах. В частности, этот аргумент может быть использован при ответе на типичный вопрос заказчика «а почему не MySQL?». А то обычно это довольно сложно объяснить, без конкретных примеров.
Мы используем MySQL по 2-м причинам:
1. Так исторически сложилось и переписать тонны кода под другую БД очень долго и дорого.
2. Оракл и Postgre никто просто не знает так как MySQL. Мы обновляли MySQL с 5.1 до 5.3 и ещё месяц находили косяки. При переходе на тот же Оракл просто будем смотреть на него и не понимать чего оно не работает.
Поэтому имеем то что имеем и выкручиваемся как можем. Вообщем то недостатки MySQL — это последнее на что мы жалуемся.
1. Так исторически сложилось и переписать тонны кода под другую БД очень долго и дорого.
2. Оракл и Postgre никто просто не знает так как MySQL. Мы обновляли MySQL с 5.1 до 5.3 и ещё месяц находили косяки. При переходе на тот же Оракл просто будем смотреть на него и не понимать чего оно не работает.
Поэтому имеем то что имеем и выкручиваемся как можем. Вообщем то недостатки MySQL — это последнее на что мы жалуемся.
Да, я понимаю. Например, переход на Oracle с любой СУБД всегда очень болезненен, одна лишь эквивалентность пустой строки '' и NULL чего стоит. Ну да ладно, это оффтопик уже.
По теме. Только что прошел по ссылке в конце Вашей статьи на решение от Facebook, прочитал, осознал. Еще раз спасибо, это была новая информация для меня. Время от времени приходится использовать и MySQL в работе, теперь буду использовать и эту информацию.
По теме. Только что прошел по ссылке в конце Вашей статьи на решение от Facebook, прочитал, осознал. Еще раз спасибо, это была новая информация для меня. Время от времени приходится использовать и MySQL в работе, теперь буду использовать и эту информацию.
и все таки раз у вас такие большие объемы данных, то я бы рекомендовал переход на Oracle как стратегическое решение, которое избавит вас от разных других проблем больших баз. Хотя в принципе я вас могу понять — у нас кроме Oracle никто других баз практически не знает и миграция на mySql кажется неподъемной задачей )))
Оракл не панацея. Я с ним никогда не работал но разве он горизонтально масштабируется?
СУБД oracle для больших баз и нагрузок подходит как нельзя лучше. Поддерживается партиционирование, кластеризация и многое другое. Обзор см habrahabr.ru/blogs/oracle/120003/.
UFO just landed and posted this here
Я для таких целей делал другую таблицу с такой же структурой + новым полем, и правильным значением для автоинкремента. После этого менял таблицы местами и не спеша переливал старые данные. Ни потерь данных, ни проблем при этом не было. Хотя кончено данные переливались достаточно долго.
Если выполнять перелив с помощью insert ignore, то можно не делать лок исходной таблицы.
Записи, которые проапдетятся/добавятся во время перелива в основной таблице, попадут в копируемую таблицу с помощью триггеров и insert ignore их пропустит.
Записи, которые проапдетятся/добавятся во время перелива в основной таблице, попадут в копируемую таблицу с помощью триггеров и insert ignore их пропустит.
Мда, у нас есть таблица, миллиард записей, терабайт в объёме. Если у вас 60 гиг лилось 12 часов — мы месяц проторчим :)
Да… насколько проще работать с древовидными не SQL структурами при изменении — для того что бы добавить параметр (столбец) — достаточно 1 строки и 1 секунды…
Все таблицы в MySQL на движке InnoDB на сколько я понимаю являются кластерными в терминологии Oracle, т.е. неким их подобием, именно по этой причине на каждой таблице будет PK даже если вы его не заказывали, по которому будет сформирован кластер. По сему добавление колонки вызывает существенное изменение самой структуры таблицы а не только словарей.
Способ описанный выше в реально сложной ситуации помогет не сильно, особенно если одаренные разработчики навешали констрейнтов на эту горячую таблицу и сделали кучу индексов, но автор молодец, мы такую таблицу мигрировали ручками… по частям… так ничего и не предумав для автоматизации процесса :) в итоге плюнули остановили сервис и просто добавили поле :) ну постояли часок другой… ну ничего страшного… бывает
Помнится во избежании данной ситуации — мы просто создавали новую таблицу — пихали в нее новой поле и ссылку на большую. Решение кривое, но гарантированно работает без простоя.
Способ описанный выше в реально сложной ситуации помогет не сильно, особенно если одаренные разработчики навешали констрейнтов на эту горячую таблицу и сделали кучу индексов, но автор молодец, мы такую таблицу мигрировали ручками… по частям… так ничего и не предумав для автоматизации процесса :) в итоге плюнули остановили сервис и просто добавили поле :) ну постояли часок другой… ну ничего страшного… бывает
Помнится во избежании данной ситуации — мы просто создавали новую таблицу — пихали в нее новой поле и ссылку на большую. Решение кривое, но гарантированно работает без простоя.
Sign up to leave a comment.
ALTER очень больших таблиц в MySQL