MySQL 실행중인 쿼리 확인(show processlist) 및 강제종료(kill, stop) 방법

MySQL 실행중인 쿼리 확인(show processlist) 및 강제종료(kill, stop) 방법

MySQL에서 아래와 같은 Lock wait timeout exceeded. 오류가 발생하는 경우가 있죠.
그전까지는 아무런 문제가 없이 잘 수행되던 쿼리도 어느날 갑자기 Lock 이 발생하여 이후의 관련된 테이블의 Update/Insert/Delete와 같은 쿼리문의 수행에 장애가 발생하게 됩니다.

우선 아래의 오류 로그를 확인해보시죠.
붉은색과 파란색으로 표시한 부분이 눈에 띕니다.

### Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve co.kr.wecan.mapper.ticket.TicketMapper.createUserTicketUseInfo-Inline
### The error occurred while setting parameters
### SQL:  INSERT INTO TB_USER_TICKET_USE_INFO    (USER_SEQ_NO,    COUPON_ID,    COUPON_TYPE,    TICKET_NO,    TICKET_APPLY_START_DATE,    TICKET_APPLY_END_DATE,    PAYMENT_AMT,    PAYMENT_DATE,    TRADEID,    USE_YN,    STATUS
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:948)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:

 

그럼. 어떠한 쿼리를 수행할때 Lock이 발생하는지에 대해서 알아봐야겠지요.
그걸 알아보기 위해서는 'show processlist' 명령을 통해서 확인할 수 있습니다.

show processlist;

아래의 결과에서 '5036170' ID의 Query 수행시간이 7854초를 경과하고 있는 것으로 보아 Lock이 발생했을 것으로 보입니다.

mysql> show processlist;
+-----------+------------+----------------------+--------+---------+-------+-------+------------------+
| Id        | User       | Host                 | db     | Command | Time  | State | Info             |
+-----------+------------+----------------------+--------+---------+-------+-------+------------------+
|   5036170 | wecan_app  | 152.**.**.**:45960   | wecan  | Query   |  7854 |       | NULL             |
|   5036171 | wecan_app  | 152.**.**.**:45961   | wecan  | Sleep   |     1 |       | NULL             |
| 233115780 | wecan_app  | 2**.***.**.***:59118 | wecan  | Query   |     0 | init  | show processlist |
+-----------+------------+----------------------+--------+---------+-------+-------+------------------+
3 rows in set (0.00 sec)

Lock 발생한 경우에는 Command(Query/Sleep)가 Query 상태이며, Time의 값(단위 : 초)이 계속해서 증가만하고 종료가 되지 않는 경우일 가능성이 높습니다.
보통의 쿼리는 수초 이내에 끝나야겠지만 그렇지 않고, 몇십초~몇분이 걸리거나 몇십분이 걸리는 경우도 있습니다.

'show processlist' 명령을 통해서 성능저하가 발생하는 쿼리가 있다면 반드시 쿼리튜닝을 해주시기 바랍니다.

서비스 초기에는 관련 테이블의 데이터 양이 많지 않았지만,
서비스가 활성화되면서 테이블 데이터의 양이 많아지고, 해당 데이터들을 처리하기 위해서 부하가 많이 걸리다보니 쿼리 수행시간도 늘어나게 되면서 'Lock wait timeout exceeded' 현상이 나타나게 되는 것입니다.

'Lock wait timeout exceeded'을 개선할 수 있는 방법을 몇가지 나열해 봅니다.

• Lock을 발생시킨 쿼리의 관련 테이블에 INDEX 설정
• 쿼리의 수행 속도를 높여주기 위한 쿼리 튜닝
• 월별 파티셔닝을 통한 테이블 분할

위의 방법들은 추후에 개선을 해주는 방법이지만,
당장에 Lock 현상이 있다면 어떻게 해야할까요?

관련 process의 ID를 Kill 명령을 통해서 강제 종료해주는 방법이 있습니다.

mysql> kill 5036170;
Query OK, 0 rows affected (0.00 sec)
mysql>

Usage : kill 'ID번호'

