In this blog, I will provide answers to the Q & A for the Troubleshooting MySQL Concurrency Issues with Load Testing Tools webinar.
First, I want to thank everybody for attending my May 23, 2018, webinar on troubleshooting tools for MySQL. The recording and slides for the webinar are available here. Below is the list of your questions that I was unable to answer fully during the webinar.
Q: What do you recommend for benchmarking NDB cluster? Which should be used and how?
A: The issue with benchmarking NDB cluster is not the tool choice, but the limitations that this product has compared to “regular” InnoDB MySQL installations. You can use any tool you like (Sysbench!), just make sure you are not running a test case that can be affected by NDB limitations (full list, performance). Or, at least, adjust your expected results for such a test. You may check these slides by former MySQL Product Manager Mat Keep. He covers benchmarks too. Just note that NDB cluster is improving over the years and you need to consult the current documentation to find out which limitations have already been relaxed.
Q: Can SysBench coordinate load testing in a multi-master scenario, or master and slave, at the same time? Or does it have a single connection?
A: SysBench version > 1.0 can open more than one connection per test (and even per thread). But you will need to write your own custom tests to coordinate load testing in a multiple server scenario.
Q: What do you think about the function BENCHMARK()? Compared with mysqlslap?
A: Function BENCHMARK just evaluates the scalar expression whilst mysqlslap runs the SQL query. BENCHMARK() can only be used to test how fast a function or a subquery, returning a single value, executes. This limits its usage to only a small number of real life use cases. It may help to test, say, if a function is effective enough for calculating expressions. It runs everything in the same thread, therefore you cannot use it for testing concurrency issues unless you start it in multiple clients.
mysqlslap is very basic tool, but it can run SQL in multiple threads which makes its tests closer to real life scenarios.