Início / dba / Perguntas / 338296
Accepted
0xC0DEGURU
Asked: 2024-04-03 23:18:00 +0800 CST

Como excluir em massa de uma tabela InnoDB muito grande, evitando erro de tamanho do buffer de bloqueio excedido?

  • 772

Eu tenho uma tabela InnoDB muito grande que atualmente armazena cerca de 260 milhões de linhas e 40 GB de tamanho.

mysql> SELECT * FROM  INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'db' AND   TABLE_NAME   = 'objects';
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| def           | db           | objects    | NULL           | NULL              |                       NULL |                          NULL | NULL             | NULL                | NULL                 | NULL                    | NULL                  |  225970904 |            171 | 38667747328 |            NULL |   8046510080 |         0 | 2024-04-02 12:08:15 | NULL        | NULL       |     NULL |                   |           | NULL            |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
1 row in set (0.04 sec)

Para evitar o crescimento infinito da tabela, pretendo excluir as linhas com mais de 2 anos durante os horários em que o banco de dados está menos ocupado todos os dias. Eu tentei a seguinte consulta.

DELETE FROM objects WHERE DATEDIFF(NOW(), timestamp ) >= 731;

Mas isso me dá erro:

Error 1206: The total number of locks exceeds the lock table size

O tamanho innodb_buffer_pool_sizeé de 128 MB, o que acho que é muito baixo. Infelizmente, a máquina host não possui mais de 300 MB de RAM livre. Ainda não tentei aumentar o tamanho do innodb_buffer_pool_sizebuffer, mas suponho que algumas centenas de MB não serão suficientes e não há mais espaço para aumentá-lo. A consulta é muito lenta, a máquina host tem pouca memória RAM, o banco de dados está atendendo ativamente aos clientes e há um aplicativo em execução que insere dados de forma consistente no banco de dados. Tem outro cara que opera com essa aplicação e se quiser reiniciar o banco de dados tenho que pedir para ele primeiro parar aquela aplicação. Portanto, definir innodb_buffer_pool_sizepor tentativa e erro é uma tarefa complicada. Você pode me sugerir como calcular aproximadamente o tamanho mínimo innodb_buffer_pool_sizepara evitar esse erro?

Outra abordagem que não tentei sim - como a tabela possui colunas timestampe objectIDe é indexada por essas colunas as linhas expiradas podem ser excluídas objeto por objeto. Primeiro, vamos coletar todos os IDs de objetos exclusivos:

SELECT DISTINCT objectID FROM objects;

Demora cerca de 30-40 segundos. Em seguida, exclua por objectID:

DELETE FROM objects WHERE objectID = ... DATEDIFF(NOW(), timestamp ) >= 731;

Mas como unir as duas consultas em uma só?

DELETE FROM objects WHERE objectID IN (SELECT DISTINCT objectID FROM objects) AND DATEDIFF(NOW(), timestamp ) >= 731;

dá um erro

ERROR 1093 (HY000): You can't specify target table 'objects' for update in FROM clause

Description:    Ubuntu 12.04.1 LTS

mysql> select version();
+-----------------------------------+
| version()                         |
+-----------------------------------+
| 5.6.14-1+debphp.org~precise+1-log |
+-----------------------------------+
mysql

2 respostas

  1. Best Answer

    Para evitar bloqueios longos, você deve criar um procedimento armazenado que excluirá um número limitado de linhas em um loop:

     massdel: LOOP
   DELETE 
     FROM table
    WHERE <conditions>
    LIMIT 1000;

   IF ROW_COUNT() = 0   -- number of actually deleted rows
     THEN LEAVE massdel;
   END IF;
 END LOOP massdel;

    Cada iteração do loop massdelbloqueará sua tabela não mais do que o necessário para excluir 1.000 linhas e será repetida até que todas as linhas atendidas por conditionssejam excluídas. Portanto, sua execução será intercalada com outras consultas sem lentidão significativa - mas ao preço de uma exclusão mais longa. Você pode tentar outro limitvalor para alterar a granularidade da exclusão.

    • 2

  2. O MySQL está lhe dizendo que não tem espaço suficiente para armazenar todos os bloqueios de linha necessários para executar sua consulta.

    para consertar você precisa ajustar innodb_buffer_pool_size e reiniciar o MySQL.

    Por padrão, isso é definido para apenas 8 MB, então aumente-o passo a passo até atender às suas necessidades.

    Leia no manual do MySQL como abordar um tamanho ideal

    • 1

relate perguntas