Início / dba / Perguntas / 12304
Accepted
quanta
Asked: 2012-02-06 22:05:40 +0800 CST

LOAD DATA (400k rows) INFILE leva cerca de 7 minutos, não pode matar o processo de "logging slow query"?

  • 772

Versão do MySQL: 5.5.13

A consulta que estou usando:

SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY  ','; 
COMMIT;

Normalmente, leva 3s para ser concluído. Mas, às vezes, a consulta de inserção leva cerca de 3 a 7 minutos para ser executada:

     Id: 97
   User: tom
   Host: 192.168.6.31:27059
     db: test
Command: Query
   Time: 155
  State: logging slow query
   Info: COMMIT

e meu banco de dados está completamente bloqueado (muito lento ao conectar). O ID do processo 97 continua em execução depois que tento eliminá-lo. O script init mostra o sinalizador [FAILED] ao tentar reiniciar o MySQL, então devo usar kill -9e começar de novo.

Vou tentar desabilitar o unique_checkssiga este guia. Mas estou tentando descobrir por que está trancado.

Existem alguns Waiting for table metadata lockestados em tabelas temporárias:

     Id: 180098
   User: jerry
   Host: 192.168.6.31:54909
     db: test
Command: Query
   Time: 142
  State: Waiting for table metadata lock
   Info: DROP TABLE IF EXISTS norep_locationtmp

e um Waiting for table level lockestado:

     Id: 180233
   User: bob
   Host: 192.168.6.31:43537
     db: test
Command: Query
   Time: 65
  State: Waiting for table level lock
   Info: SELECT COUNT(DISTINCT A.`campaignid`)  INTO _c
FROM `ox_campaigns` A 
INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
WHERE A.`revenue_type` = 5 AND A.`deleted` = 0 AND A.`expire` = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) 
AND A.`isExpired` = 0 AND IF( NAME_CONST('_permitid',3) = -1, 1=1, IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN (SELECT C.`user_id` FROM `selfserving_users` C WHERE C.`groupid` =  NAME_CONST('_groupid',12) ) ,A.`uid` =  NAME_CONST('userid',388)))

Não há nada relacionado à tabela acima - v3_zone_datee nenhum impasse detectado em SHOW ENGINE INNODB STATUS. Onde devo dar uma olhada primeiro para solucionar este caso? Deixe-me saber se você precisar de mais informações.

Responder a @RolandoMySQLDBA:

A parte assustadora sobre a consulta é a auto-referência

Você está selfserving_usersagindo de maneira egoísta contra si mesmo.

Vou pedir ao desenvolvedor para reescrevê-lo.

  • Você está martelando o Buffer InnoDB
  • Alguma troca de memória pode estar acontecendo

Eu tenho 40 GB de RAM e:

innodb_buffer_pool_size     = 20G
innodb_buffer_pool_instances    = 8

O gráfico de memória exibe que não há aumento.

Possíveis problemas de bloqueio de tabela completa que estão afetando as páginas de dados fora da v3_zone_datetabela (como aconteceu com a selfserving_users tabela)

Você tem alguma ideia para evitar o problema de bloqueio de tabela completa?

Pode haver uma maneira de acelerar o processo LOAD DATA INFILE em uma tabela InnoDB. Não posso dar uma resposta sólida sobre isso, mas tente este link do Barão Schwartz.

Vou tentar o método fifo e informo o resultado.

ATUALIZAÇÃO: quarta-feira, 22 de fevereiro 12:19:03 ICT 2012

Abaixo está o trecho da SHOW ENGINE INNODB STATUSsaída ao pendurar:

---TRANSACTION 1EF3CC26, ACTIVE (PREPARED) 332 sec
68 lock struct(s), heap size 14776, 6933 row lock(s), undo log entries 3465
MySQL thread id 4088, query id 11411947 192.168.6.31 bob
COMMIT
Trx read view will not see trx with id >= 1EF3CC27, sees < 1EF06C8E

Com base no ID do thread, descobri que o culpado é um processo .NET que está carregando dados no banco de dados seguindo as etapas:

  • BLOQUEIO DE MESAS;
  • SET autocommit=0;
  • SET verificações_únicas=0;
  • SET checagem_chave_externa=0;
  • CARREGAR DADOS;
  • COMPROMETER-SE;
  • DESBLOQUEAR MESAS;
  • SET verificações_únicas=1;
  • SET Foreign_key_checks=1;

Parece que o processamento de inserção trava na etapa COMMIT, a tabela não pode ser desbloqueada e congelando meu banco de dados. Devo trocar a etapa COMMIT e UNLOCK TABLES?

De acordo com o documento MySQL:

