Оптимизация MySQL для ALTER TABLE of InnoDB

В скором времени нам нужно будет внести изменения в нашу производственную базу данных. Нам нужно минимизировать время простоя для этих усилий, однако инструкции ALTER TABLE будут работать довольно долго. Наши самые большие таблицы имеют 150 миллионов записей, самый большой файл таблицы - 50G. Все таблицы InnoDB, и он был настроен как один большой файл данных (вместо файла за стол). Мы запускаем MySQL 5.0.46 на 8-ядерном компьютере, 16G-памяти и конфигурации RAID10.

У меня есть некоторый опыт настройки MySQL, но это обычно фокусируется на чтении или записи нескольких клиентов. В Интернете есть много информации об этом в Интернете, однако, как представляется, очень мало информации об оптимальных методах (временно) настройки вашего сервера MySQL для ускорения ALTER TABLE на таблицах InnoDB или для INSERT INTO. SELECT FROM (мы, вероятно, будем использовать это вместо ALTER TABLE, чтобы иметь еще несколько возможностей немного ускорить работу).

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

Какими были бы идеальные настройки для выполнения этой задачи как можно быстрее?

Ответ 1

Вам нужно немного подумать о своих требованиях.

На простейшем уровне "самый быстрый" способ изменить таблицу состоит в том, чтобы сделать это как можно меньше инструкций ALTER TABLE, желательно один. Это связано с тем, что MySQL копирует данные таблицы, чтобы изменить схему и сделать пятнадцать изменений, в то время как сделать одну копию, очевидно (и на самом деле), быстрее, чем копировать таблицу пятнадцать раз, делая одно изменение за раз.

Но я подозреваю, что вы спрашиваете, как это сделать с минимальным временем простоя. Как бы я это сделал, вы в основном синтезируете способ работы неблокированного ALTER TABLE. Но у него есть некоторые дополнительные требования:

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

Основной метод, как вы предложили, то есть с помощью INSERT INTO ... SELECT .... По крайней мере, вы находитесь впереди, потому что вы начинаете с таблицы InnoDB, поэтому SELECT не будет блокироваться. Я рекомендую сделать ALTER TABLE в новой пустой таблице, которая сохранит MySQL, копируя все данные снова, что будет означать, что вам нужно правильно перечислить все поля в инструкции INSERT INTO ... SELECT .... Затем вы можете сделать простой оператор RENAME для его замены. Затем вам нужно сделать еще один INSERT INTO ... SELECT ... WHERE ... и, возможно, UPDATE ... INNER JOIN ... WHERE ..., чтобы захватить все измененные данные. Вам нужно быстро выполнить INSERT и UPDATE, или ваш код начнет добавлять новые строки и обновления к вашему снимку, что будет мешать вашему обновлению. (У вас не будет этой проблемы, если вы может помещать ваше приложение в режим обслуживания в течение нескольких минут до RENAME.)

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

Ответ 2

Вы можете посмотреть pt-online-schema-change из инструментария Percona. По сути, он делает это:

Копирует исходную структуру таблицы, запускает ALTER.
Копирует строки из старой таблицы в новую.
Использует триггеры для отслеживания и синхронизации изменений при копировании.
Когда все будет завершено, он меняет таблицы, переименовывая оба.

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

Там также хороший веб-семинар об этом здесь.

PS: Я знаю, это старый вопрос, просто отвечающий на случай, если кто-то ударит его через поисковую систему.

Ответ 3

Настройка slave
Остановить репликацию.
Сделать ALTER на подчиненном устройстве
Пусть подчиненный догоняет мастер
swap master и slave, поэтому slave становится производственным сервером с измененной структурой и минимальным временем простоя

Ответ 4

К сожалению, это не всегда так просто, как staticsan приводит в ответ. Создание новой таблицы в режиме онлайн и перенос данных достаточно просто, а очистка в режиме обслуживания также достаточно эффективна, однако операция Mysql RENAME автоматически манипулирует любыми ссылками внешнего ключа на вашу старую таблицу. Это означает, что любые ссылки внешнего ключа на исходную таблицу будут по-прежнему указывать на то, к чему вы переименовали таблицу.

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

