A restauração de um backup MYSQL é muito lenta

Question

Estou restaurando um arquivo mysql dump de 26GB em uma memória RAM de 16GB do MySQL rodando em MacOS.

Primeiro eu tentei restaurar o backup do mysql assim

mysql -ufoo -pbar foo < foo.dump

Isso caiu em mySQL porque o foo.dump contém muitos personagens internacionais muito engraçados e o comando acima não estava cuidando das codificações.

Então eu tentei

mysql -uroot -p --default-character-set=utf8 foo
mysql> SET names 'utf8'
mysql> SOURCE foo.dump

Isso funcionou e o processo de restauração não travou, porque acho que os personagens internacionais engraçados foram manipulados corretamente.

Mas agora o processo de restauração é muito lento. Para arquivos de 26 GB, ele está sendo executado durante a noite (uma tabela com 40 milhões de linhas é a culpada). Eu posso ver que está restaurando aproximadamente 3000 linhas a cada 15 segundos. Mas, nesse ritmo, o processo de restauração levará uma eternidade.

Então existe uma maneira de restaurar o arquivo de despejo rapidamente e de não estragar as codificações?

    

Answer 1

Seu processo é lento devido à atividade I/O do disco pesado para operações SQL. Você precisa otimizar o innodb para operações intensivas. Modifique seu arquivo de configuração mysql para ter estas linhas:

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16

innodb_buffer_pool_size: a área de memória onde o InnoDB armazena dados de tabela e índice. Quando os dados da tabela são armazenados em cache no buffer pool InnoDB, eles podem ser acessados repetidamente por consultas sem a necessidade de qualquer E / S de disco. As alterações de dados são armazenadas em cache e não imediatamente gravadas no disco.

Um buffer pool maior requer menos E / S de disco para acessar os mesmos dados da tabela mais de uma vez. Em um servidor de banco de dados dedicado, você pode definir o tamanho do buffer pool para 80% da memória física da máquina, caso contrário, use 50 to 75 por cento da memória do sistema. O tamanho padrão do buffer pool é de 128 MB

innodb_log_buffer_size: O tamanho em bytes do buffer que o InnoDB usa para gravar nos arquivos de log no disco.

Um buffer de log grande permite que transações grandes sejam executadas sem a necessidade de gravar o log em disco antes que as transações sejam confirmadas. Portanto, se você tiver transações que atualizem, insiram ou excluam muitas linhas, tornar o buffer de log maior economiza E / S de disco.

innodb_log_file_size: O tamanho em bytes de cada arquivo de log em um grupo de log. Um tamanho grande garante que o servidor possa suavizar os altos e baixos na atividade de carga de trabalho, o que geralmente significa que há espaço de log de repetição suficiente para manipular mais de uma hora de atividade de gravação. Quanto maior o valor, menos atividade de liberação do ponto de verificação é necessária no conjunto de buffers, economizando E / S de disco

Innodb_write_io_threads: O número de threads de E / S para operações de gravação no InnoDB. O valor padrão é 4. O InnoDB usa encadeamentos em segundo plano para atender a vários tipos de solicitações de I / O. . Você pode configurar o número de encadeamentos em segundo plano que atendem a E / S de leitura e gravação em páginas de dados usando os parâmetros de configuração innodb_read_io_threads e innodb_write_io_threads.

Esses parâmetros significam o número de encadeamentos em segundo plano usados para solicitações de leitura e gravação, respectivamente. Cada encadeamento de segundo plano pode manipular até 256 solicitações de E / S pendentes.