한빛출판네트워크

제공 : 한빛 네트워크
저자 : Baron Schwartz
역자 : 노재현
원문 : How to Optimize Rank Data in MySQL

[이전 기사 보기]
MySQL에서의 랭킹 데이터 최적화 - (2)
MySQL에서의 랭킹 데이터 최적화 - (1)

다른 방법에 비해서 이 방법이 얼마나 더 효율적일까?

이제 까지 설명했던 방법은 O(n) 알고리즘이라고 볼 수 있다.(물론 가장 좋은 케이스는 O(1)일 때이겠지만, 최악의 경우는 n의 크기가 커질 수록 안 좋아지게 된다) 대안으로 O(n2) 알고리즘을 이용하는 방법도 있다. 다음을 보자.

-- MySQL에서는 사용할 수 없는 쿼리 이다. 설령 된다고 하더라도 굉장히 느릴것이다.
update score_ranked as o
   set rank_in_game = (
      select count(distinct score) from score_ranked as i
      where i.score >= o.score and i.game = o.game);

-- 이 쿼리 역시 실행 불가능하다.
-- 최악의 쿼리이므로 절대 사용하지 말자.
update score_ranked as o
   set rank_in_game = (
      select count(distinct score) from (select * from score_ranked) as i
      where i.score >= o.score and i.game = o.game);

update score_ranked as o
   inner join (
      select l.game, l.score, count(distinct r.score) as rank
      from score_ranked as l
         left outer join score_ranked as r on l.game = r.game
            and l.score <= r.score
      group by l.game, l.score
   ) as i on o.game = i.game and o.score = i.score
   set o.rank_in_game = i.rank;

위의 Query들이 여러분이 SQL Query로 할 수 있는 마지막 방법이라면, 문제를 해결 할 수 있는 다른 방법은 없을까? 한 가지 방법은 커서를 PHP와 같은 프로그래밍 언어로 흉내내는 방법이다. 무슨 의미인기하면 우선 전체 테이블의 데이터를 모두 읽어들인후에 각 row들에 대해서 루프를 돌면서 랭킹을 계산하게 된다. 그리고 계산된 결과를 MySQL 서버에 업데이트를 하게 된다. 하지만 이 방법은 정말 최악의 방법이기 때문에 사용하지 않는 것이 좋다.

컨설턴트로서 보건데 대부분의 사람들은 위의 방법에 실패하게 되면, 아마도 랭킹 테이블에서 랭킹 컬럼을 삭제하고 COUNT 명령과 오프셋 값을 가진 limit 명령을 사용하게 될 것이다. 하지만 이 방법 마저도 대부분은 잘 작동하지가 않아서 필자에게 도움을 요청하는 사람들이 많이 있었다.

이렇게 한 번 생각을 해보자: 필자가 제안했던 1번 시나리오에서 읽기 명령은 O(n)의 알고리즘에 해당되고, 쓰기 명령은 O(1)에 해당되게 된다. 여기서 COUNT 명령과 오프셋 값을 가진 LIMIT 명령을 제거하고 나면 읽기 명령은 O(1) 알고리즘에 해당되고, 쓰기명령은 O(n)에 해당되게 된다. 필자는 여기서 보통 쓰기 명령보다는 읽기 명령이 많을 것이라고 생각한다.

랭킹 컬럼은 정말 성능 향상에 도움이 되는 것일까?

항상 직감에만 의존해서 Query의 성능을 판단하는 것은 별로 좋은 방법이 아니다. 실제로 어떻게 Query가 실행이 되는지 쿼리 실행계획이나 Query를 실행해서 실행시간을 확인해 보는 것이 좋다. 또 프로파일링과 벤치마킹을 통해서 최적화된 쿼리가 얼마나 효과가 있었는지를 확인할 수 있다. 쿼리를 만든 후에 필자는 먼저 Query를 다른 컴퓨터(다른 프로세스를 거의 실행시키지 않고 있는) 먼저 프로파일링을 해보게 된다. 이렇게 해서 Query가 얼마나 부하를 가지는지를 확인해 볼 수가 있다. 이런 목적으로 필자가 MySQL의 Query Profiler를 만들게 되었다.

여기서 랭킹 게시판에서 사용하고 있는 세 개의 Query를 프로파일링을 해보았다. 1010번 유저는 전체 랭킹에서도 상위에 있는 유저 이기 때문에 반대로 낮은 랭킹을 가지고 있는 755번 유저도 같은 방법으로 프로파일링을 해보았다. 프로파일링 결과를 한 번 보도록 하자.

