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:
- Intel Core 2 Quad Q9400 @ 2.66GHz
- 8GB RAM
- Windows Server 2008 R2 Standard
- 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.
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:
I will post the "real" results when we update the values in the production server.
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.