title: Can I/O latency cause a simple UPDATE to take seconds in MySQL?
body: <p>My MySQL application is experiencing <strong>slow performance</strong> when running some <code>UPDATE</code>, <code>INSERT</code> and <code>DELETE</code> queries. In this question, I will only discuss one particular <code>UPDATE</code>, because it's enough to demonstrate the problem:</p>
  
  <pre><code>UPDATE projects SET ring = 5 WHERE id = 1
  </code></pre>
  
  <p>This <code>UPDATE</code> is usually fast enough, around 0.2ms, but every now and then (enough to be a problem) <strong>it takes several seconds</strong>. Here's an excerpt from the log (look at the 4th line):</p>
  
  <pre><code> ~ (0.000282) UPDATE `projects` SET `ring` = 5 WHERE `id` = 1
   ~ (0.000214) UPDATE `projects` SET `ring` = 6 WHERE `id` = 1
   ~ (0.000238) UPDATE `projects` SET `ring` = 7 WHERE `id` = 1
   ~ (3.986502) UPDATE `projects` SET `ring` = 8 WHERE `id` = 1
   ~ (0.000186) UPDATE `projects` SET `ring` = 9 WHERE `id` = 1
   ~ (0.000217) UPDATE `projects` SET `ring` = 0 WHERE `id` = 1
   ~ (0.000162) UPDATE `projects` SET `ring` = 1 WHERE `id` = 1
  </code></pre>
  
  <p><code>projects</code> is an InnoDB table with 6 columns of types <code>INT</code> and <code>VARCHAR</code>, 17 rows and an index on <code>id</code>. It happens with other tables too, but here I'm focusing on this one. When trying to solve the problem, I ensured that the queries were all sequential, so this is <strong>not a lock issue</strong>. The <code>UPDATE</code> above is executed in the context of a transaction. Other <strong>information on the server</strong>:</p>
  
  <ul>
  <li>VPS with 4GB RAM (was 1GB), 12GB free disk space</li>
  <li>CentoOS 5.8 (was 5.7)</li>
  <li>MySQL 5.5.10 (was 5.0.x)</li>
  </ul>
  
  <p>The "was" bit above means it didn't work before or after the upgrade.</p>
  
  <p><strong>What I've tried so far, to no avail</strong>:</p>
  
  <ul>
  <li>Setting <code>innodb_flush_log_at_trx_commit</code> to 0, 1 or 2</li>
  <li>Setting <code>innodb_locks_unsafe_for_binlog</code> on or off</li>
  <li>Setting <code>timed_mutexes</code> on or off</li>
  <li>Changing <code>innodb_flush_method</code> from the default to <code>O_DSYNC</code> or <code>O_DIRECT</code></li>
  <li>Increasing <code>innodb_buffer_pool_size</code> from the default to 600M and then to 3000M</li>
  <li>Increasing <code>innodb_log_file_size</code> from the default to 128M</li>
  <li>Compiling MySQL from source</li>
  <li>Running <code>SHOW PROCESSLIST</code>, which informs me that the state is "updating"</li>
  <li>Running <code>SHOW PROFILE ALL</code>, which says that almost all the time was spent on "updating", and that, within that step, not so much time was spent on CPU cycles and there were many voluntary context switches (like 30)</li>
  <li>Monitoring <code>SHOW STATUS</code> for changes in <code>Innodb_buffer_pool_pages_dirty</code>. There may be some relation between dirty pages being flushed and the slow queries, but the correlation isn't clear.</li>
  </ul>
  
  <p>Then I decided to check the system's I/O latency with <code>ioping</code>. This is my first VPS, so <strong>I was surprised to see this result</strong>:</p>
  
  <pre><code>4096 bytes from . (vzfs /dev/vzfs): request=1 time=249.2 ms
  4096 bytes from . (vzfs /dev/vzfs): request=2 time=12.3 ms
  4096 bytes from . (vzfs /dev/vzfs): request=3 time=110.5 ms
  4096 bytes from . (vzfs /dev/vzfs): request=4 time=232.8 ms
  4096 bytes from . (vzfs /dev/vzfs): request=5 time=294.4 ms
  4096 bytes from . (vzfs /dev/vzfs): request=6 time=704.7 ms
  4096 bytes from . (vzfs /dev/vzfs): request=7 time=1115.0 ms
  4096 bytes from . (vzfs /dev/vzfs): request=8 time=209.7 ms
  4096 bytes from . (vzfs /dev/vzfs): request=9 time=64.2 ms
  4096 bytes from . (vzfs /dev/vzfs): request=10 time=396.2 ms
  </code></pre>
  
  <p>Pretty erratic, I would say.</p>
  
  <p><strong>Having said all of that, I ask:</strong></p>
  
  <ol>
  <li><p><strong>Can the I/O latency be occasionally killing MySQL performance?</strong> I always thought that, when you ran an <code>UPDATE</code>, the thread taking care of that connection wasn't going to flush data to disk or wait for such a flush; it would return immediately and the flushing would be done by another thread at another time.</p></li>
  <li><p>If it can't be disk I/O, <strong>is there anything else I can possibly try, short of renting a dedicated server?</strong></p></li>
  </ol>