하츠의 꿈 :: [MySQL] JOIN 과 그에 따른 Slow Query(슬로우 쿼리)

[MySQL] JOIN 과 그에 따른 Slow Query(슬로우 쿼리)

엔지니어스 마인드/Database 2009/07/09 13:30

작업 중인 모 사이트가 갑자기 다운되는 현상이 발생했다.

원인은 MySQL DB connect 숫가가 꽉 차서 연결이 안되서였다.(max_connections)

급하게 MySQL Daemon을 restart해서 다시 정상이 되기는 했지만 문제를 찾아야 했다.

DB를 확인해 보니 1천만 row 가까운 테이블이 존재했고, 별다른 인덱스가 걸려있지 않았다.

Slow Query Log와 해당 테이블을 사용하는 웹프로그램을 뒤져서 해당 테이블을 다른 테이블과 JOIN 하여 데이터를 추출하고 있다는 것을 알았다....

웹프로그램을 짜다보면...

'이 서비스가 얼마나 활성화되겠어? 조금 서비스하다가 말겠지..'

라는 안일한 생각과 게으름으로 쿼리를 대충 사용하는 경우가 종종 있다.

그런 경우 약간 만 동시접속자가 증가하면 서비스가 멈추는 황당한 일을 겪게 된다.

이번도 그런 경우였다. 그래서 정리해 본다.

예제 테이블 정의

* table name : T1
+------+-------------+
| id | name |

+------+-------------+
| 1 | honggil |
| 2 | nari    |
| 3 | abcd    |
+------+-------------+

* table name : T2
+------+-------------+
| id | comment    |
+------+-------------+
| 1 | hohoho |
| 2 | yo!!    |
+------+-------------+

1. JOIN을 생략한 JOIN

mysql> SELECT * FROM T1, T2 WHERE T1.id = T2.id
+------+-------------+-------------+
| id | name | comment |
+------+-------------+-------------+
| 1 | honggil | hohoho |
| 2 | nari | yo!! |
+------+-------------+-------------+

JOIN한 각 테이블에 값이 모두 있는 필드 만 추출한다.

2. INNER JOIN

mysql> SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.id
+------+-------------+-------------+
| id | name | comment |
+------+-------------+-------------+
| 1 | honggil | hohoho |
| 2 | nari | yo!! |
+------+-------------+-------------+

1번의 JOIN을 생략하고 Where 절로 처리한 것과 같은 결과를 보인다.

3. LEFT JOIN

mysql> SELECT * FROM T1 LEFT JOIN T2 ON T1.id = T2.id
+------+-------------+-------------+
| id | name | comment    |
+------+-------------+-------------+
| 1 | honggil | hohoho |
| 2 | nari    | yo!!    |
| 3 | abcd    | NULL    |
+------+-------------+-------------+

JOIN의 왼쪽에 있는 테이블을 기준으로, JOIN의 오른쪽 테이블에 조건에 맞는 값이 없으면 NULL 로 표시한다.
결국 JOIN의 왼쪽 테이블의 값은 모두 표시된다.

4. RIGHT JOIN

mysql> SELECT * FROM T1 RIGHT JOIN T2 ON T1.id = T2.id
+------+-------------+-------------+
| id | name | comment |
+------+-------------+-------------+
| 1 | honggil | hohoho |
| 2 | nari | yo!! |
+------+-------------+-------------+

LEFT JOIN의 반대.
즉, JOIN의 오른쪽에 있는 테이블을 기준으로, JOIN의 왼쪽 테이블에 조건에 맞는 값이 없으면 NULL 로 표시한다.
결국 JOIN의 오른쪽 테이블의 값은 모두 표시된다.

이 밖에 여러가지 JOIN이 있지만 생략한다.

JOIN을 하면 간편하긴 하지만 Slow Query의 원인이 되는 경우가 많다.
어떤 쿼리가 Slow Query인지 찾기 위해 로그를 쌓는 방법을 알아보자.

1. 데몬을 실행시킬 때 Slow Query Log를 적용하는 방법

MySQL 데몬 실행 시 --log-slow-queries=[저장할파일명] 라는 옵션을 준다. [저장할파일명]은 원하는 위치의 파일명을 입력하면 된다.

/etc/init.d/mysqld 파일의 'start' 에서 아래 부분을 찾아 옵션을 추가한 후 서버를 restart하면 된다.

$bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file --log-slow-queries=slow_query.log >/dev/null 2>&1 &

2. 설정 파일에 Slow Query Log를 적용하는 방법

my.cnf 파일에 설정사항을 입력할 수가 있다.
일반적으로 my.cnf 파일은 /etc 아래에 존재한다.
아래의 내용을 적당한 위치에 기재하고 서버를 restart하면 된다.

long_query_time = 3 # 3초 이상 시간이 걸리는 Query
log-slow-queries=[저장할파일명]

[저장할파일명]은 텍스트로 저장되므로 아무 에디터에서나 열수 있고, 쌓이는 내용은 어렵지 않으므로 쉽게 알 수 있을 것이다.

<추천>해 주시면 더욱 많은 분들이 글을 볼 수 있습니다.

Posted by 명섭이

TAG Connection, Join, MySQL, mysql error, Slow Query, 슬로우쿼리

트랙백 3개, 댓글 0개가 달렸습니다

트랙백 주소 :: http://blog.bsmind.co.kr/trackback/84

Subject: 하츠의 생각
Tracked from itry's me2DAY 2009/07/09 13:35 삭제
[MySQL] JOIN 과 그에 따른 Slow Query(슬로우 쿼리) 작업 중인 모 사이트가 갑자기 다운되는 현상이 발생했다. 원인은 MySQL DB connect 숫가가 꽉 차서 연결이 안되서였다.(max_connections) 급하게 MySQL Daemon을..
Subject: SYSLOG-NG와 MySQL 만으로 시스로그 서버 구축하기
Tracked from 뭐든지 용한의 블로그 2009/07/10 18:38 삭제
syslog-ng 와 데이타베이스개념은 시스로그 메시지가 날라 오면 filter 키워드에 의하여 걸러진 시스로그는 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> templete 에 의하여 MySQL 의 쿼리를 실행하게 되는데 실행시 쿼리를 이용하여 적절하게 메시지를 분석하고 그에 맞는 처리를 합니다. 그리고 난후 log에 의하여 destination이결정 되게..
Subject: SQL 에서 join 이란?
Tracked from DEV 2009/07/12 18:21 삭제
SQL SELECT 문은 다음과 같은 일반적인 형식을 갖고 있었다: SELECT column_name(s) [AS alias] FROM table_name [ORDER BY column_name(s) (ASC | DESC) ] WHERE (search_criteria); CD의 데이터베이스를 검색할 때 가장 다양하게 사용했던 형식이다. 그러나 필자는 하나의 데이터베이스 테이블에서 레코드를 가져오는 것만 살펴보았다. 지금까지 테이블 관계에 대해 설명했..