MySQL Proxy benchmarking and profiling
By Kay Roepke on Aug 07, 2009
On our road to a MySQL Proxy GA release, there are quite a few things that need attention, one of them being multithreading.
We are actively working on release 0.8, which brings the first stage of multithreading to the table: Enabling worker threads to handle network events in parallel. The code has been available for quite some time now and we’ve started to implement several performance benchmarks to track our progress, catch regressions and deficiencies early on.
Benchmarking is an interesting field, especially since you can screw up so easily :)
To avoid making mistakes and to spend less time reinventing the wheel, we are doing the same as we are doing with the code: Stand on the shoulders of giants.
To that end, we are using existing tools and toolsets as much as we can, and one of the most popular drivers to run with MySQL is sysbench. However, we’ve found that while sysbench generates load, it doesn’t support all the different scenarios we are trying to test in.
Let me quickly summarize what we have to look at when judging performance with MySQL Proxy:
- We need lots of connections, to make sure the event-based IO we are based on scales appropriately.
- We want those connections to be of different kinds:
- lots of idle connections
- lots of active connections
- a predetermined ratio of the two above
- Ideally we would like to run a predetermined number of queries/second, to compare CPU usage as we optimize the code (optimization is another ankle biter…)
- We need to vary the different variables that happen in real world scenarios
- resultset sizes (average ones, standard deviation of them, fixed sizes etc)
- query execution times (our performance characteristics depends on the distribution of really fast queries and slower ones, we generally do better with slower ones right now)
- connection establishment/persistent connections ratio (there’s overhead involved with establishing new connections, e.g. the entire handshake)
As you can see we are aiming to cover the most common scenarios you’d encounter in production environments. Now, obviously we don’t have a private copy of Facebook to play with, let alone the load its users generate (and to be honest, I don’t want the headaches involved on top of my existing headaches ;)).
We’ve established that sysbench is not the silver bullet. Then, what is?
So far we haven’t found anything pre-made that lets us do what we need, thus our only option is to write the benchmarks ourselves. But since we aren’t really in the business of doing performance analysis (after all we are getting paid to ship software, not to write benchmarks all day) we have been looking around.
It turns out that there’s a really nice framework that enables you to focus on writing exactly your benchmark, without worrying too much about scaling the drivers, creating graphs and deploying the whole lot: Faban. What’s even funnier is that it’s written by our colleagues here at Sun – sometimes the good is so near.
Faban takes care of most things for you, including running monitoring tools like mpstat or iostat during the benchmark run, includes fenxi to create nice graphs and to compare benchmark runs and is written in Java which makes it nicely portable across different systems (though you really want to have Java6 to fully take advantage of it – hardly a problem nowadays).
The best thing about all of this is: We will be able to scale out our benchmarks to many driver machines to create insane amounts of traffic, much like JMeter supports.
Now, that all being said, sysbench has been proven to be useful to us and until our new benchmarks are ready to be put to use, we are “stuck” with it. Actually we probably will end up running it in the future, too, but that’s a different story. To make it all nicer on the people who actually run the tests, I’ve integrated sysbench into Faban, so we can run it and gather its output from and in a single place. If nothing else, it was a nice way to dive into Faban’s internals and gather experience for the other benchmarks.
Another nice side effect of using Faban is, that other people can run it on their systems, Faban is open source, and once we publish our benchmark code, other people can verify our results, on their own machines, a prerequisite for getting valid results.
And who knows, maybe they will be useful to people for completely other purposes than the ones we are putting them to use for.
Thus, a little request: If you have suggestions or requests on what you would like to see in a generic MySQL-specific benchmark leave a comment or contact us on our Launchpad discuss list (you need to register on Launchpad and then join the list – all free as in beer).