Reductions in MySQL & MariaDB CPU Stalls With Dynimize

Dynimize Cross-Microarchitecture Performance Comparison

CPU Stall Events

Overview

The below graphs plot front-end CPU stall events for various CPU bound MySQL workloads generated by Sysbench, run with and without Dynimize for comparison. Here Sysbench and MySQL were both run locally on the same server. All changes measured represent the difference after applying Dynimize. Plots illustrating the throughput numbers associated with these tests can be found here. This was done across three systems that each utilize a very different Intel CPU microarchitecture, spanning five years of Intel CPU designs. As can be seen below, Dynimize reduces instruction cache misses, instruction TLB misses, and branch mispredictions, and increases instructions per cycle (IPC) across all three microarchitectures. All Sysbench runs utilized ten OLTP tables of one million rows each. A detailed description of how to recreate these runs can be found here, with the scripts and raw results uploaded to the github repository here. The traces of every command executed by the scripts for each run can be found in the output.log files in the results directories of that repository.

Note that Kaby Lake, Broadwell, and Ivy Bridge are the names of the different Intel CPU microarchitectures that were tested.

Software Specifications:

MySQL Version:	5.7.19-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu))
MariaDB Version:	10.2.7-MariaDB-10.2.7+maria~xenial-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution)
Percona Server Version:	5.7.18-16 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '16', Revision 'd7301f8')
Linux Distro:	Ubuntu 16.04.3 LTS (Xenial)
Linux Kernel:	Linux ns3042788.ip-51-255-93.eu 4.4.0-87-generic #110-Ubuntu SMP Tue Jul 18 12:55:35 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
Sysbench Version:	1.0.8
Dynimize Version:	1.0.1

Hardware Specifications:

Kaby Lake System:

CPU:	Intel(R) Xeon(R) CPU E3-1270 v6 @ 3.80GHz, 4 cores, 8 Threads
RAM:	32 GB of 2400 MHz DDR4
Storage:	HGST Ultrastar HUS726020AL, SoftRaid 2x2TB HDD

*This is a dedicated server rented from OVH, model: SP-32 Server, data center BHS 5

Broadwell System:

CPU:	Intel(R) Xeon(R) CPU D-1521 @ 2.40GHz, 4 cores, 8 Threads
RAM:	32 GB of 2400 MHz DDR4
Storage:	HGST Ultrastar HUS724020AL, SoftRaid 2x2TB HDD

*This is a dedicated server rented from OVH, model: HOST-32L Server, data center BHS 5

Ivy Bridge System:

CPU:	Intel(R) Xeon(R) CPU E5-1620 v2 @ 3.70GHz, 4 cores, 8 Threads
RAM:	16 GB of DDR3 1333 MHz
Storage:	SAMSUNG MZ7LM240, SoftRaid 2x240GB SSD

*This is a dedicated server rented from OVH, model: GPU-970-S Server, data center GRA 1

MySQL 5.7 with Sysbench 1.0

Microarchitecture: Kaby Lake

Summary of Changes

Increase in Instructions Per Cycle

Increase in Transactions Per Cycle

Decrease in Instruction Cache Misses

Decrease in Branch Mispredictions

Decrease in Instruction TLB Misses

MySQL :: Kaby Lake :: OLTP Read-Only

OLTP Read-Only: Instructions Per Cycle

OLTP Read-Only: Instruction Cache Miss Rate

OLTP Read-Only: Branch Misprediction Rate

OLTP Read-Only: Instruction TLB Miss Rate

MySQL :: Kaby Lake :: OLTP Simple

OLTP Simple: Instructions Per Cycle

OLTP Simple: Instruction Cache Miss Rate

OLTP Simple: Branch Misprediction Rate

OLTP Simple: Instruction TLB Miss Rate

MySQL :: Kaby Lake :: Select

Select: Instructions Per Cycle

Select: Instruction Cache Miss Rate

Select: Branch Misprediction Rate

Select: Instruction TLB Miss Rate

MySQL :: Kaby Lake :: Select Random Ranges

Select Random Ranges: Instructions Per Cycle

Select Random Ranges: Instruction Cache Miss Rate

Select Random Ranges: Branch Misprediction Rate

Select Random Ranges: Instruction TLB Miss Rate

Microarchitecture: Broadwell

Summary of Changes

Increase in Instructions Per Cycle

Increase in Transactions Per Cycle

Decrease in Instruction Cache Misses

Decrease in Branch Mispredictions

Decrease in Instruction TLB Misses

MySQL :: Broadwell :: OLTP Read-Only

OLTP Read-Only: Instructions Per Cycle

OLTP Read-Only: Instruction Cache Miss Rate

OLTP Read-Only: Branch Misprediction Rate

OLTP Read-Only: Instruction TLB Miss Rate

MySQL :: Broadwell :: OLTP Simple

OLTP Simple: Instructions Per Cycle

OLTP Simple: Instruction Cache Miss Rate

