Início / dba / Perguntas / 54626
Accepted
Franck Dernoncourt
Asked: 2013-12-07 20:37:10 +0800 CST

Por que importar um despejo do MySQL causa tantas E/S?

  • 772

Eu importei 100 arquivos dump em formato SQL. O tamanho total foi de 20 GB. Os arquivos .ibd resultantes após a importação tinham um tamanho total de 40 GB. mysqld.exe gravou 1,1 TB e leu 120 GB no disco. Por que tantas E/S?

Usei as opções padrão do MySQL Workbench para criar os arquivos de despejo no formato SQL, a saber:

insira a descrição da imagem aqui

Parece corresponder muito bem ao conselho da seção Carregamento de dados em massa para tabelas InnoDB do Manual de referência do MySQL (exceto para autocommit=0, que não está presente em meus dumps). Os cabeçalhos de código resultantes no despejo se parecem com:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `askbot_activityauditstatus`
--
LOCK TABLES `askbot_activityauditstatus` WRITE;
/*!40000 ALTER TABLE `askbot_activityauditstatus` DISABLE KEYS */;
INSERT INTO `askbot_activityauditstatus` VALUES 
etc

Sei que poderia tentar usar outras ferramentas de despejo para acelerar, mas estou curioso. Estou especialmente impressionado com o número de gravações.

Eu uso Windows 7 SP1 x64, MySQL 5.6 e MySQL Workbench 6.0.

mysql mysqldump

1 respostas

  1. Best Answer

    A restauração de um arquivo dump, fundamentalmente, envolve a inserção de todas as linhas e a criação de todos os índices para todos os dados. Diante disso, não é particularmente relevante como o arquivo de despejo é gerado, se você usa um ou vários arquivos ou qual das opções disponíveis você seleciona (embora algumas delas, como, extended-insertpossam piorar a situação por não serem selecionadas).

    Ao restaurar um arquivo, você também:

    • gravando os dados no log de transações
    • gravando os dados no buffer de gravação dupla
    • gravando os dados nos arquivos de tablespace
    • gravando os índices no buffer de gravação dupla
    • gravando os índices nos arquivos de tablespace
    • gravando os dados no log binário, se estiver ativado ... e se você tiver a binlog_formatconfiguração padrão de "instrução", estará literalmente gravando quase todos os bytes no arquivo de despejo mais a sobrecarga ... mas se binlog_formatestiver definido como " linha," você está escrevendo uma versão mais compacta para os binlogs... e se o seu binlog_formatestiver definido como "misto", então qual formato é realmente usado no binlog depende do seu nível de isolamento de transação padrão.

    Configurando innodb_flush_log_at_trx_commitpara o valor um pouco menos seguro de 2 ou valor significativamente menos seguro de 0 do valor padrão muito caro, mas compatível com ACID, de 1, com certeza acelerará sua inserção, embora não seja provável que reduza a E/S real porque esse valor não altera o que está escrito no log de transações, apenas altera a frequência com que o InnoDB insiste na confirmação do sistema operacional de que o conteúdo do log persistiu no disco. Eu uso "seguro" no sentido de segurança contra a perda de transações recentes se ocorrer uma falha durante o tempo em que o valor é definido como 2 ou 0; 1 protege contra perda de dados se o MySQL ou o sistema travar; 2 protege contra perda de dados se o MySQL travar, mas não o sistema, e 0 protege contra nenhum dos dois. Depois de configurá-lo, não há efeitos posteriores.

    Nota lateral rápida, algumas das coisas que parecem comentários mostradas não são comentários. O formato /*!mnnrr é uma extensão de compatibilidade com versões anteriores do MySQL que informa ao servidor "Se você for o MySQL versão m.nn.rr ou superior, execute esta instrução, caso contrário, desconsidere." 

    /*!40000 ALTER TABLE `table_name` DISABLE KEYS */;
/*!40000 ALTER TABLE `table_name` ENABLE KEYS */;

    Cada tabela é cercada por eles em um arquivo de despejo. Estes foram mais úteis com MyISAM do que com InnoDB, porque DISABLE KEYSdirecionaram o mecanismo de armazenamento para não atualizar nenhum índice não exclusivo até ENABLE KEYSque fosse emitido, permitindo que todos os dados de linha fossem gravados e, em seguida, indexados em um lote. Com o InnoDB, os índices são construídos à medida que as inserções são processadas... portanto, há muito potencial de E/S à medida que as árvores de índice são construídas e embaralhadas.

    O tamanho do pool de buffer InnoDB vai desempenhar um papel na quantidade de E/S do disco - possivelmente um papel significativo se for relativamente pequeno - porque o que quer que não possa permanecer na memória terá que ser imediatamente descarregado no disco , apenas para ser lido novamente quando for necessário novamente, e isso será particularmente verdadeiro com índices, menos com as linhas reais, porque no InnoDB, as linhas são armazenadas fisicamente na ordem da chave primária e mysqldumpas gravam no arquivo na chave primária ordem... então eles são inseridos na ordem em que serão armazenados... mas os índices secundários terão que ser transferidos para frente e para trás no disco à medida que as páginas de índice são atualizadas à medida que as operações de inserção em uma determinada tabela progridem .

    Portanto, há uma linha de base de atividade "extra" que ocorre a cada inserção, devido a todos os diferentes mecanismos de log, segurança e ACID... e os índices parecem um provável candidato curinga para potencialmente criar uma quantidade substancial de E/S adicional.

    • 3

relate perguntas