Como ajustar o MySQL para restauração a partir do MySQL dump?

Question

Meu pequeno banco de dados de 5GB, que leva 5 minutos para ser descarregado via mysqldump, leva 9 horas para ser restaurado. Por sorte, descobri isso durante um teste, não uma situação real de emergência.

Quais são os melhores parâmetros para otimizar para acelerar isso?

Eu tentei as seguintes configurações no meu servidor com 2 GB de RAM:

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

O mais estranho é que, mesmo com essas configurações agressivas, top apenas mostra que o mysqld mal está usando uma fração da memória designada:

 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

Você deve conseguir calcule estes números antes do mysqldump.

Com relação às configurações que você deu na pergunta,

innodb_log_file_size=1G

Essa configuração é muito grande !!!

O innodb_log_file_size deve ser 25% do innodb_buffer_pool_size

innodb_log_file_size = 128 milhões

Depois de definir isso em /etc/my.cnf , você deve fazer o seguinte para redimensionar seus arquivos de log do InnoDB:

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

Quanto à outra configuração

innodb_log_buffer_size=1G

Você nunca quer armazenar uma tonelada de dados aqui antes de enviá-los para os Arquivos de Log do InnoDB, especialmente para recarregamentos do mysqldump ou COMMITs transacionais pesados. O deve ser uma ordem de grandeza menor.

innodb_log_buffer_size=32M

BTW Você deve desativar o registro binário antes de recarregar. Caso contrário, todos os dados chegam aos seus registros binários. Por favor, faça um dos seguintes procedimentos:

1. Faça isso - > SET SQL_LOG_BIN=0; a primeira linha do arquivo mysqldump.
2. Na linha de comando do MySQL, execute SET SQL_LOG_BIN=0; e execute source < mysqldumpfile >
3. Comente o log-bin de /etc/my.cnf e reinicie o MySQL 5.1, carregue o arquivo mysqldump, remova o comentário do log-bin e reinicie o MySQL.

ATUALIZAÇÃO 2011-07-24 20:30

Se você tem um arquivo mysqldump /root/MyData.sql , você ainda pode executar os comandos como este

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

Isso cai na opção 2.

    

Answer 2

Eu enfrentei uma situação semelhante com uma restauração de despejo demorando muito. No meu caso, consegui acelerar a restauração de 3 a 10 vezes, adiando a criação do índice do MySQL.

Em suma, o índice é recalculado em cada inserção. Portanto, é melhor calcular o índice para toda a tabela de uma só vez.

Aqui está um exemplo, como o MySQL exporta dados:

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 (...), (...), ... ;

Se você tiver milhões de linhas em uma tabela com índices, ela será restaurada por muito mais tempo, como se os índices fossem aplicados após as inserções.

Você terá uma maior velocidade de importação se apenas mover as instruções de criação de índice após as inserções:

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');

Verifique meu repositório para um script python simples, que faz a transformação acima.

    

Answer 3

Seu problema é E / S, não memória. Se você criar um perfil no disco, você verá que ele está se debatendo para ler e escrever, especialmente se estiver no mesmo disco que o backup.

Eu movo o arquivo para uma unidade física diferente e vejo se isso ajuda.