위와 같이 kill 명령을 통해서 급한 불을 끄시고,
이후에는 반드시 쿼리 튜닝과 같은 개선 방법을 꼭 찾아서 진행해주셔야 똑같은 문제가 발생하지 않습니다.

---

MySQL wait timeout 시간 설정하기

아래의 예를 한번 볼까요?
show processlist 명령을 통해서 확인해보니, Time의 시간이 18,977초를 넘기는 것도 있네요?
왜 그럴까요?
기본적으로 MySQL에서의 wait_timeout의 설정값은 28,800초 입니다.
너무 길군요.

wait_timeout의 값을 줄이는 방법을 아래에서 확인해보시고,
여러분의 DB서버의 시간을 조정해보시기 바랍니다.
꼭 필요하다 싶으신 경우에만 하시면 되겠습니다.

mysql> show processlist;
+-----------+------------+----------------------+--------+---------+-------+-------+------------------+
| Id        | User       | Host                 | db     | Command | Time  | State | Info             |
+-----------+------------+----------------------+--------+---------+-------+-------+------------------+
|   5036170 | wecan_app  | 17*.**.**.*4:45960   | wecan  | Sleep   |     2 |       | NULL             |
|   5036171 | wecan_app  | 17*.**.**.*4:45961   | wecan  | Sleep   |     1 |       | NULL             |
|   5036172 | wecan_app  | 17*.**.**.*4:45962   | wecan  | Sleep   |    10 |       | NULL             |
|   5036173 | wecan_app  | 17*.**.**.*4:45963   | wecan  | Sleep   |     9 |       | NULL             |
|   5036174 | wecan_app  | 17*.**.**.*4:45964   | wecan  | Sleep   |     8 |       | NULL             |
|   5036175 | wecan_app  | 17*.**.**.*4:45965   | wecan  | Sleep   |     7 |       | NULL             |
|   5036176 | wecan_app  | 17*.**.**.*4:45966   | wecan  | Sleep   |     6 |       | NULL             |
|   5036177 | wecan_app  | 17*.**.**.*4:45967   | wecan  | Sleep   |     5 |       | NULL             |
|   5036180 | wecan_app  | 17*.**.**.*4:45968   | wecan  | Sleep   |     4 |       | NULL             |
|   5036181 | wecan_app  | 17*.**.**.*4:45969   | wecan  | Sleep   |     3 |       | NULL             |
| 231731585 | wecan_app  | 21*.**.**.*13:37522  | wecan  | Sleep   |   469 |       | NULL             |
| 232529926 | wecan_app  | 21*.**.**.*12:44427  | wecan  | Sleep   |  4176 |       | NULL             |
| 232685518 | wecan_app  | 21*.**.**.*12:50454  | wecan  | Sleep   | 18977 |       | NULL             |
| 232968410 | wecan_app  | 21*.**.**.*12:55616  | wecan  | Sleep   |    77 |       | NULL             |
| 232969009 | wecan_app  | 21*.**.**.*12:55791  | wecan  | Sleep   |  4178 |       | NULL             |
| 233095457 | wecan_app  | 21*.**.**.*12:54717  | wecan  | Sleep   |  2140 |       | NULL             |
| 233110170 | wecan_app  | 17*.**.**.*54:45071  | wecan  | Sleep   |     2 |       | NULL             |
| 233110171 | wecan_app  | 17*.**.**.*54:45072  | wecan  | Sleep   |     1 |       | NULL             |
| 233110173 | wecan_app  | 17*.**.**.*54:45073  | wecan  | Sleep   |    10 |       | NULL             |
| 233110174 | wecan_app  | 17*.**.**.*54:45074  | wecan  | Sleep   |     9 |       | NULL             |
| 233110175 | wecan_app  | 17*.**.**.*54:45075  | wecan  | Sleep   |     8 |       | NULL             |
| 233110176 | wecan_app  | 17*.**.**.*54:45076  | wecan  | Sleep   |     7 |       | NULL             |
| 233110177 | wecan_app  | 17*.**.**.*54:45077  | wecan  | Sleep   |     6 |       | NULL             |
| 233110178 | wecan_app  | 17*.**.**.*54:45078  | wecan  | Sleep   |     5 |       | NULL             |
| 233110180 | wecan_app  | 17*.**.**.*54:45079  | wecan  | Sleep   |     4 |       | NULL             |
| 233110182 | wecan_app  | 17*.**.**.*54:45080  | wecan  | Sleep   |     3 |       | NULL             |
| 233113814 | wecan_app  | 21*.**.**.*13:60797  | wecan  | Sleep   |     0 |       | NULL             |
| 233115317 | wecan_app  | 21*.**.**.*13:33764  | wecan  | Sleep   |   113 |       | NULL             |
| 233115780 | wecan_app  | 21*.**.**.*12:59118  | wecan  | Query   |     0 | init  | show processlist |
+-----------+------------+----------------------+--------+---------+-------+-------+------------------+
29 rows in set (0.00 sec)

