Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Aug 22 at 16:02

This question came from our site for system and network administrators.

1  
Are the databases even the same size on each server? A smaller testing database will run faster than a larger production database. Also, the testing server has almost double the RAM. What's the memory usage look like? –  Bert Aug 22 at 14:31
1  
Yes, the DB are the same (actually a dump of the production DB into the testing DB). I didn't check the memory usage in the production server yet, I will now and inform. –  juantxorena Aug 22 at 14:33
1  
Could you pull the same RAM stats from the testing box? See if they look significantly different? –  Chris S Aug 22 at 15:24
2  
Can you post an example of a query that runs great in TEST but is horrible in PROD ? In addition, please run the EXPLAIN plan on the query in both systems and post the results. –  RolandoMySQLDBA Aug 22 at 16:17
3  
@juantxorena: you need to discard reasons, and not suppose anything, from easy to hard to check. Step 1 reason: differences in query plan. Run EXPLAIN on production and on development, check for differences (even if minimal). Add that information and then we can go to step 2. –  jynus Aug 22 at 16:17

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

share|improve this answer
    
Thank you for your thorough answer. I have performed the ANALYZE TABLE on all the tables, and the execution times are similar. PROD is a bit slower in most of the cases, a bit faster in others, but I'm talking about milliseconds, so I don't think that's the cause. I'm still looking. –  juantxorena Aug 25 at 13:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.