[관리] Mysql 과부하에 대한 긴 분석

Mysql 과부하에 대한 긴 분석  

      

  작성자 : 조동현 (chodong at tt.co.kr)    http://for100years.com  

  작성일 : 2001/08/24 13:32  

        

  – 서 론 –

최근 Mysql 과 연동된 프로그램에서 프로세스 누적을 유발하여 접속불능

상태가 되는 현상이 빈번히 발생하고 있습니다.

뭐 하루 이틀의 일도 아니고, 딱히 이렇다 할 해결책이 있는 것은 아니지

만 이 같은 현상의 원인분석을 나름대로 해봤습니다.

– 증 상 –

일부 호스팅 서버에서 발생하는 Mysql 과부하로 웹접속시 ‘Lost

connection’ 또는 ‘Mysql connection error’ 와 같은 메시지를 내고 서버

에서는 Mysql 프로세스가 제한치까지 증가하여 죽지 않으며 때로는 Mysql

Defunct 와 같은 프로세스로 남게 되어 접속 불능상태가 되는 현상이 나타

나고 있습니다.

때에 따라서는 콘솔에서 Mysql Shell 로의 로그인도 불가능할 때가 있습니다.

현재는 임시책으로 apache keepalive를 조정하고 Mysql connection

lifetime을 줄이고 mysql 제한 프로세스 수를 늘려주고 이를 감지하여

refresh하는 방법을 쓰고 있습니다.

– 원 인 –

위의 증상에 대한 원인은 대략 두가지 가지로 나뉩니다.

첫째, mysql_pconnect() 함수를 이용한 프로그램에서 SELECT 쿼리에 의한

대량의 데이터 전송시에 발생합니다. 이 경우, Mysql Defunct 는 발생하

지 않는 것으로 알려져 있습니다.

mysql_pconnect() 은 하나의 접속루트를 열어 프로그램에서 mysql_close

() 함수가 나타날때까지 접속을 유지한 상태로 버티게 됩니다.

물론 상당시간(기본 8시간)이 지나면 자동으로 죽지만, 만일 대량의 데이

터를 연속적으로 처리하는 경우 Mysql 의 제한된 프로세스를 다 소비하여

접속불능상태가 됩니다.

일반적으로 INSERT, UPDATE, DELETE 쿼리보다 SELECT 가 자료전송량이 많

습니다. Mysql 의 자체 트래픽만 체크해 본다면 놀랄만한 수치가 나올 수도 있습니다.

이 경우, 때에 따라서 콘솔에서의 접속도 상당시간 지연이 됩니다.

두번째, 잘못된 쿼리와 테이블 설계에 의한 대량의 데이터 전송시에 발생합니다.

특히 SELECT 문에서 조건(<, >, = 등)에 의한 검색과 전역검색(like 절과

% 를 이용한 검색) 그리고 인덱스를 이용하지 않는 order by 절에 의해 나타납니다.

참고로 인덱스는 데이터 베이스에서 지원하는 색인 기능입니다.

인덱스를 사용하게 되면 데이터 베이스 파일에 *.ISM 또는 *.MYI 라는 인

덱스 파일에 인데스로 지정된 자료가 저장되며 인덱스로 지정된 테이블의

필드에 저장되는 자료들이 오름차순으로 자동 정렬됩니다.

인덱스와 관련된 예로 아이디, 이름, 주민등록번호 로 이루어진 테이블이 있을 때,

인덱스를 지정하지 않은 테이블에서 일반적인 쿼리(SELECT * FROM 테이블

이름)를 하게 되면 인덱싱 타입이 ALL 이 나옵니다.

인덱싱 타입이라는 것은 데이터 베이스에서 자료를 가져올 때 입력된 자료

를 검색(스캔한다고 함)하는 방식입니다.

인덱싱 타입은 여러 가지가 있지만 이중 ALL, INDEX, RANGE 가 일반적입니다.

ALL 타입은 위에서 말한 전역스캔입니다. 즉 입력된 모든 자료를 훑어서

조건에 맞는 자료를 가져옵니다.

만일 SELECT * FROM 테이블이름 WHERE 아이디=’chodong’ ; 이라고만 쿼리

를 줘도 데이터 베이스에 저장된 전체 데이터를 검색하면서 아이디 필드에

서 값을 순차적으로 검색해서 chodong 을 찾아 가져옵니다. 이때 출력에

대한 정렬을 하기 위해 메모리나 임시 파일을 생성하여 사용하기도 합니

다.(이 과정에서 Mysql 프로세스가 sleep 상태로 남거나 defuct 상태가 되

는 문제가 발생합니다.) 당연히 시간도 많이 걸리고 속도도 늦고 부하도

많이 걸립니다.

그러나 인덱스를 지정해 주는 경우에는 이와 같은 인덱싱 타입이 INDEX

나 RANGE 또는 REF, CONST 타입으로 바뀌게 됩니다.

INDEX와 RANGE 타입은 색인으로 따로 작성된 파일(*.ISM, *.MYI)에서만 검

