대용량 트래픽 속에서도 무거운 DB 조회 연산을 효율적으로 처리하는 방법은 무엇이 있을까요? (1)

INTRO

안녕하세요. Soo 입니다. 성능은 프로그램 개발에 있어서 항상 중요하게 회자되는 이슈 중 하나입니다.  어떤 프로그램이 '성능이 좋다'고 했을 때, 평가할 수 있는 요소는 여러 가지가 있겠습니다만, 웹 애플리케이션의 성능을 이야기 해보자면 가장 중요한 부분 중 하나가 바로 사용자로 부터 요청이 들어왔을 때 이를 얼마만큼 빠르게 처리한 뒤 응답을 보내줄 수 있는가를 의미하는 '응답시간'입니다.

한 조사에 따르면 어떠한 서비스를 이용할 때 사용자들이 기꺼이 감내할 수 있는 응답시간은 최대 3초라고 합니다. 때문에 아무리 좋은 서비스라고 하더라도 이를 처리할 수 있는 성능이 뒷받침 되어야만 이를 통해 더 많은 비즈니스 기회를 창출할 수 있을 것입니다. 

사실 처리하는 요청, 즉 서버로 들어오는 트래픽이 많지 않은 경우에는 이를 처리할 때 자원을 넉넉하게 할당할 수 있기 때문에 성능 관련 이슈가 적게 드러날 수 있습니다. 그러나 대용량 트래픽이 한 번에 들어온다면 이야기가 달라집니다. 서버가 가진 자원은 한계가 있기 때문에 각 요청을 처리 시 최적화가 필수입니다. 그렇지 않으면 병목현상이 쉽게 발생할 수 있고, 이로 인해 처리 속도가 느려짐으로써 전체적인 성능에도 영향을 주게 됩니다.

현재 진행하고 있는 언어교환 플랫폼을 구현하는 프로젝트인 Hello-World는 서비스의 대상을 전 세계의 언어 학습자로 가정하고 진행하고 있습니다. 때문에 항상 실제로 대용량의 트래픽이 들어왔을 때 이를 어떻게 원할하게 처리할 수 있을지를 고민합니다. 이번 글은 개별 사용자의 프로필을 조회 기능을 구현하면서 마주쳤던 문제점과 해결과정을 담고 있는 포스팅입니다.

 

프로필 조회 기능과 문제점 

Hello-World 프로젝트의 DB 구조를 나타낸 ERD를 살펴보면 위와 같습니다. 사용자에 대한 정보를 저장하고 있는 users 테이블의 경우 꽤나 많은 정보를 담고 있기 때문에, 저장공간을 좀 더 효율적으로 사용할 수 있습니다. 여기서 국가와 해당 국가에 존재하는 도시들의 이름은 이미 정해져있으므로, 정해진 이름만을 입력 받을 수 있도록 처음에는 ENUM 클래스를 활용하였습니다. 그러나 ENUM을 활용할 경우 차후에 국가 정보를 추가할 경우 코드 자체를 다시 수정해야 해서 유연성이 조금 떨어지는 부분이 있었고, 저장되는 타입이 VARCHAR인 데다가, 이름이 긴 경우에는 40자 이상까지도 가는 경우가 있었기에 저장 공간의 활용에 있어 조금의 비효율성이 나타났습니다.

그래서 users 테이블에 곧바로 국가/도시 이름을 저장하는 것이 아니라 이름을 담고 있는 테이블을 따로 두고, 여기에 id를 부여하고, 해당 id를 대신 users 테이블에 저장한 뒤 차후에 조인을 활용해 이름을 받아올 수 있도록 설계하였습니다. 각 사용자의 언어 정보를 등록한 speak 테이블의 경우도 마찬가지 입니다. Hello-World 서비스를 이용하는 사용자들은 모국어와 구사 가능언어 각 4개 씩 그리고 학습중인 언어 8개, 즉 최대 16개 까지 언어가 등록가능합니다. 그런데 추가할 언어들 또한 마찬가지로 미리 정해져 있는 데이터이며, 위의 사례처럼 언어 이름을 직접 DB에 저장한다면 불필요한 저장 공간을 더 차지하게 되므로 여기서도 미리 언어관련 표를 만들어 놓고, 언어 이름에 해당하는 id값을 대신 저장하도록 하였습니다.

