Huge differences of performance of MySQL in two servers

Question

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:

• 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 when others are not is a mystery to me, IF this is the actual problem, which I'm not sure.

Edit #2: Here is the config file for both servers: http://pastebin.ca/2834906

Edit #3: Here is the EXPLAIN of one of the slow queries: https://mariadb.org/ea/v36zj The EXPLAIN is exactly the same in both test and prod. The query itself is here: http://pastebin.com/VXgBxXmt It has been formatted with an autoformatter, so maybe is not very clear. As you can see, is quite long and complex. It hasn't be generated by hand, they are moreless automatically generated by the software, which uses a dialect of the standard SQL with some functions.

Also, more information: We have temporary patched the problem by reducing the data in the production server and removing most of the old data in the DB, which is not going to be used. This is not a solution, of course, since we need also the old data, and it will be a problem in the future. The DB is not that big: the full DB is 1308MB, the reduced version currently in production is 332MB.

UPDATE: SOLVED??

I think I have solved the problem. I haven't tested it yet, since the production server is actually being used, but the possible problem was the parameter "innodb_buffer_pool_size", which was set to 182M. Actually, the line in the config file shows: innodb_buffer_pool_size=321 which is a mistake since it doesn't have the unit prefix, giving a non valid value (the minimum is 5242880 according to the docs), then putting it at the previous value. This value in the testing server was set at the desired 321M.

As I said, I haven't tested it completely. What I did is to reduce the value in testing and try the application. Everything goes more slower, and the particular query that I posted executes in 3 minutes.

I have put in testing a more sane value of 3Gb, which I don't know if it's a good idea, so if somebody has some comment about this value, I will appreciate it.

My conclusions, the "sane value" of 3Gb and the info I used for this comes from these two posts, particularly the second:

MySQL query, 2 similar servers, 2 minute difference in execution times

How large should be mysql innodb_buffer_pool_size?

I will post the "real" results when we update the values in the production server.

Thanks everybody.

SOLVED

So, we finally tested this in prod, and it was the problem I commented on previously. I put the value of innodb_buffer_pool_size in 321M, which is the value recommended by the vendor of the SDK we use, even though according to the previous links it should be around 3G for a DB of this size and usage.

I still have a doubt, though: the value of 321 was an invalid value (too small), so MySQL took another value. My suspicion is that it took the previous valid number, 321M in test, and 182M in prod, hence the speed differences. It's just out of curiosity, but I would like to know if this is right.

Thank you all again for the help.

Answer 1

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.

• 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.