Measuring CPU stall reductions from Dynimize

Duration: 30 min

Level: Intermediate

In this tutorial we are going to install and experiment with Dynimize using MySQL running the Sysbench OLTP benchmark. We also play around with the Linux perf command, top and vmstat. This tutorial assumes that you have MySQL and the Linux perf tool installed, and that there are no other CPU intensive workloads on the system other than those being tested. In order for the Linux perf tool to report CPU event counts, this tutorial should be completed on either a bare metal Linux server, or if using a virtual machine guest then virtual PMU support must be enabled by the hypervisor.

The initial part of this tutorial is meant to illustrate how to determine if there is potential for Dynimize to speedup a mysql (or any other program) workload, by checking to see if it's CPU bound, executing mostly in user mode (as opposed to kernel mode) and exhibits classic front-end CPU stalls,

In the latter part of the tutorial we'll also use the vmstat tool to measure CPU usage reduction after an I/O bound workload is dynimized.

Two terminals will be used for this tutorial; the first for running the benchmark workload, and the second for controlling Dynimize and collecting CPU performance statistics.

Because no two setups are identical, your results will differ from those here to some extent.

1. Install Dynimize

In case you haven't already, install and license Dynimize with the following commands:

Use your access token to start a subscription license for your host.

2. Benchmarking MySQL with the Sysbench 0.5 OLTP workload in a CPU bound case

For this CPU bound part of the tutorial, all steps were done using Centos 7.2, with MySQL community server version 5.7.11 for x86_64, and Sysbench 0.5. The system used here is a 2x8 core 2.4 GHz Xeon 2xE5-2630v3, with 128 GB of RAM and 2x480GB Intel SSD drives. Note that Sysbench 0.5 or newer is required since we're using the --report-interval option.

Install sysbench:

The latest version of Sysbench can be installed from packages hosted by Alexey Kopitov on package cloud. Note that Sysbench versions newer than 0.5 may require slightly different usage:

Debian/Ubuntu

RHEL/CentOS:

Fedora:

Install the Linux perf tool:

Debian/Ubuntu:

RHEL/CentOS/Fedora:

Make sure that innodb_buffer_pool_size=1G or larger is set in /etc/my.cnf. Here is the simple my.cnf file that was used which is sufficient for this experiment.

Make sure Dynimize is not running, which might be the case after a reboot. Also replace user and password appropriately for your tests, both in the mysql and sysbench commands.

Restart mysql:

Create a test database (drop it first in case these steps are repeated):

Fill the test database with 1,000,000 rows of sysbench oltp data. Note that different versions of Sysbench will install oltp.lua into different dirs, so you may need to perform a search for oltp.lua before specifying the path in the --test parameter.

Now run the benchmark in the first terminal, printing transactions/second every 10 seconds. The server used in this example has 16 cores plus hyperthreading (32 hardware threads), and it was found that 64 threads provides the best results on this setup, so we'll use –num-threads=64. Adjust the number of threads appropriately for your system for maximum performance.

Note that because we are running with -oltp-read-only=on, the benchmark is generally CPU bound so long as the dataset can fit in the MySQL innodb bufferpool, which it does in this case (innodb_buffer_pool_size=1G in the above my.cnf with --oltp-table-size=1000000).

The output should look something like the following:

3. Measure CPU performance statistics in the CPU-bound MySQL workload

While sysbench is running in the previous step, open a second terminal which we will use to monitor some CPU usage statistics.
While sysbench is running in the first terminal, in the second terminal run:

which produces the following output:

The above command is sampling performance statistics over 10 second intervals. It is generally advisable to disregard the first output statistics line which can be considered meaningless. The "us" column shows that user mode CPU execution is consuming around 89% of the system's CPU resources, while the "sy" column shows kernel mode execution consumes around 11%. Dynimize can only improve machine code that executes in user mode, and here we see that there is a lot of opportunity in this workload at 89%.

Lets see which processes are consuming CPU resources. The top command shows the following output. In the second terminal run:

which produces output similar to the following:

Note that a lot of the output here has been removed, with only the first three process output lines shown for readability. As we can see, only two processes, mysqld and sysbench are consuming most of the the CPU resources on the system. To optimize mysqld, it must be specified in the exeList in /etc/dyni.conf, which happens to be the case on installation.

Now lets measure a few detailed CPU statistics with the Linux perf command, which counts CPU hardware events. While sysbench is running in the first terminal, in the second terminal run this as a single command:

The above usage of the command counts system wide CPU events for 10 seconds, specifically instructions executed, CPU cycles consumed, L1 instruction cache misses, iTLB (instruction translation lookaside buffer) misses, branch prediction misses, all taking place during user mode execution (as opposed to kernel mode). After 10 seconds the command outputs the following:

We can see that the IPC (instructions per cycle) is 0.60, and the branch prediction miss rate is 3.19%. We can also calculate the following:

