CPU高利用率及IO高负载故障定位分析

一、系统表说明

MySQL 5.7 版本起，performance_schema.threads线程表可以查询各个线程的信息，THREAD_OS_ID值对应OS中的线程，这就为故障定位提供了便捷，SQL如下：

参数：30502为OS中的线程

select
    t.THREAD_ID,
    t.PROCESSLIST_ID,
    t.THREAD_OS_ID,
    t.PROCESSLIST_USER,
    t.PROCESSLIST_HOST,
    t.PROCESSLIST_DB,
    t.PROCESSLIST_TIME,
    t.PROCESSLIST_STATE,
    esc.SQL_TEXT
from
    performance_schema.threads t
    join performance_schema.events_statements_current esc on t.THREAD_ID = esc.THREAD_ID
where
    t.THREAD_OS_ID = 30502 \G

二、高CPU利用率定位分析

执行 top -H 可以开启按线程模式显示各线程的CPU利用率情况，如：

本测试用例中，只运行了一个SQL查询，所以在上图cpu利用率显示中，只显示了一个mysqld线程。在实际生产环境中，执行 top -H 可以看到无数个mysqld线程。

PID为30502的mysqld线程，CPU利用率为99.8%，下面可以在mysql中定位具体正在执行的SQL

mysql> select
    ->     t.THREAD_ID,
    ->     t.PROCESSLIST_ID,
    ->     t.THREAD_OS_ID,
    ->     t.PROCESSLIST_USER,
    ->     t.PROCESSLIST_HOST,
    ->     t.PROCESSLIST_DB,
    ->     t.PROCESSLIST_TIME,
    ->     t.PROCESSLIST_STATE,
    ->     esc.SQL_TEXT
    -> from
    ->     performance_schema.threads t
    ->     join performance_schema.events_statements_current esc on t.THREAD_ID = esc.THREAD_ID
    -> where
    ->     t.THREAD_OS_ID = 30502 \G
*************************** 1. row ***************************
        THREAD_ID: 7059
   PROCESSLIST_ID: 7034
     THREAD_OS_ID: 30502
 PROCESSLIST_USER: admin
 PROCESSLIST_HOST: 127.0.0.1
   PROCESSLIST_DB: NULL
 PROCESSLIST_TIME: 38
PROCESSLIST_STATE: Sending data
         SQL_TEXT: select * from vodb.st_stock_stream_m a join cctest.assign_bill b on a.DISTRIBUTE_NO=b.unpack_task_no
1 row in set (0.00 sec)

三、高IO负载故位分析

执行以下命令查看mysql用户有IO操作的线程

iotop -ou mysql

上图中IO最高的线程为760，代入查询SQL中定位当前执行的SQL

[5.7.37-log]>select
    ->     t.THREAD_ID,
    ->     t.PROCESSLIST_ID,
    ->     t.THREAD_OS_ID,
    ->     t.PROCESSLIST_USER,
    ->     t.PROCESSLIST_HOST,
    ->     t.PROCESSLIST_DB,
    ->     t.PROCESSLIST_TIME,
    ->     t.PROCESSLIST_STATE,
    ->     esc.SQL_TEXT
    -> from
    ->     performance_schema.threads t
    ->     join performance_schema.events_statements_current esc on t.THREAD_ID = esc.THREAD_ID
    -> where
    ->     t.THREAD_OS_ID = 760 \G
*************************** 1. row ***************************
        THREAD_ID: 49
   PROCESSLIST_ID: 24
     THREAD_OS_ID: 760
 PROCESSLIST_USER: root
 PROCESSLIST_HOST: localhost
   PROCESSLIST_DB: sbtest
 PROCESSLIST_TIME: 0
PROCESSLIST_STATE: update
         SQL_TEXT: INSERT INTO sbtest1(k, c, pad) VALUES(5042779, '92414202661-14291859035-18757691049-37971670709-39848532945-68164973695-93689826843-17273836296-17788170033-19574183929', '82925489196-46585790756-00581345240-37316052533-20517590705'),(5041036, '59768436110-23240951351-40223287131-23740572435-63972849623-59183147843-49098832696-40206889682-14289155558-32123313030', '82189982731-38559033588-89246968857-92972666956-33851751539'),(5048250, '95960091487-54004786710-32600387985-84029513929-67245136040-31229188369-77916894516-76888970498-81344806525-37743671127', '12881706178-74526035548-33548228051-14595138541-82928318994'),(5025715, '07539261061-81259858297-60149918396-97971301643-94310233981-09148716378-55589217946-97962328164-88674101455-82765330323', '04632441305-51989938485-52290253061-66946222871-76661974910'),(5009567, '76181600625-17957947278-91679969207-37832788082-06929514414-78894003749-15112597584-93539201611-39948365662-89932644973', '38904927900-34069635252-09973493630-25564150041-65570282520'),(501...
1 row in set (0.00 sec)