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 |
+----------------------+----------+