MySQL RAID configuration

Question

I attached 4 10GB disks to MySQL Server and I used RAID 0:

but still MySQL performance is the same. Do I have to change configuration in my.cnf to let MySQL know about RAID 0?

This are my mount results:

This is not development box, it is my production server. I used to have MySQL server on AWS but now I am using Azure, and I/O really hurts MySQL.

I am not a server admin expert, but I am trying to solve here my problem, a consultant recommended to me to do RAID 0.

I am using newrelic to test my performance. It shows how bad is MySQL, my server is 8 cores and 14 GB Azure server.

Answer 1

Firstly: unless this is a replication slave, development box, or similar, that can easily be reconstructed from other sources should the worst happen, RAID0 is not recommended. It provides good performance but potentially seriously bad reliability: if any one of your four drives fails then the whole array is toast.

Edit after new information: it would appear from a quick search that people do recommend RAID0 on Azure, on the assumption that the underlying (virtual) disks are reliable due to the redundancy in the platform. I'm not convinced this is a good idea, though I've not used the platform so might not be the best person to comment there.

Secondly: without knowing anything at all about your data size, basic data structure (for the parts being tested in your benchmarks), read/write load patterns, other details of server's specification (RAM, CPU, ...), your mySQL configuration (out of the box or some tweaks done?) (if out of the box, which box?), how you are testing performance to show it is the same, and so on, we can't really help you on the performance front at all.

The only general thing we can say is if your performance is being limited mainly by CPU resource then RAID is not going to make any difference because that only affects IO performance.

Thirdly: you don't show if that RAID volume is mounted anywhere as a file system or part of an LVM arrangement. Have you done this and told mySQL to store data on that array?

Answer 2

While there are some manual things that could be done at the database level (moving tablespaces around, etc.), most of the settings for MySQL are transparent to the storage layer, meaning that you do not need to do anything to take advantage of the RAID.

While a RAID 0 could provide you a better theoretical throughput in reads and writes (at the cost of having 4 unique points of failure), the optimization depends basically on being disk-bound. If your working set fits mostly on RAM (and with 10GB of disk, it would not be surprising), the advantage would be minimal. It also depends on the storage engine used- for example, MyISAM tend to end up with all its used blocks cached on the key buffer or the filesystem cache, at the cost of its durability.

What I would suggest you do is to execute a pure disk IO test (with tools like IOzone and Bonnie++) to make sure that everything is working as expected. Second: ask yourself if a RAID 0 is the right way to lower your latency/make your throughput higher.