Design	Design #1		Design #2		Design #3
Gamer	1010	755	1010	755	1010	755
Time	0.03	0.2	0.05	0.2	0.2	0.01
Queries	4	4	3	3	2	2
Optimizer Cost	21793	21793	5177	5163	2	2
Table locks	5	5	5	5	3	3
Table scans	1	1	2	2	1	0
Index range scans	2	2	1	1	0	1
Rows sorted	320	10000	40	18	20	8
Rows read	30943	59988	20403	20051	27482	39
Fixed reads	320	10000	20	9	20	8
Next-row reads	20002	20002	42	20	8728	9
Bookmark lookup	10010	10010	32	22	8734	14
Full scans initiated	1	1	0	0		0
Next in index	610	19975	20309	20000	10000	8
Temp table inserts	10000	10000	40	18	8727	8

잘 보니 몇몇 Query는 두명의 유저에 대해서 같은 인덱스를 이용하고 있는 것 같지 않다. 하지만 최적화에 들어가기 전에 랭킹 컬럼부터 시작해서 조금씩 속도를 증가시켜 보도록 하겠다. 위의 프로파일링 데이터를 보고 있자니 실행계획과 실행횟수에만 의존해서 Query의 성능을 측정하는게 꼭 맞는것만은 아니라는 것을 알 수 있다. 쿼리의 실행시간 자체는 크게 다르지 않지만 다른 수치들이 Query실행에 따라서 발생되는 부하가 얼마나 다른지를 보여주고 있다.

프로파일링 결과 자체는 전체 랭킹에서 상위 랭크가 포함된 페이지를 볼 때 더 좋은 성능을 보인다는 것을 보여주고 있다. 여러분의 애플리케이션이 이와 같은 패턴을 잘 이용해서 최적화를 한다면 성능에 크게 문제가 없겠지만 항상 이런 경우만 있을 것이라고는 장담할 수가 없다. 예를 들어, 낮은 랭킹을 가진 유저가 자기 자신의 랭킹을 자주 확인하려고 하는 경우에 성능에 크게 문제가 될 수가 있다. 그럼 이제 낮은 랭킹을 가진 페이지를 Query할때의 경우에 대해서 최적화를 해보도록 하자.

위의 결과를 보면 랭킹 컬럼을 이용할 때 프로파일링 결과 자체는 문제가 없어보이기 때문에 이제 벤치마킹을 해보도록 하겠다.

벤치마크 결과

필자가 Query 들의 성능을 더 자세히 알아보기 위해서 벤치마킹을 한 번 해보았다. 벤차마킹은 이전에 보았던 Query 들을 랜덤하게 선택해서 실행했다. 여기서 사용된 벤치마킹 코드는 이 글이 있는 사이트에서 다운로드 받을 수 있다.

벤치마킹에 사용한 데이터는 처음에 필자가 제시했던 100,000개의 데이터를 그대로 사용하였고, 대부분의 부하는 하나의 디스크와 하나의 CPU를 가지고 있는 필자의 컴퓨터의 CPU에 집중되었다.

여기서는 innotop을 이용해서 어떤 Query가 느린 성능을 보이는지를 확인해 보았다. 이전에도 말했지만 확실하게 COUNT 명령과 오프셋을 가진 LIMIT 명령어가 가장 느린 성능을 보이고 있었다.

동시에 많은 유저들이 접속하는 환경을 시뮬레이션 하기 위해서 몇 개의 쓰레드를 더 추가하고 랜덤하게 선택된 점수를 가지고 지속적으로 업데이트를 해보았다. 두 번째, 세 번째 시나리오의 경우에는 단순히 점수만 변경하지 않았고 랭킹 컬럼도 같이 업데이트를 하고 있다. 여기서는 부하가 많이 걸리는 연산이라고 판단이 되더라도 배치 업데이트를 이용하지 않고 매번 업데이트를 실행하도록 하고 있다.

이 테스트에서는 총 5번을 실행해서 평균을 구했다. 각각의 실행은 1부터 20까지 쓰레드의 수를 변경하면서 테스트를 진행하였고 각각의 쓰레드는 총 100페이지에 달하는 랭킹 게시판의 페이지를 요청하게 하였다. 그럼 벤치마킹한 결과를 보도록 하자.

Read Threads	Design #1			Design #2			Design #3
	Reads Only	1 Writer	5 Writers	Reads Only	1 Writer	5 Writers	Reads Only	1 Writer	5 Writers
