http://kldp.org/node/70379
모든 문제의 시작은 윗글에서 시작되었습니다. 쉽게말해 말려들어 버리고 만거죠. ㅋㅋ
KLDP에 알게 모르게 신세도 많이 졌고해서 최근업무에서의 경험을 바탕으로 조금이나마
도움이 되고자 했다가 이렇게 되었습니다. 사실 최근업무에서도 조금 신세를 졌다고 볼
수 있습니다.
이 문서는 정확한 정보 보다는 현재 상황과 여러테스트를 통해서 얻은 경험에 기반하므
로 다른 경우에는 적용되지 않을 수 있습니다.
처음엔 김정균님께서 알려주신 아래 EXPLAIN 결과를 보고 '쿼리가 문제가 있겠군..' 이
라고 생각했습니다.
+----+-------------+-------+--------+-------------------------------------+---------+---------+--------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------+---------+---------+--------------+-------+---------------------------------+
| 1 | SIMPLE | l | ALL | PRIMARY | NULL | NULL | NULL | 70132 | Using temporary; Using filesort |
| 1 | SIMPLE | n | eq_ref | PRIMARY,status,uid,node_status_type | PRIMARY | 4 | junseo.l.nid | 1 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | junseo.n.uid | 1 | Using where |
| 1 | SIMPLE | c | ref | lid | lid | 4 | junseo.n.nid | 6 | Using where; Distinct |
+----+-------------+-------+--------+-------------------------------------+---------+---------+--------------+-------+---------------------------------+
쿼리는 아래와 같습니다. 나중에 알게 된 거지만 아래쿼리는 '나의 최근 글' 목록을 가
져오는 쿼리입니다.
SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count
FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = u.uid
LEFT JOIN comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL)
WHERE n.status = 1 AND (n.uid = 7289 OR c.uid = 7289) ORDER BY last_post DESC LIMIT 0, 25;
위 EXPLAIN결과를 보고 처음에 Using temporary; Using filesort가 눈에 들어왔습니다.
임시 테이블에 파일소트라니, 속도가 우선시되는 디비에서 이런건 절대 용납이 안될 상
황일 것입니다.
그래서 막무가내로 쿼리를 건드렸습니다. 쿼리를 건드리기 전에 권순선님께서 제가 각
페이지마다 사용되는 쿼리의 목록과 처리시간을 볼 수 있도록 권한을 주셨습니다. 그럭
저럭 속도가 향상 되더군요. 하지만 제가 drupal시스템에 대해 이해가 부족한 상황이어
서 실수를 했습니다. drupal 사이트에 들어가서 퍼포먼스나 개선에 관한 글을 읽던 중
drupal이 그렇게 단순하게 디자인된 시스템이 아니란걸 알게되었습니다. 그리고, 저 쿼
리도 괜히 저런 모양이 된건아니란 것두요. 하지만 우선 직접 원복은 하지 않고 디비서
버의 튜닝에 들어갔습니다. 원복은 나중에 권순선님께서 해주셨습니다.ㅋㅋ
나중에 더 알아보고 싶은 내용이지만 drupal에는 hook 이라는게 존재합니다. 저의 수준
에서 말하면 Listener와 같은 것인데 drupal 코어에서 특정이벤트, 예를들어 게시물 혹
은 코멘트 추가, 삭제, 업데이트등의 상황이 발생하면 모듈개발자가 이 hook을 통해 이
벤트 정보를 받아 추가적인 처리를 할 수 있도록 만들어져 있습니다. api만 보면 이런
게 가능 한것 같습니다. 하지만 제 짧은 영어로 인한 오류일 가능성도 있습니다. ^^
1. 메모리 임시 테이블 사용 극대화
이제 my.cnf를 통한 본격적인 서버튜닝에 들어갔습니다. 튜닝작업은 제가 가장 많이 쓰
고 제일 느려보이는 '최근 글'을 위주로 이루어 졌습니다. 우선 서버에 들어가서 mysql
임시 디렉토리로 들어가 '최근글'링크를 누르고 ls를 해봤습니다. 바로 임시 테이블 파
일이 만들어졌습니다. 그것도 수십메가 바이트의 크기였습니다. IO에서 디스크 작업,
특히 쓰기가 대량으로 이루어지면 병목이 그 어떤 상황보다도 크다는 판단하에 이 작업
이 최대한 메모리 임시테이블을 이용해 이루어 지도록 아래 값들을 수정했습니다. 이때
의 임시 테이블은 명시적인 CREATE TEMPORARY TABLE인 아닌 조인 상황에서 발생하는 임
시 테이블이므로 이와 같은 설정 변경을 통한 작업이 가능했습니다.
max_heap_table_size=64M
tmp_table_size=64M
잠시후에 '나의 최근 글'에서 쓰이는 위의 쿼리는 64M 보다 큰 임시 파일을 만든다는걸
알게 되고 값을 128M로 수정해 주었습니다. 현재는 위의 두 값이 세션당 할당되는 메모
리가 아니라는걸 알고 1024M까지 올렸다가 다시 768M로 설정해 놓은 상태입니다. 다시
줄인 이유는 대부분의 테이블 타입을 InnoDB로 변환한 상태인데 현재 버퍼의 사용률이
100퍼센트에 달해 256M를 더 쓰려고 빼 주었습니다.
위의 쿼리에서 쓰인 row를 카운트 해보면
table | node
------------------------+------------------
node | 70748
node_comment_statistics | 70748
users | 25262
comments | 334462
의 row를 가지고 있습니다.
사실 이 node의 갯수와 node_comment_statistics가 같은게 제가 '모든 최근 글' 쿼리에
손을 댄 주요 이유입니다. 검색, 정렬 조건이 두 테이블에 찢어져 있는데 조인을 하는
관계로 아마 전체 row들의 조인이 발생하는 듯 합니다. 이미 조건 잘 타고 정렬해 놓은
상태에서 가져올 갯수 지정해 놓고 조인하면 임시 테이블이 만들어져도 훨씬 가볍게 돌
텐데 제가 아는 한도 내에서는 테이블의 스키마가 그렇게 되어있지 않았습니다.
이 작은 작업자체가 전체 체감속도 향상에서 이미 90퍼센트 이상을 차지했다고 보고 있
습니다. 디스크 작업을 메모리 작업으로 바꾸는 것의 효과가 엄청난 것이죠. 물론 데이
터가 늘어나면 이 값을 계속 올려주어야 하고 시스템의 메모리 요구량도 증가할테니 좋
은 해결책은 아니라고 봅니다. 하지만 임시 처방으로는 최선의 방법이었습니다.
2. 테이블 타입 변경을 통한 테이블 버퍼링
다음으로 한 작업이 위의 쿼리와 관련된 테이블의 타입을 InnoDB 로 변환한 것이었습니
다. MySQL의 최적화 관련된 공식 문서를 뒤지던 중에 MyISAM의 경우 key_buffer를 통해
key(index)만을 메모리에 버퍼링 하지만 InnoDB의 innodb_buffer_pool의 경우에는 전체
row를 버퍼링 한다고 합니다. 이번엔 IO에서 읽기를 최대한 줄이고자 했습니다.
innodb_buffer_pool_size=256M
후에 512M에서 1024M로 현재는 768M로 설정된 상태입니다.
참고로 임시 메모리 테이블과 innodb_buffer_pool을 늘리는 과정에서 MyISAM의 key_buffer
와 query_cache_size를 줄여주었습니다. 사용률이 20% 정도씩 밖에 안되었거든요. 아래
는 현재 설정된 값입니다.
query_cache_size=128M
query_cache_limit=16M
key_buffer_size=128M
위에서 언급했다 시피, InnoDB로 대부분의 테이블을 변환한 이유는 IO를 최대한 줄이기
위해서였습니다. 물론 대부분의 쿼리에서 쓰이는 row들에 대해 인덱스를 준다면 MyISAM
에서도 같은 효과를 볼 수 있겠지만, 이는 작업 시간이 매우 오래 걸리기 때문에 이 방
법은 피했습니다. 데이터가 많고 메모리에 여유가 있다면 InnoDB의 버퍼풀이 전체적인
처리의 미미한 향상에 효과가 있을거라는 생각하에 한 작업이었습니다. 물론 서버 시작
초기에는 대부분의 데이터를 버퍼 풀로 불러오는 작업때문에 잠시 '매우 느린'상태이긴
하지만 그 이후의 쿼리들에서 부터는 약간씩의 속도 향상이 있었습니다.
물론 MyISAM에서 InnoDB로 바꾸면서 멀티쓰레드 상황에서는 효과가 없을수도 있습니다.
효과가 있다고 해도 매우 미미할 수도 있구요. 최소한 제가 수차례 확인해 본 drupal의
페이지 로그에서는 '최근글 보기'에서 0.x초의 응답속도 향상이 MyISAM 에 비해 있었습
니다.
이후에 추가로 다른 테이블도 InnoDB로 변환해서 현재는 검색과 관련된 가장 큰 테이블
만 MyISAM인 상태입니다.
검색과 관련된 튜닝은 사실 실패로 결과가 났습니다. 검색어 인덱스 테이블로 보이는
search_index 의 테이블 크기가 꽤 큰 상황이었는데, 이를 잘못보고 어설프게 InnoDB로
바꾸려다가 다시 원복하는 상황이 일어났습니다. MyISAM일때는 인덱스 포함 500메가 정
도였는데 InnoDB로 바꾸니 1.7기가 정도가 되었습니다. row의 갯수는 약 1천만개 정도
입니다. 현재 테이블 파일 크기나 row의 갯수가 전체 테이블들 중에 최고로 크고 많은
상황입니다. 이 상황에서 InooDB의 버퍼풀이 전체 테이블들을 버퍼링 하기에 매우 부족
하게되어서 기존에 버퍼링이 충분하게 되던 테이블에까지 영향을 주어 전체적으로 성능
저하가 일어났습니다. 그래서 다시 MyISAM으로 바꾸게 되었습니다.
원래 검색관련 테이블을 InnoDB로 변환하려 했던 이유는 속도 개선 보다는 검색 결과의
개선을 위해서 였습니다. drupal이 자체 검색테이블을 두긴 했지만 제가 봤을때 이는
MySQL이 지원하는 풀텍스트 인덱스와 같은 방법으로 생각됩니다. 전문 용어는 잘 모르
겠는데 간단히 설명을 하면 검색을 할 내용을 미리 검색어 단위로 분리해 놓고 실제 검
색시에는 이 단어들에서 일치하는 단어를 찾게 됩니다. 하지만 이런 방식은 서구권 언
어들에는 잘 맞는 경향이 있지만 조사(어미?)가 있는 우리말이나 띄어쓰기가 없어서 단
어간의 구분이 불분명한 중국어 일본어 등에서는 부적절한 면이 있어서 현재 KLDP의 검
색과 같이 예상되는 조사(어미?)가 포함된 단어를 검색어로 입력을해야 검색 결과가 나
오는 상황이 발생합니다. 이를 개선할 방안으로 제가 운영진 분들께 제안한 방법이 일
치 검색이 아닌 LIKE '검색어%' 방식을 제안했습니다. 물론 이 방식은 검색속도가 매우
느린 단점이 있긴 하지만 결과는 우리말에서 나름대로 효과가 있고 만일 전체 테이블이
버퍼로 올라가 있다면, 이전 방식(MyISAM+검색어일치)에 비해 그리 많이 느리지는 않다
는 결론을 테스트를 통해 얻었습니다. 하지만 결과는 많은 테스트를 해보지 않은 제 불
찰로 판명이 났습니다. 주된 원인은 테이블의 크기를 제대로 확인하지 않은데 있었습니
다. 버퍼링이 충분히 되지 못했기 때문에 이전보다 훨씬 많은 IO가 발생했습니다.
3. 명시적인 임시테이블 생성 쿼리 변경
위의 설정변경을 완료하고 확인해 본 결과 아직도 디스크 임시 테이블의 사용이 빈번하
게 일어났습니다. 물론 '최근 글'에서는 아니였구요. 그래서 응답속도가 느린 검색페이
지의 쿼리를 확인해본 결과 명시적으로 임시 테이블을 생성하는데, 이때 이 임시테이블
이 디스크에 생성되는 상황이었습니다. 그래서 drupal의 임시테이블 생성 소스 부분에
TYPE=HEAP을 추가했습니다. 하지만 큰 속도 향상은 없었습니다. 결과로 만들어 지는 임
시 테이블의 크기가 그리 크지 않기 때문이었습니다.
4. 의문점
이렇게 디스크의 사용을 최소화하기 위해 노력했음에도 불구하고 아직도 전체 임시테이
중에서 약 50 퍼센트 정도는 디스크를 사용하고 있다고 global status에 나오고 있습니
다. 예상 가능한 상황은 네가지 정도가 될 것 같습니다.
첫번째. 전체 사용에 비해 메모리 임시 테이블의 크기가 작을수 있습니다. 이는 메모리
할당이 더 가능해 지는 상황이 되면 확인이 가능할 것으로 보입니다.
두번째. 위와 같은 조인, 임시테이블 생성 외에도 임시 테이블이 만들어지는 상황이 있
을 수 있습니다. 찾아내기 힘들겠죠. ㅡ,.ㅡ
세번째. 파라미터 설정을 제대로 못했을 가능성이 있을 수 있습니다. 제가 알고 있는게
정확한게 아닐 가능성이 높습니다.
네번째. mysql status의 버그이다. 배제할 순 없습니다.
이상 kldp.org의 디비서버 최적화를 위한 저의 고군분투를 정리(?)해 보았습니다. 비록
정리가 안되긴 했지만 혹시라도 비슷한 상황을 겪으실 분들을 위해서..
혹시라도 마음과 시간의 여유가 된다면 (가능성이 희박하긴 하지만), drupal에 대해 더
알아보고 모듈을 개발을 해보고 싶은 욕심이 생기네요. ㅋㅋ