Назад | Перейти на главную страницу

Изменение столбцов очень больших таблиц mysql с минимальным временем простоя или без него

Мне периодически нужно вносить изменения в таблицы в mysql 5.1, в основном добавляя столбцы. Очень просто с командой alter table. Но в моих таблицах сейчас до 40 миллионов строк, и они быстро растут ... Так что эти команды alter table занимают несколько часов. Полагаю, через пару месяцев они займут дни.

Поскольку я использую amazon RDS, у меня не может быть подчиненных серверов, с которыми можно было бы играть, а затем повышать их до мастера. Итак, мой вопрос в том, есть ли способ сделать это с минимальным временем простоя? Я не возражаю против того, чтобы операция занимала часы или даже дни, если пользователи, конечно, все еще могут использовать базу данных ... Могут ли они хотя бы читать, пока добавляются столбцы? Что произойдет, если мое приложение попытается написать? Вставить или обновить? Если он сразу выходит из строя, это на самом деле не так уж плохо, если он просто зависает и вызывает проблемы для сервера db, это большая проблема ..

Это, должно быть, довольно распространенная проблема масштабирования, каждый должен добавлять столбцы .. Что обычно делается с производственной базой данных? Ведомый -> главный миграция?

Обновить - Я забыл упомянуть, что использую механизм хранения innodb

Мне просто пришлось сделать это недавно. Amazon рекомендовала использовать Percona Toolkit. Я загрузил его и смог запустить что-то вроде:

./pt-online-schema-change h=databasenameHostName,D=databasename,t=tablename --recursion-method=none --execute --user username --password password --alter "MODIFY someColumn newDataType"

и он отлично работает. Он сообщает вам, сколько времени осталось в процессе.

Фактически он создает новую таблицу с новым столбцом, а затем копирует существующие данные. Кроме того, он создает триггер, чтобы новые данные также передавались в новую таблицу. Затем он автоматически переименовывает таблицы, удаляет старую таблицу, и вы начинаете работать с новым столбцом, без простоев, пока вы ждали обновлений.

Мне периодически нужно вносить изменения в таблицы в mysql 5.1, в основном добавляя столбцы.

Не надо. Нет, правда. Только не надо. Это должен быть очень редкий случай, когда это Когда-либо нужно.

Предполагая, что ваши данные действительно нормализованы для начала, правильный способ решить проблему - добавить новую таблицу с отношением 1: 1 к базовой таблице (не обязательно для новой таблицы).

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

Наконец, если ваша схема действительно нормализована, и вы действительно, действительно должны продолжать добавлять столбцы, тогда:

  1. Убедитесь, что у вас есть столбец с меткой времени в базе данных или что он создает журналы репликации
  2. Создайте копию (B) таблицы (A)
  3. добавьте новые столбцы в B (это все еще будет заблокировано с помощью myisam)
  4. отключить транзакции
  5. переименовать исходную таблицу (A) как что-то другое (резервная копия)
  6. переименуйте новую таблицу (B) с именем исходной таблицы (A)
  7. воспроизвести транзакции с начала операции из журнала репликации или из резервной таблицы
  8. разрешить транзакции.

symcbean предоставляет некоторые твердые рекомендации.

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

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

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

Из руководства: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

В большинстве случаев ALTER TABLE создает временную копию исходной таблицы. MySQL включает изменение в копию, затем удаляет исходную таблицу и переименовывает новую. Пока выполняется ALTER TABLE, исходная таблица доступна для чтения другим сеансам. Обновления и записи в таблицу останавливаются до тех пор, пока новая таблица не будет готова, а затем автоматически перенаправляются в новую таблицу без каких-либо неудачных обновлений.

Итак, чтение будет работать нормально. Запись будет остановлена, но впоследствии выполнена. Если вы хотите предотвратить это, вам придется изменить свое программное обеспечение.

Я в аналогичной ситуации, когда мне нужно изменить одну из моих таблиц транзакций, которая составляет почти 65 ГБ. Я слышу 2 решения

  1. Используйте прямую ALTER и дайте ему поработать (X чисел часов или дней)
  2. Убедитесь, что у вас есть столбец с меткой времени в базе данных или что он создает журналы репликации
    • Создайте копию (B) таблицы (A)
    • добавьте новые столбцы в B (это все еще будет заблокировано с помощью myisam)
    • отключить транзакции
    • переименовать исходную таблицу (A) как что-то другое (резервная копия)
    • переименуйте новую таблицу (B) с именем исходной таблицы (A)