1	24.14	16.99	10.20	95.66	52.23	40.00	3071.69	1614.22	1690.79
2	13.94	12.85	11.25	49.83	39.51	24.90	1567.32	999.32	898.81
3	9.73	9.18	8.00	34.48	26.45	23.31	1052.53	647.87	639.45
4	8.21	7.52	6.07	24.45	24.37	15.93	783.22	468.53	549.41
5	6.59	5.77	5.05	20.61	16.17	13.14	622.64	410.76	410.57
6	5.77	5.07	4.22	16.14	16.68	10.32	510.60	416.45	375.16
7	4.78	4.33	3.76	14.45	15.06	7.82	431.09	373.96	230.78
8	4.39	3.76	3.33	12.05	12.17	6.41	382.65	344.19	248.74
9	4.00	3.30	2.96	11.04	11.18	5.15	344.93	278.55	188.06
10	3.49	3.02	2.69	9.56	9.51	4.82	307.32	231.17	164.88
11	3.14	2.70	2.46	8.69	9.51	4.00	273.30	227.49	186.22
12	2.90	2.50	2.25	8.39	7.30	3.62	247.84	215.50	134.52
13	2.37	2.33	2.10	7.40	6.58	3.50	226.10	200.11	186.03
14	2.16	2.16	1.96	6.62	5.96	3.08	216.78	182.34	178.42
15	1.91	2.01	1.85	7.35	5.16	2.96	195.52	185.21	156.92
16	1.74	1.88	1.71	6.70	4.75	2.90	180.02	165.87	151.03
17	1.66	1.75	1.61	5.91	4.40	2.53	181.98	130.18	135.61
18	1.51	1.66	1.53	5.08	3.97	2.44	157.87	147.98	117.41
19	1.42	1.57	1.45	4.94	3.72	2.33	149.26	147.36	106.36
20	1.32	1.50	1.38	5.09	3.52	2.18	140.16	134.02	114.53

시나리오 3번의 성능이 얼마나 뛰어난지 보자. 이 성능 측정결과가 바로 나의 최적화에 대한 육감을 입증하고 있다. 최적화에 가장 중요한 핵심은 바로 많은 row를 검색하는 부분을 제거하는 것이다.

그것말고도 랭킹 컬럼에 대한 업데이트가 성능에 끼치는 영향을 파악하는 것도 중요하다. 왜냐하면 위의 결과 자체가 좋지 않았을 경우 랭킹 컬럼을 추가하는 방법 자체가 좋지 않은 방법이었기 때문이다. 위에서 보듯이 업데이트 Query로 인해서 성능의 하락이 조금 있었는데 바로 이 것으로 업데이트 성능을 향상시키는게 얼마나 중요한 것인지를 알 수 있다.

rank_in_game 컬럼에 대한 부하 측정값을 보면 균일하지 않은것을 볼 수 있다. 이전에도 설명했듯이 일부 업데이트는 아주 가볍게 실행할 수 있지만, 다른 일부 업데이트는 DB에 부하가 상당히 많이 가는 명령어 일 수가 있다. 위의 벤치마크 스크립트에 있는 몇몇 Query중에 부하를 발생시키는 쿼리들 때문에 이런 균일하지 않은 결과가 나타나게 된것이다.

시나리오 2번과 3번을 결과를 보면 시나리오 1번의 업데이트와 동일한 문제로 퍼포먼스가 떨어져 보이는 것 처럼 보인다. 하지만 실제 상황에서는 위와 같은 결과가 나오지 않을 것이다. 진짜 문제는 랭킹 정보를 포함한 업데이트가 필자의 벤치마킹에서는 최적화가 되어 있지 않기 때문에 느린 성능을 보이고 상대적으로 랭킹 정보를 포함하지 않은 업데이트에서는 빠르게 업데이트를 할 수 있기 때문이다. 실제 상황에서는 시나리오 1번이 가장 느린 성능을 보일 것이고, 시나리오 2번과 3번이 큰 성능향상을 보일 것이다. 그러나 지금으로서는 벤치마크 프로그램을 수정하고 다시 실행하지 않는한 이 말을 증명하기 싶지가 않다.

필자가 백만개의 row를 가지고 다시 벤치마크 프로그램을 시작했을때는 결과를 모두 다 볼 수가 없엇다. 1번 시나리오의 실행속도가 현저하게 느렸기 때문에 실행이 다 끝나기까지 기다릴 수가 없었고 중간에 얻은 결과로만 봤을때 1번 시나리오는 테이블이 커질 수록 점점 더 안 좋은 결과를 보였다. 동시에 5개의 리더(Reader)를 이용해서 랭킹게시판의 페이지를 요청했을때 1번 시나리오는 초당 0.1페이지를 가져온 반면에 2번 시나리오는 2.5페이지, 3번 시나리오는 420페이지를 가져왔다.

이 부분에 대해서 필자에게 궁금한 점이 있으면 언제든지 연락하기 바란다.

더 나은 방법을 찾아서

위에서 나온 벤치마킹 결과를 보면 랭킹 테이블에 랭킹 컬럼을 넣는 것만으로도 많은 성능향상이 있음을 알 수 있다. 이 결과는 곧 조금 더 연구해 보면 더 좋은 결과를 얻을 수도 있다는 것을 의미하기도 한다. 여기서는 필자가 비록 테스트해보고 벤치마크의 결과를 보여주지는 못하지만 생각해본 아이디어를 한 번 공유해 보고자 한다.

