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

Question

Моя маленькая база данных объемом 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

Answer 1

Вы должны быть в состоянии вычислить эти числа до 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.

Answer 2

Я столкнулся с подобной ситуацией, когда восстановление дампа заняло слишком много времени. В моем случае я смог ускорить восстановление в 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, который выполняет приведенные выше преобразования.

Answer 3

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

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