Java web应用性能分析之【CPU飙高分析之MySQL】

        

Java web应用性能分析之【Linux服务器性能监控分析概叙】-CSDN博客

        在上面文章中，我们从整体上描述了Linux服务器性能监控分析，下面我们将通过sysbench模拟mysqld的cpu飙高情况，同时进一步分析是那些sql导致cpu飙高。

一般分析流程

        第一步：系统监控（zabbix会发出告警邮件或者短信；top命令是实时监控），发现cpu飙高，并且是mysqld进程导致的。

        第二步：分析问题找原因（定位到具体的sql，结合业务综合分析）：top -Hp  mysqld的pid，按P以cpu占用高到低排序，列出线程ID；通过performance_schema.threads s 和 information_schema.PROCESSLIST 来定位具体sql。同样也可以通过iotop -o 来定位io高的会话TID，即列出线程ID，然后同样通过performance_schema.threads s 和 information_schema.PROCESSLIST 来定位具体sql。

SELECT     b.HOST,     b.db,     b.USER,     a.THREAD_OS_ID "os_id",     b.id "processlist_id",     b.command,     b.time,     b.state,     a.PROCESSLIST_INFO,     substr(b.info,1,100) "sql"  FROM performance_schema.threads s a,     information_schema.PROCESSLIST b  WHERE     b.id = a.processlist_id      AND a.THREAD_OS_ID in (9935 ,9817,9933)\G;

        第三步：优化配置，试图解决问题。

        第四步：检查优化效果，验证问题是否修复。

        第五步：问题复盘归档，便于后续问题排查。

Sysbench模拟高负载

        sysbench脚本

root@zhouxx:~# cat sysbenchtest.sh 
#!/bin/bash

current_data=$(date "+%Y-%m-%d")
current_time=$(date "+%H:%M:%S")

#链接配置信息
dbhost="172.26.35.10"
dbport=3306
dbuser="sbuser"
dbpwd="zhouxx1234"
dbname=sbtest
#单表数据量
tbsize=3
#表数据量
tables=1
#选择引擎，默认innodb
testengine=innodb
# 压测使用的lua 脚本
luascript=bulk_insert.lua
# 线程数组
#threads=(1 5 10 15)
# 测试时间
sbtime=600

 mkdir -p /usr/share/sysbench/sblog
sbtestlogdir=/usr/share/sysbench/sblog         #存放log的地方，需要手动自己创建
path=/usr/sysbench/share/sysbench/         #存放sysbench脚本point_select.lua等其他脚本的地方


#for thread in  ${threads[@]}
for thread in 1 5 10 15
do
    #测试结果输出日志
    sblogname=${sbtestlogdir}/sbtench_${testengine}_${luascript}_${thread}_$(date "+%Y%m%d%H%M%S").log
    echo "============================压测开始! 当前日期：$(date "+%Y-%m-%d %H:%M:%S")======================================
    压测地址:${dbhost},压测端口:${dbport}，测试引擎对象：${testengine}压测数据量:${tables}张表每张表${tbsize}条数据量，压测时间：${sbtime} s,当前压测线程：${threads}
    日志地址:${sbtestlogdir}
    sysbench ${path}${luascript} --db-driver=mysql --mysql-host=${dbhost} \
    --mysql-port=${dbport} --mysql-user=${dbuser} \
    --mysql-password=${dbpwd} \
    --mysql-db=${dbname} --table_size=${tbsize} \
    --tables=${tables} --events=0 --time=${sbtime} \
    --mysql_storage_engine=${testengine}  \
    --threads=${thread} --percentile=95  \
    --range_selects=0 --skip-trx=1 --report-interval=5 prepare " >> ${sblogname}

    sysbench ${path}${luascript} --db-driver=mysql --mysql-host=${dbhost} \
    --mysql-port=${dbport} --mysql-user=${dbuser} \
    --mysql-password=${dbpwd} \
    --mysql-db=${dbname} --table_size=${tbsize} \
    --tables=${tables} --events=0 --time=${sbtime} \
    --mysql_storage_engine=${testengine}  \
    --threads=${thread} --percentile=95  \
    --range_selects=0 --skip-trx=1 --report-interval=5 prepare >> ${sblogname}


    sysbench ${path}${luascript} --db-driver=mysql --mysql-host=${dbhost} \
    --mysql-port=${dbport} --mysql-user=${dbuser} \
    --mysql-password=${dbpwd} \
    --mysql-db=${dbname} --table_size=${tbsize} \
    --tables=${tables} --events=0 --time=${sbtime} \
    --mysql_storage_engine=${testengine}  \
    --threads=${thread} --percentile=95  \
    --range_selects=0 --skip-trx=1 --report-interval=5 run >> ${sblogname}
    
   sysbench ${path}${luascript} --db-driver=mysql --mysql-host=${dbhost} \
    --mysql-port=${dbport} --mysql-user=${dbuser} \
    --mysql-password=${dbpwd} \
    --mysql-db=${dbname} --table_size=${tbsize} \
    --tables=${tables} --events=0 --time=${sbtime} \
    --mysql_storage_engine=${testengine}  \
    --threads=${thread} --percentile=95  \
    --range_selects=0 --skip-trx=1 --report-interval=5 cleanup >> ${sblogname}     