Другой подход, который работал для нас в прошлом, заключался в том, чтобы манипулировать набором реплик Mysql, обрабатывающих alter. Я не лучший человек, чтобы поговорить с процессом, но в основном это разбиение репликации на одно подчиненное, запуск патча на этом экземпляре, повторение репликации после того, как таблица alter завершена, чтобы догнать репликацию. Как только репликация завершается, вы помещаете сайт в режим обслуживания (при необходимости), чтобы переключиться с вашего мастера на это новое исправленное ведомое устройство, как новая основная база данных.

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

Ответ 5

Я тестировал различные стратегии, чтобы ускорить работу одной таблицы. В конце концов я получил примерно 10-кратное увеличение скорости в моем конкретном случае. Результаты могут или не могут применяться к вашей ситуации. Однако на основе этого я бы предложил экспериментировать с параметрами файла журнала журнала/буфера InnoDB.

Короче говоря, только увеличение innodb_log_file_size и innodb_log_buffer_size имело измеримый эффект (будьте осторожны! Изменение innodb_log_file_size опасно. Посмотрите ниже для получения дополнительной информации).

Основываясь на грубой скорости передачи данных (iostat) и активности процессора, узкое место было основано на io, но не на пропускной способности данных. В более быстрых 500-х годах пропускная способность записи по крайней мере находится на том же самом шаге, который вы ожидаете от жесткого диска.

Прогнозируемая оптимизация производительности:

выберите * в outfile + загрузить данные infile, как предложено в fooobar.com/questions/183473/...
"отключить ключи". Я пропустил тот факт, что он не влияет на InnoDB, который также основан на результатах. Это было во всех случаях, кроме окончательной таблицы изменений. Как отключить индекс в innodb
innodb_buffer_pool_size, "Советы по дискам ввода/вывода" на http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html
foreign_key_checks = 0 и др., "Советы по загрузке массовой информации" на http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
Bigger innodb_log_file_size (см. предупреждения ниже) и innodb_log_buffer_size. См. "Советы по регистрации" в http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html и http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_log_file_size и http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_log_buffer_size

Изменение параметра innodb_log_file_size может быть опасным. См. http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/ Техника (перемещение файла) объясняется в ссылке хорошо работал в моем случае.

Также см. http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/ и http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ для информации о размерах журнала innodb и tuning. Одним из недостатков больших файлов журнала является более длительное время восстановления после сбоя.

Тесты и грубые тайминги:

Простая загрузка данных в новую таблицу createad: 6500s
загрузить данные w. innodb_log_file_size = 200M, innodb_log_buffer_size = 8M, innodb_buffer_pool_size = 2200M, autocommit = 0; unique_checks = 0, foreign_key_checks = 0: 500s
загрузить данные w. innodb_log_file_size = 200M, innodb_log_buffer_size = 8M: 500s
Эквивалентная таблица прямого изменения w. datainnodb_log_file_size = 200M, innodb_log_buffer_size = 8M: 500s

Детали тестирования: таблица: InnoDB, 6M строк, 2.8G на диске, один файл (параметр innodb_file_per_table), первичный ключ - 1 целое число, +2 unque constraints/indices, 8 columns, avg. длина строки - 218 байт. Сервер: Ubuntu 12.04, x86_64, виртуальная машина, 8 ядер, 16 ГБ, диск с потребительским классом sata, отсутствие рейда, активность базы данных, незначительная другая активность процесса, незначительная активность в других и гораздо меньших виртуальных машинах. Mysql 5.1.53. Начальная конфигурация сервера довольно стандартная, за исключением увеличенного innodb_buffer_pool_size 1400M. В таблице alter добавлены 2 небольших столбца. Я не просматривал необработанную таблицу alter, но вместо этого экспериментировал с эквивалентной инструкцией с информацией о загружаемой информации, наконец, я сделал прямую таблицу alter и получил сопоставимый результат.

Этот вопрос связан, по крайней мере, с следующими вопросами:

Ответ 6

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

Затем вы можете запускать свои сценарии БД на, скажем, 3 часа ночи, поэтому это не имеет большого значения, если время простоя больше дольше, чем идеальное.