I have MySQL
MySQL version: 5.6.16-enterprise-commercial-advanced-log
MySQL Engine: InnoDB
MySQL Data Size: 35GB (including 9GB of indexes)
Which is running on
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
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)
mysql> 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 |
+----------------------+------------+
Does the following network latency and throughput is good for above mentioned DB instance?
$ 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
Does the following changes to MySQL system variables gives positive results in the context of MySQL I/O tuning?
It's very hard to answer your question, because with performance problems, the answer is generally 'it depends'. Sorry.
The first thing you need to do is understand what's actually going on, and why you're performance is less than expected. There's a variety of tools for that, especially on a Linux system.
First off, grab a benchmark of your system read and write performance.
The simple test I tend to use is to time a dd:
time dd if=/dev/zero of=/your/san/mount/point/testfile bs=1M count=100
time dd if=/your/san/mount/point/testfile of=/dev/null bs=1M count=100
(increase the 100 to 1000 if it's quick to complete). This will give you an idea of sustained throughput of your storage system.
Testing IO operations per second is a similar thing - do the same, but use a small block size and a large count. 4k block size, 10,000 as the count - again, if it goes a bit too quick, increase the number.
This will get you an estimate of IOPs and throughput of your storage subsystem.
Now, you haven't been specific as to what type of disks, and number of spindles you're using. As an extremely rough rule of thumb, you should expect 75 IOPs from a SATA drive, 150 from an FC or SAS drive, and 1500 from an SSD before performance starts to degrade.
However, as you're using a RAID-5, you need to consider the write penalty of RAID-5 - which is 4. That means your RAID-5 needs 4 ops to do one write IO. (There is no read penalty, but for obvious reasons, your 'parity' drive doesn't count as a spindle).
How does your workload look? Mostly reads, mostly writes? How many IOPs? And how many spindles? In all honesty, it's more likely that the root of your problem is expectations of the the storage subsystems.