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?
