데이터쟁이 입장으로 “슬로우 쿼리”를 다시 고민해보았습니다.

Posted on | by gywndi

Overview

서비스를 하면 당연히 실행이 오래 걸리는 쿼리, 슬로우 쿼리는 발생합니다. 원인은 정말 비효율적인 쿼리인 것도 있겠지만,  때로는 Lock, Disk fault 등등 원인은 다양합니다. DB 내/외부 요소에 의해서, 슬로우 쿼리가 발생하게 되는데.. 이것을 늘 모니터링하고 적시에 바로 최적화 적용을 하는 것이야말로, 안정적인 서비스 최상 품질 보장의 첫 걸음이라고 생각합니다.

물론, 이 관련해서는 여러가지 방법론이 있겠지만, (예를들면 fluentd를 활용한 수집 방안) 슬로우 쿼리를 데이터로써 제가 생각하는 원칙으로 접근해보았습니다.

스크립트는 하단 github를 참고하시지요. 아마도 잘 될꺼예요. (50% 상상코딩이라..흐흐)
https://github.com/gywndi/kkb/tree/master/mysql_slow_log_gather

Requirements

우선 여기서 말하고자 하는 것은 슬로우 로그 파일 로테이션에 대한 내용이 아닙니다. 관련 내용은 하단 URL 을 참고하세요. ^^
>> https://www.percona.com/blog/2013/04/18/rotating-mysql-slow-logs-safely/

서비스 튜닝에 필수 요소인 슬로우 쿼리 제거.. 이 쿼리 로그 역시 “데이터” 중 한가지이고, 이 데이터의 특성을 고려해보고자, 다음 세 가지 질문을 나에게 던져보았습니다.

  • 실시간으로 데이터를 모아보아야 할 것일까?
  • 로그 수집이 정말 안정적일까?
  • 진짜 슬로우 데이터만 수집하고 있는가?

특히 두번째는 가장 중요한 질문입니다. 서비스 최적화를 위해 수행하는 프로세스가 서버의 안정성에 “일말의 부담”이 될 수 있다면, 하느니 못한 것과 같겠죠. 물론, 그동안 전혀 문제가 없었을 수도 있겠지만.. 주어진 외부 사례가 최악의 상황에서 적절한 대응책을 마련해주고 있는지.. 진지하게 고민을 해보았죠.

그리고 풀스캔 쿼리를 추출하기 위해 “log_queries_not_using_indexes” 옵션을 사용하는 경우, 의도된 파라메터로 조정된 쿼리 경우에는 “진짜 슬로우 쿼리”가 아니면 수집하고 싶지 않았습니다. 초당 수천건 이상 유입되는 상황에서 불필요한 내용은 걸러내는 것이 안정성에도 도움이 될테니요. ^^

Gather Slow Log

우선, 슬로우 쿼리 수집을 특별한 데몬을 띄워서 수행하지 않았습니다. 정상적으로 잘 수집하고 있는지 추가 관리 포인트가 들어가야할 것이고, 서버 대수가 급증할 수록 이 또한 굉장히 부담이기 때문이죠.

그래서 제 결론은 “한번 돌고 끝나는 스크립트”입니다. 주기마다 수집 스크립트가 “마지막 수집 시점 이후”부터 로그를 수집자는 것이죠.  제가 생각하는 슬로우 쿼리의 목적은 “사후 대응 및 튜닝”을 위한 수집이지, “실시간 모니터링”을 통한 즉각 대응을 위함이 아닙니다.

물론 간단하게라면, 이는 라인단위로 수행을 할 수 있겠지만.. 문제는 백만라인짜리 슬로우 쿼리 파일 경우에는 이 또한 부담이겠죠. 그래서, 마지막 처리 바이트 기준으로 이후부터 데이터를 읽고, 로그 수집을 합니다.

그런데 여기서 문제 몇가지가 더 발생합니다. 각각에 나름의 결론을 지어봅니다.

1. 지나치게 슬로우가 많이 쌓이면 어떻하지?

>> 결론 : 버리자. 대신 통보는 하자.

서버가 미쳐서(?) 10분간 1G이상의 데이터가 쌓인 경우를 상상해보죠. 이것을 어찌어찌 중앙 서버로 취합을 해야겠죠. 그런데.. 1G 데이터를 파싱하고, 처리하고, 전송하고.. 서버에 분명 영향을 미칠 것입니다. 특히나, 저정도 슬로우가 발생한 상황이라면, 거의 서비스 마비상태일 것인데.. 슬로우 수집이 서버에 부담을 가중시키는 것이 아닐지??

2. 파일이 바뀌면 어떻하지?

>> 결론 : 첨부터 읽어버리자. 대신 너무 크면 버리고 통보하자.

파일이 바뀌는 경우라면.. 로그 로테이트가 이루어지면서 새로운 파일로 치환되는 경우(아이노드가 변경되는 케이스)를 생각해볼 수 있겠습니다. 이 경우에는 파일명은 같지만, 전혀 다른 데이터파일로 봐야합니다. 이런 케이스? 첨부터 읽자입니다.

