patternsqlMinor
Huge differences of performance of MySQL in two servers
Viewed 0 times
servershugedifferencesmysqltwoperformance
Problem
We have a MySQL server installed in two different machines, a testing server and a production server, both windows, which is used by a web application.
The problem is that there are HUGE performance differences among the two machines when executing the some queries (the production server being the slower one). The MySQL version in both servers is the same, even the config files are the same (the only difference is the path of the data and the fact that the production server doesn't log anything but the errors). The difference in performance that I'm talking about is 3 or 4 orders of magnitude greater (e.g. a query in the testing server executes in 0.2 s, whereas in the production server executes in 84 s).
The offending queries make extensive use of clauses with "WHERE [...] IN [...]", which is my understanding that they are usually very slow and they should be replaced with JOINs. However, the version of MySQL that we are using is 5.6.19, which optimizes those queries automatically, that's why they perform fast in the testing server (and they are in a part of the program that we cannot change so we cannot optimize them manually anyway).
As I said, the MySQL installation and configuration are identical, so I'm completely clueless about where the problem may be. On one hand, I suspect that it must be a configuration problem of some kind since the program and the DB are the same, on the other hand, this doesn't makes sense since the config are identical.
Some data on the servers:
Testing server:
Production server:
Edit: I forgot to say an important thing: there are more queries being executed which uses "WHERE ... IN" clauses apart for the "offending" ones. They are executed fast in both machines, which suggest me that they are being correctly optimized by MySQL. The fact that some queries are optimized
The problem is that there are HUGE performance differences among the two machines when executing the some queries (the production server being the slower one). The MySQL version in both servers is the same, even the config files are the same (the only difference is the path of the data and the fact that the production server doesn't log anything but the errors). The difference in performance that I'm talking about is 3 or 4 orders of magnitude greater (e.g. a query in the testing server executes in 0.2 s, whereas in the production server executes in 84 s).
The offending queries make extensive use of clauses with "WHERE [...] IN [...]", which is my understanding that they are usually very slow and they should be replaced with JOINs. However, the version of MySQL that we are using is 5.6.19, which optimizes those queries automatically, that's why they perform fast in the testing server (and they are in a part of the program that we cannot change so we cannot optimize them manually anyway).
As I said, the MySQL installation and configuration are identical, so I'm completely clueless about where the problem may be. On one hand, I suspect that it must be a configuration problem of some kind since the program and the DB are the same, on the other hand, this doesn't makes sense since the config are identical.
Some data on the servers:
Testing server:
- Intel Core 2 Quad Q9400 @ 2.66GHz
- 8GB RAM
- Windows Server 2008 R2 Standard
Production server:
- Intel Xeon E5530 @ 2.40GHz
- 5GB RAM
- Windows Server 2012 R2 Standard
Edit: I forgot to say an important thing: there are more queries being executed which uses "WHERE ... IN" clauses apart for the "offending" ones. They are executed fast in both machines, which suggest me that they are being correctly optimized by MySQL. The fact that some queries are optimized
Solution
I might be flying blind on this one, but here it goes ...
In your question and comments, you stated the following:
The MySQL version in both servers is the same, even the config files are the same (the only difference is the path of the data and the fact that the production server doesn't log anything but the errors)
Yes, the DB are the same (actually a dump of the production DB into the testing DB).
Similar results: 2.31Gb in a stable way
You should provide the EXPLAIN plan for a query. Since the data is identical, it may not be necessary.
There are a couple of things that may be different
YOUR PROCESSOR
I looked up the Intel Core 2 Quad Q9400 @ 2.66GHz (TEST) and the the Intel Xeon E5530 @ 2.40GHz (PROD) and found a difference.
You would think PROD should be faster.
Its internal bus speed may be the bottleneck. I would suspect this because TEST has a higher bus speed and a bus multiplier of 8. What is a bus multiplier ?
Internal frequency of microprocessors is usually based on Front Side Bus frequency. To calculate internal frequency the CPU multiplies bus frequency by certain number, which is called clock multiplier. It's important to note that for calculation the CPU uses actual bus frequency, and not effective bus frequency. To determine actual actual bus frequency for processors that use dual-data rate buses (AMD Athlon and Duron) and quad-data rate buses (all Intel microprocessors starting from Pentium 4) the effective bus speed should be divided by 2 for AMD or 4 for Intel.
Based on this, the internal bus speed for AMD (TEST) is at least 2 times more that Intel (PROD).
YOUR INDEX STATISTICS
Since you loaded TEST with the same data, one thing might have been overlooked. I am thinking of the index statistics. For TEST, the index statistics would be fairly new. For PROD, it may be stale if the indexed tables have experienced lots of INSERTs, UPDATEs, and DELETEs.
Running a SELECT on two different machines with identical mysql version, identical mysql configs, identical datasets, and even identical hardware, could be affected by index statistics on the table involved.
I would run ANALYZE TABLE across all tables on PROD and TEST and then try comparing performance.
In your question and comments, you stated the following:
The MySQL version in both servers is the same, even the config files are the same (the only difference is the path of the data and the fact that the production server doesn't log anything but the errors)
Yes, the DB are the same (actually a dump of the production DB into the testing DB).
Similar results: 2.31Gb in a stable way
You should provide the EXPLAIN plan for a query. Since the data is identical, it may not be necessary.
There are a couple of things that may be different
YOUR PROCESSOR
I looked up the Intel Core 2 Quad Q9400 @ 2.66GHz (TEST) and the the Intel Xeon E5530 @ 2.40GHz (PROD) and found a difference.
- CPU for TEST has 4 threads
- CPU for PROD has 8 threads
You would think PROD should be faster.
Its internal bus speed may be the bottleneck. I would suspect this because TEST has a higher bus speed and a bus multiplier of 8. What is a bus multiplier ?
Internal frequency of microprocessors is usually based on Front Side Bus frequency. To calculate internal frequency the CPU multiplies bus frequency by certain number, which is called clock multiplier. It's important to note that for calculation the CPU uses actual bus frequency, and not effective bus frequency. To determine actual actual bus frequency for processors that use dual-data rate buses (AMD Athlon and Duron) and quad-data rate buses (all Intel microprocessors starting from Pentium 4) the effective bus speed should be divided by 2 for AMD or 4 for Intel.
Based on this, the internal bus speed for AMD (TEST) is at least 2 times more that Intel (PROD).
YOUR INDEX STATISTICS
Since you loaded TEST with the same data, one thing might have been overlooked. I am thinking of the index statistics. For TEST, the index statistics would be fairly new. For PROD, it may be stale if the indexed tables have experienced lots of INSERTs, UPDATEs, and DELETEs.
Running a SELECT on two different machines with identical mysql version, identical mysql configs, identical datasets, and even identical hardware, could be affected by index statistics on the table involved.
I would run ANALYZE TABLE across all tables on PROD and TEST and then try comparing performance.
Context
StackExchange Database Administrators Q#74686, answer score: 4
Revisions (0)
No revisions yet.