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 |
+-----------------------------------+
