September 30, 2014

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.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. David Hicks says:

    I’m on board with Dominic, here. While I agree that testing under pristine, startup circumstances isn’t exactly “real world,” it is exactly the state that I need to see things in for my immediate need. For instance, I have a “slow query” that I’m decomposing a complex join for, because I know that I can get better performance from two less complex queries as opposed to one very complex query, then let my code join the result sets. Now, how can I measure the results and know that I’m comparing apples to apples? To make matters more complicated, I’m working with a database that is really too large to put on a desktop machine to play with. My only option is to run my query against a production machine. Shutting it down or fiddling with the live buffers is not an option.

  2. Nick says:

    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.

  3. peter says:

    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.

  4. Alexey says:

    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.

  5. peter says:

    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.

  6. Bob says:

    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

  7. peter says:

    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.

  8. Sarit Hod says:

    IMHO – Load2Test by http://www.dbainfopower.com is a the best for MySQL load testing – they can record actual MySQL production traffic and replay it in staging mutliple ways, like “as is”, “load parallelization”, “think time reduction”, etc and generate performance issues root cause analysis on a spot.

    I used it with number of my customers and it takes no time to do load testing and get analytics RCA back (like slow-log data without any impact on a system and more), compare runs, etc

    They are small startup that operates “under the radar”. I got introduced to this product throug the person who works there – and I really like the tool!!!

  9. You’re talking about MySQL Administrators sitting around and making odd little query enhancements to squeeze a little more performance out of their queries. Of course this is going to need a real world testing environment.

    What about users who are getting to straps with MySQL Query enhancements? The ones who write the wrong queries and don’t quite understand why a particular query is slow?

    User wants 5 rows
    Query 1 isn’t utilising the index and pulls out 5,000 rows – Executed in 0.5s
    Query 2 is using the index and pulls out 5 rows – 0.01s

    Once you’ve run them and played about with them they’re all Executing at 0.01s because of cache and you no longer know which is the better query.

    So I think when people say they want to disable cache. They want to know how it run the first time.

Speak Your Mind

*