A partir do MySQL 5.5.5, não é mais possível definir @@session.sql_log_bin em uma transação ou subconsulta. (Bug #53437)

Pelo que entendi, 400 mil linhas não são grandes o suficiente, não consigo descobrir por que bloqueou meu banco de dados?

Alguma ideia?

mysql innodb

2 respostas

  1. Best Answer

    A consulta sob o ID de processo 180233 parece estar em perigo.

    Aqui está a consulta em si

    SELECT COUNT(DISTINCT A.`campaignid`)  INTO _c
FROM `ox_campaigns` A 
INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
WHERE A.`revenue_type` = 5 AND A.`deleted` = 0
AND A.`expire` = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) 
AND A.`isExpired` = 0
AND IF( NAME_CONST('_permitid',3) = -1, 1=1,
IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN
(SELECT C.`user_id` FROM `selfserving_users` C
WHERE C.`groupid` =  NAME_CONST('_groupid',12) ) ,
A.`uid` =  NAME_CONST('userid',388)));

    A parte assustadora sobre a consulta é a auto-referência

    Você está selfserving_usersagindo de maneira egoísta contra si mesmo.

    Às vezes, o MySQL Query Optimizer jogará jogos de isca e troca, fumaça e espelhos com dados, especialmente com uma auto-referência, a fim de formular o melhor plano EXPLAIN possível. Embora o mysql seja muito capaz de concluir sub-SELECTs, ainda pode ser caro .

    No entanto, este é apenas um sintoma que se manifestou devido ao ID do processo 97. Qual é realmente o problema aqui?

    LOAD DATA INFILE contra uma tabela InnoDB pode deixar o mysqld um pouco bêbado. Não acredito (ou pelo menos não tenho total confiança) que você pode encapsulá-lo como uma transação normal, embora isso tenha sido resolvido no MySQL 5.0 .

    Imagine só:

    • Você está martelando o Buffer InnoDB
    • Alguma troca de memória pode estar acontecendo
    • Possíveis problemas de bloqueio de tabela completa que estão afetando as páginas de dados fora da v3_zone_datetabela (como aconteceu com a selfserving_userstabela)

    Pode haver uma maneira de acelerar o processo LOAD DATA INFILE em uma tabela InnoDB. Não posso lhe dar uma resposta sólida sobre isso, mas tente este link do Baron Schwartz .

    ATUALIZAÇÃO 2012-02-22 12:00 EST

    Há um relatório de bug aberto no MySQL 5.5.7 chamado Deadlock quando DDL em LOCK TABLES WRITE, READ + PREPARE. No final do relatório, uma pessoa reclamou de um problema de bloqueio causado pelo LOCK TABLES.

    Iniciar um COMMITem linhas bloqueadas em uma tabela travaria devido à tentativa de desvendar os dados MVCC associados às linhas bloqueadas. Com base no status do InnoDB que você mostrou, haveria 6933 bloqueios de linha na tabela que você está importando. Eu sei que no Oracle, ao introduzir novas linhas em uma tabela, o MVCC ainda é gerado porque a versão anterior da linha recém-inserida é uma linha inexistente. O mesmo deve estar ocorrendo para o InnoDB.

    ATUALIZAÇÃO 2012-02-22 12:42 EDT

    Em sua pergunta, você afirmou o seguinte sobre seu processo .NET

    • BLOQUEIO DE MESAS;
    • SET autocommit=0;
    • SET verificações_únicas=0;
    • SET checagem_chave_externa=0;
    • CARREGAR DADOS;
    • COMPROMETER-SE;
    • DESBLOQUEAR MESAS;
    • SET autocommit=1;
    • SET verificações_únicas=1;

    Todos esses eventos estão sendo executados na mesma sessão de banco de dados. Isso também está acontecendo dentro de uma conexão de banco de dados. Portanto, não se trata de um impasse no sentido tradicional. É apenas um caso de bloquear seu COMMIT dentro de uma determinada conexão/sessão do banco de dados porque as tabelas foram bloqueadas dentro da mesma conexão/sessão do banco de dados.

    ATUALIZAÇÃO 2012-02-23 19:00 EDT

    Eu mudaria a sequência para esta:

    • SET autocommit=0;
    • SET verificações_únicas=0;
    • SET checagem_chave_externa=0;
    • BLOQUEIO DE MESAS;
    • CARREGAR DADOS;
    • DESBLOQUEAR MESAS;
    • COMPROMETER-SE;
    • SET autocommit=1;
    • SET verificações_únicas=1;
    • SET Foreign_key_checks=1;

    Lembre-se de COMMITque não é possível prosseguir se você tiver as tabelas bloqueadas de maneira serial. Portanto, UNLOCK TABLESdeve preceder COMMIT.

    • 2

  2. Em primeiro lugar, quero saber sobre a ferramenta MySQL usada por você, seja GUI ou Command Line. Como, há muita diferença entre estande as ferramentas.

    A configuração do meu sistema é P4 com 3 GB de RAM e 500 GB de HDD. Portanto, usando recursos mínimos ou muito menores em comparação com a disponibilidade do seu sistema. Carreguei um arquivo de cerca de 1 GB contendo cerca de 2.258 mil linhas e tentei inserir por meio da GUI e da linha de comando, para minha surpresa, a diferença é muito alta. Através da GUI, o tempo necessário para inserir é de cerca de 8 a 10 minutos e, através da linha de comando, leva apenas 1,45 minutos.

    Por que há tantas diferenças é o funcionamento de ambas as ferramentas. A ferramenta GUI depende da memória disponível para ela e os threads disponíveis são limitados, pois dependem do sistema operacional que controla toda a memória do sistema. As transferências de bytes dependem da memória do sistema disponível e ele enviaria os bytes limitados para a ferramenta em muitas divisões. Portanto, se você tiver 40 GB de RAM, o máximo de RAM disponível para a ferramenta seria de cerca de 2 a 3 GB e a GUI primeiro o lê, em seguida, envie o comando, verifique a execução e, em seguida, vá para outro conjunto. Mas os bytes permaneceriam na Memória até que a execução fosse concluída. Outro motivo são os encadeamentos, os encadeamentos máximos usados ​​pela GUI no meu caso são 45 encadeamentos.

    Por outro lado, na ferramenta de linha de comando, todas as coisas são gerenciadas pela própria ferramenta, pois não depende de nenhuma memória do sistema. Ele iniciará quantos threads forem necessários para o processo e o processo será feito rapidamente, em seguida, GUI. Neste caso o máximo de roscas utilizadas pela ferramenta é de 90 roscas.

    Espero que esta informação resolva muito seu problema para ..

    • 0

relate perguntas