롯뽕기... 그리고 쿼리 튜닝

영상버전 : https://youtu.be/S7CDcs0bLJM

고객사에서 환영회를 하자고 해서 관련 사람들 7명이 모인 작은 노미까이에 초대 받아서 롯뽕기에 왔습니다. 

롯뽕기는 수도고속도로 아래의 자투리 공간에 바이크 주차장을 운영중이네요.. 

국가에서 관리하는 곳이라 저렴하니 바이크로 롯뽕기에 오시는 분들은 참고 바랍니다. 

30분에 100엔이고 12시간 이내라면 최대 1000엔으로 고정이므로 주차비 걱정을 안해도 될 듯 합니다. 

노미까이에서 저를 극찬을 아끼지 않아주셔서 몸둘바를 몰랐는데.. 

오히려 제가 이 환경에선 담당자분들이 정말 좋은 환경의 튜닝을 경험할 수 있어서 행운이라고 말씀을 드렸지요. 

SQL Server라는 RDBMS의 대표격인 제품의 특장점에서, 무료 MySQL엔진의 Aurora에 IOPS가 떨어지는 클라우드 환경에서의 튜닝기법, 그리고 Key Value 베이스인 TiDB환경에서의 튜닝방법까지 제게서 배워간다면, 

어떠한 교육기관에서도 배울 수 없는 경험을 할 수 있으니 제가 가진 경험을 받아가실 수 있는 최고의 환경이라고 했지요.. 

참고로 여기 DB운영 사람이 부족해서 추가로 더 모집한다고 합니다. N1자격이나 동등의 일본어 능력을 가지신 분들 중에 이 프로젝트에서 저와 같이 하고 싶으신 분들은 연락 주세요~ ^^

회사의 밸류가 구치코미, 즉 유저 평가의 분석을 무기로 한 기업이다 보니 AI에 대한 활용 방법론 등도 관심을 많이 가지고 있어 미래가 기대되는 곳이었습니다… 이런 건전한 이야기를 하면서 두 시간 코스로 식사를 하고 헤어졌는데요.. 2차를 가자고 했는데, 2차는 회사 내부 사람들과 가라고 하고 전 빠졌지요.. 

그런데 여기 사람들과는 좀 더 오랫동안 좋은 관계를 가지고 싶네요.. 

담당자 분들도 순수하고 밝고 내부에서도 서로 배려하는 모습이 아주 좋았습니다. 

일단, 정치질 하려는 사람이 저랑 엮인 분들 사이에선 안보여서 좋았네요.. ^^;;

이렇게 즐거운 노미까이를 마치고.. 

이번 주를 한 번 돌아보겠습니다. 

 Sql server의 비용때문에 tidb와 aurora로 서비스 단위로 하나 둘씩 이동 중인데요..

 갑자기 쿼리 퍼포먼스가 떨어졌는데 도저히 해결할 수가 없다고 개발팀이 손을 든 쿼리들을 리스팅 해서 줬습니다.

 Dbre팀이 받아서 각자 해결하려는데 해결이 안되서 저한테까지 떨어진 내용입니다

 첫 번째 쿼리는 쉽게 찾아서 간단히 설명드리자면 sql server와 oracle은 필드타입이 다른 필드를 조인해도 그다지 성능이 안떨어지지만..

Aurora는 무한정 떨어지네요..

 게다가 날짜로 범위를 줄여서 보면서 날짜필드는 인덱스에 없어요..

 그래서 인덱스 추가 및 필드 속성이 다르니 맞춰서 하라고 하고 

 속성을 맞춘 테이블과 비교해 주었습니다.

 느리다던 쿼리는 5분 정도에 나왔는데,

 인덱스를 맞추자 57초, 

 필드타입을 맞추자 2초에 나와서 종료…

 다음 쿼리가 문제였는데요..

 일단 쿼리를 가볍게 보면..

 뭔가 쿠폰이 있는데 돈을 내고 사는 것 같네요.

 그 구매한 쿠폰 중 특정기간 내에 사용가능한 쿠폰 리스트를 가져오려 하는 것 같습니다.

 얼핏 보면 문제는 없어 보였습니다. 그래서 실행을 했더니…

5분이 지나도 끝나지 않은 쿼리..

 그래서 이게 sql서버 땐 얼마나 걸렸고, 지금은 얼마나 걸렸냐고 물어보니

Sql서버에선 54분 걸렸고, 지금은 끝나질 않는다고 하네요..

 그래서 원래 시간이 걸렸구나 하면서 일단 분해를 해 봤습니다.

 보기 쉽게 erd로 그려봤습니다.

 아무리 봐도 지극히 평범하네요..

 그래도 뭐가 문제인지 아직 찾을 순 없으니 

 일단 전부 카운트를 해봐야겠네요..

 제가 erd뿐만 아니라 대부분의 문서를 정해진 규칙대로 안쓰는걸 절 아시는 분들은 잘 아실 겁니다.

 이번에도 마찬가지인데요… 문서는 보고용으로 이쁘게만 쓰는것이 아니고 활용도가 높아야지요..

 그래서 저의 erd에는 온갖 유용한 정보들이 들어가 있죠..

 건수를 카운트 해보니

