我有一个非常大的 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 |
+-----------------------------------+