mysql> select count(*) from information_schema.processlist where command='Sleep';
+----------+
| count(*) |
+----------+
|       28 |
+----------+
1 row in set (0.00 sec)

mysql>  show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'interactive%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> set global interactive_timeout=30;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> set global wait_timeout=30;
Query OK, 0 rows affected (0.00 sec)

※ 위와 같이 wait_timeout, interactive_timeout의 시간을 조정하게 되면, 조정된 이후부터 적용되게 됩니다. 따라서, 이전의 processlist에 대해서는 이전의 설정값들이 적용되어져 있게됩니다.

 

!주의 : 아래와 같이 'Access denied' 오류가 발생한다면 SUPER 권한이 없기 때문입니다.
해당 권한을 갖고 있는 계정을 통해서 설정을 해주시면 되겠습니다.

mysql> set global wait_timeout=30;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql>

---

아래는 MySQL에서 제공하는 'SHOW PROCESSLIST'문에 대한 설명입니다.
참고하시기 바랍니다.

MySQL : SHOW PROCESSLIST 문

SHOW [FULL] PROCESSLIST

MySQL 프로세스 목록은 서버 내에서 실행 중인 스레드 집합에서 현재 수행 중인 작업을 나타냅니다. SHOW PROCESSLIST문은 프로세스 정보를 하나의 소스입니다. 이 설명을 다른 소스와 비교하려면 프로세스 정보 소스를 참조하십시오 .

메모 MySQL 8.0.22부터, 기본 구현 과 달리 뮤텍스가 필요하지 않고 더 나은 성능 특성을 갖는 SHOW PROCESSLIST성능 스키마 processlist테이블을 기반으로 에 대한 대체 구현을 사용할 수 있습니다 SHOW PROCESSLIST. 자세한 내용은 27.12.21.5절, “프로세스 목록 테이블”을 참조하십시오 .

 

PROCESS 권한 이 있는 경우 다른 사용자의 스레드를 포함하여 모든 스레드를 볼 수 있습니다. 그렇지 않으면( PROCESS권한 없이 ), 익명이 아닌 사용자는 자신의 스레드에 대한 정보에 액세스할 수 있지만 다른 사용자의 스레드에는 액세스할 수 없으며 익명 사용자는 스레드 정보에 액세스할 수 없습니다.

FULL키워드가 없으면 필드 SHOW PROCESSLIST에 있는 각 명령문의 처음 100자만 표시 Info합니다.

이 SHOW PROCESSLIST명령문은 " 너무 많은 연결 " 오류 메시지가 표시되고 무슨 일이 일어나고 있는지 알고 싶을 때 매우 유용합니다 . MySQL은 관리자가 항상 시스템에 연결하고 확인할 수 있도록 하기 위해 CONNECTION_ADMIN권한(또는 더 이상 사용되지 않는 SUPER권한) 이 있는 계정에서 사용할 하나의 추가 연결을 예약 합니다(모든 사용자에게 이 권한을 부여하지 않는다고 가정).

스레드는 KILL명령문 으로 죽일 수 있습니다 . 13.7.8.4절 . “KILL 문”을 참조하십시오 .

SHOW PROCESSLIST 출력 예 :

mysql> SHOW FULL PROCESSLIS
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1030455
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 1004
  State: Has read all relay log; waiting for the slave
         I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 3112
   User: replikator
   Host: artemis:2204
     db: NULL
