[Mysql/MariaDB] DB 프로세스 목록 확인(show processlist)

1. 테이블 정보

1. INFORMATION_SCHEMA DB

• 데이터를 저장하기 위한 데이터들의 집합체
• DB 서버 내에 존재하는 DB의 메타 정보(테이블, 칼럼, 인덱스 등의 스키마 정보)를 모아둔 DB
• 각 DB에 대한 정보들을 저장해 놓은 DB
• INFORMATION_SCHEMA 데이터베이스 내의 모든 테이블은 읽기 전용이며, 단순히 조회만 가능합니다.

 

2. INFORMATION_SCHEMA.PROCESSLIST Table

• PROCESSLIST 테이블을 조회하여 서버 내에서 실행 중인 Thread 집합에서 현재 수행 중인 작업을 볼 수 있습니다.

DESC INFORMATION_SCHEMA.PROCESSLIST;

+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| ID              | bigint(4)     | NO   |     | 0       |       |
| USER            | varchar(128)  | NO   |     |         |       |
| HOST            | varchar(64)   | NO   |     |         |       |
| DB              | varchar(64)   | YES  |     | NULL    |       |
| COMMAND         | varchar(16)   | NO   |     |         |       |
| TIME            | int(7)        | NO   |     | 0       |       |
| STATE           | varchar(64)   | YES  |     | NULL    |       |
| INFO            | longtext      | YES  |     | NULL    |       |
| TIME_MS         | decimal(22,3) | NO   |     | 0.000   |       |
| STAGE           | tinyint(2)    | NO   |     | 0       |       |
| MAX_STAGE       | tinyint(2)    | NO   |     | 0       |       |
| PROGRESS        | decimal(7,3)  | NO   |     | 0.000   |       |
| MEMORY_USED     | bigint(7)     | NO   |     | 0       |       |
| MAX_MEMORY_USED | bigint(7)     | NO   |     | 0       |       |
| EXAMINED_ROWS   | int(7)        | NO   |     | 0       |       |
| QUERY_ID        | bigint(4)     | NO   |     | 0       |       |
| INFO_BINARY     | blob          | YES  |     | NULL    |       |
| TID             | bigint(4)     | NO   |     | 0       |       |
+-----------------+---------------+------+-----+---------+-------+

[주요 컬럼 정보]

ID : 프로세스 ID,  Connection Identifier (KILL 가능) ->  SELECT CONNECTION_ID()
USER : Thread에 접속하고 있는 MySQL 사용자
HOST : 유저가 접속하고 있는 호스트명 , IP 어드레스
DB : 사용중인 DB
COMMAND : Thread 실행 중인 명령 타입 (Sleep, Query, Quit, Kill 등) → https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html
TIME : Thread가 현재 상태를 유지한 시간(초)
STATE : Thread의 상태 (Starting, Rolling back, System lock 등) → https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
INFO : 실행중인 SQL 쿼리
TIME_MS : Thread가 현재 상태를 유지한 시간(밀리초)
MEMORY_USED : Thread가 사용 중인 메모리(바이트)
MAX_MEMORY_USED : Thread가 사용한 최대 메모리(바이트)

 

2. show process

• 실행 중인 스레드를 보여줍니다.
• information_schema.PROCESSLIST 테이블 또는 mysqladmin processlist 명령으로도 확인할 수 있습니다. 

 

MySQL Command-Line Client 접속하여 확인

mysql> SHOW PROCESSLIST;
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+
| Id    | User | Host                 | db                 | Command | Time | State    | Info             | Progress |
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+
| 53636 | root | ?.?.?.?:?            | test_db            | Sleep   |   12 |          | NULL             |    0.000 |
| 53637 | root | localhost            | NULL               | Query   |    0 | starting | SHOW PROCESSLIST |    0.000 |
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+

mysql> SHOW FULL PROCESSLIST;
+-------+------+----------------------+--------------------+---------+------+----------+-----------------------+----------+
| Id    | User | Host                 | db                 | Command | Time | State    | Info                  | Progress |
+-------+------+----------------------+--------------------+---------+------+----------+-----------------------+----------+
| 53637 | root | localhost            | NULL               | Query   |    0 | starting | SHOW FULL PROCESSLIST |    0.000 |
| 53647 | root | ?.?.?.?:?            | test_db            | Sleep   |   29 |          | NULL                  |    0.000 |
+-------+------+----------------------+--------------------+---------+------+----------+-----------------------+----------+

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
+-------+------+-----------+------+---------+------+----------------------+-----------------------------------------------------------------------+------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-----------------------------------------------------------------------+------+
| ID    | USER | HOST      | DB   | COMMAND | TIME | STATE                | INFO                                                                  | TIME_MS    | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY                                                           | TID  |
+-------+------+-----------+------+---------+------+----------------------+-----------------------------------------------------------------------+------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-----------------------------------------------------------------------+------+
| 53637 | root | localhost | NULL | Query   |    0 | Filling schema table | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where HOST = 'localhost' |      0.342 |     0 |         0 |    0.000 |      113168 |          113168 |             0 |  2023653 | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where HOST = 'localhost' | 2186 |
| 53630 | root | localhost | NULL | Sleep   |  271 |                      | NULL                                                                  | 271907.620 |     0 |         0 |    0.000 |       81144 |           81144 |             0 |  2022247 | NULL                                                                  | 4036 |
+-------+------+-----------+------+---------+------+----------------------+-----------------------------------------------------------------------+------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-----------------------------------------------------------------------+------+

 

MySQL Command-Line Client 접속 없이 확인

