MySQL 서버의 CPU를 알뜰하게 다 사용해서 사용률이 100% 를 친다면?
MySQL은 쓰레드로 동작한다. CPU 사용률이 가장 높은 쓰레드를 찾아서 performance_schema.threads 에서 THREAD_OS_ID로 조회할 수 있다.
pidstat 를 사용해서 CPU 사용률이 높은 쓰레드를 찾아본다.
pidstat -t -p <mysqld_pid> 1 # 1 은 1초 간격으로 실행
-- mysqld 의 pid 확인
# ps -ef |grep mysqld
mysql 955 1 0 22:21 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root 1622 1573 0 22:22 pts/1 00:00:00 grep --color=auto mysqld
# pidstat -t -p 955 1
-bash: pidstat: command not found
-- pidstat가 없을 경우 sysstat를 설치한다.
# yum -y install sysstat
-- CPU 사용률이 가장 높은 TID 확인
# pidstat -t -p 955 1
Linux 4.18.0-305.19.1.el8_4.x86_64 (localhost.localdomain) 10/05/2022 _x86_64_ (1 CPU)
10:24:46 PM UID TGID TID %usr %system %guest %wait %CPU CPU Command
10:24:47 PM 27 955 - 1.98 0.99 0.00 0.00 2.97 0 mysqld
10:24:47 PM 27 - 955 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 959 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 963 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 965 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 966 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 967 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 968 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 969 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 970 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 971 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 972 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 973 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 974 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 994 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 995 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 996 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 997 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 998 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 999 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 1000 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 1001 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 1015 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 1016 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 1017 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 1018 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 1053 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 1057 0.00 0.00 0.00 0.00 0.00 0 |__mysqld
10:24:47 PM 27 - 1564 2.97 0.99 0.00 0.99 3.96 0 |__mysqld
CPU 사용률이 가장 높은 TID 1565 번을 확인한다.
-- THREAD_OS_ID = 1564 확인
mysql> select * from performance_schema.threads where THREAD_OS_ID=1564\G;
*************************** 1. row ***************************
THREAD_ID: 28
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 3
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 101
PROCESSLIST_STATE: query end
PROCESSLIST_INFO: select * from tb_test where col1 = 'aaaa'
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 1564
1 row in set (0.00 sec)
ERROR:
No query specified
-- PROCESSLIST_ID = 3 kill
mysql> kill 3;
Query OK, 0 rows affected (0.01 sec)'MySQL' 카테고리의 다른 글
| xtrabackup 옵션 (1) | 2023.11.30 |
|---|---|
| lock session 찾기 (0) | 2022.10.01 |
| auto_increment 값 모니터링 (0) | 2022.09.28 |
| Query Cache (0) | 2022.09.24 |
| Index Hint (0) | 2022.09.22 |