MySQL的DELETE查询非常慢，而Apache在查询运行时很奇怪

Question

首先，一些细节来描述整个情况:

在功能非常强大(32个CPU内核，64GB RAM)的FreeBSD 8.1-RELEASE计算机上运行的MySQL(5.1.50)数据库，该计算机还运行Apache 2.2。

Apache每秒平均获得约50次点击。这些命中的绝大多数是针对销售平台的API调用。

API调用通常需要大约半秒或更短的时间才能生成结果，但是取决于第三方，可能最多需要30秒。

每个API调用都会在数据库中存储一行。在那里存储的信息很重要，但只能保留大约十五分钟，此后必须过期。

在存储API调用信息的表中(此表的架构在下面)， InnoDB行级锁定用于在线程(实际上是Apache连接)之间同步，同时请求相同的信息，这种情况经常发生。这意味着几个线程可能会等待一行锁长达30秒，因为API调用可能花费那么长的时间(但通常不会这样)。

最重要的是要注意的是，在正常情况下，一切都可以正常运行。

就是说，这是一个使用率很高的表(每秒五十个左右的INSERT，许多SELECT，使用行级锁定)，我在以下位置运行DELETE查询:

CREATE TABLE `sales` (
  `sale_id` int(32) unsigned NOT NULL auto_increment,
  `start_time` int(20) unsigned NOT NULL,
  `end_time` int(20) unsigned default NULL,
  `identifier` char(9) NOT NULL,
  `zip_code` char(5) NOT NULL,
  `income` mediumint(6) unsigned NOT NULL,
  PRIMARY KEY  USING BTREE (`sale_id`),
  UNIQUE KEY `SALE_DATA` (`ssn`,`zip_code`,`income`),
  KEY `SALE_START` USING BTREE (`start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=FIXED

DELETE查询如下所示，并在cron上每五分钟运行一次(我希望每分钟运行一次):

DELETE FROM `sales` WHERE 
    `start_time` < UNIX_TIMESTAMP(NOW() - INTERVAL 30 MINUTE);

我已经将 INT用于时间字段，因为很明显MySQL在使用带有 DATETIME字段的索引时遇到了麻烦。

所以这就是问题所在: DELETE查询在大多数情况下似乎运行良好(可能是10次中的7次)。其他时候，查询很快完成，但是之后MySQL似乎停滞了一段时间。我不能完全证明是MySQL在起作用，但是症状出现的时间肯定与运行此查询的时间一致。这是一切阻塞时的症状:

登录到MySQL并使用SHOW FULL PROCESSLIST;，只有少数INSERT INTO销售...查询正在运行，通常有一百多个。实际上，这里的异常是流程列表中没有任何任务，而不是太多。看来MySQL完全停止了连接。

检查Apache服务器状态，Apache已达到MaxClients。所有线程均处于“正在发送答复”状态。

Apache开始使用大量系统时间CPU。平均负载上升，我看到1分钟平均负载高达100。这台机器的正常平均负载约为15。我看到它使用的是系统CPU(而不是用户CPU)，因为我使用GKrellM进行监视它。

在top中，有许多使用大量CPU的Apache进程。

该网站和API(当然是由Apache提供)在大多数时间都无法访问。有些请求会通过，但大约需要三到四分钟。其他请求在一段时间后回复“无法通过/tmp/mysql.sock连接到MySQL服务器”错误-这与我在MySQL容量过大且连接过多时收到的错误相同(只有它不会实际上说了太多的联系)。

MySQL最多接受1024个连接，mysqltuner.pl报告“[!!]最高连接使用率:100％(1025/1024)”，这意味着在某一点它承担的工作量超过了它的处理能力。通常，在正常情况下，最多只有几百个并发MySQL连接。 mysqltuner.pl没有其他问题，如果有人愿意，我很乐意粘贴输出。

最终，大约一两分钟后，情况会自行恢复，无需任何干预。 CPU使用率恢复正常，Apache和MySQL恢复正常运行。

那么，我该怎么办？ :)我什至可以开始调查为什么会发生这种情况？我出于各种原因需要运行DELETE查询，为什么在运行时事情变得一团糟(但并非总是如此)？

Answer 1

辛苦了。这不是回应，而是头脑 Storm 的开始。

我会说，也许是关于删除的重新索引问题，在文档上我们可以找到“快速删除”，然后是“优化表”，以尝试避免多索引合并。

可能还有另一种可能性，那就是在删除时与至少一个其他线程建立一条死锁链，行锁可以暂停删除操作，而删除操作可以暂停下一个行锁定。然后，您要么检测到死锁，要么检测不到死锁，从而发生超时。您如何检测此类并发中止的异常？您是否重新运行交易？如果线程在同一事务中执行许多不同的行锁，则第一个死锁很可能会影响越来越多的线程(流量阻塞)。

您是否试图在删除事务中锁定表？查看手册，锁定Innodb中事务中的表的方式，或获取所有行上的SHARE LOCK。也许要花一些时间才能只为您获取表格，但是如果删除速度非常快，没人会注意到您只为您拿了一张表格。

现在，即使您之前没有尝试过，也可能是删除操作正在执行的操作。还要检查this doc on implicit locks，您的删除查询应该使用start_time索引，因此，我很确定您当前的删除操作不会锁定所有行(不是完全确定，它们不仅锁定了与where条件匹配的行，而且还锁定了所有已分析的行)，但是删除肯定会阻止插入。解释了带有事务执行删除操作的死锁的一些示例。祝你好运!对我来说，现在了解所有锁隔离的影响为时已晚。

编辑，您可以尝试通过 UPDATE 设置 Deleted = 1 来更改 DELETE ，并在使用率较低时(如果有的话)执行真正的删除。并更改客户端查询以检查此索引已删除状态。