Mysql performances

[abonanno]

Hi all, my last resort 🙂

We are running on a dell r720xd equipped with 24 hdd sas 10k an esxi 5.5. We have chosen to build a RAID-1 disk where we had installed esxi itself and in the same datastore we have chosen to host also a guest running linux rhel6.5 x86 (yes...32 bit os  ). Then, we moved forward and decided to build another raid volume, this time is RAID-0 on top of 5 disks (6TB raw). On top of this volume we have created another datastore and built a vmdk file of 5TB, then assigned to the guest as "data" partition. All vmdks disks were created following the slow way (thick provision eager zeroed and linked as Independent and Persistent)

Now comes the hassles. You can guess that the disk built on top of raid-0 volume is very "fast"... and this is true especially with file creation/read (remeber raid0 there ) but, if i run mysql with data on this datastore (data is in innodb) is very sloppy, to much sloppy.... in an insert test (we have done many test run) it's about 3 time more slow compared with the same dataset hosted in the vmdk file of the guest OS (the one on RAID-1)

I know that maybe as a description of the problem, is rather generic and so if someone would take care of a poor sysadm which is going to be "slightly mad"... ask me and help to find my way.

Thanks!

Cheers,

Antonio-

[JPM300]

Well first off, if your using RAID0 I hope you have good backups as if any drive in the RAID 0 fails you loose everything.

Secondly, you say your getting poor performance out of your MySQL VM which lives on the RAID 0. What kind of writes / reads is your MySQL doing? Are they mostly reads? Writes?  What kind of reads or writes are they, random or sequential?

Also if your VM stressed in any other aspect, CPU or Memory?

[abonanno]

Hello, thanks for you reply.

We have good backup.

We have poor performances during write operations (simple crete table and inserts)

cheers

Antonio

[JPM300]

Can you give a better description on how you build your Datastores

It sounds like you did this

24 disks 10k SAS

5 disks = Raid0  Datastore1 - Raid0

21 disks - Raid1 Datastore2-X?

Also when you say you make the RAID groups is this a hardware raid with a raid controller in the ESXi host?  Or is this software raid?

Also what flavor of linux is running the MySQL?  Is that VM having any performance issues?

[abonanno]

Hi, we have a couple of disk in RAID-1, in which we installed esxi and also OS partition of guest, then 5 disk in raid-0 used for another datastore which contains a vmdk file for mysql data.

We have done some test using dd and write speed on virtual disk is 500 MB/s on raid0 Vs 140 MB/s on raid1 volume.

Despite this mysql writes on virtual disk in raid0 volume are slower (about 5 times) than writes on virtual disk in raid1.

the other 21 Disks at the moment are free.

Raid is HW raid: PERC H710 Raid controller.

OS is RHEL6.5 32bit and the VM does not have any performance issue....

Thank you.

Cheers

Antonio

[JPM300]

Hey abonanno,

How many disks are in your RAID-1, I'm guessing 2-4, but just curious

The only thing I can think of is your PERC H710 controller have any write cache?  Sometimes the raid controllers allow your to adjust how the write/read% of the write cache works.  So you have have 20% read 80% Write ect.  This can help with certain applications.  For instance when we build Disk-to-Disk Backup systems we put it to 80% write and 20% read as most of the time the backup server is writing.

The only other thing I can think of is your MySQL box could have the tempdb on the C:\OS drive which could be causing your slow down maybe depending on what your doing in your MySQL.  What happens if you move the entire VM OS and Data disk over to the RAID0, does performance improve?