설계 자체는 나쁘지 않다고 생각했으나, 사용자들의 개별 프로필을 조회하는 기능을 구현하면서 문제가 발생하였습니다. Hello-World는 언어교환 플랫폼인 만큼 서비스를 이용하는 사용자들이 자신이 원하는 언어교환 파트너를 찾을 수 있도록 각 사용자들이 자신을 소개하는 정보를 서비스에 등록하고 이렇게 등록된 서비스를 다른 사용자들의 프로필을 조회할 수 있어야 합니다. 이때, 각 프로필 내부에는 해당 사용자의 국가, 도시, 언어정보를 아래 사진처럼 정확한 이름을 매핑해서 출력해줘야 했습니다.

때문에 처음 설계했던 것처럼 테이블 간의 조인이 필수적이었습니다. 그런데 문제가 되는 부분은 출생 국가와 현재 거주국가의 이름을 얻어오고 난 뒤, 거주 중인 도시가 저장되어 있는 회원의 경우 도시 이름도 얻어와야 했으며 거기에 해당 사용자가 등록한 언어를 얻어와야 했으므로 많은 수의 조인이 필요하다는 점이었습니다. 여기에 추가로 각 언어ID에 대한 이름도 연결해줘야 했기 때문에 다음과 같이 총 다섯 번의 조인을 필요로 했습니다.

조인 자체는 꼭 필요한 연산입니다. 그러나, 조인 연산의 기본적인 특성 때문에 조인 테이블이 많아질수록 그리고 조인해야 하는 row 수가 더 많을수록 시스템에 더 많은 부하가 가게됩니다. 뿐만 아니라 조인의 수가 많아질수록 의도치 않은 결과가 나올 수 있는 가능성 또한 무시할 수 없는 부분이었습니다. (실제로, 프로필에 넣을 추천 글까지 userId를 활용해 조인했을 때, 조인 순서를 어떻게 하던지 결과적으로 이전까지 조인해서 나온 총 row의 수에 해당 사용자를 대상으로 한 추천글의 총 row 수가 곱해짐으로써 어마어마한 수의 row가 최종 결과로 등장하는 현상이 발생하기도 했습니다.) 트래픽이 많아질수록 요청에 대한 쿼리 처리 속도 하나하나가 전체적인 성능에 큰 영향을 미칠 것은 자명했으므로 위의 쿼리에 대한 최적화는 필수적으로 이루어져야 했습니다.

 

EXPLAIN을 활용한 쿼리 실행계획 조회

MySQL은 EXPLAIN 명령어를 통해 각 쿼리가 인덱스를 적절히 활용하고 있는지 혹은 인덱스를 제대로 활용하지 못한 채 테이블 풀 스캔을 실시하는 지에 대한 정보 등과 임시 테이블, 조인 버퍼등을 사용하고 있는지 등에 대한 정보를 제공해줍니다. 때문에 EXPLAIN을 활용해 해당 쿼리의 실행계획을 활용하는 것을 성능 개선의 첫 번째 방향으로 설정하였습니다. 그리고 위의 쿼리를 조회해보니 다음과 같은 결과를 얻을 수 있었습니다.