390만명이 9456종류의 쿠폰을 구입한게 58만건.. 충전한 포인트는 2000만건이네요..

 각 쿠폰의 이력이 7천만건 이네요.

 대충 일반적인 데이터 분포라 문제 될게 없는데요..

 그럼 쿼리의 조건들을 넣어서 더 줄여봤습니다.

 음… coupon member rel을 제외한 모든 테이블은 바로 나왔으나

 쿠폰멤버렐만 넣으면 먹통이네요..

Coupon member rel이 7천만 건이나 되기 때문에 aurora에서 설정한 instance에서 무리가 있을 수 있겠다 싶어서 다른 쿼리에서 필요로 한 건수가 얼마나 되나 역산하는 쿼리를 만들어 봤습니다. 

그렇게 해서 봤더니 178만건 밖에 안되었네요..

 

이 정도면 쉽게 결과가 나와야 하는데 하면서 의아해 하면서 보고 있었습니다. 

그럼 저 데이터만 따로 테이블을 만들어서 연결해보면 금방 나오는거 아닐까?

하고 신규 테이블을 만들어서 필요한 인덱스 중에 없는 게 있어 추가하고 기다려 보기로 했습니다. 

그런데도 약 70초 정도 시간이 걸리더니

1억2822만 건으로 늘었네요.. 

178만건이 왜 1억이 넘어버리지?

그럼 중복이 있다는건데?

하면서 열심히 봤는데.. 쿼리만으론 뭔가 이상했습니다. 

그래서 개발자가 준 노트를 봤더니 

원래 쓰던 쿼리에는 유저 id로 한 번 더 필터가 되는 것이었지요.. 

즉, 유저까지 합치지 않으면 중복이 생겨서 1억이 넘어가는 것이었습니다. 

그래서 유저 아이디는 왜 뺐냐고 물어보니

유저 ID는 다른 DB서버에 있어서 

Aurora로 바뀌는 바람에 dblink가 적용이 안되어 일단 빼서 추출 후에

다시 user id로 필터를 걸 생각이었다고 했습니다. 

그래서 이야기를 했지요. 

우선 여기서 꼭 필요한 유저 아이디를 추출하고, 

그걸로 유저db에 있는 유저 id를 다시 추출, 

그리고 옮겨온 aurora db에서 전용 테이블을 만든뒤에 거기에 넣고

Join을 하라고 지시를 했습니다. 

그렇게 하니까 0.125초 만에 처리가 되네요.. 

323건으로 나오구요.. 

Dblink는 sql server와 oracle에선 당연하기 때문에 썼지만, 

만약 안되는 환경이 되면 큰데서 추출해서 필터를 하지말고, 

최소한의 데이터를 뽑아서 큰 데이터가 있는 db에 넣고 join을 하는게 좋습니다. 

생 데이터를 where in 절에 넣는 방법도 있지만, 

이건 where in 절에 들어간 user id가 바뀌면 새로운 쿼리로 인식하고 buffer cache에 쌓여가기 때문에 서버의 메모리 관리상 좋지 않으므로 이렇게 전용 테이블을 만들어서 join하는게 좋지요. 

게다가 입력 날짜 등을 넣게 되면 매번 추출하지 않고도 과거 시점 데이터로 추출할 수도 있게 됩니다. 

Dblink가 없을 때의 튜닝 방법에 대해서도 이렇게 소개를 해봤는데요.. 

연말을 대비해서 Key value가 메인인 Tidb로 넘어가면 또 전혀 다른 튜닝을 하게 됩니다. 

Key Value가 메인인 NoSQL은 데이터 저장 방식이 텍스트 파일에 JSON으로 들어가게 되므로 

전체를 읽어내는 경우가 많구요, 

JOIN을 하려고 해도 전체 join 전체 가 되는 케이스가 많아서 

Join의 개수가 많아질 수록 힘들어집니다. 

이 때에는 join을 어떻게 줄이느냐, join을 줄이기 위한 저장 방식의 변경 등도 고려를 해야 합니다. 

단순이 mysql문법을 쓸 수 있다고 mysql테이블 그대로 넣었다간 사고가 날겁니다. 

지금까지 mysql에서 한 시간 걸려도 그게 당연한 줄 알고 해왔던 환경이었지만, 

제가 참여해서 기본 1초 미만 리턴에 통계나 부하가 큰 것들은 다양한 방법으로 1분 미만으로 처리를 하고 있는데요.. 

이건 RDBMS에선 당연하지만 NoSQL환경이 되면 읽어야 하는 데이터가 테라 단위로 올라가는 것을 전제로 이 쪽으로 이동하는 경우가 많지요. 

때문에 시간이 들어가는 것은 어쩔 수 없지만, 

이게 합리적인 시간으로 줄여줄 수 있는 튜닝 방법을 제안을 해주어야 겠지요. 

그렇지 않으면 여기 개발팀들은 당연하다고 몇시간을 기다릴 것 같네요. 

이걸 보시는 분들도 실제로 접하진 않으셨겠지만 

조금이라도 간접 경험이 되시길 바랍니다. 

일본에서 안건 소개나 멤버 케어도 해드리고 있으니 

언제든 일본으로 넘어오실 분이나 이미 넘어오셔서 케어를 받고 싶으신 분들은 연락을 주세요!

매니저에 따라서 얼마나 파견이 편한 직업인지가 바뀌는 체험을 시켜 드리겠습니다

giip :: Control all Robots and Devices! Free inter-RPA orchestration tool! https://giipasp.azurewebsites.net/