Principles

Symptom

According to sysbench's point select test on MySQL, the CPU usage of the server is reaching 100%. The perf top tool detects that mysqld's dispatch_command is a hotspot function and occupies more than 20% of the CPU usage. After a further analysis on the dispatch_command function, it is found that the CPU usage percentages of add and subtract operations on an atomic variable are extremely high, each exceeding 40%. A contention occurs, which is an obvious bottleneck. See Figure 1 to Figure 3.

Figure 1 Hotspot functions of mysqld

Figure 2 Bottleneck 1 of dispatch_command

Figure 3 Bottleneck 2 of dispatch_command

Tuning Principles

The code line where one bottleneck occurs performs an auto-increment on an atomic variable, and the code line where the other bottleneck occurs performs an auto-decrement on the same atomic variable, resulting in a contention on the atomic variable. According to an analysis of related code, the two lines of code are part of the running thread counting and query function module. This module can increase or decrease the number of running threads when SQL statements are executed and provide the interface for querying the number of running threads. Figure 4 shows the main process:

Figure 4 Main process of the thread counter

In the previous counting and query process, the number of running threads is increased or decreased during SQL statement execution, and atomic variables are directly returned, which impairs service performance. By thread counter tuning, the operation of increasing or decreasing the number of running threads during SQL statement execution is deleted. In this way, the number of running threads is counted in the query process. That is, the service performance is improved at the cost of the query performance. Figure 5 shows the tuning process:

Figure 5 Tuning process of the thread counter