MySQL 최적화 방법 설명

by 조쉬 posted Feb 27, 2014
?

단축키

Prev이전 문서

Next다음 문서

ESC닫기

크게 작게 위로 아래로 댓글로 가기 인쇄

1. 동시 접속자수 설정

  1) 관련 값

    - max_used_connections : 동시 접속자 최대 건수 (읽기전용)

    - connections : 연결 시도된 총 수치

    - max_connections : 동시 접속자 수(최적화 시켜야 할 값)

    - max_connect_error : 지정된 이상의 연결장애가 발생하면 접속 호스트를 블락시킴.

    - max_user_connections = 0 (기본값(no limit) : 하나의 유저당 할당된 컨넥션 수)

  

  2) 최적화 방법

    - max_used_connections 값을 모니터링 해서 동시에 사용한 최대 사용자수를 확인한다.

  

  3) 최적화 결과

    - max_connections =

    - max_connect_error =

  

  4) 참고

    max_connect_error - DB 연결 에러가 지정한 값 이상으로 발생할 경우 접속하는 해당

    호스트(웹서버와 같은 PC에 있는 경우에는 웹서버를 가리킴)을 블락시켜버린다. 이 경우에는

    MYSQL 서버에서 mysqladmin flush-hosts 를 해줘야 하는데 실제 실행키져보면 블락이 바로

    해제되지 않는다. 이 값은 보통 max_connections 값과 동일하게 설정하거나 크게 설정한다.


2. 최대 open 테이블 수 설정

  1) 설명: 모든 쓰레드에서 오픈할 수 있는 DB테이블 총 개수

  2) 관련 값

    -max_connections

    -open_tables : 현재 오픈된 테이블 수(읽기전용)

    -open_files : 현재 오픈된 파일 수 (읽기전용)

    -opened_tables : 지금까지 오픈된 페이블 수 (읽기전용)

    -table_cache = 350 (기본값)

    -open_files_limit = 0 (기본값: max_connections * 5 혹은 max_connections + table_cache*2)

  3) 최적화 방법

    -table_cache -

      방법1: opened_tables 을 모니터링해서 이 값이 크면 table_cache값을 증가시킬 필요가 있다.

      방법2: open_table 값을 체크함으로서 테이블캐쉬값을 증가시키는것이 필요한 지 체크할 수 있다.

      보통 이 값을 max_connections 의 1.5배 정도 크기로 설정하는 것이 좋다고 한다.

  3) 최적화 결과

    - table_cache =


3. timeout 시간 설정

  1) 설명: 각종 실행 초과 값을 지정한다.

  2) 관련 값

    - max_execution_time = 2400 (php.ini파일 , php스크립트 최대 실행시간)

    - mysql.connect_timeout = 10 (php.ini파일 , db연결 타임아웃 시간)

    - connect_timeout = 5 (초과하면 db접속에러가 남, 에러발생은 aborted_connections 에 기록됨)

    - wait_timeout : 1800 (쿼리실행 타임아웃 값. mysql 접속해제시 접속 링크가 컨넥션 풀에

      계속 남아있도록 하는 것을 방지하기 위해서 설정하는 값이다. 하지만 실제로는 접속해제가 되지

      않고 계속 메모리에 남아있는다. 참고로 이 값은 php.ini에서 mysql.allow_persistent 값이 true로

      설정되어 있는 경우에만 유효하면 에러발생은 aborted_clients 에 기록된다. 실제로 해제되지 않기

      때문에 의미가 없다.)


  3) 최적화 방법

    - 웹서버와 DB서버가 같이 PC에 있는 경우에는 영구적인 접속이 비영구적인 접속에 비해 어떠한

      기능적인 향상을 주지 못할 수 있으며, 접속자 수가 많지 않은 되도 불구하고

      " Too many connections " 오류를 발생할 수 있기 때문에

      php에서 영구적인 접속을 꺼두는 것이 좋다.

  4) 최적화 결과

    - connect_timeout = 5

    - mysql.allow_persistent = off (php.ini 파일)