OLTP Simple: Branch Misprediction Rate

OLTP Simple: Instruction TLB Miss Rate

MySQL :: Broadwell :: Select

Select: Instructions Per Cycle

Select: Instruction Cache Miss Rate

Select: Branch Misprediction Rate

Select: Instruction TLB Miss Rate

MySQL :: Broadwell :: Select Random Ranges

Select Random Ranges: Instructions Per Cycle

Select Random Ranges: Instruction Cache Miss Rate

Select Random Ranges: Branch Misprediction Rate

Select Random Ranges: Instruction TLB Miss Rate

Microarchitecture: Ivy Bridge

Summary of Changes

Increase in Instructions Per Cycle

Increase in Transactions Per Cycle

Decrease in Instruction Cache Misses

Decrease in Branch Mispredictions

Decrease in Instruction TLB Misses

MySQL :: Ivy Bridge :: OLTP Read-Only

OLTP Read-Only: Instructions Per Cycle

OLTP Read-Only: Instruction Cache Miss Rate

OLTP Read-Only: Branch Misprediction Rate

OLTP Read-Only: Instruction TLB Miss Rate

MySQL :: Ivy Bridge :: OLTP Simple

OLTP Simple: Instructions Per Cycle

OLTP Simple: Instruction Cache Miss Rate

OLTP Simple: Branch Misprediction Rate

OLTP Simple: Instruction TLB Miss Rate

MySQL :: Ivy Bridge :: Select

Select: Instructions Per Cycle

Select: Instruction Cache Miss Rate

Select: Branch Misprediction Rate

Select: Instruction TLB Miss Rate

MySQL :: Ivy Bridge :: Select Random Ranges

Select Random Ranges: Instructions Per Cycle

Select Random Ranges: Instruction Cache Miss Rate

Select Random Ranges: Branch Misprediction Rate

Select Random Ranges: Instruction TLB Miss Rate

MariaDB 10.2 with Sysbench 1.0

Microarchitecture: Kaby Lake

Summary of Changes

Increase in Instructions Per Cycle

Increase in Transactions Per Cycle

Decrease in Instruction Cache Misses

Decrease in Branch Mispredictions

Decrease in Instruction TLB Misses

MariaDB :: Kaby Lake :: OLTP Read-Only

OLTP Read-Only: Instructions Per Cycle

OLTP Read-Only: Instruction Cache Miss Rate

OLTP Read-Only: Branch Misprediction Rate

OLTP Read-Only: Instruction TLB Miss Rate

MariaDB :: Kaby Lake :: OLTP Simple

OLTP Simple: Instructions Per Cycle

OLTP Simple: Instruction Cache Miss Rate

OLTP Simple: Branch Misprediction Rate

OLTP Simple: Instruction TLB Miss Rate

MariaDB :: Kaby Lake :: Select

Select: Instructions Per Cycle

Select: Instruction Cache Miss Rate

Select: Branch Misprediction Rate

Select: Instruction TLB Miss Rate

MariaDB :: Kaby Lake :: Select Random Ranges

Select Random Ranges: Instructions Per Cycle

Select Random Ranges: Instruction Cache Miss Rate

Select Random Ranges: Branch Misprediction Rate

Select Random Ranges: Instruction TLB Miss Rate

Microarchitecture: Broadwell

Summary of Changes

Increase in Instructions Per Cycle

Increase in Transactions Per Cycle

Decrease in Instruction Cache Misses

Decrease in Branch Mispredictions

Decrease in Instruction TLB Misses

MariaDB :: Broadwell :: OLTP Read-Only

OLTP Read-Only: Instructions Per Cycle

OLTP Read-Only: Instruction Cache Miss Rate

OLTP Read-Only: Branch Misprediction Rate

OLTP Read-Only: Instruction TLB Miss Rate

MySQL :: Broadwell :: OLTP Simple

OLTP Simple: Instructions Per Cycle

OLTP Simple: Instruction Cache Miss Rate

OLTP Simple: Branch Misprediction Rate

OLTP Simple: Instruction TLB Miss Rate

MariaDB :: Broadwell :: Select

Select: Instructions Per Cycle

Select: Instruction Cache Miss Rate

Select: Branch Misprediction Rate

Select: Instruction TLB Miss Rate

MariaDB :: Broadwell :: Select Random Ranges

Select Random Ranges: Instructions Per Cycle

Select Random Ranges: Instruction Cache Miss Rate

Select Random Ranges: Branch Misprediction Rate

Select Random Ranges: Instruction TLB Miss Rate

Microarchitecture: Ivy Bridge

Summary of Changes

Increase in Instructions Per Cycle

Increase in Transactions Per Cycle

Decrease in Instruction Cache Misses

Decrease in Branch Mispredictions

Decrease in Instruction TLB Misses

MariaDB :: Ivy Bridge :: OLTP Read-Only

OLTP Read-Only: Instructions Per Cycle