가장 주의깊게 살펴봐야 하는 부분은 type 칼럼입니다. 다행히 인덱스를 제대로 활용하지 못해 테이블 풀 스캔이 이루어진다는 의미를 담고 있는 ALL을 가진 테이블은 존재하지 않았습니다. 테이블 풀 스캔의 경우는 인덱스가 없거나 제대로 활용하지 못할 경우에 발생하며, 말 그대로 테이블의 모든 row를 스캔하면서 해당 조건에 맞는 레코드를 찾는 형태이기 때문에 당연히 DB에 저장되는 데이터가 많아질수록 성능이 급격히 저하될 수밖에 없습니다. 특히나, 현재 사용 중인 MySQL의 InnoDB는 인덱스의 활용 유무가 검색 성능에 있어 극한의 차이를 만들기 때문에 인덱스를 제대로 활용하는지 반드시 확인할 필요가 있었습니다.

eq_ref의 경우는 여러 테이블이 조인되는 쿼리의 실행계획에서만 나타나는 타입으로, 다음에 읽어야 할 테이블의 Primary Key(이하 PK)나 혹은 유니크 키(Unique Key)를 검색조건에 활용하는 경우에 표시됩니다. 조인에서 두 번째 이후에 읽는 테이블에서부터 등장하는데 해당 테이블에 존재하는 row가 딱 1건이라는 의미이므로 해당 테이블에 대한 인덱스들(여기서는 PK)이 효율적으로 활용되고 있다는 결론을 내릴 수 있었습니다. ref의 경우에도 eq_ref만큼은 아니겠지만 기본적으로 인덱스를 효율적으로 활용하고 있음을 의미합니다.

문제가 될 만한 부분은 타입이 index라고 나와있는 테이블입니다. 이름덕분에 인덱스를 효율적으로 활용하고 있다고 착각하기 쉬우나 실제로 의미하는 바는 인덱스 풀스킨입니다. 테이블의 row를 모두 살펴보는 테이블 풀스캔만큼은 아니지만 그래도 추린 인덱스에 대해서 모든 row를 스캔한다는 의미이므로 조금 더 효율적인 쿼리와 인덱싱을 통해 충분히 개선이 가능할 것이라고 보았습니다.  

다음으로는 Extra를 칼럼을 통해 개선해야 할 특이점들이 존재하는지 살펴보았습니다. 다행히 임시테이블을 사용한다는 문구는 없었습니다. Real MySQL에 따르면 별도의 표기 없이 임시테이블이 사용되는 패턴(From절에 사용되는 Derived 테이블, 적절한 인덱싱이 없는 정렬, Count를 포함하는데 인덱싱을 사용할 수 없는 쿼리, UNION or UNION ALL)도 없었기에 이부분은 넘어가도 고려할 필요가 없다는 결론을 내리게 되었습니다.

임시테이블을 사용여부를 꼭 체크해야 하는 이유는 디스크 I/O가 발생할 수 있다는 문제점이 존재하기 때문입니다. MySQL은 임시테이블을 사용할 때 다루는 데이터의 용량이 정해진 메모리의 한도를 넘어가는 경우 곧바로 디스크에 임시테이블을 생성합니다. 프로그래밍의 어느 분야에서나 디스크 I/O의 발생은 눈에 띄는 성능 저하를 의미하므로 임시테이블을 사용한다면 꼭 개선해줄 필요가 있습니다. (현재 읽고 있는 리눅스 커널이야기라는 책에서는 서버 운영 시 디스크 I/O가 발생하면 장애 수준의 성능 저하가 발생한다고 표현하기도 했습니다.)  

하지만 5번째 줄의 결과를 보니 type은 index로 표시되어 있었으며, 조인 버퍼도 사용하고 있었습니다. 조인 버퍼의 경우는 사실 인덱싱이 잘 되어있다면 과하게 사용하지 않는 이상 크게 신경쓸 필요가 없다고 합니다. 그런데 index와 결합이 되어있기 때문에 문제가 될 수 있을 것이라 생각했습니다. 특히 지금처럼 조인이 여러 번 이루어지는 경우에는 데이터가 많아질 수록 성능에 큰 영향을 미칠 수도 있을 것이라 생각했습니다. 또한 문제가 없더라도, 이 부분을 개선해서 인덱스 풀스캔이 아닌 인덱스 범위 검색을 의미하는 range 까지만 검색 범위를 줄일 수 있더라도 꽤나 큰 성능 개선을 가져올 수 있을 것이라 생각했습니다.  

 

