Início / dba / Perguntas / 91388
Accepted
bafromca
Asked: 2015-02-08 12:30:00 +0800 CST

Comando DELETE não concluído na tabela de 30.000.000 linhas

  • 772

Eu herdei um banco de dados e estou procurando limpá-lo e acelerá-lo. Eu tenho uma tabela que contém 30.000.000 de linhas, muitas das quais são dados inúteis inseridos devido a um erro em nome do nosso programador. Antes de adicionar quaisquer índices novos e mais otimizados, converti a tabela de MyISAM para InnoDB e estou procurando excluir muitas das linhas que contêm dados indesejados.

O banco de dados é MySQL 5.0 e tenho acesso root ao servidor. Eu estava executando esses comandos primeiro pelo Adminer e depois pelo phpMyAdmin, ambos com os mesmos resultados.

O comando que estou executando é,

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%'

Essencialmente, exclua qualquer coisa nesta coluna que comece com um traço -.

Ele é executado por cerca de 3 a 5 minutos e, quando vejo a lista de processos, desaparece.

Eu então corro,

SELECT *
FROM `tablename`
WHERE `columnname` LIKE '-%'

e retorna milhões de linhas.

Por que minha declaração de exclusão não está sendo concluída?

PS, estou ciente de como o MySQL 5.0 está desatualizado. Estou trabalhando para mover o banco de dados para o MySQL 5.6 w InnoDB (talvez MariaDB 10 w XtraDB), mas até que isso aconteça, estou procurando responder isso com o banco de dados como está.

--

Edição removida, veja minha resposta.

mysql innodb

6 respostas

  1. Por favor, olhe para a Arquitetura do InnoDB (foto de Percona CTO Vadim Tkachenko)

    Encanamento InnoDB

    As linhas que você está excluindo estão sendo gravadas nos logs de desfazer. O arquivo ibdata1 deve estar crescendo agora durante a exclusão. De acordo com mysqlperformanceblog.comReasons for run-away main Innodb Tablespace :

    • Muitas mudanças transacionais
    • Transações muito longas
    • Tópico de limpeza atrasado

    No seu caso, o motivo nº 1 ocuparia um segmento de reversão junto com algum espaço de desfazer, pois você está excluindo linhas. Essas linhas devem ficar em ibdata1 até que a exclusão seja concluída. Esse espaço é descartado logicamente, mas o espaço em disco não diminui.

    Você precisa matar essa exclusão agora. Depois de eliminar a consulta de exclusão, ela reverterá as linhas excluídas.

    Você faz isso em vez disso:

    CREATE TABLE tablename_new LIKE tablename;
INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%';
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

    Você poderia ter feito isso na versão MyISAM da tabela primeiro. Em seguida, converta-o para InnoDB.

    • 25
  2. Best Answer

    Acho que podemos ter complicado demais a resposta que era exigida no meu caso . Não tenho dúvidas de que tanto Roland quanto Rick James estão corretos com a criação de uma tabela temporária, injetando apenas linhas que passam no filtro NOT LIKE '-%', mas a solução para mim foi "mais fácil" porque houve um erro importante que eu desconhecia até agora e por que eu peço desculpas.

    Executei a consulta no mysqlprompt interativo e notei a mensagem de erro,

    mysql> DELETE FROM `slugs` WHERE `slug` LIKE '-%';
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

    Através do Google do erro, descobri que a solução era aumentar innodb_buffer_pool_sizevia /etc/my.cnfarquivo e reiniciar o daemon mysql. Para o meu servidor foi definido para o padrão 8Me eu aumentei para 1G(o servidor tem 32 GB e esta é a única tabela que atualmente é InnoDB).

    mysql> DELETE FROM `slugs` WHERE `slug` LIKE '-%';
Query OK, 23517226 rows affected (27 min 33.23 sec)

    Então consegui executar o comando e excluir 23 milhões de registros em ~ 27 minutos.

    Para aqueles curiosos sobre o que innodb_buffer_pool_sizedeve ser definido, anote a quantidade de RAM que você possui e, em seguida, dê uma olhada neste tópico que fornece uma estimativa sugerida em GBs.

    • 21

  3. A sugestão de Roland pode ser acelerada fazendo as duas coisas ao mesmo tempo:

    CREATE TABLE tablename_new LIKE tablename;
ALTER TABLE tablename_new ENGINE = InnoDB;
INSERT INTO tablename_new 
    SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key;
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

    Mas aqui está um blog que explica como fazer grandes DELETEs em pedaços, em vez de aparentemente demorar uma eternidade: http://mysql.rjweb.org/doc.php/deletebig A essência é percorrer a tabela através do PK, fazendo 1K fileiras de uma vez. (É claro que há mais detalhes a serem observados.)

    E este blog aborda possíveis pegadinhas na conversão para InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb

    • 12

  4. Meu primeiro instinto seria fazer várias exclusões menores, limitando o número de resultados da consulta e executando a consulta várias vezes:

    DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%' LIMIT 1000000
    • 5

  5. A solução mais fácil é simplesmente não fazer isso - fazer uma exclusão menor, que pode ser processada mais facilmente.

    Nesse caso, eu recomendaria tentar exclusões sequenciais do formulário:

    DELETE
FROM `tablename`
WHERE `columnname` LIKE '-a%'
    • 4

  6. Talvez você possa fazer algo assim:

    • Adicione um novo campo chamado deleted.
    • Faça uma atualização como UPDATE tablename SET deleted=1 WHERE `columnname` LIKE '-a%'.
    • Defina cronpara excluir isso durante a noite.
    • 2

relate perguntas