게임 서비스 시 서버 측면에서 성능상 문제가 되는 부분은 DB와 관련된 부분일 것이다. 과거에는 거의 모든 게임 데이터 관리 및 최종 동기화 등을 RDBMS에 의존하였기 때문에 쿼리 최적화는 게임 서버 최적화에서 매우 중요한 부분이었다. 최근에는 성능상의 이유로 캐시나 NoSQL 을 이용하는 경우가 많지만, 결제 관련 내용이나 사용자 간 거래 등 atomic 한 처리가 필요한 데이터들을 관리하는 데는 여전히 RDBMS를 사용하는 경우도 많다. 또한 이러한 결제나 거래 관련 데이터들은 통계 처리 등의 이유로 복잡한 쿼리의 대상이 되는 경우가 많기에 아직도 RDBMS 쿼리 튜닝은 게임 서버 성능 최적화에서 적지 않은 비중을 차지한다.
쿼리 최적화시에는 실행 시간을 기반으로 한 프로파일링이 크게 의미가 없다. 쿼리의 실행 시간에 가장 큰 영향을 주는 요소들은 RDBMS가 설치된 머신의 스펙과 쿼리의 대상이 되는 레코드의 수, 그리고 인덱스 접근/ 사용 방식과 중첩 쿼리의 실행 순서 등 쿼리 자체의 실행 과정이다. 이 중 머신의 스펙과 대상 레코드의 수는 최적화의 대상으로 보기는 어렵다. 따라서 최적화의 대상은 쿼리 실행 과정인데, 이는 단순히 실행 시간만을 측정해서는 알 수 없기 때문이다.
따라서 여러 RDBMS(Mysql, Mariadb, Oracle, Postgresql 등)에서는 어떠한 쿼리가 주어지면 해당 쿼리를 어떤 순서로 어떤 데이터를 활용하여 처리하겠다는, 쿼리 계획(query plan) 을 보여주는 기능을 제공한다.
Mariadb 에서는 쿼리 계획을 확인하는 명령어를 두 개 제공한다. EXPLAIN과 ANALYZE가 그것이다. EXPLAIN는 예상되는 실행 계획을 보여주고, ANALYZE는 쿼리를 실제 실행한 후 실행한 쿼리 계획을 보여준다. 언뜻 보기엔 ANALYZE가 더 유용해 보이지만, 쿼리 실행 시 디비에 부하가 가해질 수 있고, 레코드가 늘어나거나 하면 실행 계획 또한 바뀔 수 있으므로 실 서비스에서 실행시와 동일한 정보를 알려주지는 않는다. 두 명령어가 제공하는 쿼리 계획에 대한 정보는 같다.
Mariadb는 (10.0.1 이후부터) SELECT, UPDATE, DELETE 쿼리의 실행 계획을 조회하는 기능을 제공한다. UPDATE와 DELETE의 경우 각각 수정/삭제할 대상 레코드를 어떻게 찾느냐에 대한 계획을 보여주기에 실행 계획은 SELECT의 그것과 큰 차이가 없다.
EXPLAIN을 이용하여 쿼리 계획을 보는 방법은 간단하다. 실행할 쿼리 앞에 EXPLAIN을 붙여 주면 된다.
EXPLAIN을 실행하면 위와 같은 결과가 출력된다. JOIN이나 서브 쿼리가 포함되어 여러 단계의 처리가 필요한 경우, 각 단계별로 실행 계획을 보여준다.
쿼리 계획에는 다음 항목들이 표시된다.
● id : 대상 쿼리문에 JOIN이 포함되어 있을 때, 어떠한 순서로 테이블이 JOIN되는지를 나타내는 값이다.
● select_type : 각 단계를 실행할 때 어떤 종류의 SELECT가 실행되었는지를 나타낸다. 최적화 시에는 크게 중요하지는 않으나, 값이 DEPENDENT SUBQUERY, 혹은 DEPENDENT UNION 인 경우 의존성 등의 문제로 쿼리가 특정 순서로만 실행되어야 함을 뜻하므로 비효율적인 쿼리일 가능성이 있다.
● table : 해당 단계에서 접근하는 테이블의 이름이다. 실제 테이블, 혹은 임시 테이블일 수 있다.
● type : 테이블 내에서 접근이 필요한 레코드를 어떻게 찾았는지에 대한 정보이다.
인덱스 접근 여부 및 방식 등에 대한 내용도 포함하므로 쿼리 최적화 시 반드시 확인해야 할 값이다. 몇 가지 중요한 값에 대해서만 부연 설명하겠다.
○ system : 테이블 내에 레코드가 1개 이하인 경우이다. 이 경우에는 레코드를 더 추가한 후 다시 쿼리 계획을 확인하는 것이 좋다.
○ const, eq_ref : 해당 단계가 PK 나 유니크 인덱스 검색을 이용해 레코드에 접근함을 뜻한다. 일반적으로 가장 빠른 검색 방법이다.
○ ref : 인덱스를 이용하여 동등 비교 연산을 통해 레코드에 접근함을 뜻한다. 위의 두 개만은 못하지만, 역시 매우 빠른 검색 방법이다.
○ fulltext : 전문 인덱스(Fulltext Index) 를 이용하여 레코드에 접근함을 뜻한다. 전문 인덱스는 일반적인 비교 연산으로 접근이 어려운 경우에 주로 사용되므로 최적화하기 어려운 경우가 많다.
○ range: 인덱스를 이용하여 값 비교 연산 ( , BETWEEN 등 )을 이용하여 레코드에 접근함을 뜻한다.
○ index : index 전체를 스캔해야만 필요한 레코드에 접근할 수 있음을 뜻한다. 풀 테이블 스캔보다는 빠르지만, 인덱스가 매우 큰 경우 등에는 비효율적이다.
○ ALL : 인덱스를 이용하여 필요한 레코드를 검색할 수 없어, 전체 테이블을 스캔해야만 함을 뜻한다. 당연히 테이블 내 레코드 수에 따라 실행 시간이 매우 길어지므로 적절한 인덱스 추가나 HINT 문 사용 등을 통해 최적화하는 것이 좋다.
● possible_keys : 레코드에 접근하기 위해 사용할 수 있는 키, 혹은 인덱스 목록을 보여준다. 실제로 사용된다는 의미가 아니므로 실제로 어떠한 키가 사용되었는지는 key 항목을 확인해야 한다.
● key, key_len : 레코드에 접근하기 위해 어떠한 index를 참조하는지, 인덱스 중 몇 바이트를 참조했는지에 대한 정보이다. key_len 은 둘 이상의 컬럼으로 구성된 인덱스를 참조했을 경우에만 의미가 있다.
● ref: 인덱스 검색 시 비교 연산 등에 사용되는 기준값을 보여준다. 최적화 시에는 큰 의미는 없다.
● rows : 필요한 레코드들을 추려내는 과정에서 몇 개의 레코드에 접근해야 하는지를 예측하여 보여준다.
● extra : 이상의 항목 외의 특이 사항들이 있다면 해당 내용을 표시해준다. 예를 들어, 접근해야 하는 컬럼이 모두 인덱스에 포함되어 있어 인덱스 검사만으로 필요한 값을 반환할 수 있다면 Using index 가 표시된다. 때에 따라 성능에 영향을 줄 수 있는 값들이 있으므로, 최적화 시에 이 컬럼이 비어 있지 않다면 확인할 것을 권한다.
MariaDB는 쿼리 계획을 만들 때 인덱스의 크기나 수, 레코드의 수 등을 같이 고려하므로 같은 쿼리라 하더라도 실행 계획 조회 시점에 따라 실행 계획이 달라질 수 있다. 그러므로 유효한 데이터를 얻기 위해서는 될 수 있는 대로 실 서비스에서, 혹은 실 서비스와 최대한 비슷한 환경에서 실행 계획을 조회하는 것이 좋다.
한 가지 유의할 점은, MariaDB는 쿼리 계획을 만들 때 인덱스의 크기나 수, 레코드의 수 등을 같이 고려하므로 같은 쿼리라 하더라도 실행 계획 조회 시점에 따라 실행 계획이 달라질 수 있다. 그러므로 유효한 데이터를 얻기 위해서는 될 수 있는 한 실 서비스에서, 혹은 실 서비스와 최대한 비슷한 환경에서 실행 계획을 조회하는 것이 좋다.
지금까지 RDBMS 의 쿼리를 최적화할 때 쿼리 계획 조회가 왜 필요한지, 그리고 MariaDB 에서 쿼리 계획 조회 시 어떠한 정보들을 알 수 있는지에 대해 알아보았다. 쿼리 계획은 최적화에 필요한 모든 정보를 제공하지는 않지만 기본이 되는 정보를 제공해 주기에, 쿼리 최적화 시 꼭 한번은 쿼리 계획을 확인할 것을 권하고 싶다.
아이펀팩토리 아이펀 디플로이 테크니컬 디렉터 민영기
