RDS Slow Query Log 세팅 및 쿼리 개선

최근에 팀원이 기간에 따른 급여 이체 대상자 수 통계가 달력에 나타나는 기능을 개발하였는데,
3개월 이상의 단위로 조회할때 속도가 매우 느리다는 이야기를 들었다.

통계성 데이터기 때문에 현재 데이터를 쿼리하기보다는 리포트용으로 별도로 데이터를 적재하는 배치를 구현하는게 좋지 않나?

라는 의견을 개발 초기 PR 단계에서 냈었는데, 우선 개발 당시에는 과거 데이터를 보는 용도가 아니라, 다가올 급여이체에 대한 정보를 보는 용도의 목적이 크다고 하여 개발되었었는데, 처음 속도가 느리다는 문의가 들어올때에는 내가 개발한 부분이 아니라서 음 데이터 양이 많아서 그런가? 라고 넘겼으나.. 30초 이상이 걸린다는 이야기를 듣고 부랴부랴 slow query log 를 찾아보았다.

그러나 왠걸, slow query log 설정 (default false) 자체가 되어 있지 않아 설정부터 시작하였다.

RDS 의 시스템 변수는 파라미터 그룹 으로 관리된다.
RDS 의 파라미터 그룹에 들어가면 시스템 변수의 현재 상태들을 볼 수 있다.

값은 현재 적용된 값, 허용된 값은 입력할 수 있는 값의 목록을 보여준다.
그리고 넘어가서 적용 유형은 static 과 dynamic 으로 나뉘는데 dynamic 인 변수는 동적변수로서 런타임에서 변경의 적용이 가능하다.
static 의 경우는 DB를 재 기동하여야 하기 때문에 운영하는 서비스는 거의 변경하기 힘들다고 보면된다.

slow query log 관련 시스템 변수

• slow_query_log : 슬로우 쿼리를 로깅할지 여부(0,1) 를 나타낸다
• long_query_time: 슬로우 쿼리로 지정할 최소 시간을 초단위로 설정한다 . 나는 3초로 설정하겠다.
• log_queries_not_using_indexes: 인덱스를 타지않는 모든 쿼리를 로깅할지 여부를 설정한다. (0, 1) long_query_time 과는 상관없이 실행계획에서 인덱스를 타지않으면 모두 기록된다.
• log_output: TABLE, FILE, NONE 으로 지정할 수 있다. 로그를 내보낼 위치를 지정한다. TABLE 의 경우 I/O 가 많은 서비스의 데이터베이스의 경우 데이터베이스에 부하가 걸릴 수 있으니 지양한다. 하지만 우리는 트래픽이 많지 않으므로 TABLE 로 지정했다.]

파일로 저장할 경우 AWS RDS 의 경우 AWS 콘솔에서, 설치형 mysql 의 경우 /var/log/mysql/mysql_slow.log 에서 확인하면 된다. 물론 output 위치도 지정할 수 있지만 처음 생성시에 설정해야한다.

이제 발생했던 slow query 를 조회해보자.

select query_time, lock_time, sql_text from mysql.slow_log;

39초가 걸린 쿼리의 정체가 나왔다.

혹시나 몰라 하는 블러처리

실행계획을 열어보자..

무려 payrolldep4_ 조인버퍼에서 인덱스도 안태우고 쿼리하고 있었다.
위 쿼리에서 payrolldep4_ 를보면 id 2개가 조인 조건절에 있다.

이런 경우는 복합인덱스를 걸어주어야한다.
만약 개별 인덱스 2개를 건다면, mysql 은 조인에서 테이블당 1개의 인덱스만 태우기 때문에, 하나의 최적 인덱스를 탄 후, 모든 row 를 돌며 조건절을 검사할 것이다. (물론 하나의 인덱스로 걸러진 후에 확실하게 로우수가 줄어드는 쿼리라면 굳이 복합으로 걸 필요는 없다.)

create index tablename_payroll_id_payday_user_id_index
	on tablename (payroll_id, payday_user_id);

인덱스를 걸어준 후, 실행계획을 다시 실행시켜보자

10927 건을 풀스캔 하던 부분이 단일 로우만 남기도록 바뀌엇다.

수행시간도 37초에서 무려 41ms 로 변경되었다.

결론.

결국 통계성 데이터의 별도 뷰용 테이블 적재도 문제가 아니었고 너무나 말도안되는 실수로 인한 문제였다.
꼭 쿼리를 짤때는 혹시나 인덱스를 안걸거나 안태우지 않았는지 실행계획을 확인해보자..
ORM 을 쓴다면 더더욱 본인이 짠 코드에서 나가는 쿼리를 확인하자