为什么一个简单的MySQL update查询偶尔需要几分钟？

Question

我有一个强大的数据库服务器，上面有很多非常相似的InnoDB数据库。我经常运行的一个查询只是更新一个小表中一行上的时间戳。大多数情况下，这需要1-2毫秒。偶尔，在晚上，当备份和maatkit复制工具正在运行时，这些查询中的一个或多个可能会显示几分钟的“正在更新”。在此期间，其他查询(包括maatkit查询)似乎正在正常进行，并且似乎没有其他查询正在执行。我一直无法解释或解决这个问题。

我们在一对4路至强上使用mysql4.1.22和gentoo 2.6.21，它们有16G的内存和RAIDed驱动器用于存储。复制已经就位，并且运行良好，maatkit每晚都会确认复制。InnoDB占用了大部分内存，通常有70-80%是空闲的。所讨论的表有大约100行，每行大约200字节。我尝试在WHERE子句上使用和不使用索引，但没有明显的变化。未发现异常日志消息(已检查系统消息和mysql错误)。

还有人听说过这个吗？解决了这样的问题？有没有关于如何调查的想法？

Answer 1

使用响应中提供的一些信息，我们继续调查，并在我们的服务器上发现了一些令人不安的行为。任何数据库中任何表的简单"check table“都会导致简单的update查询锁定在其他数据库和其他表中。我不知道为什么会发生这种情况，尽管我们不能在MySQL v5.1上重现它，所以我们打算升级我们的数据库服务器。

我不认为maatkit的mk-table-checksum做了一个“检查表”，但它有类似的效果。关闭这个脚本可以极大地减少问题，但是我们相信没有这个脚本我们不能生活。

我将把这个标记为我的问题的答案。谢谢你的帮助。

Answer 2

在进行DML操作时，InnoDB会在行和索引间隙上放置锁。

问题是它会锁定所有被检查的行，而不仅仅是那些受影响的行。

比方说，如果您运行以下查询：

UPDATE  mytable
SET     value = 10
WHERE   col1 = 1
        AND col2 = 2

，锁定将取决于用于查询的索引：

• 如果使用col1, col2上的索引，则仅锁定受影响的行
• 如果使用col上的索引，则将锁定具有col1 = 1的所有行
• 如果使用col2上的索引，则将锁定
• 如果不使用索引，将锁定所有行和索引间隙(包括PRIMARY KEY列上的行和索引间隙)

更糟糕的是，MySQL中的EXPLAIN不适用于DML操作，因此您必须猜测使用了哪个索引，因为优化器可以选择它认为最好的索引。

因此，您的复制工具和更新可能会同时锁定记录(正如您所看到的，即使WHERE条件不重叠，也可能会发生这种情况)。

Answer 3

如果您可以在此查询挂起时到达服务器，请尝试执行"show innodb status“。您从中获得的混乱数据的一部分是InnoDB表上所有活动连接/查询的状态。如果您的查询因为另一个事务而挂起，它将在那里指示。这里有锁数据here的示例。

此外，您还提到在备份过程中似乎会发生这种情况。您是否使用mysqldump来实现此目的？这将在转储处于活动状态时锁定表，以便转储的数据是一致的。