색을 하는 방식입니다.

만일 아이디를 인덱스로 테이블에 지정해 주면 다음과 같은 인덱스를 이용

한 쿼리는 INDEX 나 RANGE 타입으로 검색을 합니다.

SELECT * FROM 테이블이름 WHERE 아이디 like ‘cho%’ ;

INDEX 타입은 정렬순서 그대로 원하는 자료까지만 출력해주는 것이고,

RANGE 타입은 자료의 대략적인 위치에서부터 일정 범위를 검색하는 방식입니다.

위의 경우 RANGE 타입으로 나타납니다.(like 절과 % 때문에)

만일 다음과 같이 검색하면 INDEX 타입으로 검색합니다.

SELECT * FROM 테이블이름 ORDER BY 아이디 ;

보시다시피 인덱스를 이용한 쿼리는 일반 쿼리와는 약간 다릅니다.

대략 두 가지 검색방식이 있습니다. WHERE 절을 이용한 것과 ORDER BY 를

이용하는 것이 있는데…

두 가지의 다른 점은 WHERE 는 조건절 검색이고 ORDER BY 는 정렬을 위한

검색입니다.

둘 다 인덱스를 이용한다고 가정했을 때, ORDER BY 가 빠릅니다.

인덱스를 이용한다는 것은 쿼리문에서 검색을 할 때 인덱스로 지정된 필드

만을 이용하는 것입니다.

그러나 이것도 이론적인 이야기일 뿐이고 실제로 쿼리문이 제대로 최적화

되었는지 확인 하려면 EXPLAIN 이라는 SELECT 문 쿼리 분석 도구를 이용합니다.

Mysql Shell 에 접속하셔서 다음과 같이 해보세요.

> EXPLAIN SELECT * FROM 테이블이름 ;

쿼리문은 마음대로 넣어 보시면 아마 다음과 같은 분석결과를 출력할 겁니다.

+——–+——–+—————+——+———+——+——+—

—-+

| table | type | possible_keys | key | key_len | ref | rows | Extra

|

+——–+——–+—————+——+———+——+——+—

—-+

| nadmin | index | NULL | NULL | NULL | NULL | 1 | |

+——–+——–+—————+——+———+——+——+—

—-+

40001 row in set (0.00 sec)

여기서 type 항목에 index 가 나타나는 것이 보일 것입니다.

중요한 것은 Extra 라는 항목에 Using File sorting 또는 Using

temporary file 라고 나타나는 것은 좋지 않습니다.

여기서 테이블 구조와 인덱스의 관계를 생각해야 합니다.

만일 테이블 설계시 인덱스가 지정되지 않는다면 위의 쿼리분석 결과는 너

무도 당연합니다.

따라서 테이블의 구조를 살펴 쿼리에서 인덱스를 이용하도록 되어 있는지

점검해야 합니다.

위의 쿼리 분석 결과에 대한 테이블 구조입니다.

+———-+————-+——+—–+———+—————-+

| Field | Type | Null | Key | Default | Extra |

+———-+————-+——+—–+———+—————-+

| code | int(2) | | PRI | NULL | auto_increment |

| username | varchar(20) | YES | | NULL | |

| id | varchar(20) | YES | | NULL | |

| password | varchar(20) | YES | | NULL | |

| div | varchar(20) | YES | | NULL | |

| status | varchar(20) | YES | | NULL | |

| email | varchar(35) | YES | | NULL | |

| reg_date | date | YES | | NULL | |

| priority | varchar(20) | YES | | NULL | |

+———-+————-+——+—–+———+—————-+

보시면 code 라는 필드가 PRIMARY KEY(MAIN 인덱스) 로 잡혀 있는 것을 보

실 수 있습니다.

즉 인덱스로 지정된 필드를 이용한 쿼리는 검색 타입이 INDEX 나 RANGE,

REF, SYSTEM 등으로 나타납니다.

대충 원인은 이 정도 일겁니다.

다른 이유가 있다면 프로그램 자체의 문제일 것입니다.

이 부분을 개별 프로그램의 쿼리와 테이블 구조를 보고 고객님께 적절히

설명해야 할 것입니다.

– 과부하 유발 여부 점검 방법 –

과부하 유발로 보여지는 사이트가 발견되면 다음과 같은 절차에 의해 점검

을 실시 합니다.

1. 프로세스 점검 및 확인

현재 실행중인 프로세스에서 mysql 데몬이 많이 떠 있거나 defunct 가 떠있는지

확인합니다.

Msyql Shell 로 접속해서 show processlist; 를 하여 프로세스를 많이 점

유한 사용자를 감지합니다.

2. show processlist; 에서 나타나는 처리 중인 쿼리, 주로 SELECT 문을

캡쳐합니다.

3. 해당 쿼리문을 분석해 봅니다.

첫째, WHERE —- LIKE —- ‘%—%’ 라는 행이 있으면 과부하 주범일 가능성이

높습니다.

둘째, EXPLAIN 으로 분석하여 ALL 타입이나 Using filesorting 등의 문구

