DB Library

현상

높은 비중의 Thread State 상태가 유지되면서, CPU사용량이 높아지면서 성능 이슈가 발생

원인

Front-End 서버에서 MySQL에 만들어둔 SP 사용시 SP의 Body 호출이 필요함

MySQL.PROC에 해당 정보가 있지만 권한이 없어 INFORMATION_SCHEMA 정보를 가져가게 되면서 발생

해결 방안

db 접속 계정에 MySQL DB의 PROC 테이블에 SELECT 권한 부여

※현상

DB를 single_user에서 multi_user로 변경하는 도중 프로세스 Deadlock 발생

원인

DB 커넥션이 존재하는 상황에서, 사용자 세션이 master를 바라보는 도중 DB  커넥션을 모두 off시켜,

사용자 세션이 DB를 바라보지 않아 system 내부에서 DB의 소유자를 찾지 못하는 상태

deadlock이 발생하는 세션을 끊으려 하여도 사용자 세션이 아닌 시스템 세션이라 kill이 불가한 상태 

해결방안

1번으로 작업해야 하지만, 이미 deadlock이 발생한 상태라면 2번으로 해결

1. 작업하려는 DB를 소유하고 single_user로 변경

use [작업DB]
 
alter database [작업DB] set single_user WITH ROLLBACK IMMEDIATE-- immediate 커넥션 한번에 off
go
  
-- 작업 할 쿼리 내역
  
alter database [작업DB] set multi_user
go

2. Deadlock 발생 시, DB 재기동 후 아래 쿼리 실행

SET DEADLOCK_PRIORITY HIGH -- 교착상태 최상위 레벨로 격상
GO
 
ALTER DATABASE [작업DB] SET MULTI_USER WITH ROLLBACK IMMEDIATE -- 작업 DB multi_user로 변경
GO
  
-- https://myadventuresincoding.wordpress.com/2014/03/06/sql-server-alter-database-in-single-user-mode-to-multi-user-mode/

현상

MySQL의 SP를 사용하는 DB에서 지속적으로 Memory가 상승하는 현상 발생

원인

MySQL 내부적으로 SP 내 Subquery 사용 시 Memory Leak 이 발생

참조: The optimizer sometimes generates an index for a derived table (subquery in the FROM clause).

If this occurred for a statement executed within a stored program, a memory leak could occur. (Bug #76349, Bug #20728894)

해결방안

해당 버그가 5.6.27 이후 fixed 된 것으로 확인되어 MySQL upgrade

upgrade 후 memory leak 현상 해소

• 문제 : 주기적으로 MySQL과의 접속이 끊어지는 현상
• 내용 확인 : MySQL error log를 확인해 보면 아래 문구가 기록되어 있음.XXXX [Note] Aborted connection XXXXX to db: 'XXXXX' XXXXX: 'XXXXX' host: '10.10.10.10' (Got timeout reading communication packets)
• 원인 : MySQL에 접속한 이후 timeout 설정값 보다 오랫동안 요청이 없어서 강제로 접속이 끊어짐.
  
  

[timeout 관련 정보 - MySQL 5.7기준]

• connect_timeout : MySQL 서버 접속에 접속실패를 메시지를 보내기까지 대기하는 시간
• delayed_insert_timeout : insert시 delay될 경우 대기하는 시간
• have_statement_timeout : 
• innodb_flush_log_at_timeout : 
• innodb_lock_wait_timeout : innodb에 transaction 처리중 lock이 걸렸을 시 롤백 될때까지 대기하는 시간
• innodb_rollback_on_timeout : innodb의 마지막 구문을 롤백시킬지 결정하는 파라미터 (timeout은 진행중인 transaction을 중단하고 전체 transaction을 롤백하는 과정에서 발생)
• interactive_timeout : 활동중인 커넥션이 닫히기 전까지 서버가 대기하는 시간
• lock_wait_timeout : 
• net_read_timeout : 서버가 클라이언트로부터 데이터를 읽어들이는 것을 중단하기까지 대기하는 시간
• net_write_timeout : 
• rpl_stop_slave_timeout
• slave_net_timeout : 마스터/슬레이브로 서버가 클라이언트로부터 데이터를 일겅들이는 것을 중단하기까지 대기하는 시간
• wait_timeout : 활동하지 않는 커넥션을 끊을때까지 서버가 대기하는 시간

 [해결 방안]

• [어플 로직 수정] MySQL에 접속하는 세션(서버)에 reconnect 로직을 추가
• [DB 설정 수정] wait_timeout, interactive_timeout 수치를 증가
• wait_timeout, interactive_timeout 설정 정보
  • wait timeout PropertyValue

• interactive timeout PropertyValue

• DB 설정 수정 방법 :
  • set  global session wait_timeout = [설정값]
  • set  global session interactive_timeout = [설정값]
  • [DB 재시작 필요] 아래 설정 값 추가 이후 MySQL 재시작my.cnf 파일에 wait_timeout = [설정값]interactive_timeout = [설정값]
  • [DB 재시작 필요 없음] DB 접속 후 아래 명령어 실행my.cnf 파일에 수정 내용을 추가하지 않으면 DB 재시작 시 초기화됨.

※ 참고 자료 : https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html