database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 30142 게시물 읽기
No. 30142
mysql community server 성능문제
작성자
최진규(cjg1012)
작성일
2011-11-15 02:51ⓒ
2011-11-15 10:29ⓜ
조회수
8,533

 
 
안녕하세요..언제나 많은 도움이 되고 있습니다. 감사합니다. 
 
항상 오라클 게시판만 왔다갔다 하다 여기 mysql게시판에는 처음으로 질문 올리네요..
 
 
 
오라클을 제공해주는 호스팅을 써오다 사용자가 급증하여 제공업체에서 제 계정 하나때문에 cpu부하가 엄청 심해서 조치하지 않으면 서비스를 막는다고 하여
 
단독으로 서버호스팅을 하고 그 서버에 mysql community server 설치하였습니다.
DB서버로만 활용합니다.
 
서버 스펙은
 
CPU : Intel Xeon Quad-Core E3-1230 (Sandybridge)(3.20GHz / L3 8MB / 5.0GT/s)
RAM : DDR3 4G 10600 U-ECC
HDD : SATA2 500GB (7200.ES/기업용)
BAY2 : BAY / 1U (4.5cm)
회선 : 1Gbps 광(光) Dedicated Line
이구요...
 
현재 innodb로 설정한 상태이구요 설정값은 
 
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path=ibdata1:10M:autoextend:max:10000M
innodb_data_home_dir = /home/mysql/var
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
입니다.
 
설치후 오라클에서 mysql로 마이그레이션 하고 mysql에 맞게 sql문을 수정하고 있는데요..
 
이전 호스팅에서 cpu 부하의 원인이 되던 sql문을 mysql 문법으로 바꾸고 실행을 했더니 1분이 넘게 걸리더군요..-_-;;
 
여차여차 하여 1~2초까지 줄였는데요..문제는 순간 cpu가 30%가 오릅니다.
 
이정도 자원을 잡아먹으면 3명만 동시에 쿼리를 날려도 답이 없어 보이는데요...
 
혹시 mysql community server 버전이 오라클로 말하면 XE버전 같은건가요?? 단순히 학습용으로 쓰이는..그런 버전..
 
mysql에 대해서 잘 몰라 그래요..ㅜㅜ
 
혹시 상용으로 따로 있는지요??
 
 
쿼리도 올립니다.
 
SELECT T1.EMAIL, T1.NICK_NAME, T1.BIRTH_DATE, T1.JENDER,
T1.INTRODUCE, T1.INTEREST_SCORE, T1.DISTANCE_NUM,
(SELECT CONCAT(FILE_URL_PATH,FILE_NAME_THUMB) 
  FROM MT_MEMBER_FILE 
 WHERE MEMBER_EMAIL=T2.MEMBER_EMAIL AND FILE_INDEX=T2.FILE_INDEX) URL
  FROM (
SELECT A.EMAIL, A.NICK_NAME, A.BIRTH_DATE, A.JENDER,
      A.INTRODUCE, IFNULL(A.INTEREST_SCORE, 0) AS INTEREST_SCORE,
      ROUND( 6371 * ACOS( COS( RADIANS(37.62709565) ) * COS( RADIANS( A.LATITUDE ) ) * 
     COS( RADIANS( A.LONGITUDE ) - RADIANS(127.0335156) ) + SIN( RADIANS(37.62709565) ) * 
     SIN( RADIANS( A.LATITUDE ) ) ), 1) AS DISTANCE_NUM
   FROM MT_MEMBER A, MT_CONNECT_LOG B 
WHERE A.EMAIL = B.EMAIL 
  AND B.CONNECT_DATE > DATE_ADD(NOW(), INTERVAL-10 DAY)
  AND A.LATITUDE IS NOT NULL
  AND A.BIRTH_DATE BETWEEN '19120101' AND '20111231'
  AND A.REPORT_COUNT < 4
  AND A.JENDER = 'M'
  AND A.EMAIL <> 'abc@abc.kr'
  AND A.EMAIL <> 'abc@naver.com'
  AND A.EMAIL NOT IN (SELECT EMAIL FROM MT_C2DM_KEY_NOT_REGI C)
  AND A.EMAIL NOT IN (SELECT BLOCK_EMAIL FROM MT_BLOCK_MEMBER D WHERE EMAIL = 'abc@abc.kr')
  AND A.EMAIL NOT IN (SELECT EMAIL FROM MT_BLOCK_MEMBER E WHERE BLOCK_EMAIL = 'abc@abc.kr')
  AND DISTANCE_NUM > 100
  AND DISTANCE_NUM < 99999
ORDER BY DISTANCE_NUM
LIMIT 1,50
) T1
LEFT JOIN 
(SELECT MEMBER_EMAIL, MIN(FILE_INDEX) FILE_INDEX
 FROM MT_MEMBER_FILE
GROUP BY MEMBER_EMAIL) T2 ON T1.EMAIL = T2.MEMBER_EMAIL ;
 
