http://www.mysqlkorea.co.kr
한글매뉴얼 5.0 , 한글매뉴얼 5.1 , MySQL 5.1 HA , 사용자매뉴얼
공지사항  
뉴스  
질문과 답변
DBA
Developer
Cluster
토크박스  
이벤트  
서포트 티켓  
최신글
mysql 관련해서 …
이제 날씨가 가을…
killed 상태가 너…
mysql 백업/복구 …
apm 설치 헤메고…
 
질문과 답변 (Cluster) > 커뮤니티 존 > 질문과 답변 (Cluster)
killed 상태가 너무 오래 걸리는데요....왜그런지.....도움 부탁드립니다.
글쓴이 : ohmy   날짜 : 18-08-03 15:01   조회수 : 500
galera cluster 구성을 한 상태 입니다.
update문이 너무 오래걸려서 kill했는데...2일 가까이 이 상황 입니다.ㅠㅠㅠㅠ

PROCESSLIST 는 아래와 같습니다.

id , command,time,state,info,memory_used,max_memory_used,examined_rows
"148" "Killed" "84023" "Checking permissions" "drop table innodb_lock_monitor" "78008" "4309944" "0"
"143" "Killed" "84401" "Checking permissions" "Create table innodb_lock_monitor" "77744" "4309680" "0"
"123" "Killed" "149080" "Checking permissions" "CREATE INDEX xxxx USING BTREE ON xxxx" "78008" "4309944" "0"
"117" "Killed" "163324" "Commit" "UPDATE xxx" "79760" "4309680" "20294527"
"107" "Killed" "167683" "Init" "COMMIT" "275000" "2412288" "0"

ERROR LOG 입니다.

2018-07-31 21:12:33 55 [Warning] WSREP: transaction size limit (2147483647) exceeded: 2149580800
2018-07-31 21:12:33 55 [ERROR] WSREP: rbr write fail, data_len: 0, 2
2018-07-31 23:35:17 104 [Warning] Aborted connection 104 to db: 'unconnected' user: 'root' host: '192.168.0.22' (Got an error writing communication packets)
2018-08-01 1:40:42 100 [Warning] WSREP: transaction size limit (2147483647) exceeded: 2149580800
2018-08-01 1:40:42 100 [ERROR] WSREP: rbr write fail, data_len: 0, 2


show engine innodb status\G  결과 입니다.
TRANSACTIONS
Trx id counter 34661355 Purge done for trx's n:o < 34661354 undo n:o < 0 state: running History list length 26 LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421960302430616, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421960302429800, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421960302426536, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421960302428168, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421960302428984, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421960302424904, not started 0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 34661350, ACTIVE 79426 sec, thread declared inside InnoDB 2231 mysql tables in use 7, locked 7 229456 lock struct(s), heap size 28319952, 14604 row lock(s), undo log entries 7302
MySQL thread id 117, OS thread handle 140484957980416, query id 13508338 192.168.0.22 root Commit UPDATE tablename SET xxx = NULL WHERE xxxx= 0 AND yyyy = 'P' AND kkk = ' ' LIMIT 7302
---TRANSACTION 34661346, ACTIVE 84032 sec 87713 lock struct(s), heap size 10707152, 200012 row lock(s), undo log entries 100000 MySQL thread id 107, OS thread handle 140485293561600, query id 13499600 192.168.0.22 root Init

이전글 이제 날씨가 가을로 가고 있네요 
다음글 The table '' is full 문제 
MySQL Korea 사이트의 컨텐츠 소유권은 (주)상상이비즈에 있으므로 무단전재를 금합니다.
ⓒ 2010-2011 ssebiz All Rights Reserved.