MySQL 5.6 @ AWS读取IOPS平台低于写入IOPS

Question

我有一个RDS MySQL 5.6实例，它使ReadIOPS稳定在6.5k左右。我的WriteIOPS最常比ReadIOPS低，但有时比ReadIOPS高，但从未进入如此僵硬的高原。几天前，磁盘是gp2 750 GiB (2250 IOPS)，然后是gp2 1000 GiB (3000 IOPS)，现在是io1 1000 GiB / 10000提供的IOPS，平台水平是相同的。实例类型为db.r4.xlarge。

以下是显示问题的图表：

尽管看起来很奇怪，但MySQL方面似乎有什么东西在限制读取。已经启用了实例中的各种监视，而且没有明显的跳出。

MySQL本身是否存在某种限制读取(但不限制写入)的内容？

编辑:表格结构：

CREATE TABLE `position` ( `id` binary(16) NOT NULL, `created_at` datetime DEFAULT NULL, `analyzed` int(11) DEFAULT NULL, `version` varchar(255) DEFAULT NULL, `machine_serial` varchar(255) DEFAULT NULL, `station_id` int(11) DEFAULT NULL, `accelerometer` varchar(255) DEFAULT NULL, `altitude` float DEFAULT NULL, `area_id` int(11) DEFAULT NULL, `coordinates` geometry DEFAULT NULL, `course` float DEFAULT NULL, `gps_status` varchar(255) DEFAULT NULL, `gps_date` datetime DEFAULT NULL, `original_speed` float DEFAULT NULL, `speed` float DEFAULT NULL, `analytics_determined_field_1` bit(1) DEFAULT NULL, `gps_precision` float DEFAULT NULL, `area_uuid` binary(16) DEFAULT NULL, PRIMARY KEY (`id`), KEY `positioncreated_at_idx` (`created_at`), KEY `positionanalyzed_idx` (`analyzed`), KEY `positionstation_idx` (`station_id`), KEY `gps_date_idx` (`gps_date`), KEY `gps_date_station_id_idx` (`station_id`,`gps_date`), KEY `area_id_idx` (`area_id`), KEY `area_uuid_idx` (`area_uuid`), KEY `created_at_idx` (`created_at`), CONSTRAINT `FK_station` FOREIGN KEY (`station_id`) REFERENCES `stations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

我使用r5.xlarge使用io1和10k IOPS运行了一个基准测试，问题似乎是在AWS端，而不是MySQL端，而且绝对是与实例类型相关的。这并不奇怪，因为每个实例类型都有一个最大的IOPS容量，正如亚马逊优化实例文档所指出的，尽管有些细节是没有预料到的。

由于这份文件在分析中起着核心作用，所以我的文本中对“EBS文档”的大多数引用都是含蓄的。

主要调查结果如下：

• IOPS硬顶似乎只适用于读取IOPS，而不是写IOPS，甚至适用于整个IOPS。我对此没有任何解释。
• 读取IOPS的上限是持续的(而不是突发的) 6.5k IOPS，而EBS文档则为6k IOPS做广告。由于EBS文档假设的I/O为16 KB，这种差异可以归因于不同的I/O操作大小。
• 一些较新的实例类型，如t3、r5和m5，具有“最大IOPS速率”和明显较小的基线IOPS速率。EBS文档说，“这些软件支持最高性能，至少每24小时一次”，但不清楚它是一个固定的30分钟窗口(然后使用它还是丢失它)，还是类似于预算的方法。在我的实验中，我能够保持接近最高性能的120分钟，而不是30在高峰时期(这之前进入一个硬平台6.5k左右)，并放大似乎更近，似乎即使是从6k r5 IOPS基线的微小下降，随后不久就会相应增加。这似乎表明，“最大IOPS期”是在信用基础上度过的，如T2/T3无限期，尽管EBS文档似乎没有明确说明这一点。
• 因为我以前有一个1TB的gp2磁盘(3k IOPS)，所以我假设平台将位于3k IOPS，但它却处于实例类型似乎支持的6.5k IOPS。因此，在我的实验中，gp2磁盘在实例类型max而不是3k IOPS达到峰值，因为"Amazon卷类型“doc声明。

PS:里克·詹姆斯的回答是一个很好的总体概述，如果可能的话，优化桌子，避免撞上天花板。

Answer 2

读取被缓存。如果您的数据小于buffer_pool，那么所有的数据都会被读取一次，那么读取的IOP将下降到几乎为零。

有很多写-写数据，更新索引，双缓冲区，撤消日志，二进制日志等等。不过，还有一些缓存在进行。请参阅“更改缓冲区”。

所以，我很惊讶读到的IOP这么高。我无法解释它的平整度。请描述你的应用程序做什么和数据有多大。

UUID

因此，问题的根源在于PRIMARY KEY(id)是一个“随机”值，同时按一定的时间顺序进行读取，或者由station_id读取。

首先，让我们尝试摆脱id。是否有另一种组合的列是唯一的？如果是这样的话，它可能是一个更好的PK。

也许，这是最好的PK：

PRIMARY KEY(station_id, created_at  -- in this order
            , id   -- include only if needed for uniqueness
           ),
INDEX(created_at) -- for finding today's set of rows for analytics?

我想大概只有几百个，或者几千个车站吧？

使用PK(id)，每个INSERT都会碰到表中的一个随机点。考虑到buffer_pool仅为表大小的10%，这意味着90%的时间会出现缓存丢失。SELECTing也是。

使用PK(station_id, created_at)，将会有成百上千的“热点”，因此INSERTs将更容易缓存。即使是需要INDEX(created_at)的读取器，也只会在这几个点上弹跳，而不是在整个表上弹跳。

如果一个块(InnoDB中的16 90 )包含100行(方便的经验规则)，那么到目前为止讨论的插入和选择将是以前的90倍。(好的，它可能只有5到10倍的速度，但这仍然很好，对吧？)

(我讨论了UUID问题这里，但它的解决方法取决于类型1；您使用的是Type 4，相反，我们使用created_at。)

另一个问题..。我看到那张桌子上有几根长串。这些列是否一遍又一遍地包含相同的字符串？如果是这样的话，你应该认真考虑让它们正常化。然后你可以用2字节的VARCHARs代替SMALLINT UNSIGNED (对于65K的不同值)。缩小表将有助于性能，特别是当它大于buffer_pool时。

我看到了几个多余的索引；删除额外的。

INDEX(a), INDEX(a)  -- drop one of them
INDEX(a), INDEX(a,b)  -- drop (a), since the other one can handle its needs

适当时使用NOT NULL。

INT占用4个字节；在适当的地方使用较小的数字类型。