4. 인덱스 모니터링

  1) 설명: 인덱스를 제대로 사용하고 있는지 확인할 수 있다.

  2) 관련 값

    - handler_read_first : 인덱스로부터 읽혀진 처음 엔트리 수, 이 값이 높으면 서버는 많은 full index

      scans를 하고 있다는 것을 의미한다. 예를 들어 select col1 from foo 는 col1은 인덱스되었다는

      것을 추정한다.

    - handler_read_rnd : 이 값이 크면 모든 테이블을 스캔하는 많은 쿼리가 있다거나 key를 적절히

      사용하지 않는 조이들이 있을지 모른다.

  

5. 인덱스 버퍼 설정

  1) 설명: 인덱스블럭에 사용되어지는 버퍼사이즈

  2) 관련 값

    - key_buffer_size = 8,388,600 M

    - key_blocks_used = 2947 (읽기전용)

    - key_read_requests = 173351 (읽기전용)

    - key_reads = 2226 (읽기전용)

    - key_wirte_requests =1027 (읽기전용)

    - key_writes =772 (읽기전용)

  3) 최적화 방법

    - key_reads/key_read_requests 율이 보통 0.01보다 작아야 한다.

      update ,delete 를 대부분 사용한다면 key_write/key_write_requests 가 1에 가까워지는게

      일반적이고 동시에 update을 많이 하거나 delay_key_write 를 사용한다면 이 비율은 작아진다.

      key_blocks_used * 1024 면 충분하다.

  4) 최적화 결과

    - key_buffer_size > 3.017728 M (key_blocks_used * 1024) 설정하면 된다.

      현재 설정된 값을 그대로 사용한다.


6. SQL 정렬 버퍼 설정

  1) 설명: 더 빠른 order by group by 수행을 위해서 이 값들을 증가시켜라

  2) 관련 값

    - max_connections = 200

    - sort_buffer = 2.097144 M

    - record_buffer = 131.072 K (연속적인 데이타 스캔)

    - record_rnd_buffer = 131.072 K (정렬된 순서로 데이터 스캔)

    - handler_read_key : 키가 존재하는 ros를 일는 요청수, 이 값이 높으면 테이블이 적절히 인덱스

      되었다는 되었다는 것을 말해준다.

    - max_sort_length = 1024 ( BLOB,TEXT 테이타를 정렬할 때 사용됨)

    - sort_rows : 정렬된 레코드 수

    - sort_scan : 테이블 스캔에 의해 행해진 정렬 수

  3) 최적화 방법

    - good_sort_buffer_size = (sort_buffer + record_buffer ) * max_connections 라고 정의한다.

      이 값이 실메모리크기보다 크지 않도록 한다.

  4) 최적화 결과

    - 현재 good_sort_buffer_size 값이 445.343200 M 이므로 실메모리값(2G)보다 크지 않으므로 현재

      설정된 값을 그대로 사용하면 된다.

  

7. SQL 쿼리 모니터링

  1) 설명: 서버에 보내는 SQL쿼리에 문제는 없는지 확인할 수 있다.

  2) 관련 값

    - Questions : 서버에 보낸 쿼리수

    - query_buffer_size = 0 (no limit : 쿼리버퍼의 초기할당, 대부분의 쿼리가 길다면 이 값을 증가시킨다.)

    - long_query_time = 10 (일정 시간 이상 실행되는 쿼리 기준 시간)

    - log_long_queries : long_query_time 이상 실행된 쿼리를 로그에 남김

    - slow_queries : long_query_time 보다 더 많은 시간이 걸리는 쿼리수. 이 값은 0이면 좋다.

    - table_locks_immediate : 즉시 획득된 테이블 lock 시간

    - table_locks_waited : 즉시 획득되지 않고 기다림이 필요한 테이블 lock 시간

      이 것이 높아지면 성능에 문제가 있으므로 먼저 쿼리를 최적화 시키고 , 테이블을 분산시키거나

      복제를 사용해야 한다.

  

