Как настроить MySQL для восстановления из дампа MySQL?

7053
Alex R

Моя маленькая база данных объемом 5 ГБ, для которой требуется 5 минут для сброса через mysqldump, требуется 9 часов для восстановления. К счастью, я узнал об этом во время пробного запуска, а не в реальной чрезвычайной ситуации.

Каковы лучшие параметры для оптимизации, чтобы ускорить это?

Я пробовал следующие настройки на моем сервере с 2 ГБ оперативной памяти:

innodb_buffer_pool_size=512M innodb_additional_mem_pool_size=50M innodb_file_per_table innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=0 innodb_log_file_size=1G innodb_log_buffer_size=1G

Странно то, что даже с этими агрессивными настройками topтолько показывает, что mysqld едва использует часть выделенной памяти:

 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4421 mysql 20 0 247m 76m 5992 S 91 3.7 4:09.33 mysqld
4

3 ответа на вопрос

4
RolandoMySQLDBA

Вы должны быть в состоянии вычислить эти числа до mysqldump.

Что касается настроек, которые вы дали в вопросе,

innodb_log_file_size=1G

Этот параметр СЛИШКОМ БОЛЬШОЙ !!!

innodb_log_file_sizeДолжен быть 25% отinnodb_buffer_pool_size

innodb_log_file_size = 128M

Как только вы установите это /etc/my.cnf, вы должны сделать следующее, чтобы изменить размер ваших файлов журнала InnoDB:

  1. service mysql stop
  2. rm -f /var/lib/mysql/ib_logfile[01]
  3. service mysql start

Что касается других настроек

innodb_log_buffer_size=1G

Вы никогда не захотите кэшировать тонну данных здесь, прежде чем отправлять их в файлы журналов InnoDB, особенно для перезагрузок mysqldump или тяжелых транзакций COMMIT. Значение должно быть на порядок меньше.

innodb_log_buffer_size=32M

Кстати, вы должны отключить бинарное ведение журнала перед перезагрузкой. В противном случае все данные попадают в ваши двоичные журналы. Пожалуйста, сделайте одно из следующего:

  1. Сделайте это -> SET SQL_LOG_BIN=0;первая строка файла mysqldump.
  2. Из командной строки MySQL запустите, SET SQL_LOG_BIN=0;затем запуститеsource < mysqldumpfile >
  3. Закомментируйте log-bin /etc/my.cnfи перезапустите MySQL 5.1, загрузите файл mysqldump, раскомментируйте log-bin и перезапустите MySQL.

ОБНОВЛЕНИЕ 2011-07-24 20:30

Если у вас есть файл mysqldump /root/MyData.sql, вы все равно можете выполнить такие команды

SET SQL_LOG_BIN=0; source /root/MyData.sql

Это подпадает под вариант 2.

Некоторые файлы sql могут быть неудобными для редактирования и добавления «SET SQL_LOG_BIN = 0;» Вместо этого: echo "SET SQL_LOG_BIN = 0;" | gzip> sql_log_bin.sql.gz; затем запустите это: zcat sql_log_bin.sql.gz backup.sql.gz | MySQL Andy Lee Robinson 12 лет назад 0
Я просто хочу убедиться, что вы используете --opt с утилитой mysqldump. Это ОГРОМНАЯ экономия времени при перезагрузке. без этого mysqldump создает файл sql строка за строкой. --opt делает: добавляет блокировки, отбрасывает и создает заново со всеми включенными параметрами создания, временно отключает ключи для быстрой перезагрузки, а затем снова включает ключи в конце, чтобы перестроить индексы после завершения записи. Он объединяет несколько строк вместе во вставках, чтобы уменьшить размер файла и время синтаксического анализа каждого оператора (до 70%), и позволяет --quick уменьшить нагрузку на базу данных при запуске. сокращает время загрузки на 80% ppostma1 8 лет назад 0
@ ppostma1 - опция включена по умолчанию. Об этом уже говорится в документации: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_opt RolandoMySQLDBA 8 лет назад 0
У меня есть 4 разных варианта linux, работающих на онлайн-серверах, и некоторые менеджеры пакетов mysql в дистрибутивах отключают его! Это может быть разным между 12 часами для перезагрузки базы данных WP и 8 минутами, когда она включена. Я считаю, что объяснение менеджеров - «наличие дополнительных резервных копий». Насколько я понимаю, если резервное копирование выполняется на сервере, по умолчанию оно может быть отключено. Затем пользователь должен вручную добавить параметр --opt, чтобы указать, что это не попытка инкрементного резервного копирования. ppostma1 8 лет назад 0
@ ppostma1 О, это страшно !!! В документации сказано, что он включен по умолчанию. Я предполагаю, что дистрибутивы, у которых нет `--opt`, должны быть скомпилированы из исходного кода, а опция забыта. Я обязательно оставлю эту проблему в своем заднем кармане для любых будущих встреч, подобных этой. Благодарю вас. RolandoMySQLDBA 8 лет назад 1
0
Denis Kokorin

Я столкнулся с подобной ситуацией, когда восстановление дампа заняло слишком много времени. В моем случае я смог ускорить восстановление в 3-10 раз, отложив создание индекса MySQL.

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

Вот пример того, как MySQL экспортирует данные:

CREATE TABLE `SOME_TABLE` ( `id` int(11) NOT NULL AUTO_INCREMENT, `SOME_COLUMN` varchar(255) NOT NULL, `OTHER_COLUMN` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `UK_SOME_COLUMN` (`SOME_COLUMN`), KEY `IDX_OTHER_COLUMN` (`OTHER_COLUMN`) );  INSERT INTO `SOME_TABLE` (`id`, `SOME_COLUMN`, `OTHER_COLUMN`) VALUES (...), (...), ... ;

Если у вас есть миллионы строк в таблице с индексами, она будет восстановлена ​​намного дольше, как если бы индексы применялись после вставок.

Вы получите более высокую скорость импорта, если будете просто перемещать операторы создания индекса после вставок:

CREATE TABLE `SOME_TABLE` ( `id` int(11) NOT NULL AUTO_INCREMENT, `SOME_COLUMN` varchar(255) NOT NULL, `OTHER_COLUMN` varchar(255) NOT NULL, PRIMARY KEY (`id`) );  INSERT INTO `SOME_TABLE` (`id`, `SOME_COLUMN`, `OTHER_COLUMN`) VALUES (...), (...), ... ;  ALTER TABLE `SOME_TABLE` ADD UNIQUE KEY `UK_SOME_COLUMN` (`SOME_COLUMN`), ADD KEY `IDX_OTHER_COLUMN` (`OTHER_COLUMN`);

Проверьте мой репозиторий на наличие простого скрипта Python, который выполняет приведенные выше преобразования.

Это только ссылка, ответ обернутый в комментарии. Кроме того, хотя ваш скрипт на Python полезен сам по себе, что, если кто-то не хочет использовать Python или скрипт? Разве не ответ тогда просто: «Не устанавливайте индексы, пока все данные не будут вставлены?» JakeGould 5 лет назад 3
Это отличный ответ, который был бы бесполезным (расплывчатым и не предписывающим), если бы он не сопровождался ссылкой на эталонную реализацию. Alex R 5 лет назад 0
-1

Ваша проблема ввода / вывода, а не памяти. Если вы профилируете диск, вы обнаружите, что он безумно тратит время на чтение и запись, особенно если он находится на том же диске, что и резервная копия.

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

Похожие вопросы