MT_MEMBER -> 73530 row
MT_MEMBER_FILE -> 94479 row
MT_CONNECT_LOG  -> 69155 row
이구요..
 
explain 결과가
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL \N \N \N \N 50
1 PRIMARY <derived7> ALL \N \N \N \N 43753
7 DERIVED MT_MEMBER_FILE index \N PRIMARY 306 \N 113957 Using index; Using temporary; Using filesort
3 DERIVED A ALL PRIMARY,idx1_mt_member,idx2_mt_member \N \N \N 84272 Using filesort
3 DERIVED B eq_ref PRIMARY,idx_connect_date PRIMARY 302 comalong.A.email 1 Using where
6 DEPENDENT SUBQUERY E unique_subquery PRIMARY PRIMARY 604 func 1 Using index; Using where
5 DEPENDENT SUBQUERY D eq_ref PRIMARY PRIMARY 604 func 1 Using where; Using index
4 DEPENDENT SUBQUERY C unique_subquery PRIMARY PRIMARY 302 func 1 Using index
2 DEPENDENT SUBQUERY MT_MEMBER_FILE eq_ref PRIMARY PRIMARY 306 T2.FILE_INDEX,T2.MEMBER_EMAIL 1
 
이렇게 나왔습니다. 쿼리 자체의 문제로 cpu자원이 많이 쓰이는걸까요???
 
ps. MySQL GUI Tools 정말 좋군요..마이그레이션을 무지 걱정했는데 이 툴로 자동으로 금방 되네요...완전 강추입니다. ㅎㅎ
 
이 글에 대한 댓글이 총 3건 있습니다.

 짧은 소견.. 

 

1.  mt_meber A, me_connect_log B  

이 테이블에 대해서는 인덱스를 사용하지 못하는 듯.(테이블 전체를 풀스캔하는 듯) 

=> 적절한 인덱스 생성을 ... 

예) alter table mt_meber  add index mt_member_001 (birth_date,email)

 

2.    AND A.EMAIL <> 'abc@abc.kr'

  AND A.EMAIL <> 'abc@naver.com'

  AND A.EMAIL NOT IN (SELECT EMAIL FROM MT_C2DM_KEY_NOT_REGI C)

  AND A.EMAIL NOT IN (SELECT BLOCK_EMAIL FROM MT_BLOCK_MEMBER D WHERE EMAIL = 'abc@abc.kr')

 

mt_block_member를 몇 번 이나 읽고 있으며 not은 인덱스를 사용하지 않는 대표적인 연산입니다.

 

3. mt_mbmer_file을 조인에서 읽고 또 서버쿼리에서 또 읽는 

   물론 이 부분은 인덱스를 사용하고 있는 것으로 나오는데... 될 수 있으면 한 테이블은 한번 접근하는 것이. ... 

 

 

 

쿼리에 사용된 테이블의 정보를 좀 더 볼 수 있으면 좋겠구요.. 

show create table MT_MEMBER\G ... 등등의 결과라도 ㅎ

 아울러.. 이 결과도 같이.. 

select table_name, table_rows from information_schema.Tables where Table_schema = '<적당한 스키마네임>' && Table_name in ('MT_MEMBER', ....);

.님이 2011-11-15 10:08에 작성한 댓글입니다.
이 댓글은 2011-11-15 10:14에 마지막으로 수정되었습니다. Edit Ｘ

 답변감사합니다.

우선

information_schema.Tables 결과는

table_name table_rows
mt_block_member 37015
mt_c2dm_key_not_regi 11301
mt_connect_log 73542
mt_member 70873
mt_member_file 111561
이거구요..
본문의 쿼리에서 문제가 mt_member_file 과 조인하는것이 문제 같아요..
mt_member_file 조인전에 t1 블럭안에 있는 쿼리는 0.01초 정도 나옵니다.
그래도 cpu는 15% 먹네요..ㅜㅜ..
 