L1-icache-load-misses rate = 12,493,143,978/442,928,540,208 instructions = 2.82%
ITLB-load-misses rate = 1,467,653,054/442,928,540,208 = 0.33%

Note that the 2.82% i-cache miss rate is very high and would likely consume a large portion of the CPU cycles of this workload. This problem is common in many enterprise software workloads.

4. Apply Dynimize to this CPU-bound MySQL workload

In the second terminal we used for perf, start Dynimize:

Wait a few seconds, and then check the status of Dynimize:

which outputs the following:

This shows that it is currently dynimizing mysqld. In about 70 seconds after starting Dynimize, we can check back to see if it has finished dynimizing this process. The amount of time actually required depends on the workload and hardware:

which outputs:

Now go back to the first terminal where sysbench is running and observe the latest output:

You can see a dip in performance which lasted a few seconds. That drop in performance was mostly due to the profiling overhead Dynimize incurs on its target processes during the dynimizing phase. Once that is complete, steady state performance is observed to be around 20150 tps, which is approximately a 31.5% throughput improvement.

Let's now return to the second terminal and rerun the same perf command:

which outputs:

We can see that the user mode instructions per cycle (IPC) has increased to 0.78 (from the original 0.6) and the branch miss rate has decreased to 2.76% (from 3.19%). We can again deduce the other following miss rates:

L1-icache-load-misses rate = 8,797,026,557/566,161,551,736 instructions = 1.55% (down from the original 2.82%)

ITLB-load-misses rate = 820,245,959/566,161,551,736 = 0.14%
(down from the original 0.33%)

Note that while user mode IPC increased on a relative basis by 0.78/0.60 = 30%, Dynimize also does a small amount of redundant instruction elimination driven by live profiling statistics and other live information. It therefore slightly reduces the total instructions executed per transaction, increasing performance marginally beyond the IPC improvements alone.

5. Apply Dynimize to an I/O-bound MySQL workload

We are now going to try and make the previous sysbench OLTP benchmark I/O bound by setting --oltp-read-only to off. In the previous section with --oltp-read-only=on and a large enough buffer pool, all queries were able to be satisfied by reading from RAM. By writing to disk with --oltp-read-only=off, the workload can become I/O bound where in terms of hardware components, the speed of the hard drive is the bottle neck and ultimately determines performance.

Because the system used in the first part of the tutorial had relatively fast SSD drives, running --oltp-read-only=off was still CPU bound and so the results shown in the remaining part of this section below were performed using a system with a slower magnetic disk drive in order to fully accentuate the effects of Dynimize in I/O bound workloads. If your system has a fast enough hard drive, you'll likely still see speedup with Dynimize when using ---oltp-read-only=off. If using a slow hard drive, you won't see any speedup, but instead experience reduced CPU resource consumption, as seen in the following example. Many systems will be somewhere in between these two extremes and exhibit both a bit of speedup and reduced CPU resource consumption to some extent, although less of each when compared to the extreme cases of a purely CPU or IO bound setup as shown in this tutorial.

The results for the remainder of the tutorial were obtained on a system with a dual core 2.3 GHz i3-M350 CPU with hyperthreading enabled, 4 GB ram, with a HITACHI HTS545032B9A300 HDD. The system is running Ubuntu 14.04 LTS, using MySQL 5.5.41 and the same sysbench version.

It is assumed that you will be continuing on the same machine, in which case stop Dynimize in the second terminal:

In the first terminal hit Ctrl+C to interrupt sysbench, then restart MySQL:

service mysql restart

In the first terminal run sysbench changing --oltp-read-only to off, making this stress the IO subsystem by performing writes to disk as well. We also change –report-interval to 60 so as to better smooth out the results. Expect throughput updates every 60 seconds now. Execute the following commands in the first terminal:

The above command should produce similar output to the following. Notice the reduced throughput compared to the read-only case. You may also find that the throughput fluctuates more:

Let's monitor the CPU usage with top in the second terminal:

Here we use 60 to take 60 second samples, smoothing out the results. This should produce output similar to the following:

Notice the reduced CPU utilization compared to the CPU bound case, which is now at around 23% for user mode and 6% for system mode, for a total of 29% CPU utilization. This workload is clearly IO bound.

Now start Dynimize in the same terminal:

Because the workload is consuming far fewer CPU resources, Dynimize may take longer to optimize the workload.

You can repeatedly check dyni -status until mysld is dynimized. This may take a bit longer because of the low CPU usage of this workload.

Now run vmstat 60 again:

Which outputs:

The sysbench throughput numbers in this example did not change on our system after the workload was dynimized because it was not CPU bound, so we won't bother to show the remaining sysbench output.

Notice the further reduction in user mode CPU utilization which in this system is now at around 17%, compared to the original 23%, with system mode unchanged. We can assume that CPU power consumption would also be reduced due to the increased idle and wait time, and that more CPU resources are now free to run other workloads, potentially improving their 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).