파일을 조작하는 경우도 있겠죠. 물론, 스크립트 구조 상 세세하게 체크는 불가하지만, 적어도 마지막 포지션이 현재 파일 사이즈보다 작은지 체크정도는 가능합니다. 예를들어 “cat /dev/null > mysql-slow.log”를 수행해서, 깡통으로 만드는 경우입니다.

3. 슬로우 시간을 넘지않으면 어떻하지?

>> 결론 : 버리자.

슬로우 쿼리 수집의 목적은 다름아닌 이슈 있는 쿼리 수집입니다. 제 경우에는 이 시간이 0.5초이고, 이 시간 이상의 쿼리는 최대한 OLTP환경에서는 돌지 말아야 합니다. (사실 이 시간도 길다고 생각해요. ㅋㅋ)

그런데 때로는 관리자 의도에 의해, 풀스캔 쿼리를 찾아내기 위해 “log_queries_not_using_indexes” 옵션을 켜는 경우가 있는데.. 이 경우 풀스캔 쿼리가 무조건 슬로우로 기록되기 때문에.. 설혹 쿼리 시간이 굉장히 짧아도 다수 로그로 쌓이고 말지요.

(그럴일은 없겠지만..) 이 옵션을 켰을 때 10건짜리 테이블 풀스캔이 수백건 단위로 초당 유입이 된다면 어쩔까요? 이런 쿼리를 중앙에서 수집하는 것이 의미가 있을까요? 결정은 각자의 몫이겠지만, 제 입장에서는 “필요없다”입니다.

Parse Slow Log

위에서 언급한 기준으로 수집을 한 슬로우쿼리.. 이번에는 파싱을  해봅시다. 하단은 MySQL 5.7 기준, 슬로우 로그는 아래 포멧입니다.

# Time: 2017-06-27T07:34:39.086551Z
# User@Host: xxxxxx[xxxxxx] @  [127.0.0.1]  Id: 78316
# Schema:   Last_errno: 0  Killed: 0
# Query_time: 1.001540  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 85  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1498548879;
select 
  1,
  2,
  3,
  4, 5, 
  sleep(1);

그런데.. 난제 발생..

처음에는 단순하게 접근(tail)을 했었지만.. 쿼리의 시작과 종료를 정의하는 포인트가 생각보다 난해합니다. 이 경우 “슬로우 쿼리 정보의 패턴을 매칭해서 관련 정보를 추출”하고, “슬로우 쿼리 시작 패턴이 다시 들어오기 전”까지를 사용자 쿼리로 인지시키는 과정이 필요합니다.

my $pattern1 = "^# Time: (.*)T(.*)\\.(.*)\$";
my $pattern2 = "^# User\@Host: (.*)\\[.*\\] @  \\[(.*)\\]  Id: (.*)\$";
..
my $pattern8 = "^SET timestamp=(.*);\$";
my $pattern9 = "^USE (.*);\$";

이 패턴을 벗어난 데이터는 쿼리로 인지하고, 위 패턴의 라인이 들어오기 전까지는 “사용자의 쿼리로 취합하자”입니다. 뭐 마지막 쿼리 수집을 위해 스크립트 종료 직전에 한번더 flush 날려주는 것은 서비스죠.

Send to..

파싱도 했으니, 중앙 서버로 보내야 합니다. 여기서 0.5초 이상되는 대상들만 선별해서 보내도록 하여, 불필요한 리소스 사용을 금지시켰습니다.

sub flush_log(){
  if($query_time gt 0.5){
    .. 어디론가 슬로우 쿼리를 쏘세 ..
  }
}

Conclusion

지금까지 안정적으로 사용했던 다른 방안도 있고 내부적으로 활용하고 계신 방법론도 있겠죠. 그렇지만, 슬로우 쿼리 수집을 수행하기에 앞서서, “슬로우 쿼리” 데이터에 대한 재정의가 필요하다고 생각했습니다.

당연하겠지만, 서버에 모든 데이터를 실시간으로 취합(쓰든 안쓰든)해서 관리하면 좋습니다. 그러나, 서버의 리소스를 고려하고, 이 수집이 오히려 전체 서비스에 영향을 줄 수 있는 상황을 고려해봐야 합니다. 더구나, 서비스를 안정적으로 만들기 위해 수집하는 데이터라면 더욱 그렇겠죠. 게다가 활용되지 않은 데이터는 죽은 데이터로.. 공간만 잡아먹는 몬스터일 뿐이닐테니요.

이 자리를 빌어 말하고 싶었던 것은.. “데이터에 대한 정의”가 명확하게 하고 있는지입니다. 정말로, 내가 쓰려고 수집하는 데이터인지.. 그냥 통상적으로 수집했던 데이터인지..

가벼운 주제.. 슬로우 쿼리 수집 방법론을 빌어 가벼운 썰을 풀어보앗네요. ㅎㅎㅎ