title: MySQL High Write Latency
body: <p>I'm developing a social-like application which is currently deployed using AWS services. In particular, the DB runs on RDS using MYSQL. 
  So far, we're testing the app using a limited number of users (mostly friends) resulting in an average of 15 Write IOPS/sec.</p>
  
  <p>The real problem is related to the very high writing latency of the db, which is always above 100ms. The RDS instance is a db.m3.xlarge which is much more than what we need.</p>
  
  <p>I tried to perform a load test in a separate instance (identical configuration of DB and EC2) but i've not been able to reproduce such a high latency, even if I was sending a much higher number of requests. So I thought it may be due to table fragmentation, but i've not yet run a table optimisation, because the db wouldn't be accessible during this procedure.</p>
  
  <p>Do you have any experience with this problem?</p>
  
  <p><strong>MORE INFO</strong></p>
  
  <ul>
  <li>We're using mysql version 5.6.21 with INNODB as storage engine.</li>
  <li>The whole DB is about 100MB in size</li>
  <li><p>The biggest table (called <code>Message</code>) has about 790k rows. Concerning this table, the following query</p>
  
  <pre><code>insert into Message (user_id, creationDate, talk_id, text, id) 
  values (2015, '2015-02-01 16:40:06.737', 18312, 'Some text ', 904870)
  </code></pre>
  
  <p>took 11s to be executed.</p></li>
  <li><p>Even worse, the query</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>took 14s, but the table Comment has about 160k.</p></li>
  </ul>
  
  <p>Those two tables are generated by:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>and </p>
  
  <pre><code>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;
  </code></pre>
  
  <p><strong>SOME PLOTS</strong></p>
  
  <p>Using <a href="http://www.appdynamics.com" rel="noreferrer" title="AppDynamics">AppDynamics</a> I've been able to extract the following plots:</p>
  
  <ul>
  <li><p><em>Wait States</em>: Isn't the query end time too big?</p>
  
  <p><img src="https://i.stack.imgur.com/2N83L.png" alt="SQL Wait states"></p></li>
  <li><p><em>Page Buffer</em>:</p>
  
  <p><img src="https://i.stack.imgur.com/lAveE.png" alt="Page Buffer"></p></li>
  <li><p><em>Write Latency and Queue</em>:</p>
  
  <p><img src="https://i.stack.imgur.com/sozSQ.png" alt="RDS Stats"></p></li>
  </ul>
  
  <p><strong>Query Cache</strong></p>
  
  <pre><code>+------------------------------+-----------+
  | 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       |
  +------------------------------+-----------+
  </code></pre>
  
  <p>Thank for your help!</p>
  
  <p>Andrea</p>