Measuring the Impact of Dynimize on Your MySQL Workload

While it's easier to measure the impact of Dynimize if you are running a MySQL benchmark with clear metrics, it can sometimes be a challenge on a production workload where you don't have precise performance analytics or metrics available. There are many great MySQL performance analysis tools out there, however they can often take time and effort to setup.

The simple measureDyniMysql script was created for this exact reason, and does not incur any MySQL downtime. You can find it at /opt/dynimize/measureDyniMysql after installing Dynimize. It will report the change in MySQL queries per second and mysqld CPU usage after applying Dynimize. Here is how to use it.

1. Install and start dynimize, and get your mysqld process into the dynimized state. For example, the command:

should show something like:

2. Open /opt/dynimize/measureDyniMysql, find the lines containing mysqlUser= and mysqlPass= and set them to a user/password combination that is granted access to the MySQL SHOW STATUS query. Root has access by default.

3. In the line containing exe= set the value to the target process name, which would be exe=mysqld in this case.

4. Make sure the mysqld process has high CPU usage with a consistent workload for at least the duration of this script, which is 141 seconds by default. The Linux top command can help to indicate that your target process has a consistent workload with high CPU usage.

5. Run sudo /opt/dynimize/measureDyniMysql until completion.

Optional
Search for the line measureTime= and change the value to change the duration (seconds) of each QPS and CPU usage measurement window with and without Dynimize. It is set to 60 seconds by default.

All done! You have now measured the performance impact of Dynimize. However if you are interested in more details and analysis then please read on.

Sample Output

Below is a snippet from the output of a sample running the old mysql_tpcc benchmark in memory on my laptop:

Combined Improvement Factor

Mathematically, the Combined Improvement Factor in the sample output above is (QPS change)/(CPU usage change). It represents the overall efficiency improvement when using Dynimize. Let's use an example to explain why. Image mysqld is able to push through twice (2.0X) the QPS after it's dynimized, all other things being equal. If the other components of the system are feeding mysqld the same number of queries per second regardless of whether or not mysqld is dynimized, you won't see an actual increase in QPS. Instead, dynimize will consume half the CPU usage to perform the same amount of work. In theory, that would yield a QPS change of 1.0X, and a CPU usage change of 0.5X, with an overall improvement factor of 2.0X. The reality is that many systems see a combination of both CPU usage and QPS changes, and so the Combined Improvement Factor helps measure this with a single number.

When should I expect an improvement in QPS and CPU usage?

It all comes down to bottlenecks and what's driving the MySQL queries.

1. Traffic or app usage driving queries
   Often the limiting factor for QPS is app usage or website traffic. Optimizing MySQL here will not directly increase traffic (I wish it did!) and so the average QPS required to serve that traffic over an extended period of time is not going to change. However because the CPU does not need to work as hard to accomplish the same amount of work, you should simply see a reduction in CPU usage.


2. Batch workload
   If it is a batch workload where you are trying to push through as many queries as possible over the entire period and the CPU is the bottle neck, then the total queries and average QPS may increase. However if the limiting factor is the network or disk, then you should not see much of a change in QPS and we revert back to case 1 (CPU usage reduction) because Dynimize only optimizes the CPU component of the workload.

How does the script actually work?

The script first measures performance when mysqld is in the dynimized state, then runs dyni -undo to revert to the baseline performance and measures that for comparison. The dyni -undo operation takes less than a second, and so helps provide a more accurate apples to apples comparison of performance by minimizing the time between the dynimized and baseline measurements, to minimize the opportunity for your production workload to have changed between measurements. This is in contrast to measuring baseline performance first, then pausing measurements while we bring mysqld to the dynimized state, which could take several minutes before measuring again, allowing for more time for the workload to change.

The script then ends by starting to dynimize mysqld again to bring mysqld back to the original state that the script found it in.

More on dyni -undo

The dyni -undo command simply undoes all the machine code changes that Dynimize applied to all targeted processes, reverting mysqld back back to its original undynimized machine code.

You may want to restart MySQL after many runs of this script, because this script will undo the Dynimize optimizations and then start dynimizing the target process again at the end to bring mysqld back to its original state before the script started. After too many undymimized/dynimized transitions the final improvement in the dynimized state may be reduced (however it should not affect the undynimized performance).

David Yeager

David Yeager is the founder of Dynimize Inc, with over a decade of experience in just-in-time compiler development. He is passionate about computer architectures and software performance, and his mission is to see dynamic compilation and optimization accelerate all workloads (through Dynimize).