MySQL高写入延迟

Question

我正在开发一款类似社交的应用程序，目前使用 AWS 服务进行部署。具体而言，DB 运行在使用 MYSQL 的 RDS 上。 到目前为止，我们正在使用有限数量的用户（大多是朋友）测试该应用程序，结果平均每秒写入 IOPS 为 15。

真正的问题与 db 的非常高的写入延迟有关，始终超过 100ms。RDS 实例是一个 db.m3.xlarge，比我们所需的要多得多。

我尝试在单独的实例中执行负载测试（DB 和 EC2 的配置相同），但即使我发送了更多的请求，也无法复制出这样高的延迟。因此我认为可能是由于表碎片导致的，但我还没有运行表优化，因为在此过程中 DB 将不可访问。

您是否有这个问题的经验？

更多信息：

• We're using mysql version 5.6.21 with INNODB as storage engine.
• The whole DB is about 100MB in size

• The biggest table (called Message) has about 790k rows. Concerning this table, the following query

  insert into Message (user_id, creationDate, talk_id, text, id) 
  values (2015, '2015-02-01 16:40:06.737', 18312, 'Some text ', 904870)
  

  took 11s to be executed.

• Even worse, the query

  insert into Comment (anonymous, user_id, creationDate, deleted, post_id, text, id) 
  values (1, 107347, '2015-02-01 16:40:01.849', 0, 124888, 'Comment text', 265742)
  

  took 14s, but the table Comment has about 160k.

这两个表格是通过以下方式生成的：

CREATE TABLE `comment` (
    `id` bigint(20) NOT NULL,
    `anonymous` bit(1) NOT NULL,
    `creationDate` datetime NOT NULL,
    `deleted` bit(1) NOT NULL,
    `text` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
    `user_id` bigint(20) NOT NULL,
    `post_id` bigint(20) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_jhvt6d9ap8gxv67ftrmshdfhj` (`user_id`),
    KEY `FK_apirq8ka64iidc18f3k6x5tc5` (`post_id`),
    CONSTRAINT `FK_apirq8ka64iidc18f3k6x5tc5` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`),
    CONSTRAINT `FK_jhvt6d9ap8gxv67ftrmshdfhj` FOREIGN KEY (`user_id`) REFERENCES `kuser` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

并且。

CREATE TABLE `message` (
    `id` bigint(20) NOT NULL,
    `creationDate` datetime NOT NULL,
    `text` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
    `user_id` bigint(20) NOT NULL,
    `talk_id` bigint(20) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_d0j091jvk2y4mmfbadnqlohtf` (`user_id`),
    KEY `FK_64tr15t6wu5y9u143gxt6o3g2` (`thread_id `),
    CONSTRAINT `FK_64tr15t6wu5y9u143gxt6o3g2` FOREIGN KEY (`thread_id`) REFERENCES `thread` (`id`),
    CONSTRAINT `FK_d0j091jvk2y4mmfbadnqlohtf` FOREIGN KEY (`user_id`) REFERENCES `kuser` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

一些图表

使用AppDynamics，我能够提取以下图表：

• 等待状态：查询结束时间是否过长？

  

• 页面缓存：

  

• 写延迟和队列：

  

查询缓存

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 1048576   |
| query_cache_type             | OFF       |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

感谢您的帮助！

安德烈

- a.periz

---

我们需要更多的细节。您还没有说明MySQL版本，存储引擎，数据库模式，数据大小，示例查询等。我们有一个非常高的写入系统，没有任何问题。 - Marcus Adams

谢谢你的回答，马库斯。我已经添加了更多信息。 - a.periz

我期望看到 id 是 UNSIGNED 和 AUTO_INCREMENT 的。你是如何生成这些 ID 的？ - Marcus Adams

我们正在使用Hibernate（JPA）来查询数据库。Hibernate会保留每个表的最后一个使用的ID，并将其用于插入新行。 - a.periz

MySQL每次分配4个extent（每个1MB）的空间。Profile你的查询以确保它是MySQL时间，而不是其他什么。是的，它可能会变得碎片化，但是碎片化只会影响读取大量连续记录，而不是搜索和选择单个记录，这是最常见的情况。 - Marcus Adams

Answer 1

我联系了亚马逊的RDS工程师，他们给了我解决方案。

这么高的延迟是由于存储类型性能非常低。事实上，我使用的是默认的5GB SSD（称为GP2），每GB存储提供3个IOPS，导致我的应用程序需要约50个或更多IOPS时只有15个IOPS。

因此，他们建议我将存储类型更改为 Magnetic，基准值为100 IOPS。此外，我还可以减小实例类型，因为瓶颈只在磁盘上。

由于源磁盘（GP2）性能非常低，迁移花费了约3小时。

希望这对某些人有所帮助！

- a.periz

---

不知道存储规则中每GB的IOPS为3。通过谷歌搜索了解是否超出限制，AWS支持表示：“目前没有可用于查看GP2 IO Credit使用和余额的指标。一种可行的方法是监视磁盘队列深度、读取延迟和写入延迟等指标。如果您的RDS实例耗尽了IO Credit，则这些指标将上升。因此，如果您看到这些指标突然增加并保持一致高，这表明您已经耗尽了IO Credit。”来源：https://forums.aws.amazon.com/thread.jspa?threadID=182707 - sandre89

Answer 2

您的查询配置文件显示“查询结束”时间非常长。这可能是由于查询缓存太大了。每次执行更新语句（INSERT、DELETE、UPDATE）时，都必须更新查询缓存（读取更新表的每个查询都会失效）。

- Marcus Adams

---

再次感谢！我检查了与查询缓存相关的变量（请参见问题），看起来缓存已被禁用。 - a.periz