Início / dba / Perguntas / 31752
Accepted
David Parks
Asked: 2013-01-12 01:59:24 +0800 CST

MySQL LOAD DATA INFILE diminui em 80% após alguns shows de entrada com o mecanismo InnoDB

  • 772

Estou carregando um arquivo de 100 GB via LOAD DATA INFILE. Eu tive um bom sucesso com o MyISAM, algumas horas e pronto.

Estou tentando agora usando o InnoDB. A carga começa rapidamente em mais de 10 MB/s (observando o crescimento do arquivo da tabela, file_per_tableestá ativado).

Mas depois de cerca de 5 GB de dados, ele diminui para o intervalo de 2 a 4 MB/s, quando chego a mais de 20 GB, ele diminui cerca de 2 MB/s.

O tamanho dos buffer pools do InnoDB é 8G. E eu fiz o seguinte antes de executar o comando LOAD DATA INFILE:

SET @@session.sql_log_bin=0;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
alter table item_load disable keys;
//Run LOAD DATA INFILE....

Não consigo ver a razão pela qual está começando bem e diminuindo ao longo do tempo.

Além disso, usando as mesmas configurações, executei o mesmo comando LOAD DATA INFILE com a tabela usando InnoDB e MyISAM e um conjunto de dados de teste de 5 GB, MyISAM foi 20x mais rápido:

InnoDB:

mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (21 min 25.38 sec)
Records: 2630886  Deleted: 0  Skipped: 0  Warnings: 6

MeuISAM:

mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (1 min 2.52 sec)
Records: 2630886  Deleted: 0  Skipped: 0  Warnings: 6

Qualquer outra coisa que eu deveria considerar tentar? O mecanismo MyISAM é capaz de manter a taxa de carga muito melhor.

Detalhes adicionais:

  • Já tentei carregar os arquivos individualmente, sem diferença.

  • Aliás, tenho 150 arquivos de 500MB cada, dentro de cada arquivo as chaves são ordenadas.

  • Depois de obter 40 GB durante a noite, 12 horas depois, a taxa de carregamento caiu para 0,5 MB/s, o que significa que a operação é praticamente impossível.

  • Não encontrei outras respostas para perguntas semelhantes em outros fóruns, parece-me que o InnoDB não suporta o carregamento de grandes quantidades de dados em tabelas com alguns GB de tamanho.

mysql performance

4 respostas

  1. Best Answer

    OBSERVAÇÃO #1

    Notei que você desligou autocommit. Isso vai acumular tantos dados em ibdata1. Por quê?

    Existem sete (7) classes de informações que são armazenadas em ibdata1:

    • Páginas de dados para tabelas InnoDB
    • Páginas de índice para tabelas InnoDB
    • Dicionário de dados
    • Buffer de gravação dupla
      • Rede de segurança para prevenir a corrupção de dados
      • Ajuda a ignorar o SO para armazenamento em cache
    • Inserir Buffer (Simplifica as Alterações nos Índices Secundários)
    • Segmentos de reversão
    • Desfazer registros
    • Clique aqui para ver uma representação pictórica deibdata1

    Algumas dessas informações ficam visíveis para determinadas transações, dependendo do nível de isolamento. Essas ações podem produzir bloqueios de chave primária não intencionais e muitos dados fantasmas . À medida que essas duas coisas aumentam, você deve esperar uma desaceleração justa.

    Recomendação: deixe o autocommit ativado

    OBSERVAÇÃO #2

    Vejo que você tem isso:

    alter table item_load disable keys;

    DISABLE KEYS não funciona com InnoDB . Aqui está o porquê:

    • MyISAM : DISABLE KEYSsimplesmente desativa a atualização do Índice Secundário para a tabela MyISAM. Quando você massa INSERT em uma tabela MyISAM com chaves desabilitadas, resulta em um carregamento rápido da tabela junto com a construção da PRIMARY KEY e todos os índices exclusivos. Quando você executa ENABLE KEYS, todos os índices secundários são construídos linearmente na tabela e anexados ao arquivo .MYD.
    • InnoDB : Conforme mostrado na imagem interna do InnoDB, o tablepave do sistema ibdata1possui uma estrutura dedicada a inserções de índice secundário. No momento, não há provisão para lidar com índices da mesma forma que MyISAM.

    Para ilustrar isso, observe minha tentativa de executar DISABLE KEYS em uma tabela InnoDB no MySQL

    mysql> show create table webform\G
