[MYSQL] OS에서 과도한 CPU사용하는 MYSQL Thread 확인

 

1. yum 설치

-. yum -y install sysstat (리눅스 성능 측정 도구 패키지)

 

2. 과도한 CPU 사용 MYSQL THREAD 확인 및 kill

1) mysql process 검색

ps -ef | grep mysqld mysql 20645 19411 65 Feb21 ? 9-17:34:52 /usr/local/mysql/bin/mysqld

 

 

2) Thread 검색 

pidstat -t -p 1708 2 -> 실시간 갱신  pidstat -t -p 1708 $ pidstat -t -p 20645 11:05:57 AM   UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command 11:05:57 AM  1003     20645         -   51.34   13.67    0.00   65.01     5  mysqld 11:05:57 AM  1003         -     20645    0.41    1.36    0.00    1.78     5  |__mysqld 11:05:57 AM  1003         -     20649    0.00    0.00    0.00    0.00    25  |__mysqld 11:05:57 AM  1003         -     20708    0.00    0.01    0.00    0.01    13  |__mysqld 11:05:57 AM  1003         -     20709    0.00    0.00    0.00    0.00     7  |__mysqld 11:05:57 AM  1003         -     20710    0.00    0.00    0.00    0.00     6  |__mysqld 11:05:57 AM  1003         -     20711    0.00    0.00    0.00    0.00     0  |__mysqld 11:05:57 AM  1003         -     20712    0.00    0.00    0.00    0.00     3  |__mysqld 11:05:57 AM  1003         -     20713    51.00    0.00    0.00    65.00     1  |__mysqld

 

3) MySQL에서 문제의 Thread 검색

select * from performance_schema.threads where THREAD_OS_ID=20713\G;

mysql> select * from performance_schema.threads where THREAD_OS_ID=1737\G;
*************************** 1. row ***************************
          THREAD_ID: 27
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 2
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 69
  PROCESSLIST_STATE: query end
   PROCESSLIST_INFO: insert into test1 values(~~~)
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 20713
1 row in set (0.00 sec)

ERROR: 
No query specified

 

4) kill