MySQL服务器sys cpu过高的解决
One MySQL database server's load was very high, especially the SYS CPU, then the applications could not get enough connections. See the chart below.
数据库突然出现load上涨，特别是cpu sys飙涨，导致sql响应变慢，应用连接数不够的情况。
现象非常明显，如下面图示
MySQL version: 5.1.60
Analyze
1. The qps was not incresed obvious, also the volume of business
2. The SYS CPU was significant changed， this maybe caused by hardware/OS/MySQL
分析
1. qps并没有明显增长，业务量没有变化（这个判断后面会再说明）
2. cpu特别是syscpu出现了明显的变化，可能是硬件/mysql/os的问题
Troubleshooting:
1. switch the query to the slaver, but the appearance was the same. Eliminate the hardware.
2. seperate 50% query to the slaver, and 50% to the master, the appearance was the same. Eliminate the query pressure.
3. adjust the MySQL parameters. Reference from WeiXI
处理过程：
1. 使用主备切换工具做切换，将流量切到备库，现象依旧。排序硬件问题
2. 将流量分别放50%在主备库，现象依旧。 压力不是主要问题
3. 调整了mysql参数 参考了 维西的博客
innodb_thread_concurrency 32（The old value is 0, not open）
innodb_sync_spin_loops 100
innodb_spin_wait_delay 30
When the innodb_thread_concurrency is 0, the parameters of innodb_spin_wait_delay，innodb_sync_spin_loops are canceled.
If the thread running are very high, it will be generate a lot of kernel_mutex wating, then the cpu resource will be used up by sys. The QPS will be fall to Zero in serious conditon.
The 3 parameters were benchmarked by Percona
当该参数为0时，以下参数作废innodb_spin_wait_delay，innodb_sync_spin_loops；
如果此时并发度非常高，会产生大量kernel_mutex等待，cpu资源大量消耗在sys中，严重时qps会降为0
percona的这篇文章对这三个参数做了benchmark。
We get the great result from the parameter adjusts.
调整参数后，效果非常明显，应用不再报连接数问题
--eof
This work is licensed under a CC A-S 4.0 International License.