# mysql -u root -p'계정 비밀번호' -e "show processlist;"
...

# mysql -u root -p'계정 비밀번호' -e "show full processlist;"
...

# mysql -u root -p'계정 비밀번호' -e "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;"
...

 

3. 모니터링

MYSQL 관리 전용 유틸리티인 mysqladmin를 이용하여 쉽게 프로세스를 모니터링할 수 있습니다.

// 프로세스 확인
# mysqladmin -u root -p'계정 비밀번호' processlist

// 상태 확인
# mysqladmin -u root -p'계정 비밀번호' status 

// -i 옵션을 사용하여 특정 초마다 반복 실행
# mysqladmin -u root -p'계정 비밀번호' processlist -i초

// -i 옵션을 사용하여 특정 초마다 반복 실행 + status를 이용하여 상태도 출력
# mysqladmin -u root -p'계정 비밀번호' processlist status -i초

// 3초에 1번씩 100번 실행 (–sleep=3 –count=100)
# mysqladmin processlist -u root -p'계정 비밀번호' --sleep=3 --count=100

 

# mysqladmin -u root -p'계정 비밀번호' processlist
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+
| Id    | User | Host                 | db                 | Command | Time | State    | Info             | Progress |
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+
| 53815 | root | ?.?.?.?:?            | test_db            | Sleep   | 1    |          |                  | 0.000    |
| 53816 | root | localhost            |                    | Query   | 0    | starting | show processlist | 0.000    |
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+

# mysqladmin -u root -p'계정 비밀번호' status
Uptime: 303242  Threads: 50  Questions: 2029442  Slow queries: 1  Opens: 201  Open tables: 189  Queries per second avg: 6.692

# mysqladmin -u root -p'계정 비밀번호' processlist | grep localhost
| 53630 | root | localhost            |                    | Sleep   | 1318 |          |                  | 0.000    |
| 53836 | root | localhost            |                    | Query   | 0    | starting | show processlist | 0.000    |

# mysqladmin -u root -p'계정 비밀번호' processlist status
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+
| Id    | User | Host                 | db                 | Command | Time | State    | Info             | Progress |
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+
| 53815 | root | ?.?.?.?:?            | test_db            | Sleep   | 1    |          |                  | 0.000    |
| 53816 | root | localhost            |                    | Query   | 0    | starting | show processlist | 0.000    |
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+
Uptime: 303242  Threads: 50  Questions: 2029442  Slow queries: 1  Opens: 201  Open tables: 189  Queries per second avg: 6.692

 

[Status]

status 명령 결과

Ex)

mysqladmin -u root -p'계정 비밀번호' status

Uptime: 303242  Threads: 50  Questions: 2029442  Slow queries: 1  Opens: 201  Open tables: 189  Queries per second avg: 6.692

 

Uptime : MySQL 서버가 실행된 시간(초)입니다.
Threads : 활성 스레드(클라이언트)의 수입니다.
Questions : 서버가 시작된 이후 클라이언트의 질문(쿼리) 수입니다.
Slow queries : long_query_time초 이상 소요된 쿼리 수입니다. 
Opens : 서버가 연 테이블 수입니다.
Open tables : 현재 열려 있는 테이블의 수입니다.
Queries per second avg : 평균 초당 쿼리 수

 

※ Threads = Threads_cached + Threads_connected

 

# mysqladmin -u root -p'계정 비밀번호' status

Uptime: 303242  Threads: 50  Questions: 2029442  Slow queries: 1  Opens: 201.....

 

# mysql -u root -p'계정 비밀번호' -e "show status like'threads%';"

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 4     |
| Threads_connected | 46    |
| Threads_created   | 372   |
| Threads_running   | 1     |
+-------------------+-------+

 

4. Thread Kill

• 서비스를 운영하다 보면 Thread를 Kill 하는 경우가 생각보다 있습니다.
• 서비스를 운영하다보면 LOCK 현상을 자주 보게 됨, 이럴 경우 서비스에 과부하 성능 저하를 주는 경우
• 크기가 큰 DB를 백업하면서 DB에 너무 많은 부하를 주게 되어 DB 성능에 문제를 주게 되는 경우 
• 쓸데없이 sleep 상태로 존재하는 Thread가 많은 경우

[형식]

KILL ID

 

mysql> SHOW PROCESSLIST;
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+
| Id    | User | Host                 | db                 | Command | Time | State    | Info             | Progress |
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+
| 53636 | root | ?.?.?.?:?            | test_db            | Sleep   |   12 |          | NULL             |    0.000 |
| 53637 | root | localhost            | NULL               | Query   |    0 | starting | SHOW PROCESSLIST |    0.000 |
+-------+------+----------------------+--------------------+---------+------+----------+------------------+----------+

mysql> KILL 53636;

 

응용) Thread가 현재 상태를 유지한 시간 + Memory 사용량 내림차순 Thread 출력

SELECT ID, USER, HOST, COMMAND, TIME_MS, MEMORY_USED, INFO 
FROM INFORMATION_SCHEMA.PROCESSLIST 
ORDER BY TIME_MS DESC, MEMORY_USED DESC;

 

응용) SLEEP 상태인 Threads KILL

# sleep 상태인 Thread 찾아서 /tmp/kill.txt 파일로 생성
SELECT concat('KILL ',id,';')
FROM information_schema.processlist
WHERE command='sleep'
INTO OUTFILE '/tmp/kill.txt';

# kill 실행
source /tmp/kill.txt;

# 파일 삭제
rm -f /tmp/kill.txt;