David Parks's questions

Eu tenho uma grande tabela de referência, 100 GB, em uma tabela MyISAM, carregá-la do zero vai bem (4 horas e meia) usando LOAD DATA INFILE.

Mas temos 1 GB por dia que queremos atualizar em um processo em lote usando LOAD DATA INFILE. Infelizmente, LOAD DATA INFILE impede os usuários de consultar essa tabela.

Existe uma maneira de contornar isso?

Por exemplo:

• Posso carregar os novos dados em outra tabela e inserir a partir dessa tabela ou algo semelhante para permitir o carregamento dos dados e ler as consultas na tabela principal ao mesmo tempo?
• Posso fazer algum tipo de particionamento com novas tabelas adicionadas diariamente, unidas por uma visão? Isso seria uma má ideia com uma mesa tão grande?

2 milhões de linhas inseridas usando LOAD DATA INFILE para innodb levam 7,5 minutos, e a criação de perfil mostra que 99% desse tempo está em "Bloqueio do sistema" .

Isso me diz algo útil?

   mysql> set profiling=1;
   Query OK, 0 rows affected (0.00 sec)

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

   mysql> show profile for query 1;
   +------------------------------+------------+
   | Status                       | Duration   |
   +------------------------------+------------+
   | starting                     |   0.000206 |
   | checking permissions         |   0.000015 |
   | Opening tables               |   0.000034 |
   | System lock                  | 447.327523 |
   | Waiting for query cache lock |   0.000352 |
   | query end                    |   0.000011 |
   | closing tables               |   0.000014 |
   | freeing items                |   0.000033 |
   | logging slow query           |   0.000007 |
   | logging slow query           |   0.000006 |
   | cleaning up                  |   0.000006 |
   +------------------------------+------------+
   11 rows in set (0.02 sec)

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.

Usamos INNODBpara todas as nossas tabelas, mas eu tenho uma tabela especial, uma tabela de referência de mais de 100 GB (somente consultas de leitura nela).

Estamos carregando alterações nesta tabela em um trabalho em lote noturno usando LOAD DATA INFILE.

MyISAMé mais compacto e eficiente para uma mesa de referência. Mas esta seria a ÚNICA tabela MyISAM que usamos.

A carga de trabalho no restante das tabelas é um aplicativo da Web típico, exceto para 1 tabela que possui uma carga de gravação pesada e carga de leitura normal.

• Pergunta 1 - Vale a pena dedicar memória ao cache MyISAM apenas para esta tabela, quando sei que preciso de uma quantidade razoável de memória dedicada ao cache InnoDB para os outros casos de uso de carga de trabalho?
• Questão 2 - Quando eu executar LOAD DATA INFILE 'from_nightly_batch' REPLACE INTO TABLE mytableo MyISAM irá travar a tabela para leituras? Será InnoDB? por exemplo, posso usar a tabela de referência enquanto faço a atualização noturna em qualquer mecanismo?

Existe uma maneira de pular uma tabela específica nos logs binários?

Eu tenho logs binários configurados como minha estratégia de backup incremental entre backups noturnos. Mas temos uma enorme tabela de referência que vamos atualizar todas as noites a partir de um processo em lote (tabela de 70 GB). Esta tabela é apenas lida e é atualizada todas as noites usando mysqlimport.

Durante o processo de atualização para esta tabela apenas de referência, os logs binários estão causando um atraso bastante sério (especialmente quando faço uma atualização completa de 70 GB). Tem que fazer 70 GB duas vezes basicamente. que nojo.

A tabela de referência está atualmente usando MyISAM (eu bloqueei o InnoDB tentando carregá-lo, então tentando MyISAM).