MySQL 과도한 CPU 사용 Thread 확인 ( Pidstat )

0.사전준비 sysstat 설치

yum -y install sysstat

1.대량의 Insert 준비

- 많은량의 CPU를 사용하게 하기 위해 Insert Procedure를 실행시킨다.

##Table 생성
MariaDB [test]> use test;
MariaDB [test]> drop table if exists tes0t_bak;
MariaDB [test]> create table test_bak (a int primary key,name varchar(10),test varchar(100),Address varchar(500));

## Procedure 생성
MariaDB [test]> drop procedure if exists PInsert;
DELIMITER $$
CREATE PROCEDURE PInsert()
begin
declare i int;
set i=1;
while(i < 1000000) do
insert into test_bak values(i,'LEE','TEST','117, Gaeumjeong-ro, Seongsan-gu, Changwon-si, Gyeongsangnam-do, Republic of Korea');
set i=i+1;
end while;
end$$
# delimiter ; $$

##Procedure 실행
MariaDB [test]> call PInsert();

2.Thread 모니터링

mysql 의 PID를 이용하여 pidstat 를 실행시킨다. 

현재 1737 TID ( Thread ID ) 가 가장 많은 량의 CPU를 사용하고 있는것을 볼 수 있다.

[root:LEE:/root > ps -ef | grep mysql
mysql     1708  1686  1 13:20 pts/0    00:00:03 mysqld --defaults-file=/etc/my5.7.cnf --user=mysql

[root:LEE:/root > pidstat -t -p 1708 2
Linux 3.10.0-1062.18.1.el7.x86_64 (primary) 	08/03/21 	_x86_64_	(4 CPU)

13:24:39      UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
13:24:41     1000      1708         -   23.00   40.50    0.00   63.50     2  mysqld
13:24:41     1000         -      1708    0.00    0.00    0.00    0.00     2  |__mysqld
13:24:41     1000         -      1709    0.00    0.00    0.00    0.00     1  |__mysqld
13:24:41     1000         -      1710    0.00    0.00    0.00    0.00     2  |__mysqld
13:24:41     1000         -      1711    0.00    0.50    0.00    0.50     3  |__mysqld
13:24:41     1000         -      1712    0.00    0.00    0.00    0.00     1  |__mysqld
13:24:41     1000         -      1713    0.00    0.00    0.00    0.00     3  |__mysqld
13:24:41     1000         -      1714    0.00    0.00    0.00    0.00     1  |__mysqld
13:24:41     1000         -      1715    0.00    0.00    0.00    0.00     1  |__mysqld
13:24:41     1000         -      1716    0.00    0.00    0.00    0.00     3  |__mysqld
13:24:41     1000         -      1717    0.00    0.00    0.00    0.00     2  |__mysqld
13:24:41     1000         -      1718    0.00    0.50    0.00    0.50     3  |__mysqld
13:24:41     1000         -      1719    0.00    0.00    0.00    0.00     2  |__mysqld
13:24:41     1000         -      1720    0.00    0.00    0.00    0.00     0  |__mysqld
13:24:41     1000         -      1722    0.00    0.00    0.00    0.00     0  |__mysqld
13:24:41     1000         -      1723    0.00    0.00    0.00    0.00     2  |__mysqld
13:24:41     1000         -      1724    0.00    0.00    0.00    0.00     0  |__mysqld
13:24:41     1000         -      1725    0.00    0.00    0.00    0.00     1  |__mysqld
13:24:41     1000         -      1726    0.00    0.00    0.00    0.00     2  |__mysqld
13:24:41     1000         -      1727    0.00    0.00    0.00    0.00     2  |__mysqld
13:24:41     1000         -      1728    0.00    0.00    0.00    0.00     3  |__mysqld
13:24:41     1000         -      1729    0.00    0.00    0.00    0.00     2  |__mysqld
13:24:41     1000         -      1730    0.00    0.00    0.00    0.00     0  |__mysqld
13:24:41     1000         -      1731    0.00    0.00    0.00    0.00     3  |__mysqld
13:24:41     1000         -      1732    0.00    0.00    0.00    0.00     1  |__mysqld
13:24:41     1000         -      1733    0.00    0.00    0.00    0.00     3  |__mysqld
13:24:41     1000         -      1734    0.00    0.00    0.00    0.00     0  |__mysqld
13:24:41     1000         -      1735    0.00    0.00    0.00    0.00     1  |__mysqld
13:24:41     1000         -      1737   23.00   40.00    0.00   63.00     1  |__mysqld

3.문제 Thread 확인

Thread_OS_ID 가 1737인 Session이 실행중인 Query를 조회한다. 해당 Query를 Tunning하거나 Kill 하여 CPU를 과도하게 사용하는 것을 방지한다.

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 test_bak values(i,'LEE','TEST','117, Gaeumjeong-ro, Seongsan-gu, Changwon-si, Gyeongsangnam-do, Republic of Korea')
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 1737
1 row in set (0.00 sec)

ERROR: 
No query specified

4.해당 Thread Kill

mysql> kill 27;