OLTP Read-Only: Instruction Cache Miss Rate

OLTP Read-Only: Branch Misprediction Rate

OLTP Read-Only: Instruction TLB Miss Rate

MariaDB :: Ivy Bridge :: OLTP Simple

OLTP Simple: Instructions Per Cycle

OLTP Simple: Instruction Cache Miss Rate

OLTP Simple: Branch Misprediction Rate

OLTP Simple: Instruction TLB Miss Rate

MariaDB :: Ivy Bridge :: Select

Select: Instructions Per Cycle

Select: Instruction Cache Miss Rate

Select: Branch Misprediction Rate

Select: Instruction TLB Miss Rate

MariaDB :: Ivy Bridge :: Select Random Ranges

Select Random Ranges: Instructions Per Cycle

Select Random Ranges: Instruction Cache Miss Rate

Select Random Ranges: Branch Misprediction Rate

Select Random Ranges: Instruction TLB Miss Rate

Percona Server 5.7 with Sysbench 1.0

Microarchitecture: Kaby Lake

Summary of Changes

Increase in Instructions Per Cycle

Increase in Transactions Per Cycle

Decrease in Instruction Cache Misses

Decrease in Branch Mispredictions

Decrease in Instruction TLB Misses

Percona Server :: Kaby Lake :: OLTP Read-Only

OLTP Read-Only: Instructions Per Cycle

OLTP Read-Only: Instruction Cache Miss Rate

OLTP Read-Only: Branch Misprediction Rate

OLTP Read-Only: Instruction TLB Miss Rate

Percona Server :: Kaby Lake :: OLTP Simple

OLTP Simple: Instructions Per Cycle

OLTP Simple: Instruction Cache Miss Rate

OLTP Simple: Branch Misprediction Rate

OLTP Simple: Instruction TLB Miss Rate

Percona Server :: Kaby Lake :: Select

Select: Instructions Per Cycle

Select: Instruction Cache Miss Rate

Select: Branch Misprediction Rate

Select: Instruction TLB Miss Rate

Percona Server :: Kaby Lake :: Select Random Ranges

Select Random Ranges: Instructions Per Cycle

Select Random Ranges: Instruction Cache Miss Rate

Select Random Ranges: Branch Misprediction Rate

Select Random Ranges: Instruction TLB Miss Rate

Microarchitecture: Broadwell

Summary of Changes

Increase in Instructions Per Cycle

Increase in Transactions Per Cycle

Decrease in Instruction Cache Misses

Decrease in Branch Mispredictions

Decrease in Instruction TLB Misses

Percona Server :: Broadwell :: OLTP Read-Only

OLTP Read-Only: Instructions Per Cycle

OLTP Read-Only: Instruction Cache Miss Rate

OLTP Read-Only: Branch Misprediction Rate

OLTP Read-Only: Instruction TLB Miss Rate

Percona Server :: Broadwell :: OLTP Simple

OLTP Simple: Instructions Per Cycle

OLTP Simple: Instruction Cache Miss Rate

OLTP Simple: Branch Misprediction Rate

OLTP Simple: Instruction TLB Miss Rate

Percona Server :: Broadwell :: Select

Select: Instructions Per Cycle

Select: Instruction Cache Miss Rate

Select: Branch Misprediction Rate

Select: Instruction TLB Miss Rate

Percona Server :: Broadwell :: Select Random Ranges

Select Random Ranges: Instructions Per Cycle

Select Random Ranges: Instruction Cache Miss Rate

Select Random Ranges: Branch Misprediction Rate

Select Random Ranges: Instruction TLB Miss Rate

Microarchitecture: Ivy Bridge

Summary of Changes

Increase in Instructions Per Cycle

Increase in Transactions Per Cycle

Decrease in Instruction Cache Misses

Decrease in Branch Mispredictions

Decrease in Instruction TLB Misses

Percona Server :: Ivy Bridge :: OLTP Read-Only

OLTP Read-Only: Instructions Per Cycle

OLTP Read-Only: Instruction Cache Miss Rate

OLTP Read-Only: Branch Misprediction Rate

OLTP Read-Only: Instruction TLB Miss Rate

Percona Server :: Ivy Bridge :: OLTP Simple

OLTP Simple: Instructions Per Cycle

OLTP Simple: Instruction Cache Miss Rate

OLTP Simple: Branch Misprediction Rate

OLTP Simple: Instruction TLB Miss Rate

Percona Server :: Ivy Bridge :: Select

Select: Instructions Per Cycle

Select: Instruction Cache Miss Rate

Select: Branch Misprediction Rate

Select: Instruction TLB Miss Rate

Percona Server :: Ivy Bridge :: Select Random Ranges

Select Random Ranges: Instructions Per Cycle

Select Random Ranges: Instruction Cache Miss Rate

Select Random Ranges: Branch Misprediction Rate

Select Random Ranges: Instruction TLB Miss Rate

Discussions