8. 테이블 JOIN

  1) 관련 값

    - select_full_join : 키없이 조인된 수(0이 되어야 한다.)

    - join_buffer_size = 131072 ( 인덱스를 사용하지 않는 full조인에 사용되는 버퍼사이즈)

    - max_join_size = 4.294967295G ( where절이 없고 오래걸리는 조인과 많은 레코드를 반환하는

      유저가 있으면 적절히 설정)

  

9. 테이블 생성

  1) 관련 값

    - tmp_table_size = 33.554432 M ( 메모리안에 임시테이블이 이 사이즈를 초과하면 자동적으로

      임시테이블을 디스크에 저장한다.)

    - created_tmp_tables : 메모리에 생성된 임시테이블 생성수

    - created_tmp_disk_tables :디스크에 존재하는 임시테이블 수

    - created_tmp_files : 임시파일 생성수

  2) 최적화 방법

    - created_tmp_disk_tables 값이 크면 tmp_table_size 값을 증가시켜야 한다.

  3) 최적화 결과

    - tmp_table_size = 100M


10. 쓰레드 관련 모니터링

  1) 관련 값

    - thread_cache_size = 0 (컨넥션이 이루어질 때 캐쉬에 있는 쓰레드를 사용하지 말고 새루운

      쓰레드를 생성하여 사용하도록 강제)

    - thread_stack = 65536

    - threads_cached : 쓰레드 캐쉬에 있는 쓰레드 수

    - threads_connected : 현재 오픈된 연결수

    - threads_created : 연결을 다루기 위해 생성된 쓰레드 수

    - threads_running : sleeping하지 않는 쓰레드 수

    - slow_launch_time : 쓰레드 생성시간이 이 값보다 길면 slow_launch_threads 수치는 증가

    - slow_launch_threads

    - delayed_insert_threads : 사용중인 insert_handler thread 가 지연되고 있는 수

    - delayed_writes : insert delayed 로 쓰여진 rows 수

    - delayed_errors : 에러로 쓰여진 rows수

    - handler_delete : 테이블로부서 지워진 rows수

  2) 최적화 방법

    - thread_created 값이 크면 thread_cache_size 값을 증가시켜야 한다.

    - 매우 바쁜 MYSQL 서버가 아니면 0 또는 2값 정도면 충분하다.

    - thread_created / connections 율이 1% 정도면 이 값을 증가시킬 필요가 있다.

  3) 최적화 결과

    - thread_cache_size = 2


11. 보안관련 설정

  1) 관련된 값

    - safe_show_database = OFF(유저에게 권한이 없는 데이터베이스에 대해 보여줄지 말지에 대한 설정 )

    - skip_show_database = OFF(process_priv권한이 없는 유저에게 show databases 를 못하게 한다. )

  

12. MYSQL데몬 구동 방법

    net stop mysql

    mysqld-nt --default-character-set=euc_kr -O back_log=50 -O max_connections=200 -O

    max_connect_err=300 -O table_cache=350 -O tmp_table_size=100M -O wait_timeout=30 -O

    interactive_timeout=1800 -O thread_cache_size=2 &

  

13. 기타

    MYSQLADMIN 툴을 이용해서 서비스 모니터링하는 방법 소개

    -MYSQL 서버가 살아있는지 확인하는 방법

      mysqladmin -u root -p ping

    - MYSQL 쓰레드 목록보기

      mysqladmin -u root -p proc stat(=processlist)

    - MYSQL 서버환경설정 확인

      mysqladmin -u root -p variables(※mysql>show valiables)

      mysqladmin -u root -p extended-status(※mysql>show stauts)

    - (팁) 배치파일로 연속해서 확인하는 방법은 다음과 같다.

      checking.bat이라는 파일을 만들어서 다음과 같이 작성한다.

1
2
3
4
:loop
mysqladmin -u root -p proc stat(=processlist)
pause
go loop


Lim min taek(c_min2000@yahoo.co.kr)

2005-10-08