Início / dba / Perguntas / 28055
Accepted
Haseena
Asked: 2012-11-02 22:18:56 +0800 CST

A instrução TRUNCATE TABLE às vezes trava

  • 772

Por que a TRUNCATE TABLEdeclaração trava às vezes? Quais são as razões para este tipo de problema?

Estou migrando do MySQL para o MariaDB. Este problema não acontece com o MySQL, apenas com o MariaDB.

A declaração suspensa é simplesmente:

TRUNCATE TABLE sampledb.datatable;

O que pode fazer com que isso aconteça e como posso corrigi-lo?

Outra observação é se a tabela tiver alguns dados, pode ser uma ou duas linhas, então a consulta truncada funciona com sucesso. Caso contrário, a tabela tem muitos dados, a consulta fica travada.

mysql myisam

7 respostas

  1. Best Answer

    A razão pela qual você experimenta degradação de desempenho ou trava durante a execução TRUNCATE TABLEé um problema conhecido com esta declaração. Consulte o Bug #68184:Truncate table causa innodb stalls. Existem outros números de bugs abertos para versões anteriores também.

    Você pode usar:

    CREATE TABLE log_table_new LIKE log_table;
RENAME TABLE log_table TO log_table_old, log_table_new TO log_table;
DROP TABLE log_table_old;

    Fica complicado para tabelas com AUTO_INCREMENTvalores: uma nova tabela é criada com um AUTO_INCREMENTvalor que é imediatamente obtido na tabela de trabalho. Se você não quiser usar os mesmos valores, você pode:

    ALTER TABLE log_table_new AUTO_INCREMENT=some high enough value;
    • 16

  2. Você deve lembrar que TRUNCATE TABLE é DDL e não DML.

    Em vez de descobrir onde no encanamento do TRUNCATE TABLE ele está ficando preso, você pode ter que resolver o problema com suas próprias mãos, substituindo este

    TRUNCATE TABLE sampledb.datatable;

    com isso

    CREATE TABLE sampledb.datatablenew LIKE sampledb.datatable;
ALTER TABLE sampledb.datatable RENAME sampledb.datatablezap;
ALTER TABLE sampledb.datatablenew RENAME sampledb.datatable;
DROP TABLE sampledb.datatablezap;

    Isso pode apenas desligar o problema (talvez pendurado no DROP TABLE), mas a tabela fica disponível rapidamente. DROP TABLEfoi melhorado no MySQL 5.5.23 .

    Eu discuti TRUNCATE TABLE em meus posts anteriores

    De uma chance !!!

    • 6

  3. Há um bug de longa data no InnoDB que afeta tanto o MySQL ( #98869 ) quanto o MariaDB ( MDEV-9459 ) que resulta em longas paralisações, proporcionais ao tamanho de seu pool de buffers (normalmente cerca de 1 segundo por 32 GB de pool de buffers, dependendo do o desempenho de sua CPU e largura de banda de memória de seu servidor).

    Isso foi corrigido no MySQL 8.0.23 e MariaDB 10.2.19. Se a atualização não for uma opção (e eu entendo que para muitos pode não ser uma opção palatável por vários motivos), existem mitigações e soluções alternativas que podem ser aplicadas para esses stalls DROP/TRUNCATE com MySQL e MariaDB .

    Por exemplo, o bug não se manifesta de você DELETE todas as linhas da tabela antes de fazer um DROP. Se isso estiver afetando você no contexto de uso de tabelas temporárias, você pode alternar seu mecanismo de tabela temporária padrão para MyISAM (ou especificar MyISAM explicitamente ao criar suas tabelas temporárias).

    • 5

  4. Sem mais informações, é difícil dizer, e não sou especialista especificamente em MySQL/MariaDB, mas os motivos gerais para uma consulta inesperadamente demorar mais do que o normal para ser executado são:

    • Bloqueios nessa tabela: a consulta pode ficar esperando que outras transações, que mantêm bloqueios nessa tabela, sejam concluídas. Nesse caso, a instrução será pausada, sem usar recursos de CPU ou E/S, até que os bloqueios concorrentes sejam liberados.
    • Bloqueios em outras tabelas: se você tiver chaves estrangeiras em outro lugar se referindo a essa tabela, a operação de truncar precisará certificar-se de que não está descartando linhas que ainda são referenciadas. Isso significa que os bloqueios nessas outras tabelas também podem fazer com que ela seja pausada.
    • Diferenças nas verificações de integridade referencial: se o seu novo banco de dados tiver FKs definidos referindo-se a essa tabela onde o antigo não tinha, isso também poderia explicar uma diferença na velocidade de truncar. Se a tabela que está sendo truncada e/ou as tabelas de referência forem muito grandes, isso pode estar longe de ser instantâneo (se esse for o motivo de seus diferentes comportamentos observados, você esperaria ver a instrução impor alguma carga de E/S e/ou CPU).
    • 2

  5. Em mariadb truncate table atua como uma tabela de descarte e recriação implícita da tabela vazia. No entanto, as operações de truncamento não podem ser executadas se a sessão tiver um bloqueio de tabela ativo.

    Somente com a tabela InnoDB, o InnoDB processa TRUNCATE TABLEexcluindo as linhas uma a uma se houver alguma FOREIGN KEYrestrição que faça referência à tabela. Se não houver FOREIGN KEYrestrições, o InnoDB executa um truncamento rápido, eliminando a tabela original e criando uma vazia com a mesma definição. Como você afirma que se uma tabela tiver poucos registros truncar é rápido e se você tiver muitos registros truncar é infinito, posso supor que você tenha InnoDB e FK.

    Além disso, antes de truncar sua tabela, tente IS_FREE_LOCK(str)a função para verificar se a tabela está bloqueada. Em caso afirmativo, existe a IS_USED_LOCKfunção de recuperar o thread que está segurando o bloqueio.

    • 1

  6. O TRUNCATEpode travar, porque outros processos podem usar esta tabela, portanto, para verificar, execute no shell:

    mysqladmin proc

    Em seguida, mate as consultas que estão usando (mude 123para Id do processo):

    mysqladmin kill 123

    Também rodando:

    mysql -e "SHOW ENGINE INNODB STATUS\G"

    também pode ajudar a investigar qualquer travamento.

    Relacionado: Como depurar o tempo limite de espera de bloqueio excedido?

    • 0

  7. Eu enfrentei o mesmo problema e foi resolvido reiniciando o servidor mysql.

    systemctl restart mysqld

    Eu sei que esta não é uma solução esperada, mas se você estiver preso, poderá reiniciar o servidor mysql.

    • -1

relate perguntas