title: MySQL I/O tuning on SAN environment
body: <p>I have MySQL </p>
  
  <pre><code>MySQL version: 5.6.16-enterprise-commercial-advanced-log
  MySQL Engine: InnoDB
  MySQL Data Size: 35GB (including 9GB of indexes)
  </code></pre>
  
  <p>Which is running on</p>
  
  <pre><code>VM: Red Hat Enterprise Linux Server release 5.9 (Tikanga)
  File system: ext3
  Storage technology: SAN
  Disk data format: RAID-5
  Disk type: SAS with Fibre channel
  </code></pre>
  
  <p>I found that lot of SELECT queries taking time because of I/O related operations (though necessary indexes and buffer is added to the same) </p>
  
  <pre><code>mysql&gt; show profile for query 1;
  +----------------------+------------+
  | Status               | Duration   |    
  +----------------------+------------+
  | starting             |   0.000313 |
  | checking permissions |   0.000024 |
  | checking permissions |   0.000018 |
  | Opening tables       |   0.000086 |
  | init                 |   0.000121 |
  | System lock          |   0.000092 |
  | optimizing           |   0.000079 |
  | statistics           |   0.000584 |
  | preparing            |   0.000070 |
  | executing            |   0.000014 |
  | Sending data         | 202.362338 |
  | end                  |   0.000068 |
  | query end            |   0.000027 |
  | closing tables       |   0.000049 |
  | freeing items        |   0.000124 |
  | logging slow query   |   0.000135 |
  | cleaning up          |   0.000057 |
  +----------------------+------------+
  </code></pre>
  
  <p>Does the following network latency and throughput is good for above mentioned DB instance?</p>
  
  <pre><code>$ time dd if=/dev/zero of=foobar bs=4k count=10000
  10000+0 records in
  10000+0 records out
  40960000 bytes (41 MB) copied, 1.22617 seconds, 33.4 MB/s
  real    0m1.233s
  user    0m0.002s
  sys 0m0.049s
  
  $ time dd if=foobar of=/dev/null bs=4k count=10000
  10000+0 records in
  10000+0 records out
  40960000 bytes (41 MB) copied, 0.026479 seconds, 1.5 GB/s
  real    0m0.032s
  user    0m0.004s
  sys 0m0.024s
  
  $ time dd if=/dev/zero of=foobar bs=128K count=10000
  10000+0 records in
  10000+0 records out
  1310720000 bytes (1.3 GB) copied, 78.1099 seconds, 16.8 MB/s
  real    1m18.241s
  user    0m0.012s
  sys 0m1.117s
  
  $ time dd if=foobar of=/dev/null bs=128K count=10000
  10000+0 records in
  10000+0 records out
  163840000 bytes (164 MB) copied, 0.084886 seconds, 1.9 GB/s
  real    0m0.101s
  user    0m0.002s
  sys 0m0.083s
  
  $ time dd if=/dev/zero of=foobar bs=1M count=10000
  10000+0 records in
  10000+0 records out
  10485760000 bytes (10 GB) copied, 461.587 seconds, 22.7 MB/s
  real    7m42.700s
  user    0m0.017s
  sys 0m8.229s
  
  $ time dd if=foobar of=/dev/null bs=1M count=10000
  10000+0 records in
  10000+0 records out
  10485760000 bytes (10 GB) copied, 4.63128 seconds, 2.3 GB/s
  real    0m4.634s
  user    0m0.003s
  sys 0m4.579s
  </code></pre>
  
  <p>Does the following changes to MySQL system variables gives positive results in the context of MySQL I/O tuning?</p>
  
  <ol>
  <li>innodb_flush_method: O_DSYNC (Referred <a href="http://bugs.mysql.com/bug.php?id=54306" rel="nofollow">http://bugs.mysql.com/bug.php?id=54306</a> for read-heavy workload)</li>
  <li>Moving from ext3 to XFS file system</li>
  </ol>