InnoDB Performance on multiple cores

Does anyone have any experience/advice on MySQL, specifically the InnoDB engine running on an 8 core box? Current setup is CentOS 5.1.4, 48 GB ram, Hardware RAID 5 on 15K SAS drives (8 drives). hdparm shows 550+ meg per second read speed and 280 write. The MySQL performance does not seem to be where it should be for the given hardware. I cannot get the QPS over about 750. I have the InnoDB buffer set to 20GB. I have tuned all the normal parameters I could think of.

I'm running MySQL Enterprise 5.1.44. I have read that InnoDB does not scale well on multiple cores, but there seems to be debate from both sides about that. Any advice would be appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What is the stripe size of the RAID set to?
gmckeown99Author Commented:
64K I believe.
For best database performance a raid 10 is suggested.

See e.g.
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

And stripe size for the filesystem?  Well, doesn't matter that much, it isn't correct.  You need to have it match.  If the stripe size for filesystem is default 4KB, then when it wants to write 4KB, the RAID engine is obligated to write a full 64KB.   Assuming you have a journal and you have to read the journal, then a lousy 4KB write could easily translate into 512KB worth of reads & writes.  

Now for your RAID config.  You have a 7+1, lets assume filesystem & the raid are set to 64KB.  I am also assuming the stripe size of the raid of 64KB means that this is 8 disks each 8KB, rather than 64KB per disk.  If it is the other way around, where each disk is 64KB then you still have a big problem, just a different type of problem and explanation below is still valid, just use the different numbers to see why...

If you write a transaction, then the engine & filesystem sends 64KB to the RAID controller, which then has to write 64KB worth of application data.
Since you have 7 data disks, then you actually get 8KB worth of data, because you have 7 disks + 1 parity (a 7+1).  So your RAID has to first write 8KB x 7 disks.  then to get the other 8KB of data, it has to write a full 64KB stripe again, meaning each disk has to write twice what you think.    (In reality each disk would do a single 16KB write, but still, it is writing 2x what you need).

The optimal RAID would be a power of 2, i.e, 8+1 for total of 9 disks, not 7+1.  A 8+1 array would not have wasted I/O.

Now, if your disk of 64KB stripe is really 64KB PER DISK, then whenever you want to write 64KB of application data, your RAID controller is making EVERY disk write 64KB, so you are effectively doing 8X the I/O you need.

So ..
1. Redo the RAID and filesystem so that they match MySQL native disk I/O size.  If MySQL write 64KB at a time, make sure that only 64KB at a time gets written to disk, and that the filesystem also agrees.    You will have to buy another HDD for this.

2. Alternately, if you have a more intelligent controller, that lets you use multiple stripe sizes, then try splitting things up, so that you have a RAID1 with small stripe size that you use for journaling, scratch table space, anything that is write intensive, then do a 4+1 RAID5 for the bulk of the data, and leave a free drive for a hot spare.   If that does not give you enough space, then go with a 5+1 for the RAID5 data.  You will still be better off because the write intensive stuff will be done on the RAID1.  

With READS, the RAID5 isn't much of a bottleneck, because it does not have to read a full stripe, it can just supply what it is asked for.  It is only WRITING where the RAID5 is obligated to do a full 100% write whether you need it or not
A 2 x RAID10 would be suitable as well, but the benefit over a RAID1 + RAID5 is going to be a function of the READ/WRITE ratio.  And unless you reconfigure the file system, then either would not be much of a help.    Furthermore, in a high IOPS environment,  2 x RAID1 is preferred over 1 x RAID10.  

Don't get hung up on throughput, databases are traditionally IOPs bound.  HDPARM throughput tests are not the correct I/O model to begin with, so don't tune performance to maximize throughput.  
gmckeown99Author Commented:
I checked the stripe size and it's 256K, not 64. The box is not I/O bound at all, nor CPU. I have not tested using different block sizes. Alot of controllers default to 64K.

The server handles many more selects than updates, so I don't think the Raid 5 is causing a bottleneck.

CPUs very low load

11:26:24 AM  CPU   %user   %nice    %sys %iowait    %irq   %soft  %steal   %idle    intr/s
11:26:24 AM  all    4.47    0.00    0.12    0.08    0.00    0.04    0.00   95.29    443.52
11:26:24 AM    0    4.64    0.00    0.12    0.07    0.01    0.05    0.00   95.12     55.44
11:26:24 AM    1    4.62    0.00    0.14    0.11    0.00    0.04    0.00   95.09     55.44
11:26:24 AM    2    4.52    0.00    0.12    0.08    0.01    0.04    0.00   95.23     55.44
11:26:24 AM    3    4.40    0.00    0.12    0.08    0.00    0.04    0.00   95.37     55.44
11:26:24 AM    4    4.45    0.00    0.12    0.07    0.00    0.04    0.00   95.31     55.44
11:26:24 AM    5    4.39    0.00    0.11    0.08    0.00    0.04    0.00   95.37     55.44
11:26:24 AM    6    4.43    0.00    0.12    0.07    0.00    0.04    0.00   95.34     55.44
11:26:24 AM    7    4.32    0.00    0.11    0.07    0.00    0.04    0.00   95.46     55.44
11:26:24 AM    8    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00      0.00

Time: 11:35:53 AM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.08    0.00    0.27    0.07    0.00   93.58

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00   131.60    0.00   35.40     0.00  1336.00    37.74     0.03    0.96   0.79   2.80

Yes your RAID is the bottleneck.  Think about it,  you have 1 big fat huge RAID5.   You have it configured so all it can give you is 35 I/Os per second.
8 fast SAS disk drives, and due to the configuration, the cap is 35 IOPs. Total.

gmckeown99Author Commented:
The Raid 5 was not the issue. It was a few other things, mostly due to the fact that and admin set the thread cache to 4 for some reason that he cannot explain. We're back and humming.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.