Изменение больших таблиц MySQL InnoDB

Добавление нового столбца или добавление нового индекса может занимать часы и дни для больших таблиц innodb в MySQL с более чем 10 миллионами строк. Каков наилучший способ повысить производительность на больших таблицах innodb в этих двух случаях? Больше памяти, настройка конфигурации (например, увеличение sort_buffer_size или innodb_buffer_pool_size), или некоторые трюк? Вместо прямого изменения таблицы можно было создать новую, изменить ее и скопировать старые данные, как это, что полезно для таблиц ISAM и несколько изменений:

CREATE TABLE tablename_tmp LIKE tablename;
ALTER TABLE tablename_tmp ADD fieldname fieldtype;
INSERT INTO tablename_tmp SELECT * FROM tablename;
ALTER TABLE tablename RENAME tablename_old;
ALTER TABLE tablename_tmp RENAME tablename;

Рекомендуем ли вы также использовать таблицы innodb или это просто команда ALTER TABLE?

Ответы

Ответ 1

Редактировать 2016: недавно (август 2016) выпустил gh-ost, изменив мой ответ, чтобы отразить его.

Сегодня существует несколько инструментов, которые позволяют вам выполнять онлайн-таблицу alter для MySQL. Это:

Рассмотрим "нормальный" `ALTER TABLE`:

Большая таблица займет много времени до ALTER. innodb_buffer_pool_size важен, а также другие переменные, но на очень большой таблице все они незначительны. Это просто требует времени.

Что делает MySQL для ALTER таблицы, чтобы создать новую таблицу с новым форматом, скопировать все строки, а затем переключиться. За это время таблица полностью заблокирована.

Подумайте о своем собственном предложении:

Скорее всего, это будет худший из всех вариантов. Почему это? Поскольку вы используете таблицу InnoDB, INSERT INTO tablename_tmp SELECT * FROM tablename выполняет транзакцию. a огромная транзакция. Это создаст еще большую нагрузку, чем обычный ALTER TABLE.

Кроме того, вам придется закрыть приложение в это время, чтобы оно не записывало (INSERT, DELETE, UPDATE) в вашу таблицу. Если это произойдет - вся ваша транзакция бессмысленна.

Что предоставляют онлайн-инструменты

Инструменты не все работают одинаково. Однако общие сведения разделяются:

  • Они создают таблицу "shadow" с измененной схемой
  • Они создают и используют триггеры для распространения изменений из исходной таблицы в таблицу призраков
  • Они медленно копируют все строки из таблицы в теневую таблицу. Они делают это в кусках: скажем, 1000 рядов за раз.
  • Они делают все выше, пока вы все еще можете получить доступ к исходной таблице и управлять ею.
  • Когда они выполнены, они меняют их, используя RENAME.

Инструмент openark-kit используется уже 3,5 года. Инструмент Percona - это несколько месяцев, но, возможно, более проверенный, чем первый. Говорят, что инструмент Facebook хорошо работает для Facebook, но не предоставляет общего решения для среднего пользователя. Я не использовал его сам.

Изменить 2016: gh-ost - это беспроблемное решение, которое значительно снижает основную нагрузку на запись на главном устройстве, развязывая миграционную нагрузку записи от нормальной нагрузки. Он одитируемый, контролируемый, проверяемый. Мы разработали его внутри GitHub и выпустили его как открытый источник; мы делаем все наши производственные миграции через gh-ost сегодня. Подробнее здесь.

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

Консервативный способ

Консервативным способом является использование репликации Active-Passive Master-Master, выполните ALTER на резервном (пассивном) сервере, затем переключите роли и снова выполните ALTER на том, что раньше было активным сервером, теперь стал пассивным. Это также хороший вариант, но для этого требуется дополнительный сервер и более глубокое знание репликации.

Ответ 2

Переименуйте винты вверх по ссылочным таблицам.

Если вы сказали table_2, который является дочерним по отношению к tablename, на ALTER TABLE tablename RENAME tablename_old; table_2 начнет указывать на tablename_old.

Теперь, не изменяя table_2, вы не можете вернуть его обратно в tablename. Вы должны продолжать делать изменения в каждом дочернем элементе и таблице с привязкой.