我有一个非常大的 InnoDB 表,目前存储了大约 2.6 亿行,大小为 40GB。
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)
为了避免表的无限增长,我计划在数据库每天不那么繁忙的时间删除超过 2 年的行。我尝试了以下查询。
DELETE FROM objects WHERE DATEDIFF(NOW(), timestamp ) >= 731;
但它给了我错误:
Error 1206: The total number of locks exceeds the lock table size
其大小innodb_buffer_pool_size
为 128 MB,我认为这非常小。不幸的是,主机的可用 RAM 不超过 300 MB。我还没有尝试增加innodb_buffer_pool_size
缓冲区的大小,但我认为几百 MB 还不够,而且没有更多的空间可以增加它。查询非常慢,主机 RAM 较低,数据库正在积极为客户提供服务,并且正在运行的应用程序将数据一致地插入到数据库中。还有另一个人正在使用该应用程序,如果想重新启动数据库,我必须先要求他停止该应用程序。因此,innodb_buffer_pool_size
通过反复试验进行设置是一项棘手的工作。您能建议我如何计算大约的最小尺寸innodb_buffer_pool_size
以避免该错误吗?
我没有尝试过的另一种方法是 - 因为表有timestamp
和objectID
列,并且它由这些列索引,所以可以逐个对象删除过期的行。首先让我们收集所有唯一的对象 ID:
SELECT DISTINCT objectID FROM objects;
大约需要 30-40 秒。然后通过objectID删除:
DELETE FROM objects WHERE objectID = ... DATEDIFF(NOW(), timestamp ) >= 731;
但如何将这两个查询合并为一个查询呢?
DELETE FROM objects WHERE objectID IN (SELECT DISTINCT objectID FROM objects) AND DATEDIFF(NOW(), timestamp ) >= 731;
给出一个错误
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 |
+-----------------------------------+
为了避免长时间锁定,您必须创建一个存储过程,它将在循环中删除有限数量的行:
循环的每次迭代
massdel
将锁定表的时间不超过删除 1000 行所需的时间,并且将重复该过程,直到conditions
删除满足 的所有行。因此,它的执行将与其他查询交错执行,不会显着减慢 - 但代价是删除时间更长。您可以尝试其他limit
值来更改删除的粒度。MySQL 告诉您它没有足够的空间来存储执行查询所需的所有行锁。
要修复它,您需要调整 innodb_buffer_pool_size 并重新启动 MySQL。
默认情况下,该值设置为仅 8MB,因此请逐步增加它,直到满足您的要求。
阅读MySQL 手册如何接近最佳大小