echo "============================压测结束! 当前时间：$(date "+%Y-%m-%d %H:%M:%S")====================================== " >> ${sblogname}
    
    #sleep 10
    #/etc/init.d/atomstore restart
    

压测结果

root@zhouxx:~# ls -lt  /usr/share/sysbench/sblog
total 384
-rw-r--r-- 1 root root 16847 Apr 23 20:57 sbtench_innodb_bulk_insert.lua_15_20240423204743.log
-rw-r--r-- 1 root root 16559 Apr 23 20:47 sbtench_innodb_bulk_insert.lua_10_20240423203741.log
-rw-r--r-- 1 root root 16155 Apr 23 20:37 sbtench_innodb_bulk_insert.lua_5_20240423202740.log
-rw-r--r-- 1 root root 15810 Apr 23 20:27 sbtench_innodb_bulk_insert.lua_1_20240423201740.log
-rw-r--r-- 1 root root 64832 Apr 23 20:11 sbtench_innodb_bulk_insert.lua_15_20240423201135.log
-rw-r--r-- 1 root root 43519 Apr 23 20:11 sbtench_innodb_bulk_insert.lua_10_20240423201135.log
-rw-r--r-- 1 root root 22374 Apr 23 20:11 sbtench_innodb_bulk_insert.lua_5_20240423201135.log
-rw-r--r-- 1 root root  5394 Apr 23 20:11 sbtench_innodb_bulk_insert.lua_1_20240423201135.log
-rw-r--r-- 1 root root 64350 Apr 23 20:06 sbtench_innodb_bulk_insert.lua_15_20240423200655.log
-rw-r--r-- 1 root root 43265 Apr 23 20:06 sbtench_innodb_bulk_insert.lua_10_20240423200655.log
-rw-r--r-- 1 root root 22178 Apr 23 20:06 sbtench_innodb_bulk_insert.lua_5_20240423200655.log
-rw-r--r-- 1 root root  5310 Apr 23 20:06 sbtench_innodb_bulk_insert.lua_1_20240423200655.log
-rw-r--r-- 1 root root   976 Apr 23 20:02 sbtench_innodb_bulk_insert.lua_15_20240423200215.log
-rw-r--r-- 1 root root   976 Apr 23 20:02 sbtench_innodb_bulk_insert.lua_10_20240423200215.log
-rw-r--r-- 1 root root   975 Apr 23 20:02 sbtench_innodb_bulk_insert.lua_5_20240423200215.log
-rw-r--r-- 1 root root   975 Apr 23 20:02 sbtench_innodb_bulk_insert.lua_1_20240423200215.log
-rw-r--r-- 1 root root   976 Apr 23 20:01 sbtench_innodb_bulk_insert.lua_15_20240423200149.log
-rw-r--r-- 1 root root   976 Apr 23 20:01 sbtench_innodb_bulk_insert.lua_10_20240423200149.log
-rw-r--r-- 1 root root   975 Apr 23 20:01 sbtench_innodb_bulk_insert.lua_5_20240423200149.log
-rw-r--r-- 1 root root   975 Apr 23 20:01 sbtench_innodb_bulk_insert.lua_1_20240423200149.log
root@zhouxx:~# 
root@zhouxx:~# 
root@zhouxx:~# 
root@zhouxx:~# cat  /usr/share/sysbench/sblog/sbtench_innodb_bulk_insert.lua_5_20240423202740.log
============================压测开始! 当前日期：2024-04-23 20:27:40======================================
    压测地址:172.26.35.10,压测端口:3306，测试引擎对象：innodb压测数据量:1张表每张表3条数据量，压测时间：600 s,当前压测线程：
    日志地址:/usr/share/sysbench/sblog
    sysbench /usr/sysbench/share/sysbench/bulk_insert.lua --db-driver=mysql --mysql-host=172.26.35.10     --mysql-port=3306 --mysql-user=sbuser     --mysql-password=zhouxx1234     --mysql-db=sbtest --table_size=3     --tables=1 --events=0 --time=600     --mysql_storage_engine=innodb      --threads=5 --percentile=95      --range_selects=0 --skip-trx=1 --report-interval=5 prepare 
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Creating table 'sbtest2'...
Creating table 'sbtest3'...
Creating table 'sbtest4'...
Creating table 'sbtest5'...
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 5
Report intermediate results every 5 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 5s ] thds: 5 tps: 400390.18 qps: 10.80 (r/w/o: 0.00/10.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 5 tps: 419423.58 qps: 12.80 (r/w/o: 0.00/12.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 5 tps: 429541.01 qps: 14.20 (r/w/o: 0.00/14.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 5 tps: 405999.95 qps: 14.00 (r/w/o: 0.00/14.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 5 tps: 395998.65 qps: 13.40 (r/w/o: 0.00/13.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 5 tps: 440948.62 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 5 tps: 415402.04 qps: 14.20 (r/w/o: 0.00/14.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 5 tps: 423947.20 qps: 14.60 (r/w/o: 0.00/14.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 5 tps: 420679.69 qps: 14.40 (r/w/o: 0.00/14.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 5 tps: 437950.27 qps: 15.38 (r/w/o: 0.00/15.38/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 5 tps: 424131.39 qps: 14.22 (r/w/o: 0.00/14.22/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 5 tps: 433914.31 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 5 tps: 411985.71 qps: 14.20 (r/w/o: 0.00/14.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 5 tps: 412298.70 qps: 14.00 (r/w/o: 0.00/14.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 5 tps: 390269.00 qps: 13.40 (r/w/o: 0.00/13.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 5 tps: 448534.45 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 5 tps: 454348.73 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 5 tps: 416630.37 qps: 14.40 (r/w/o: 0.00/14.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 5 tps: 442058.65 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 5 tps: 440286.73 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 5 tps: 407752.40 qps: 14.00 (r/w/o: 0.00/14.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 5 tps: 378615.35 qps: 13.00 (r/w/o: 0.00/13.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 5 tps: 401934.65 qps: 13.80 (r/w/o: 0.00/13.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 5 tps: 387491.00 qps: 13.60 (r/w/o: 0.00/13.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 125s ] thds: 5 tps: 386390.80 qps: 14.60 (r/w/o: 0.00/14.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 5 tps: 372219.10 qps: 14.20 (r/w/o: 0.00/14.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 135s ] thds: 5 tps: 392908.78 qps: 14.99 (r/w/o: 0.00/14.99/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 5 tps: 388240.92 qps: 14.81 (r/w/o: 0.00/14.81/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 145s ] thds: 5 tps: 372224.00 qps: 14.20 (r/w/o: 0.00/14.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 5 tps: 372224.28 qps: 14.20 (r/w/o: 0.00/14.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 155s ] thds: 5 tps: 398437.98 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 5 tps: 382709.21 qps: 14.60 (r/w/o: 0.00/14.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 165s ] thds: 5 tps: 387952.98 qps: 14.80 (r/w/o: 0.00/14.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 5 tps: 403682.21 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 175s ] thds: 5 tps: 366981.09 qps: 14.00 (r/w/o: 0.00/14.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 5 tps: 393195.21 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 185s ] thds: 5 tps: 414162.82 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 5 tps: 403680.29 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 195s ] thds: 5 tps: 377470.45 qps: 14.40 (r/w/o: 0.00/14.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 5 tps: 382706.99 qps: 14.60 (r/w/o: 0.00/14.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 205s ] thds: 5 tps: 393194.90 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 5 tps: 403680.90 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 215s ] thds: 5 tps: 408924.80 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 5 tps: 389703.23 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 225s ] thds: 5 tps: 412412.00 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 5 tps: 398436.22 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 235s ] thds: 5 tps: 408924.95 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 5 tps: 387951.77 qps: 14.80 (r/w/o: 0.00/14.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 245s ] thds: 5 tps: 419407.73 qps: 16.00 (r/w/o: 0.00/16.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 5 tps: 424652.38 qps: 16.20 (r/w/o: 0.00/16.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 255s ] thds: 5 tps: 387949.96 qps: 14.80 (r/w/o: 0.00/14.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 5 tps: 420531.98 qps: 16.20 (r/w/o: 0.00/16.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 265s ] thds: 5 tps: 434014.16 qps: 16.40 (r/w/o: 0.00/16.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 5 tps: 414157.79 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 275s ] thds: 5 tps: 416539.25 qps: 16.00 (r/w/o: 0.00/16.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 5 tps: 417019.55 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 285s ] thds: 5 tps: 403700.70 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 5 tps: 429892.70 qps: 16.40 (r/w/o: 0.00/16.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 5 tps: 393196.43 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 5 tps: 403679.86 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 305s ] thds: 5 tps: 395584.69 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 310s ] thds: 5 tps: 422269.51 qps: 16.01 (r/w/o: 0.00/16.01/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 315s ] thds: 5 tps: 387953.18 qps: 14.80 (r/w/o: 0.00/14.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 320s ] thds: 5 tps: 424649.34 qps: 16.20 (r/w/o: 0.00/16.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 325s ] thds: 5 tps: 414164.27 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 330s ] thds: 5 tps: 393080.11 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 335s ] thds: 5 tps: 370421.27 qps: 14.40 (r/w/o: 0.00/14.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 340s ] thds: 5 tps: 405592.42 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 345s ] thds: 5 tps: 414170.46 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 350s ] thds: 5 tps: 414166.45 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 355s ] thds: 5 tps: 408921.57 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 360s ] thds: 5 tps: 397288.59 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 365s ] thds: 5 tps: 411214.29 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 370s ] thds: 5 tps: 397290.84 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 375s ] thds: 5 tps: 366978.08 qps: 14.00 (r/w/o: 0.00/14.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 380s ] thds: 5 tps: 366991.34 qps: 14.00 (r/w/o: 0.00/14.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 385s ] thds: 5 tps: 377467.18 qps: 14.40 (r/w/o: 0.00/14.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 390s ] thds: 5 tps: 398437.17 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 395s ] thds: 5 tps: 367325.85 qps: 14.40 (r/w/o: 0.00/14.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 400s ] thds: 5 tps: 387607.29 qps: 14.40 (r/w/o: 0.00/14.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 405s ] thds: 5 tps: 382709.47 qps: 14.60 (r/w/o: 0.00/14.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 410s ] thds: 5 tps: 398436.50 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 415s ] thds: 5 tps: 393194.77 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 420s ] thds: 5 tps: 403681.71 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 425s ] thds: 5 tps: 387950.80 qps: 14.80 (r/w/o: 0.00/14.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 430s ] thds: 5 tps: 398439.77 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 435s ] thds: 5 tps: 393192.42 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 440s ] thds: 5 tps: 382712.08 qps: 14.60 (r/w/o: 0.00/14.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 445s ] thds: 5 tps: 372224.90 qps: 14.20 (r/w/o: 0.00/14.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 450s ] thds: 5 tps: 414160.61 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 455s ] thds: 5 tps: 398437.75 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 460s ] thds: 5 tps: 429896.24 qps: 16.40 (r/w/o: 0.00/16.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 465s ] thds: 5 tps: 429738.89 qps: 16.39 (r/w/o: 0.00/16.39/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 470s ] thds: 5 tps: 424806.97 qps: 16.21 (r/w/o: 0.00/16.21/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 475s ] thds: 5 tps: 408921.34 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 480s ] thds: 5 tps: 408213.79 qps: 15.99 (r/w/o: 0.00/15.99/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 485s ] thds: 5 tps: 410288.65 qps: 15.41 (r/w/o: 0.00/15.41/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 490s ] thds: 5 tps: 418752.00 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 495s ] thds: 5 tps: 414166.27 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 500s ] thds: 5 tps: 429894.17 qps: 16.40 (r/w/o: 0.00/16.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 505s ] thds: 5 tps: 414158.19 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 510s ] thds: 5 tps: 424656.24 qps: 16.20 (r/w/o: 0.00/16.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 515s ] thds: 5 tps: 422639.28 qps: 16.20 (r/w/o: 0.00/16.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 520s ] thds: 5 tps: 400443.85 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 525s ] thds: 5 tps: 402330.27 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 530s ] thds: 5 tps: 410275.25 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 535s ] thds: 5 tps: 393196.21 qps: 15.00 (r/w/o: 0.00/15.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 540s ] thds: 5 tps: 408916.40 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 545s ] thds: 5 tps: 414170.83 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 550s ] thds: 5 tps: 420662.45 qps: 16.20 (r/w/o: 0.00/16.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 555s ] thds: 5 tps: 412910.01 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 560s ] thds: 5 tps: 403682.32 qps: 15.40 (r/w/o: 0.00/15.40/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 565s ] thds: 5 tps: 419388.32 qps: 16.00 (r/w/o: 0.00/16.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 570s ] thds: 5 tps: 419425.32 qps: 16.00 (r/w/o: 0.00/16.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 575s ] thds: 5 tps: 408924.78 qps: 15.60 (r/w/o: 0.00/15.60/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 580s ] thds: 5 tps: 419052.37 qps: 16.00 (r/w/o: 0.00/16.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 585s ] thds: 5 tps: 398792.80 qps: 15.20 (r/w/o: 0.00/15.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 590s ] thds: 5 tps: 414164.71 qps: 15.80 (r/w/o: 0.00/15.80/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 595s ] thds: 5 tps: 424653.59 qps: 16.20 (r/w/o: 0.00/16.20/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 600s ] thds: 5 tps: 419408.18 qps: 16.00 (r/w/o: 0.00/16.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           9078
        other:                           0
        total:                           9078
    transactions:                        243385299 (405490.13 per sec.)
    queries:                             9078   (15.12 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.2236s
    total number of events:              243385299

Latency (ms):
         min:                                    0.00
         avg:                                    0.01
         max:                                  939.12
         95th percentile:                        0.00
         sum:                              2909556.25

Threads fairness:
    events (avg/stddev):           48677059.8000/418949.02
    execution time (avg/stddev):   581.9113/0.26

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
============================压测结束! 当前时间：2024-04-23 20:37:41====================================== 
root@zhouxx:~# 

top命令查看mysqld进程cpu使用情况

top -Hp命令查看mysqld中线程cpu使用情况

通过线程号定位sql

        因为sql可能很长，这里做了截取。

SELECT     b.HOST,     b.db,     b.USER,     a.THREAD_OS_ID "os_id",     b.id "processlist_id",     b.command,     b.time,     b.state,     a.PROCESSLIST_INFO,     substr(b.info,1,100) "sql"  
FROM performance_schema.threads s a,     information_schema.PROCESSLIST b  
WHERE     b.id = a.processlist_id      
AND a.THREAD_OS_ID in (9935 ,9817,9933)\G;

iotop -o命令查看io过高的线程

        因为iotop -o 输出的就是TID线程号，同样通过performance_schema.threads s 和 information_schema.PROCESSLIST 来定位具体sql。

        压测前后，磁盘空间使用的情况。这里是阿里云主机，磁盘较小，看的比较明显，用了5GB。

分析问题找原因

          系统执行应用提交查询（包括数据修改操作）时需要大量的逻辑读（逻辑 IO，执行查询所需访问的表的数据行数），所以系统需要消耗大量的 CPU 资源以维护从存储系统读取到内存中的数据一致性。

说明：大量行锁冲突、行锁等待或后台任务也有可能会导致实例的 CPU 使用率过高，但这些情况出现的概率非常低，本文不做讨论。

        本文通过一个简化的模型来说明系统资源、语句执行成本以及 QPS（Query Per Second 每秒执行的查询数）之间的关系：

• 条件：应用模型恒定（应用没有修改）。

• avg_lgc_io：执行每条查询需要的平均逻辑 IO。

• total_lgc_io：实例的 CPU 资源在单位时间内能够处理的逻辑 IO 总量。

• 关系公式：total_lgc_io = avg_lgc_io x QPS -- 单位时间 CPU 资源 = 查询执行的平均成本 x 单位时间执行的查询数量

优化配置

      mysqld占用CPU过高，可以做如下配置调整：

        1）一般来讲，排除高并发的因素，还是要找到导致你CPU过高的哪几条在执行的SQL，show processlist语句，查找负荷最重的SQL语句，优化该SQL，比如适当建立某字段的索引；
        2）打开慢查询日志，将那些执行时间过长且占用资源过多的SQL拿来进行explain分析，导致CPU过高，多数是GroupBy、OrderBy排序问题所导致，然后慢慢进行优化改进。比如优化insert语句、优化group by语句、优化order by语句、优化join语句等等；
        3）考虑定时优化文件及索引；

        4）定期分析表，使用optimize table；
        5）优化数据库对象；
        6）考虑是否是锁问题；

        7）调整一些MySQL Server参数，比如key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size等等；
        8）如果数据量过大，可以考虑使用MySQL集群或者搭建高可用环境。
        9）可能由于内存（泄露）导致数据库CPU高
        10）在多用户高并发的情况下，任何系统都会hold不住的，所以，使用缓存是必须的，使用memcached或者redis缓存都可以；
        11）看看tmp_table_size大小是否偏小，如果允许，适当的增大一点；
        12）如果max_heap_table_size配置的过小，增大一点；
        13）mysql的sql语句睡眠连接超时时间设置问题（wait_timeout）
        14）使用show processlist查看mysql连接数，看看是否超过了mysql设置的连接数。

验证结果

        暂无

复盘

        问题归档。

避免出现 CPU 使用率达到 100% 的一般原则

• 设置 CPU 使用率告警，实例 CPU 使用率保证一定的冗余度。

• 应用设计和开发过程中，要考虑查询的优化，遵守 MySQL 优化的一般优化原则，降低查询的逻辑 IO，提高应用可扩展性。

• 新功能、新模块上线前，要使用生产环境数据进行压力测试（可以考虑使用阿里云 PTS 压力测试工具）。

• 新功能、新模块上线前，建议使用生产环境数据进行回归测试。

• 建议经常关注和使用 DMS 中的诊断报告。

  注意：关于如何访问 DMS 中的诊断报告，请参见 RDS 如何访问诊断报告。

典型示例

以 CPU 使用率为 100% 的典型场景为例，本文介绍了两个引起该状况的原因及其解决方案，即应用负载（QPS）高和查询执行成本（查询访问表数据行数 avg_lgc_io）高。其中，由于查询执行成本高（查询访问表数据行数多）而导致实例 CPU 使用率高是 MySQL 非常常见的问题。

应用负载（QPS）高

现象描述

• 特征：实例的 QPS（每秒执行的查询次数）高，查询比较简单、执行效率高、优化余地小。

• 表现：没有出现慢查询（或者慢查询不是主要原因），且 QPS 和 CPU 使用率曲线变化吻合。

• 常见场景：该状况常见于应用优化过的在线事务交易系统（例如订单系统）、高读取率的热门 Web 网站应用、第三方压力工具测试（例如 Sysbench）等。

解决方案

对于由应用负载高导致的 CPU 使用率高的状况，使用 SQL 查询进行优化的余地不大，建议您从应用架构、实例规格等方面来解决，例如：

• 升级实例规格，增加 CPU 资源。

• 增加只读实例，将对数据一致性不敏感的查询（比如商品种类查询、列车车次查询）转移到只读实例上，分担主实例压力。

• 使用阿里云 DRDS 产品，自动进行分库分表，将查询压力分担到多个 RDS 实例上。

• 使用阿里云 Memcache 或者云 Redis 产品，尽量从缓存中获取常用的查询结果，减轻 RDS 实例的压力。

• 对于查询数据比较静态、查询重复度高、查询结果集小于 1 MB 的应用，考虑开启查询缓存（Query Cache）。

  注意：能否从开启查询缓存（Query Cache）中获益需要经过测试，具体设置请参见 RDS for MySQL 查询缓存（Query Cache）的设置和使用。

• 定期归档历史数据、采用分库分表或者分区的方式减小查询访问的数据量。

• 尽量优化查询，减少查询的执行成本（逻辑 IO，执行需要访问的表数据行数），提高应用可扩展性。

查询执行成本（查询访问表数据行数 avg_lgc_io）高

现象描述

• 特征：实例的 QPS（每秒执行的查询次数）不高；查询执行效率低、执行时需要扫描大量表中数据、优化余地大。

• 表现：存在慢查询，QPS 和 CPU 使用率曲线变化不吻合。

• 原因分析：由于查询执行效率低，为获得预期的结果即需要访问大量的数据（平均逻辑 IO高），在 QPS 并不高的情况下（例如网站访问量不大），就会导致实例的 CPU 使用率高。

解决方案

解决该状况的原则是：定位效率低的查询、优化查询的执行效率、降低查询执行的成本。

操作步骤

1. 通过如下方式定位效率低的查询：

   • 通过 show processlist; 或 show full processlist; 命令查看当前执行的查询，如下图所示：

     

     对于查询时间长、运行状态（State 列）是“Sending data”、“Copying to tmp table”、“Copying to tmp table on disk”、“Sorting result”、“Using filesort”等都可能是有性能问题的查询（SQL）。

     注意：

     • 若在 QPS 高导致 CPU 使用率高的场景中，查询执行时间通常比较短，show processlist; 命令或实例会话中可能会不容易捕捉到当前执行的查询。您可以通过执行如下命令进行查询：

       1. explain select b.* from perf_test_no_idx_01 a, perf_test_no_idx_02 b where a.created_on >= 2015-01-01 and a.detail = b.detail
     • 您可以通过执行类似 kill 101031643; 的命令来终止长时间执行的会话，终止会话请参见 RDS for MySQL 如何终止会话。关于长时间执行会话的管理，请参见 RDS for MySQL 管理长时间运行查询

   • 通过 DMS 查看当前执行的查询，查询步骤如下：

     1. 在 DMS 控制台上登录数据库。

     2. 选择性能 > 实例会话，显示结果如下图所示：

        

        从上图可以看出，有 10 个会话在执行下面这个查询：

        1. select b.* from perf_test_no_idx_01 a, perf_test_no_idx_02 b where a.created_on>= '2015-01-01' and a.detail= b.detail;

     3. 单击 SQL 列中的查询文本，即可显示完整的查询和其执行计划，如下图所示：

        

        从上图可以看出，在该查询的执行计划中，系统对两张约为 30 万行的数据表执行了全表扫描。由于两张表是联接操作，这个查询的执行成本（逻辑 IO）约为 298267 x 298839 = 89,133,812,013（大概 900 亿），所以查询会执行相当长的时间并且多个会话会导致实例 CPU 使用率达到 100%（对于同样规格的实例，如果是优化良好的查询，QPS 可以达到 21000；而当前 QPS 仅为 5）。

2. 得到需要优化的查询后，可以通过如下任意一种方式来获取查询的优化建议：

   • 通过 DMS 的优化查询获取：

     注意：对于 QPS 高和查询效率低的混合模式导致的 CPU 使用率高的问题，建议使用优化查询获取优化建议。

     1. 在 DMS 控制台上登录数据库。

     2. 选择 SQL 操作 > SQL 窗口。

     3. 单击优化，即可得到优化建议，如下图所示：

        

   • 通过 DMS 控制台上的诊断报告获取：

     说明：诊断报告同样适用于排查历史实例 CPU 使用率高的问题。

     1. 在 DMS 控制台上登录数据库。

     2. 选择性能 > 诊断报告。

     3. 单击发起诊断，即可创建一个针对当前实例运行情况的报告，如下图所示：

        

     4. 单击查看报告，查看优化建议。

        注意：对于 CPU 使用率高的问题，建议关注诊断报告的 SQL 优化、会话列表和慢 SQL 汇总部分。

3. 根据优化建议，添加索引，查询执行成本就会大幅减少（如下图所示，从 900 亿行减小到 30 万行，查询成本降低 30 万倍），实例 CPU 使用率 100% 的问题解决。