전 속도보다 한번쿼리에 cpu자원을 많이 먹는게 문제예요..
현재 호스팅 서비스에서 1초당 쿼리요청이 100~200번 넘게 들어오고 있는 실정에서 한번쿼리에 cpu15%라면 못쓴다가 맞는거 같은데...ㅜㅜ
 

최진규(cjg1012)님이 2011-11-15 13:17에 작성한 댓글입니다.
1. LEFT JOIN  제거 (서브쿼리를 수정 하면 될 것으로 보임)
2. NOT IN 절을 NOT EXISTS절로 변경 하는 것이 더 효과적으로 보입니다.(이건 DATA의 분산도에 따라 달라서 실행해 봐야 알 수 있습니다.)
 
 
 
SELECT T1.EMAIL, T1.NICK_NAME, T1.BIRTH_DATE, T1.JENDER,
T1.INTRODUCE, T1.INTEREST_SCORE, T1.DISTANCE_NUM,
(SELECT CONCAT(FILE_URL_PATH,FILE_NAME_THUMB) 
  FROM MT_MEMBER_FILE 
 WHERE MEMBER_EMAIL=T1.EMAIL
          ORDER BY  FILE_INDEX
          LIMIT 1        
) URL
  FROM (
SELECT A.EMAIL, A.NICK_NAME, A.BIRTH_DATE, A.JENDER,
      A.INTRODUCE, IFNULL(A.INTEREST_SCORE, 0) AS INTEREST_SCORE,
      ROUND( 6371 * ACOS( COS( RADIANS(37.62709565) ) * COS( RADIANS( A.LATITUDE ) ) * 
     COS( RADIANS( A.LONGITUDE ) - RADIANS(127.0335156) ) + SIN( RADIANS(37.62709565) ) * 
     SIN( RADIANS( A.LATITUDE ) ) ), 1) AS DISTANCE_NUM
   FROM MT_MEMBER A, MT_CONNECT_LOG B 
WHERE A.EMAIL = B.EMAIL 
  AND B.CONNECT_DATE > DATE_ADD(NOW(), INTERVAL-10 DAY)
  AND A.LATITUDE IS NOT NULL
  AND A.BIRTH_DATE BETWEEN '19120101' AND '20111231'
  AND A.REPORT_COUNT < 4
  AND A.JENDER = 'M'
  AND A.EMAIL <> 'abc@abc.kr'
  AND A.EMAIL <> 'abc@naver.com'
  AND not exists (SELECT 1 FROM MT_C2DM_KEY_NOT_REGI C WHERE C.EMAIL = A.EMAIL)
  AND not exists (SELECT 1 FROM MT_BLOCK_MEMBER D WHERE EMAIL = 'abc@abc.kr' AND BLOCK_EMAIL =  A.EMAIL)
  AND not exists (SELECT 1 FROM MT_BLOCK_MEMBER E WHERE BLOCK_EMAIL = 'abc@abc.kr' AND E.EMAIL = A.EMAIL)
  AND DISTANCE_NUM > 100
  AND DISTANCE_NUM < 99999
ORDER BY DISTANCE_NUM
LIMIT 1,50
) T1
박인호(paerae)님이 2011-11-15 13:44에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
30146top명령어로 본 현황인데요... [5]
최진규
2011-11-17
7982
30145mysql 설정관련 질문드립니다. [1]
최진규
2011-11-16
8307
30144배트남어를 mysql에 입력하려면 어떻게 하죠? [1]
허허로
2011-11-15
7615
30142mysql community server 성능문제 [3]
최진규
2011-11-15
8533
30141select 한 테이블을 field 명으로 다른 table 조인하기? [1]
mysqli?
2011-11-14
7854
30140mysql 서버가 euckr로 설정이 되어 있는데 '한글과 일어'가 동시에 입력과 웹으로 출력이 되요.왜죠? [1]
허허로
2011-11-13
7817
301324개의 테이블 안에 원하는 값 조회..!! [1]
안인철
2011-11-10
8379
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.024초, 이곳 서비스는 PostgreSQL v16.2로 자료를 관리합니다