가 나오면 주범일 가능성이 높습니다.

마지막으로 캡쳐한 쿼리문에서 테이블명을 이용해 전체 자료 수를 다음과 같이

구합니다.

SELECT count(*) FROM 테이블 명 ;

여기서 자료가 20M가 넘는 정도(자료의 크기에 따라 약 1~10만건)이며 위의 두 가지

조건을 만족하면 그냥 주범 입니다.

4. 만일을 위해 테이블 구조를 살펴봅니다. 인덱스가 지정된 필드와 쿼리 에 이용된

필드를 비교해 인덱스를 이용하지 않는다면 역시 문제의 원인입니다.

5. 해당 프로그램의 소스를 살펴봅니다. PHP라면 쉽겠지만 다른 컴파일 된 프로그램

이라면 보지 마세요.

소스상에서 SELECT 문이 연발적으로 나온다면 이것도 원인이 됩니다.

이상의 5가지 정도를 보시면 대략 과부하 유발의 주범과 원인을 파악하실

수 있을 겁니다.

– 과부사이트에 대한 대책 –

1. PCONNECT 에 의한 접속 불능.

먼저 Mysql Shell 로 접속을 해서 위의 점검을 해 봅니다.

Show processlist; 를 하여 Sleep 상태의 프로세스가 많이 발생하였고

connect time이 길다면(보통 1000 이상) mysql_pconnect() 에 의한 문제입니다.

이 경우에는 해당 DB 사용자의 홈디렉토리에서 다음과 같은 명령으로

mysql_pconnect() 사용 여부를 확인 합니다.

]# cat * |grep pconnect

위와 같이 해서 pconnect 이 나온다면 해당 소스에서 mysql_connect() 으로 바꿔

줍니다.

일단 pconnect 과 일반 connect 의 차이점은 연결방식이 다른 것 뿐이므

로 수정해도 프로그램 동작과는 무관합니다.

사안이 심각할 때는 먼저 /usr/local/mysql/bin/mysql.server stop 으로

Mysql 데몬을 정지시키거나 killall –ev –9 mysqld 로 프로세스를 죽입니다.

그리고 다시 재시작합니다.

2. defunct 프로세스 발생과 mysql 접속 불능.

Defunct 프로세스를 없애는 방법은 mysql_pconnect() 을 사용하는 것입니다.

그러나 pconnect 을 사용할 경우에는 1번과 같은 문제가 나타날 수 있습니다.

이 점을 감안하셔서 프로그램내에서 mysql_pconnect() 사용시 반드시

mysql_close() 와 쌍으로 사용하여야 합니다.

만일 pconnect() 을 사용하여도 접속 불능 상태가 나타난다면 상황에 따라 php와 mysql 설정을 바꿔봅니다.

이 부분에 대해서 테스트를 거쳐 차후에 다시 한번 올리도록 하겠습니다.

– 과부하 유발 사이트에 대한 대책 –

앞서 말씀 드렸다시피 해당 서버에서 다른 서버(서비스하는 도메인이 적은 곳 또는

단독)로 이전하는 것이 대책입니다.

어제 photokr.net 이라는 곳도 이 문제로 상당시간 통화를 해서 진땀을 뺐습니다만,

근본적으로 과부하가 걸리는 서버에서 해당 부하유발 사이트를 제거해야 문제가 해결

됩니다.

이유는 이미 사용중인 프로그램과 테이블 구조, 저장된 자료는 갈아 업기가 힘들기

때문입니다.

만일 스크립트 언어(PHP, Perl 등)로 제작되어 쿼리문 조정이 가능한 경우, 검색하는

쿼리 부분을 막거나, 쿼리를 조정합니다.

즉 테이블의 인덱스 필드를 이용하여 쿼리문을 다시 작성해야 하는 것입니다.

이런 경우 문제는 화면에 나타나는 정렬 상태가 바뀌고 일부 자료가 검색

되지 않을 수 있습니다. 특히 게시판을 접하셨을 때 이것은 저희가 할 수

있는 작업은 아닙니다. 해당 소스의 프로그래머와 통화를 하셔서 간단하

게 어느 부분을 살펴보라고 조언만 해 주십시오.

못 보겠다고 하면 물론 서버 이전 밖에 없습니다.

현재 photokr.net 이라는 사이트가 www54번에서 문제가 되고 있으니 위에

서 설명드린 부분을 한번씩 해보세요.

눈으로 한번씩 보시면 바로 고객지원 하시는데 문제가 없을 겁니다.

마지막으로 Mysql 인덱싱 및 DB 설계와 관련한 유용한 사이트와 php,

mysql 최적화와 관련된 사이트를 알려드립니다.

300만건 mysql 게시판 : http://bizlove.net

Mysql 최적화 참고문서 : http://tunelinux.pe.kr/mysql/manual/10.txt

php.ini Mysql 설정 관련 :

http://kldp.org/Translations/html/PHP-KLDP/PHP-

KLDP.html#CONFIGURATION