MySQL+Web-App Performance Issues using different servers

MySQLmysql-5.5performanceUbuntu

We are having a performance issue with our MySQL servers that does not make any sense. I have read countless articles from many people (mostly Percona) and have made my.cnf tweaks. We have even manage to squeeze out another 30% more TPS thanks to those Percona articles. However, our problem is with our in-house web-app (a Tomcat/Java/Apache model). It performs poorly when connected to certain servers – the better hardware servers.

Here is the symptom:

If we point our test application server (Ubuntu, Apache, Tomcat, Java) to server MYSQL02, the applications performance is acceptable. However, if we point the application to MYSQL01 or MYSQL03 (and these two boxes are idle!) the application performance is poor. There are high latencies. Example:

Retrieving an account (via our web-app) from MYSQL02 takes
2 to 3 seconds. On MYSQL01 or MYSQL03 it takes 12 seconds or longer.

We cannot figure out why! The MySQL servers and MONyog do NOT report any problems! If we execute the statements (100's of them) manually they return instance results and their explanations show they are all using indexes. We do NOT get any slow query, deadlock, or contention notifications.

Here is some basic information about our MySQL systems. They are all DEDICATED MySQL servers:

PROD (current production, not in replication farm, standalone)

Best/newest hardware configuration (8 core/ 20GB / 15K RPM HW RAID 10)
MySQL: 5.0 (RHEL)
We are trying to migrate from this MySQL 5.0 to our MySQL 5.5 farm (see below).
This server will then be reloaded with MySQL 5.5 and return as the primary
production server.

MYSQL01

Second best hardware configuration (8 core / 12GB RAM / 15K RPM HW RAID 10)
Supermicro X8FAH; 2xXEON X5550 @ 2.66GHz
2x LSI MegaRAID SAS 9260-8i
MySQL: 5.5.31 (Ubuntu)
OS: Ubuntu 12.04 LTS x64

MYSQL02

Worst/oldest hardware configuration(4 core / 12GB RAM / 7.2K RPM SW {mdadm} RAID 5)
Supermicro X2DBE; 2xXEON 5150 @ 2.66GHz
MySQL: 5.5.31 (Ubuntu)
OS: Ubuntu 12.04 LTS x64

This is our EDI workhorse. It spends most of it's day importing data from
our old mainframe system. It replicates the data to 01 and 03.

MYSQL03

Third best hardware configuration (8 core / 12GB RAM / 7.2K RPM HW RAID 10)
Supermicro X7DGB8-X; XEON E5410 @ 2.33GHz
MySQL: 5.5.31 (Ubuntu)
OS: Ubuntu 12.04 LTS x64

We used sysbench to test and tweak all the above systems and here are the test results with notes.

NOTE: TPS = Transactions Per Second

Results before applying any new tweaks:

PROD: 1,179.72 TPS <- Expected
MYSQL01: 442.92 TPS <- Why?
MYSQL02: 543.22 TPS <- Expected
MYSQL03: 904.22 TPS <- Surprising

Results after my.cnf tweaks:

MYSQL01: 655.00 TPS <- 32% improvement. Not bad but still very poor compared to its siblings.
MYSQL02: 754.81 TPS <- 28% improvement. This was unexpected.
MYSQL03: 969.59 TPS <- 07% improvement.

We are unsure why MYSQL01's performance is so poor. We can only summarize that there is an OS, RAID CARD or BIOS setting(s) that may be improperly set. I am leaning towards the RAID Card/Configuration. They only way to know for sure is to shutdown this server and scrutinize the configuration. A reload may be necessary. However, since it is our ultimate plan to make the current PROD hardware the primary production MySQL server then we may leave MYSQL01 alone for now and re-purpose the hardware after migrating to the 5.5 farm. However, we can't migrate until we figure out why our application is behaving so poorly on certain hardware.

Anyone have any suggestions?

Best Answer

The OS even shows that we have an IO WAIT problem.

We think we have found the issue - the LSI MegaRAID SAS 9260-8i (Supermicro SMC2108) RAID adapter. Either we have it configured incorrectly or it's just a poor performer.

We stumbled onto this blog: http://artipc10.vub.ac.be/wordpress/2011/09/12/megacli-useful-commands/

and disabled the Patrol Read function and it increased our TPS by almost 130/second.

Trying to solicit tweaking recommendations here: https://askubuntu.com/questions/304488/tweaking-recommendations-for-smc2108-lsi-megaraid-sas-9260-8i-raid-adapter

This link http://www.randombugs.com/linux/tuning-linux-system-database.html was very very useful. We implemented these changes which resulted in significant TPS improvements.