Inconsistent MySQL query execution times

Question

We run tests on two servers: one in production [Old Server] and the other one in tests [New Server]. Both tests used the same database.

[New Server]

• Dedicated MySQL 5.6.17 server on Ubuntu 14.04
• Dell PowerEdge R610
• CPU 2 x 6-Core @ 2.93GHz
• 96GB DDR3
• 4 x 120GB SSD RAID 10

[Old Server]

• Dedicated MySQL 5.1.73 server on Ubuntu 10.04
• CPU 2 x Quad Core @ 2.33GHz
• 16GB DDR2
• 2 x SAS RAID 1

Heavy queries run faster on the new server (up to 40% faster), but simple queries behave strangely. On the new server they take longer and randomly a lot more than expected.

These queries were run on a table with just under 10000 rows. The id is the primary key.

[Old Server]

SELECT * FROM `users` WHERE id = 32; -- 0.001
SELECT * FROM `users` WHERE id = 33; -- 0.000
SELECT * FROM `users` WHERE id = 34; -- 0.000
SELECT * FROM `users` WHERE id = 37; -- 0.002 
SELECT * FROM `users` WHERE id = 76; -- 0.000
SELECT * FROM `users` WHERE id = 99; -- 0.000
SELECT * FROM `users` WHERE id = 104; -- 0.000

[New Server]

SELECT * FROM `users` WHERE id = 32; -- 0.008
SELECT * FROM `users` WHERE id = 33; -- 0.005
SELECT * FROM `users` WHERE id = 34; -- 0.003
SELECT * FROM `users` WHERE id = 37; -- 0.003 
SELECT * FROM `users` WHERE id = 76; -- 0.124
SELECT * FROM `users` WHERE id = 99; -- 0.002
SELECT * FROM `users` WHERE id = 104; -- 0.002

Any idea why is this happening ?

Later Edit

This is a MyISAM table. The MyISAM key_buffer_size variable:

• [New Server] key_buffer_size = 34G
• [Old Server] key_buffer_size = 1G

It turns out the random slower queries were not MySQL related, but still [Old Server] is most of the time faster than the [New Server]. Here are the query times when executed in the MySQL console:

[New Server]

+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|     4029 | 0.00013275 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 32  |
|     4030 | 0.00015400 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 33  |
|     4031 | 0.00014800 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 34  |
|     4032 | 0.00015000 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 37  |
|     4033 | 0.00014275 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 76  |
|     4034 | 0.00014425 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 99  |
|     4035 | 0.00013800 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 104 |
+----------+------------+---------------------------------------------------+

[Old Server]

+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|       36 | 0.00019100 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 32  |
|       37 | 0.00011600 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 33  |
|       38 | 0.00011600 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 34  |
|       39 | 0.00011900 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 37  |
|       40 | 0.00011800 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 76  |
|       41 | 0.00011300 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 99  |
|       42 | 0.00011400 | SELECT SQL_NO_CACHE * FROM `users` WHERE id = 104 |
+----------+------------+---------------------------------------------------+

We actually get query times lower than 0.0001 several times.

[New Server] Query profile

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000034 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000014 |
| init                 | 0.000027 |
| System lock          | 0.000008 |
| optimizing           | 0.000008 |
| statistics           | 0.000029 |
| preparing            | 0.000005 |
| executing            | 0.000002 |
| Sending data         | 0.000011 |
| end                  | 0.000003 |
| query end            | 0.000002 |
| closing tables       | 0.000004 |
| freeing items        | 0.000009 |
| cleaning up          | 0.000003 |
+----------------------+----------+

[Old Server] Query profile

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000017 |
| checking permissions | 0.000003 |
| Opening tables       | 0.000005 |
| System lock          | 0.000003 |
| Table lock           | 0.000003 |
| init                 | 0.000017 |
| optimizing           | 0.000005 |
| statistics           | 0.000039 |
| preparing            | 0.000005 |
| executing            | 0.000001 |
| Sending data         | 0.000017 |
| end                  | 0.000003 |
| query end            | 0.000001 |
| freeing items        | 0.000012 |
| logging slow query   | 0.000002 |
| cleaning up          | 0.000002 |
+----------------------+----------+

Answer 1

There are several reasons why this can happen:

• Make sure you are not using the query cache on your old server and not on the new one. Execute always benchmarks with SELECT SQL_NO_CACHE. Execute the same queries several times.

• If it is a new server -the buffer pool, filesystem cache and other buffer are probably not yet settled for best performance, requiring MySQL to perform more operations for longer time. Remember that even if SSDs are fast, memory will always be faster. If the table is so small, it probably was fully on memory before. It will take some time to be fully on memory on the new server. That happens every time you restart MySQL, but it is minimized if you activate the buffer pool preload feature in 5.6 (after is has been filled, of course). You do not provide your buffer pool size (assuming you are running InnoDB) for comparing values, as that is critical and is probably going to be different between servers.

• MySQL 5.6 activates by default the performance_schema (it was off by default on 5.5). While this is a very nice feature, it may consume by default quite some amount of memory and many people -me included- have observer a 5-10% overhead in single-query execution performance. Independently of if you want to activate this feature or not, you should do the tests on equal conditions, which usually means setting performance_schema = off on the configuration file for 5.6.

• Aside from performance_schema, there are other changes on configuration by default (innodb_log_file_size, innodb_old_blocks_time, innodb_checksum_algorithm, join_buffer_size, query_cache_type, innodb buffer pool partitions, ...). While most of the changes provide better or at least the same performance, in very specific cases a regression has been found when relying on default values. A similar thing can be said about the optimizer. Make sure that you have the same configuration on both servers (beyond the same my.cnf) -there are tools for that- and that you are executing the same query plan.

• As you have also changed your hardware and OS, the same could be applied to Ubuntu configuration. A change on something like, for example, the disk scheduler can have a large impact on the performance, specially on high-end hardware.

In general, you want to be as specific as possible when asking why is MySQL slower. Profile your queries, profile your system/IO and find a symptom in order to find the cause.

Edit: I do not want to suggest this yet -as it is too soon-, but it is true that some people have reported small degradations in query performance for very simple queries on some of the newest versions. Normally, the difference is very small, and it is normal as applications become more and more complex (performance gets focused on other bottlenecks and more features under high concurrency). Some of those regressions are fixed if they are detected, but I must say that -as you mention you are using MyISAM-, the level of "support" and "care" that that engine gets in comparison like others such as InnoDB in the latest versions is not precisely great. For example, many SSD-specific improvements apply only to InnoDB.

Edit2: I do not see a significant statistical difference, aside from the fact that you are logging to the slow log on the old server and not on the new one. There is a 0.000033 seconds difference, which certainly could be due to the change between versions. The largest change is in the starting phase, with 0.000017 of difference. Some new features have been added which could contribute to that. The question is: are you going to execute isolated queries in concurrency 1 or are you going to execute with high concurrency? If on the first case, do you really care about 33 microseconds? If you only care about single threaded performance, and not high throughput, you are probably better with MySQL 4. If on the second case, make a more significant benchmark with higher concurrency, and probably you will get better performance overall in the newest versions.