간단히 쿼리 튜닝해보기

위에서 개선 가능할만한 부분을 인지했으니 바로 쿼리 튜닝에 돌입했습니다. 다행인점은 해당 쿼리문은 조인은 많았지만 WHERE 절에 설정되어 있는 조건은 많지 않았기 때문에 조건의 순서설정은 고려할 필요가 없었습니다. 인덱싱 같은 경우도 조인에 사용되는 키가 거의 Primary Key이거나 이미 인덱스로 지정되어 있는 대상이었기 때문에 더 이상 개선이 불가능했습니다. 그래서 간단하게 조인 테이블의 순서와 방법을 바꿔주기로 결정했습니다. 

조인의 처리는 드라이빙(driving) - 드리븐(driven) 테이블의 관계로 이루어집니다. 드라이빙 테이블은 이름처럼 조인 연산을 주도하는 역할로 각 줄마다 드리븐 테이블을 모두 읽으면서 조건에 일치하는 대상이 존재하는지의 여부를 판별합니다. 당연히 드리븐 테이블은 반복적으로 읽히게 되므로, 드리븐 테이블의 전체 대상을 줄일 수 있으면, 드라이빙 테이블에서 반복적으로 읽게되는 데이터의 수도 줄어들게 되므로 쿼리의 성능을 향상시킬 수 있습니다. 그래서 쿼리 실행계획의 최적화 방향을 결정하는 MySQL Optimiser의 경우에도 조인 연산에서 드리븐 테이블 내의 row 수를 최대한으로 줄일 수 있는 방향으로 인덱스를 사용할 수 있게 실행계획을 수립합니다.

드리븐 테이블의 대상의 수를 줄이기 위해서는 드라이빙 테이블의 선택도 중요한데 가능한 한 데이터가 가장 많은 데이터를 선택하는 것이 좋다고 합니다. 데이터를 가장 많이 가지고 있는 테이블을 드라이빙 테이블로  지정하는 경우 일차적으로 인덱스를 통해 검색 범위를 줄여줄 수 있고, 드리븐 테이블들이 갖게되는 데이터도 최대한 적어지게 되므로, 결론적으로는 드리븐 테이블에서 읽히는 데이터의 양이 줄어들어 성능의 향상이 이루어질 수 있다고 생각했습니다. 

위에 언급한 부분에 착안해서 데이터가 가장 많을 법한 테이블을 순서대로 나열해보니 'speak - users - towns - countries' 순이었습니다. 그렇게 다음과 쿼리의 같이 순서를 바꿔 실행계획을 조회해 보았고, 아래 사진처럼 인덱스 풀스캔과 조인 버퍼의 활용이 사라진 더 효율적인 쿼리로 변모했음을 발견할 수 있었습니다.

 

OUTRO

인덱싱이 되어 있지 않은 경우에는 우선적으로 알맞은 인덱싱을 해주는 것이 1순위겠지만, 조인 순서의 설정도 쿼리의 실행계획에 영향을 미칩니다. 물론 적절한 드라이빙 테이블을 선택함에 있어서 단순히 테이블이 가지고 있는 데이터의 개수 이외에도 주의할 부분이 있고, 무엇보다 출력되는 데이터가 원하는 결과인지를 확인할 필요가 있습니다. 그래도 조인 순서의 설정과 같이 간단한 튜닝도 실제적인 쿼리의 처리 성능 향상을 이끌어 낼 수 있다는 것을 배울 수 있는 시간이었습니다.

 

Reference

개발자와 DBA를 위한 Real MySQL

 

Project Link

github.com/f-lab-edu/Hello-World

f-lab-edu/Hello-World