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

45 篇文章 1 订阅
17 篇文章 0 订阅

        

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. 选择性能 > 实例会话,显示结果如下图所示:

        DMS 查看执行的查询

        从上图可以看出,有 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% 的问题解决。

    优化结果

  • 43
    点赞
  • 22
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

-无-为-

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值