Poor MySQL Performance When Sysbench Is Used to Perform Stress Tests on MySQL 5.7.21

Symptom

When sysbench is used to perform stress tests on MySQL 5.7.21 in the case of 256 concurrent requests, the TPS is 10,685 in the x86 environment and 4,197 in the Kunpeng 920 processor environment. The performance gap is huge.

Key Process and Cause Analysis

1. During the stress test using a script, the top command output shows that the CPU usage of the MySQL process is about 6000%, among which the system call usage is high. To further clarify the MySQL execution process, use perf to perform hotspot function and flame graph analysis.
2. Run the perf top command during the stress test. The command output shows that the usage of related functions is high.
3. Further analyze the function call stack relationship. Run the perf record command to capture perf data of a period of time, parse the data, and generate a flame graph.

   Analyze the flame graph. It can be found that the MySQL hotspot functions MVCC::view_open and PolycyMutex_ invoke spin_lock-related functions at the underlying layer. As a result, the system call usage is high, and a large number of threads are spinning, consuming CPU resources.

Conclusion and Solution

After MySQL is optimized, the TPS of the Kunpeng 920 processor reaches 11,700 in the case of 256 concurrent requests, which is better than the original x86 result (10,685 TPS).

1. (Optional) Modify the cache line size in the MySQL source code.

   According to MySQL-related issues on GitHub, MySQL performs hard coding on cache lines. The MySQL cache line size is 64 bytes, same size as that of the x86 platform. The cache line size of the Kunpeng 920 processor is 128 bytes. Therefore, you need to modify the MySQL source code to improve performance. For details about modifying the MySQL source code, see https://github.com/mysql/mysql-server/pull/66/files.

2. Modify MySQL configuration parameters.
   1. In MySQL, innodb_thread_concurrency, innodb_spin_wait_delay, and innodb_sync_spin_loops parameters affect spin_lock performance. You are advised to set nnodb_thread_concurrency to the number of CPU cores. You can increase the values of innodb_spin_wait_delay and innodb_sync_spin_loops parameters and run the perf top command to observe the usage of the MVCC::view_open and PolycyMutex_ hotspot functions. When the usage drops to a proper range, the expectation is met.
   2. During the stress test on MySQL under high concurrency, the atomic operation implementation on the x86 platform is different from that on the Kunpeng 920 processor. Because the Kunpeng 920 processor has a large number of cores, the usage of spin_lock-related system calls is high. To solve this problem, adjust the spin_lock-related MySQL parameters to improve performance. You can better understand the functions of the two parameters based on MySQL spin lock code implementations.