*************************** 1. row ***************************
       Table: webform
Create Table: CREATE TABLE `webform` (
  `nid` int(10) unsigned NOT NULL,
  `confirmation` text NOT NULL,
  `confirmation_format` tinyint(4) NOT NULL DEFAULT '0',
  `redirect_url` varchar(255) DEFAULT '<confirmation>',
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `teaser` tinyint(4) NOT NULL DEFAULT '0',
  `allow_draft` tinyint(4) NOT NULL DEFAULT '0',
  `submit_notice` tinyint(4) NOT NULL DEFAULT '1',
  `submit_text` varchar(255) DEFAULT NULL,
  `submit_limit` tinyint(4) NOT NULL DEFAULT '-1',
  `submit_interval` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table webform disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'webform' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)

mysql>

    OBSERVAÇÃO #3

    Você notou que o MyISAM carrega 20x mais rápido que o InnoDB. Você gostaria que fosse mais 24-25 vezes mais rápido? Em seguida, execute o seguinte:

    ALTER TABLE item_load ROW_FORMAT=Fixed;

    Isso acelerará INSERTs vezes 20-25% sem quaisquer outras alterações de DDL . Efeito colateral: A mesa MyISAM pode crescer de 80% a 100% em tamanho, possivelmente maior.

    Você também pode executar isso em uma tabela InnoDB, mas o comportamento compatível com ACID e o MVCC do InnoDB ainda seriam o gargalo de seu desempenho, especialmente se os campos VARCHAR aumentarem significativamente forem gravados em arquivos ibdata1.

    • 7

  2. A resposta final para essa pergunta foi não usar o InnoDB para uma tabela de referência massiva. O MyISAM está gritando rápido, quase com taxa de transferência total da velocidade do disco para toda a carga, o InnoDB atola. MyISAM é simples, mas neste caso também são os requisitos desta tabela. Para uma tabela de referência simples com cargas em massa sobre LOAD DATA INFILE, MyISAM é o caminho a percorrer, até agora tudo bem.

    Mas observe que, se você executar as tabelas MyISAM e InnoDB, precisará considerar a alocação de memória para 2 mecanismos de cache, cada mecanismo possui seu próprio cache exclusivo que precisa de alocação de memória separada.

    • 6

  3. Você pode tentar dividir seus arquivos de entrada em pedaços menores.

    Eu pessoalmente uso http://www.percona.com/doc/percona-toolkit/2.1/pt-fifo-split.html para isso.

    O que acontece se você obtiver um bloqueio de tabela durante a importação? Talvez o bloqueio em nível de linha do InnoDB reduza a velocidade (MyISAM usa um bloqueio de tabela).

    Você também pode ler aqui para mais ideias: http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql

    • 5

  4. Se o seu PK não for AUTO_INCREMENT ou os dados no arquivo csv não estiverem classificados no PK, isso pode estar afetando o desempenho do carregamento de dados. Como a tabela no MySQL é um índice, todos os dados são armazenados em ordem classificada, se o valor PK não estiver em AUTO_INCREMENT, o MySQL precisará fazer muitos deslocamentos de dados para obter os dados armazenados em ordem classificada. Esse é o motivo do carregamento de dados mais lento quando o tamanho da tabela começa a crescer.

    Estou carregando um arquivo csv de 91 GB com PK em AUTO_INCREMENT usando LOAD DATA INFILE e não vejo nenhuma queda na minha taxa de transferência. Estou recebendo 140K a 145K inserções por segundo. Usando Percona MySQL 5.6.38

    • 2

relate perguntas