Command: Binlog Dump
   Time: 2144
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 4. row ***************************
     Id: 3113
   User: replikator
   Host: iconnect2:45781
     db: NULL
Command: Binlog Dump
   Time: 2086
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 5. row ***************************
     Id: 3123
   User: stefan
   Host: localhost
     db: apollon
Command: Query
   Time: 0
  State: NULL
   Info: SHOW FULL PROCESSLIST

 

mysql> show processlist;
+-----------+------------+----------------------+--------+---------+-------+-------+------------------+
| Id        | User       | Host                 | db     | Command | Time  | State | Info             |
+-----------+------------+----------------------+--------+---------+-------+-------+------------------+
|   5036170 | wecan_app  | 172.27.97.14:45960   | wecan  | Sleep   |     2 |       | NULL             |
+-----------+------------+----------------------+--------+---------+-------+-------+------------------+
1 rows in set (0.00 sec)

---

SHOW PROCESSLIST 출력에는 다음 열이 있습니다.

• Id : 연결 식별자입니다. 이것은 표시 동일한 값 ID의 열 INFORMATION_SCHEMA PROCESSLIST테이블의 표시 PROCESSLIST_ID성능의 스키마 항목 threads 테이블과 의해 반환 CONNECTION_ID()실 내의 기능.
• User : 명령문을 발행한 MySQL 사용자입니다. 값은 system user내부적으로 작업을 처리하기 위해 서버에서 생성한 비클라이언트 스레드(예: 복제 호스트에서 사용되는 지연 행 핸들러 스레드 또는 I/O(수신자) 또는 SQL(적용자) 스레드)를 나타냅니다. 의 경우 열에 system user지정된 호스트가 없습니다 Host. unauthenticated user클라이언트 연결과 연결되었지만 클라이언트 사용자의 인증이 아직 발생하지 않은 스레드를 나타냅니다. event_scheduler예약된 이벤트를 모니터링하는 스레드를 나타냅니다( 25.4절 . “이벤트 스케줄러 사용” 참조 ).
• Host : 명령문을 발행하는 클라이언트의 호스트 이름(단 system user, 호스트가 없는 경우 제외 ). TCP/IP 연결을 위한 호스트 이름은 어떤 클라이언트가 무엇을 하고 있는지 쉽게 알 수 있도록 형식으로 보고됩니다 . host_name:client_port
• db : 스레드의 기본 데이터베이스 또는 NULL 아무것도 선택되지 않은 경우.
• Command : 스레드가 클라이언트를 대신하여 실행 중인 명령 유형 또는 Sleep세션이 유휴 상태인 경우입니다. 스레드 명령에 대한 설명은 8.14절. “서버 스레드(프로세스) 정보 조사” 에서 참조하십시오 . 이 열의 값은 클라이언트/서버 프로토콜 및 상태 변수 의 명령에 해당 합니다. 5.1.10절. “서버 상태 변수”를 참조하십시오 . COM_xxxCom_xxx
• Time : 스레드가 현재 상태에 있었던 시간(초)입니다. 복제본 SQL 스레드의 경우 값은 마지막으로 복제된 이벤트의 타임스탬프와 복제본 호스트의 실시간 사이의 시간(초)입니다. 17.2.3절“복제 스레드”를 참조하십시오 .
• State : 스레드가 수행하는 작업을 나타내는 작업, 이벤트 또는 상태입니다. State값에 대한 설명은 8.14절. “서버 스레드(프로세스) 정보 조사” 에서 참조하십시오 . 대부분의 상태는 매우 빠른 작업에 해당합니다. 스레드가 몇 초 동안 주어진 상태에 머무르면 조사해야 할 문제가 있을 수 있습니다.
• Info : 스레드가 실행 중인 명령문 또는 NULL실행 중인 명령문이 없는 경우입니다. 명령문은 서버로 전송된 명령문이거나 명령문이 다른 명령문을 실행하는 경우 가장 안쪽의 명령문일 수 있습니다. 예를 들어 CALL 명령문이 명령문을 실행하는 저장 프로시저를 실행하는 SELECT 경우 Info값은 SELECT 명령문을 표시합니다.

출처 : MySQL