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;
