[ MySQL ] Federated 엔진 사용 시 Sleep 프로세스 쌓임 이슈

문제 상황

하나의 프로젝트가 생각보다 잘 돼서 로그량이 많아지게 됐다.
결국 서버를 분리하게 되었지만 두 서버의 로그를 활용하는 서비스는 한 곳이다.

현재 사용하는 BI 시스템에서 여러 종류의 커넥션을 활용할 수 있어서 신규 프로젝트 관련 지표 조회 시에는 신규 서버의 데이터를 조회하도록 했다.

 

이런 상황에 특이한 이슈가 발생했다.

SHOW PROCESSLIST 실행 시 결과

 

신규 서버 DB의 데이터를 조회할 때마다 기존 서버 DB에 위 사진과 같이 Sleep 상태로 남는 프로세스들이 쌓이는 현상이었다.

데이터 조회는 신규 DB에서 하는데 Sleep 프로세스는 기존 DB에 남고 있는게 이상했다.

 

현재 BI 시스템에서는 화면을 하나 조회할 때마다 기존 DB와 신규 DB에 커넥션을 생성하고 필요에 따라 SP를 호출해 데이터를 조회하여 화면에 사용한 뒤 커넥션을 닫고 있다.

처음 의심했던 건 커넥션이 제대로 닫히지 않고 있는 건가 했는데 그건 아니었다.

 

원인 규명

생각해보니 각 SP에서 데이터를 조회해주기 전에 입력받은 계정에 따라 권한 체크하는 기능이 있었다.

권한 관련 데이터는 기존 서버에서 관리하고 있어서 신규 서버 DB에서는 권한 체크시 해당 테이블을 Federated 테이블로 만들어서 조회하고 있었다.

신규 DB에서 데이터를 조회하는데 기존 DB가 영향받을만한 부분이 여기밖에 없다 싶어서 권한체크 기능을 주석처리 후 화면을 조회 했더니 기존 서버  DB에 Sleep 프로세스가 발생하지 않았다.

 

하나의 커넥션에서 Federated 테이블을 조회하면 원본 테이블을 가지고 있는 DB에서는 데이터를 전달해주고 프로세스를 Sleep 상태로 변경한다.

동일한 커넥션 및 세션에서 다시 Federated 테이블을 조회하면 동일한 프로세스가 작업하고 Sleep이 되는데 지금은 화면을 조회할 때마다 커넥션을 만들고 닫으니 새로운 Sleep 프로세스들이 쌓이고 있었다.

 

해당 문제가 성능에 영향을 주지는 않지만 커넥션 풀 사이즈에 도달한다면 잠재적인 위험이 있다.

 

해결 방안 1)

원인을 알기 전까지 프로세스 ID를 수동으로 매번 Kill 해주고 있었다.

이걸 주기적으로 자동 실행되도록 하는 것을 첫번째 해결 방안으로 생각했다.

 

이 방법이라면 이번 원인 뿐 아니라 다른 이유에서라도 불필요하게 생기는 Sleep 프로세스를 정리할 수 있다고 생각했다.

어떤 프로세스는 Kill해도 되고 어떤 프로세스는 안되는지를 미리 알고있어야 한다.

CREATE DEFINER=`{YourId}`@`%` PROCEDURE `KillSleepProcesses`()
BEGIN

declare v_finished integer default 0;
declare v_processId varchar(100);

# 삭제해도 되는 프로세스들의 ID를 조회해서 커서 생성
# 나의 경우 삭제해도 된다의 기준은
# 1. 정해진 userid를 통해 만들어진 프로세스
# 2. sleep 상태인 프로세스
# 3. sleep 상태인지 5분(300초) 이상 지난 프로세스
declare cur_Items cursor for
select id 
from information_schema.processlist 
where user='{YourId}'
and COMMAND='sleep'
and TIME > 300
order by 1
;

declare continue handler for not found set v_finished = 1;

set @stmt = '';

# 다이나믹 쿼리로 kill 명령어를 만들어서 실행
OPEN cur_Items;
get_Items: LOOP
    FETCH cur_Items INTO v_processId;
    IF v_finished = 1 THEN LEAVE get_Items; END IF;
    
    set @stmt = concat('kill ',v_processId);
   	PREPARE dquery FROM @stmt; 
	EXECUTE dquery;
	DEALLOCATE PREPARE dquery;
    
END LOOP get_Items;
CLOSE cur_Items;

END

 

위와 같이 다이나믹 쿼리를 생성해 실행하는 SP를 작성해두고 1시간이나 30분에 한번씩 실행시키는 방법이다.

하지만 위의 방법이 제대로 수행되는가는 모른다. 두번째 방안을 사용하기로 했기 때문.

 

위의 스크립트로 SP를 만드는 데에는 오류가 나지 않는다.

하지만 최근 다이나믹 쿼리를 사용하다가 위와 같은 방식에서 @stmt 변수가 완전히 업데이트 되지 않는 상황을 겪어서 제대로 실행시키려면 테스트 및 개선이 필요할 것 같다.

 

해결 방안 2)

Federated 테이블을 조회해서 생기는 이슈라면 그 테이블을 조회하지 않으면 된다.

그래서 두번째 해결 방안은 Federated 테이블의 내용을 신규 DB의 테이블에 업데이트하고 업데이트된 테이블을 조회하는 방법이다.

 

업데이트가 제대로 되지 않으면 지표 조회가 되지 않아 권한 변경이 있을 때마다 신경써야 한다는 불편함은 있다.

권한 변경이 자주 있는 일은 아니라서 매일 한번 업데이트시키고 권한 변경 시 기억하고 업데이트 수행하기로 했다.