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.
Print This Post
del.icio.us :: 
digg Comment RSS :: Trackback URI
7 Comments
Interesting article as always
Apache JMeter is an excellent tool for load testing a MySQL database. I tend to use it via HTTP requests to the website, which in turn will load up the MySQL database.
http://jakarta.apache.org/jmeter/index.html
This makes testing ‘real world’ conditions incredibly easy, and fairly accurate.
Comment :: January 11, 2007 @ 3:47 am
I agree JMeter is great.
JMeter however does “Full Stack” benchmark loading HTTP Server, Application Server and then database server, this is of course required part of performance testing, but which adds complication if you’re looking at particular queries and you want to check how their execution speed had changed.
Comment :: January 11, 2007 @ 4:14 am
On recent linux kernels you can do:
echo “3″ > /proc/sys/vm/drop_caches
to clear OS cache.
If you also restart MySQL, comparing even a single query performance may give meaningful results.
Comment :: January 13, 2007 @ 5:21 pm
Alexey,
This assumes you do not have RAID, which has its own cache etc. But yes generally it is possible, but I do not think it is really worth to do it such way.
Comment :: January 13, 2007 @ 5:36 pm
[...] Getting real life query speeds with MySQL “need production load to warm it up or test warmup load”, well yea, but what’s the process? [...]
Pingback :: July 17, 2007 @ 7:29 pm
Could you expand on the less intrusive ways for clearing the OS and MySQL caches? I would like to avoid restarting MySQL if possible.
Thanks
Comment :: September 17, 2007 @ 9:05 am
If you do not want to restart MySQL you can set key_buffer_size to 0 and when back and do significant amount of full table scans to wipe of Innodb cache.
It is less reliable anyway.
Comment :: September 18, 2007 @ 1:40 pm