MySQL write delay

Question

I am developing a social application that is being deployed using AWS. In particular, the database runs on RDS using MYSQL. While we are testing the application using a limited number of users (mainly friends), the result is an average of 15 Write IOPS / sec.

The real problem is the very high latency of the db record, which always exceeds 100 ms. The RDS instance is db.m3.xlarge, which is much larger than we need.

I tried to run the load test in a separate instance (identical database and EC2 configuration), but I could not reproduce such a high delay, even if I sent a much larger number of requests. Therefore, I thought that this could be due to fragmentation of the table, but I have not performed table optimization yet because db will not be available during this procedure.

Do you have any experience with this problem?

ADDITIONAL INFORMATION

• We are using mysql version 5.6.21 with INNODB as the storage engine.
• The entire database is about 100 MB.

• The largest table (called Message ) has about 790 thousand rows. For 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) 

  11 seconds completed to complete.

• Worse, request

   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 14 seconds, but the Comment table has about 160k.

These two tables are generated:

 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; 

and

 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; 

SOME DEVICES

Using AppDynamics I was able to extract the following graphs:

• Waiting for states: Is the request time too long?

  

• Page Buffer:

  

• Record delay and queue:

  

Request cache

 +------------------------------+-----------+ | 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 | +------------------------------+-----------+ 

Thank you for your help!

Andrea

Answer 1

I contacted RDS engineers from Amazon and they gave me a solution. Such high latency was caused by a very low type of storage. Indeed, I used the default 5 GB SSD (which they call GP2), which gives 3 IOPS / s per GB of memory, resulting in 15 IOPS / s when my application needs about 50 IOPS / s or even more.

So they suggested that I change the storage type to Magnetic , which provides 100 IOPS / s as the baseline. Moreover, I was also able to reduce the type of instance, because only the disk was the bottleneck.

The migration took about 3 hours due to the very poor performance of the source disk (GP2).

Hope this can help someone out there!

Answer 2

The request profile shows that the request end time is very long. This can be caused by a very (too) large request cache . Each time you execute the update statement (INSERT, DELETE, UPDATE), the query cache must be updated (each query that is read from the updated tables is invalid).