랭킹 테이블에 pos_in_game이라는 위치값을 넣어보면 어떨까? 이 값을 넣으면 랭킹 게시판을 20개 단위로 나누어서 페이지를 요청할 때 좀 더 효율적으로 처리할 수가 있다. 물론 단점도 있다. 그건 테이블의 pos 값을 유지하기가 쉽지 않다는 것이다.

더 좋은 방법은 점수 테이블을 작게 만들고 랭킹 테이블을 따로 만들어서 관리하는 것이다. 이 방법을 사용하게 되면 전체적으로 테이블의 크기를 크게 줄일 수가 있는데, 그건 InnoDB가 primary key에 대한 컬럼 정보를 포함한 추가적인 인덱스를 점수 테이블에 생성하기 때문이다. 추가적인 인덱스로 인한 크기 증가가 보통은 허용할 만한 수준이기는 하지만 이 경우에는 인덱스의 크기가 필자가 원하는 것보다 더 크게 된다. rank_in_game 컬럼의 추가만으로도 데이터와 인덱스의 크기가 78~102메가 바이트까지 증가하게 된다.

랭킹 테이블을 점수 테이블의 primary key를 가지고 있어야 한다. 이렇게 되면 위의 예시 때의 인덱스의 크기보다 더 작은 크기를 유지할 수 있다.

또 count 컬럼을 만들어서 동일한 점수를 가지는 row가 얼마나 있는지를 점수 테이블에 기록할 수도 있다. 이렇게 되면 점수 테이블과 랭킹 테이블에서 COUNT() 명령을 사용하지 않아도 된다. 대신에 SUM()명령을 이용해서 같은 효과를 볼 수 있게 될 것이다. 테이블에 중복된 데이터가 많을 수록 더 많은 효과를 볼 수도 있고 추가적으로 SUM에 대한 결과도 테이블에 추가 컬럼을 만들어서 저장해 두게 되면, 원하는 점수에 대한 랭킹도 알 수 있고 점수 테이블에 얼마나 많은 동일한 row가 존재하는지도 쉽게 알 수가 있게 될 것이다.

쿼리 캐시 기능을 이용해 보는건 어떨까?

스마트 캐싱이 보통 많은 문제점을 해결하는데 도움이 되기는 하지만 MySQL의 쿼리 캐시는 위와 같은 경우에는 도움이 되지 않는다. 하나의 row를 업데이트 하게 되면 캐시되었던 쿼리들이 모두 사용할 수 없게 되고, 이로 인해서 더 많은 성능 저하를 보이기도 한다. 보통은 이런 경우에 쿼리 캐시 기능을 비활성화하는 것이 성능향상에 더 도움이 된다.

결론

이 글에서 MySQL을 이용해서 랭킹 데이터를 페이지 단위로 보여주고, 관리하는데 필요한 최적화 방법에 대해서 다루었다. 이와 같은 문제는 보통 해결하기가 만만치가 않은 문제이다. 그리고 비정규화를 이용할 경우 관리상에 부하가 많이 발생한다는 것도 보였었는데, 만약에 읽고 쓰는 양이 정말 많은 경우라면 비정규화를 사용해 보는 것도 도움이 될 것이다. 필자가 보여준 벤치마크 결과로 볼때 아주 간단한 디자인 만으로도 상당한 성능향상을 이룰 수 있다는 것을 알 수 있다. 데이터가 더 많은 경우에 진짜 제대로 된 성능향상을 볼 수 있을 것이고, 필자가 제안한 다른 방법들을 이용하면 더 뛰어난 성능 향상을 이루어 낼 수도 있다.

이 글에서 다룬 내용이 모든 데이터를 최적화 하는데 적용될 수는 없지만, 이 글로 인해서 여러분의 최적화 과정에 많은 도움이 되었으면 한다.

---

저자 Baron Schwartz는 오픈소스의 유용성을 신뢰하고 즐겨 사용하는 소프트웨어 엔지니어이자 컨설턴트이다.

---

역자 노재현님은 어렸을 때부터 컴퓨터를 접하게 된 덕에 프로그래밍을 오랫동안 정겹게 하고 있는 프로그래머 입니다. 특히나 게임 및 OS 개발에 관심이 많으며, 심심할 때면 뭔가 새로운 프로그램을 만들어내는 것을 좋아합니다. 다음에서 웹 관련 개발을 한 후에 현재는 www.osguru.net이라는 OS관련 웹사이트를 운영하며 넥슨에서 게임 개발을 하고 있습니다.
* e-mail: wonbear@gmail.com
* homepage: http://www.oguru.net

---