主页 / dba / 问题 / 18663
Accepted
Alan
Asked: 2012-06-01 17:33:17 +0800 CST

随机写入冻结

  • 772

我有一个 MySQL 5.5.23 数据库，它执行大约 3k qps，其中 5% 是写入。最近，我遇到了随机写入冻结的大问题。一切都进行得很顺利，然后突然间任何写入都停止在“更新”或“正在更新”状态。他们在这种状态下停留大约一两分钟，然后终于完成。问题是连接数量如此之多，当它们像这样随机冻结时，连接保持打开状态，然后我不可避免地会遇到太多连接错误。

在 INSERT 和 UPDATE 查询期间，它发生在所有表上。

有没有人见过这个？有什么可以做的吗？

我将 Amazon RDS 与他们提供的最大实例一起使用。我非常乐意提供你们需要的任何信息，请告诉我那是什么。

更新：我要插入的主表是：

CREATE TABLE `mytable` (
 `hash` varchar(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `name` varchar(256) DEFAULT NULL,
 `ip` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `deletehash` char(15) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `datetime` datetime NOT NULL,
 `api_key` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
 `account_id` int(10) unsigned DEFAULT NULL,
 `type` varchar(15) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `width` int(10) unsigned NOT NULL DEFAULT '0',
 `height` int(10) unsigned NOT NULL DEFAULT '0',
 `size` int(10) unsigned NOT NULL DEFAULT '0',
 `animated` tinyint(1) NOT NULL DEFAULT '0',
 `views` int(10) unsigned NOT NULL DEFAULT '0',
 `lastviewed` datetime NOT NULL,
 PRIMARY KEY (`hash`),
 UNIQUE KEY `deletehash` (`deletehash`),
 KEY `datetime` (`datetime`),
 KEY `account_id` (`account_id`),
 KEY `ip` (`ip`),
 KEY `api_key` (`api_key`),
 KEY `views` (`views`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

样本插入：

insert into mytable (hash, name, ip, deletehashe, datetime, api_key, account_id, type, width, height, size, animated, views, lastviewed) values('abcde', 'file name', '127.0.0.1', 'abcdefghijklmn', '2012-05-31 00:00:00', NULL, NULL, 'mime/type', 0, 0, 0, 0, 0, '0000-00-00 00:00:00');

示例更新：

update mytable set views = views+1, lastviewed = NOW() where hash = 'abcde';

“更新中”

mysql amazon-rds

1 个回答

  1. Best Answer

    我认为问题是由于聚集索引的选择。从 MySQL 文档，聚簇索引和二级索引：

    每个 InnoDB 表都有一个称为聚簇索引的特殊索引，其中存储行的数据。通常，聚簇索引与主键同义。为了从查询、插入和其他数据库操作中获得最佳性能，您必须了解 InnoDB 如何使用聚集索引来优化每个表的最常见的查找和 DML 操作。

    还要检查@marc_s 在这个 SO 问题中的答案：How to choose the clustered index in SQL Server? ，他提到：

    根据索引女王 - Kimberly Tripp - 她在聚集索引中寻找的主要是：

    • 独特的
    • 狭窄
    • 静止的

    如果你也能保证：

    • 不断增加的模式

    那么您就非常接近拥有理想的集群密钥了！

    现在，您的聚集索引是（主键）：

    hash varchar(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,

    其中（让我们检查清单）是：

    • 独特（是的，好的）
    • 狭窄（是的，好的）
    • 静态（也许，你知道）

    但可能不是：

    • 不断增加的模式（不，它可能不是）

    那么，当您使用非不断增加的聚集索引时会发生什么？

    没有比Kimberly L. Trip更好的回答了：不断增加的集群键 - 集群索引辩论…………再次！

    如果集群键不断增加，那么新行就有一个特定的位置可以放置它们。如果该位置位于表的末尾，则新行需要为其分配空间，但不必在表的中间腾出空间。如果一行插入到没有任何空间的位置，则需要腾出空间（例如，您根据姓氏插入，然后当行进入空间时，需要在应该放置该名字的地方制作）。如果需要腾出空间，SQL Server 会执行称为拆分的操作来腾出空间。SQL Server 中的拆分是 50/50 拆分 - 简单地说 - 50% 的数据保留，50% 的数据移动。这使索引在逻辑上保持完整（索引的最低级别 - 称为叶级别 - 是一个双链表）但在物理上不完整。当一个索引有很多拆分时，则称该索引是碎片化的。不断增加的索引的好例子是 IDENTITY 列（它们自然也是唯一的、自然静态的和自然狭窄的）或者尽可能多地遵循这些东西的东西——比如日期时间列（或者因为那不是很日期时间、身份本身可能是唯一的）。

    请注意，尽管提到了 SQL-Server，但相同的概念也适用于 InnoDB 聚集索引。我想聚集索引有两个问题：

    • 当您插入新行时（“随机”散列保证），它会插入到索引的随机位置。这意味着它有时会发现没有可插入的空间（请注意，InnoDB 总是在索引中留下一些空闲空间，但是当空闲可用空间被填满时）必须对索引进行一些重新排列——这需要时间.

    • 随着时间的推移，重新排列也会导致索引碎片化。这最终会使其他查询和语句变慢。

    • 4

相关问题