Getting real life query speeds with MySQL

To check for query performance improvements followed indexing/query changes or MySQL configuration changes our customers often decide to run the query and see if there is any significant improvement.

Leaving aside question of checking single query alone might not be the best way to see real improvement for your application, the problem they usually run into is – query speed when it is run first time may be very different from second and further runs, especially when it comes to disk IO workloads.

The attempt to fix it is often using SELECT SQL_NO_CACHE or disabling QueryCache for the test. This works by blocking query cache but still repeated runs execute much faster, why ?

The reason is – there are all kind of other caches and QueryCache is only first in line. There is also all kinds of page caches inside MySQL (key_buffer and innodb_buffer_pool_size) which can be cleared by MySQL restart. Even if you restart MySQL server there is significant chance data will still remain in Operation System cache speeding up query run.

So what can you do ?

First decide on what are you looking to test – do you want to test worse case scenario when server was just started and is warming up ? Sometimes it makes sense. In this case you would want to clean MySQL and OS caches, most simple way being to restart OS (there are less intrusive ways too).

Most typically you however are interested on server performance when it is warmed up. This is the stage your server should spend more than 99% of time in. In this case you would ether need production load to warm it up or test warmup load.

In any case testing with single query rarely gives you what you’re looking for – you would normally need a lot of queries to simulate situation even remotely close to real world. Get large number of similar queries from your MySQL server logs or generate using different user ids etc but make sure you have large enough number of them so whole data set touched by these queries will not be cached, if it is not the case in your real life environment.