I have an RDS MySQL 5.6 instance which plateaus ReadIOPS at around 6.5k. My WriteIOPS is most often lower, but sometimes higher than ReadIOPS, but has never entered such a rigid plateau. The disk was gp2 750 GiB (2250 IOPS) a couple days ago, then gp2 1000 GiB (3000 IOPS), now io1 1000 GiB / 10000 provisioned IOPS, and the plateau level is the same. Instance type is db.r4.xlarge.

Here is a chart which shows the problem:

enter image description here

As strange as it seems, it seems like something in the MySQL side is capping reads. Have enabled all sorts of monitoring in the instance and nothing obvious jumps out.

Is there anything in MySQL itself which would somehow limit the reads (but not the writes)?

EDIT: table structure:

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

  • Take a look at the EBS FAQ. RDS storage is the same EBS technology as the EC2 instances with the exception of the Aurora class databases. aws.amazon.com/ebs/faqs/?nc=sn&loc=6 The FAQ talks about a linear increase in the needed resources to handle base I/O sizes bigger than 16KB. – Aaron Feb 27 at 2:10
  • That’s interesting, but RDS reports the average I/O size for this database being in between 12 and 15 KB. Also, more than the particular value of the plateau, what caught my attention is more that the plateau level didn’t change with the increase in EBS IOPS than the particular plateau level of 6500. Also, I believe that if the problem was plain EBS IOPS, that would also have affected write IOPS, which it didn’t. – Rafael Almeida Feb 27 at 9:36
  • Have you tried to host the database on an ec2 instance matching the instance size and EBS volume specs to see if the results replicate? – Aaron Feb 27 at 17:30
  • @Aaron that would be an interesting experiment, the dump/restore would take a very long time but I may consider it if I don't find another cause. – Rafael Almeida Feb 27 at 20:05

Reads are cached. If your data is smaller than the buffer_pool, everything will be read once, then read IOPs will drop to virtually zero.

There are many writes -- write the data, update the index, the double-buffer, the undo log, the binlog, etc. Still, there is some caching going on there. See the "change buffer".

So, I am surprised that the Read IOPs are so high. And I can't explain its flatness. Please describe what your app does and how big the data is.


So, the problem stems from having the PRIMARY KEY(id) being a 'random' value, while doing reads in somewhat chronological order, or by station_id.

First, let's try to get rid of id. Is there another combination of columns that is unique? If so, it might be a better PK.

Probably, this is the best 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?

I assume there are only hundreds, maybe a few thousand 'stations'?

With PK(id), each INSERT is hitting a random spot in the table. Given that the buffer_pool is only 10% of the size of the table, this means that 90% of the time there will be a cache miss. Ditto for SELECTing.

With PK(station_id, created_at), there will be hundreds, maybe thousands of "hot spots", so the INSERTs will be more easily cached. And even the reads, which need INDEX(created_at) will bounce around only those few spots, not bounce around the entire table.

If a block (16KB in InnoDB) contains 100 rows (a convenient Rule of Thumb), then the inserts and selects discussed so far will be inherently 90 times as fast as before. (OK, it might be only 5-10 times as fast, but that is still very good, correct?)

(I discuss the UUID problem here, but the cure in it depends on Type 1; you are using Type 4. Instead, we cna take advantage of created_at.)

Another issue... I see several long strings in that table. Do those columns contain the same strings over and over? If so, you should seriously consider normalizing them. Then you could replace VARCHARs with, say, 2-byte SMALLINT UNSIGNED (for up to 65K distinct values). Shrinking the table will help performance, especially when it is bigger than the buffer_pool.

I see several redundant indexes; drop the extras.

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

Use NOT NULL when appropriate.

INT takes 4 bytes; use smaller numeric types where appropriate.

  • My "main table" is about 600 million rows long, data size around 130 GB, index size around 200 GB. When testing with a single query repeated over and over, eventually RIOPS drop to zero due to the buffer_pool, as expected. The problem appears with a batch analytics pipeline which runs every couple hours and reads records "new" to this table based on an indexed column. – Rafael Almeida Mar 7 at 19:29
  • Given the buffer_pool size of around 20 GB, the data size, the access pattern, and especially the index size, it's not too surprising that the buffer_pool is not large enough to hold all the data it needs to and has to go to disk. So I'm more concerned about the flatness than the amount of Read IOPs for now. – Rafael Almeida Mar 7 at 19:33
  • @RafaelAlmeida - Let's dig into that 'pipeline'. Please provide SHOW CREATE TABLE and count how many rows will be in analytics. If the data is continuing to grow in size, then the problems will get worse. There may be some things to work on. – Rick James Mar 7 at 20:11
  • I've edited in a somewhat anonymized version of the table structure. Overall, a simple, flat, record of GPS positions. Typical batch run analyses under 500k rows fetched in several queries by created_at and station columns. analysed column still used in some legacy stuff. But I don't see how this is related to the read IOPS plateau - other optimizations in the pipeline are ongoing already, but it seems a significant short-term gain could be achieved just by removing whatever is "holding back" disk access for now. – Rafael Almeida Mar 7 at 20:58
  • If those BINARY(16) values are "uuids" that look like 'random' values, then I can explain a terrible performance problem -- quite possibly explaining the high, flat, Read IOPs. And I can give you two possible 'fixes', either of which will eliminate perhaps 95% of the Read IOPs. First, tell me about id and the uuids. – Rick James Mar 7 at 21:51

I ran a benchmark with r5.xlarge with io1 and 10k IOPS, and it seems that the issue is in the AWS side, not the MySQL side, and definitely instance-type related. This is not too surprising since each instance type does have a max IOPS capacity, as stated by the Amazon EBS–Optimized Instances document, although some of the specifics weren't expected.

Since this document played a central role in analysis, most references to "the EBS docs" in my text will refer to it implicitly.

Key findings are:

  • The IOPS hard ceiling only seems to have applied to Read IOPS, not Write IOPS, or even to total IOPS. I have no explanation for this.
  • The Read IOPS ceiling was a sustained (not bursty) 6.5k IOPS, while the EBS docs advertise 6k IOPS. Since the EBS docs assume a 16 KB I/O, this difference could be chalked up to a different I/O operation size.
  • Some newer instance types, like t3, r5 and m5 have a "max IOPS rate" and a significantly smaller baseline IOPS rate. The EBS docs say that "these support maximum performance for 30 minutes at least once every 24 hours", but it's not clear if it's a fixed 30 minutes window (use it then or lose it), or a budget-like approach. In my experiments, I was able to sustain close to max performance due to 120 minutes instead of 30 during peak period (which previously entered a hard plateau around 6.5k), and zooming in closer it seems that even small drops from the 6k r5 IOPS baseline are followed by a corresponding increase shortly afterwards. This seems to indicate that the "max IOPS period" is spent on a credit basis, like T2/T3 Unlimited, although the EBS docs don't seem to explicitly state that.
  • Since I previously had a 1TB gp2 disk (3k IOPS), I would assume the plateau would be at 3k IOPS, but instead it was at the same 6.5k IOPS the instance type seemed to support. So, in my experiments, gp2 disk burst peaked out at instance type max instead of 3k IOPS as the "Amazon EBS Volume Types" doc states.

PS: Rick James's answer is a great general overview on optimizing the table and avoiding hitting the ceiling altogether, if possible.

Your Answer

