Skip to content
컴퓨터잡담
2009.11.13 17:43

MYSQL 최적화

조회 수 22447 추천 수 0 댓글 2
?

단축키

Prev이전 문서

Next다음 문서

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

단축키

Prev이전 문서

Next다음 문서

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

MYSQL 최적화


동시 접속자수 설정

1) 관련 값

TAG •
로그인 후 댓글쓰기가 가능합니다.

?
  • ?
    디케 2009.11.16 18:45

    http://0903.tistory.com/51



    1. mysql은 동적으로 변화 가능한 옵션은 3가지로 나눌 수 있다.
    - session : 조정된 값은 오직 현재 커넥션에만 영향을 미친다.
    - global : 조정된 값이 전체 서버에 영향을 미친다.
    - both : 값을 변화시킬 때 session/global을 반드시 명기해야 한다.


    mysql> show global variables like 'query%';
    +------------------------------+---------+
    | Variable_name                | Value   |
    +------------------------------+---------+
    | query_alloc_block_size       | 8192    |
    | query_cache_limit            | 1048576 |
    | query_cache_min_res_unit     | 4096    |
    | query_cache_size             | 0       |
    | query_cache_type             | ON      |
    | query_cache_wlock_invalidate | OFF     |
    | query_prealloc_size          | 8192    |
    +------------------------------+---------+
    7 rows in set (0.00 sec)

    mysql> show session variables like 'query%';
    +------------------------------+---------+
    | Variable_name                | Value   |
    +------------------------------+---------+
    | query_alloc_block_size       | 8192    |
    | query_cache_limit            | 1048576 |
    | query_cache_min_res_unit     | 4096    |
    | query_cache_size             | 0       |
    | query_cache_type             | ON      |
    | query_cache_wlock_invalidate | OFF     |
    | query_prealloc_size          | 8192    |
    +------------------------------+---------+
    7 rows in set (0.00 sec)


    2.  변경된 옵션 적용하기
    set global(또는 session) parameter =value
    - 동작 중에 변경된 옵션은 서버 재 시작 전까지미만 적용된다.
    - 영구히 변경하려면, /etc/my.cnf 파일을 수정해야 한다. 
    - super 권한이 있어야 바꿀 수 있다.

    3. 주요 옵션 (show varialbles like '...%' 이용)
    (1) table_cache (디폴트 64): 사용하는 테이블에 대한 핸들러를 캐시에 저장, opend_tables 항목이 높을 경우 table_cache를 높인다.
    (2) thread_cache_size(디폴트 0) : 재 사용을 위해 보관할 쓰래드 수, thread_created가 높을 경우 증가된다. 클라이언트가 커넥션 풀을 쓰는 경우는 의미가 없다.
    (3) max_connections(기본 100) : 허용 가능한 최대 동시 접속수, 쓰레드 관련 메모리 튜닝시 매우 중요, 각각의 커넥션은 최소 thread_stack의 사이즈만큼의 메모리 사용
    (4) connect_timeout  : connect요청받고 몇초간 기다릴지
    (5) net_buffer_length : 클라이언트에 보내는 초기 메시지의 바이트 수 조절한다.
    (6) max_allowd_packet : 서버 / 클라이언트간 최대 전송 가능 패킷 크기를 설정한다., Text나 bloc컬럼이 많이 있거나, 리플리케이션을 하는 경우 최소 16M이상 잡아야 한다. 
    (7) back_log (디폴트 50) : connection이 대량으로 몰릴 때 큐에 대기가능한 커넥션의 갯수
    (8) wait_timeout : 커넥션의 타임아웃
    (9) net_retry_count : 통신이 잘못되어 실패할 때, 몇 번까지 재시도할지
    (10) max_connect_error : 계속적으로 문제를 발생시키는 클라이언트를 몇번째 재시도후 block할지 지정. 한번 block되면, 서버를 재시작하거나 flush host명령을 실행하기 전까지 접속 불가 일반적인 경우.. 내부에서만 쓸 경우가 많아서 크게 잡아놓음
       나의 경우는 다음과 같이 지정
    max_connections를 1000, 이때  thread_stack은 196608, thread_cache_size는 4.
    connect_timeout : 5,  max_allowed_packet : 33,553,408(리플리케이션 적용) , back_log는 50
    max_user_connections : 0 ,  max_connect_errors  99999999


    3. 주요 환경변수 (show staus like 'thread..%' 이용)
    (1) Connection관련
    - Max_used_connections (720) : 피크 타임의 동시 접속 수, 튜닝의 제일 중요한 요소임
    - Bytes_received (3656293909) / Bytes_sent (1293843766) : 모든 클라이언트와 전송된 바이트의 총합
    - connections( 2045524 ) : 시도된 connection의 합
    - Aborted_connects (40979) : 어플리케이션 또는 mysql의 커넥션 관련 옵션이 잘 못 될 수 있다.
    (2) Thread 관련
    -thread_created:서버 시작후, 현재까지 만들어진 총 쓰레드의 갯수
    -thread_connected: 현재 열려있는 쓰레드수
    -thread_cached : 재사용 가능한 동작 중이지 않은 쓰레드
    -thread_running : sleeping 상태가 아닌 쓰레드 수
     [master]
    | Threads_cached    | 43    | 
    | Threads_connected | 23    | 
    | Threads_created   | 66    | 
    | Threads_running   | 3     | 
     [slave]
    | Threads_cached    | 120   | 
    | Threads_connected | 383   | 
    | Threads_created   | 1681  | 
    | Threads_running   | 1     | 
    - slow_launch_threads :쓰래드 생성에 걸린 시간이 slow_launch_time 이상 걸린 쓰래드의 수. Slow_launch_time은 기본 2초이다. 0에 가까와야 좋은 것이다. 내 db는 0이군.
     *쓰레드 캐쉬의 적중률은  threads_created/connections로 계산 가능 
    A DB(캐슁용) : 1681/1505789 : 0.1%의 쓰레드 캐쉬의 적중률을 보여준다 (현재 thread_cache_size : 4)
    B DB(어드민용) : 179348 / 2046107  : 8.7%의 쓰레드 캐쉬의 적중률을 보여준다 (현재 thread_cache_size : 128 )
    =>thread_cache_size나 높여볼까나? 확실히 thread_cache_size가 높은 것이 훨씬 높다.
    (3) Handler
    일반적인 해석
      - handler_read_first가 높은 경우  많은 풀 인덱스 스캔이 이루어짐
      - handler_read_next가 높은 경우  풀 인덱스 스캔과 레인지 스캔이 이루어짐
      - handler_read_rnd가 높은 경우  많은 풀 테이블 스캔과 레인지 스캔이 이루어짐
      - handler_read_key가 높은 경우  인덱스를 읽는 경우가 많음
     [A DB(캐슁용)] => 별 튜닝 요소가 없다.
    | Handler_commit             | 0     | 
    | Handler_delete             | 0     | 
    | Handler_discover           | 0     | 
    | Handler_prepare            | 0     | 
    | Handler_read_first         | 0     | 
    | Handler_read_key           | 0     | 
    | Handler_read_next          | 0     | 
    | Handler_read_prev          | 0     | 
    | Handler_read_rnd           | 0     | 
    | Handler_read_rnd_next      | 19    | 
    | Handler_rollback           | 0     | 
    | Handler_savepoint          | 0     | 
    | Handler_savepoint_rollback | 0     | 
    | Handler_update             | 0     | 
    | Handler_write              | 25    |
     [B DB(어드민용)] =>튜닝 요소가 많다.  많은 풀 테이블 스캔과 레인지 스캔이 이루어지는 것 같다.
    | Handler_commit        | 199415     |
    | Handler_delete        | 269322     |
    | Handler_discover      | 0          |
    | Handler_read_first    | 67565      |
    | Handler_read_key      | 13910141   |
    | Handler_read_next     | 429593667  |
    | Handler_read_prev     | 2196569    |
    | Handler_read_rnd      | 372568     |
    | Handler_read_rnd_next | 2211096685 |
    | Handler_rollback      | 1415609    |
    | Handler_update        | 595584     |
    | Handler_write         | 2026479    |

     

    4. 성능
    (1) MySQL의 느린 응답을 나타낸다. => slow_queries와 slow_launch_threads
    (2) 부하가 심하다는 것을 나타낸다 =>   threads_created,  max_used_connections,  opend_tables이 큰 경우 (이 경우 table_cache를 올리자)
    (3) 인덱스를 많이 읽는다. => handler_read_key가 높은 경우 
    (4) 락 경쟁과 관련된 항목 =>  table_locks_waited VS table_locks_immediate , 만약 table_locks_waited가 높으면, myisam에서 innodb로 변경하는 것이 좋다.
    A DB(캐슁)
    | Table_locks_immediate | 20498565 | 
    | Table_locks_waited    | 0        | 
    B DB(어드민) 
    | Table_locks_immediate | 9500191 |
    | Table_locks_waited    | 12509   |
    (5) 메모리에 적용할 수 없는 큰 임시 테이블이 많이 만들어졌다 => created_tmp_disk_tables 
    =>  tmp_table_size를 올려서 과도한 DISK I/O가 발생하는 것을 막는다. 즉, 디스크를 안쓰고, 메모리를 쓰도록 한다.
    A DB(캐슁) |Created_tmp_disk_tables | 0   (tmp_table_size | 67108864 )
    B DB(어드민)| Created_tmp_disk_tables | 107  ( tmp_table_size | 33554432 )
    (6)  select쿼리가 최적화 되지 못했다 => Select_xxx
    => select_full_join과 select_range_check는 많거나 다른 인덱스가 필요하다.
    (6) sort_xxx 환경변수들의 값이 크다 => 쿼리에 따라 ordering 및 grouping 비용 크다. 
    => sort_buffer_size가 크게 설정한다. 25%이상의 성능 향상을 기대할 수 있다. sort_buffer에 데이터를 정렬 한 후 실제 결과값을 처리하는 것은 read_rnd_buffer_size에 영향을 준다.
      

    5. 최적화
    (1) Table Cache 최적화
    - table_cache 값을 올릴 때, mysqld가 필요로 하는 file descriptor의 수가 증가한다.
    - MyISAM 엔진을 사용하는 경우 열려있는 각각의 테이블에 대해 하나의 file descriptor가 필요하게 되고, 게다가 인덱스 파일을 위해 하나의 file descriptor가 더 필요하다.
    - 대부분의 운영체제에서는 프로세스당 사용할 수 있는 file descriptor의 수가 제한되는데, 이를 위해서 ulimit을 이용해서 file descript를 최대한 unlimited로 수정하는 것이 좋다.
    - Opend_table 환경변수가 높을 때, 그리고 FLUSH TABLE을 자주 수행하지 않는다면 table_cache를 높이는 것이 좋다.
    (2) Table Scan 최적화
    - 디스크 억세스를 줄이기 위해 read_buffer가 사용된다.
    - read_buffer_size는 기본 128Kb의 크기를 가지고, 높을 수록 테이블 스캔의 성능을 높여준다.
    - 높여주되 지나치게 큰 값을 잡는 것은 좋지 않다. 기본적으로 테이블 스캔을 수행하는 모든 쓰래드에 할당될 수 있다. 
    (3) Join 
    - 조인되는 컬럼에 인덱스가 존재하지 않을 경우 성능이 매우 떨어진다. 
    - 인덱스를 추가할 수 없는 경우 join_buffer_size를 높인다.
    - 두 테이블 간에 풀 조인이 일어날 경우 하나의 join_buffer가 할당되고 인덱스가 적용되지 않는 조인의 테이블 수가 늘어나는 만큼의 join_buffer가 할당된다.
    (4) querty cache
    select 쿼리와 그 결과를 저장한다. Query_cache_size를 클 수록 쿼리 캐시 싸이즈도 커진다. 하지만, 테이블이 바뀌면, Query cache는 모두 reset된다.
    query_cache_limit을 조정함으로써 쿼리 캐시에 저장될 최대 쿼리 크기를 조절할 수 있고, Query_cache_min_res_unit(block size)를 설정하여 쿼리 캐시의 조각화를 줄일 수도 있다. 기본값은 4K이다. 
    Qcache_hits 와 Com_select를 비교하여 쿼리 캐시 적중률을 계산하여 성능을 최적화 할 수 있다

  • ?
    디케 2009.11.17 09:08

    MySQL의 호스트 블럭킹 & max_connect_errors



    MySQL Server가 동작중인지 원격에서 검사할 때 유의할 점이다.
    원격 서버의 MySQL 3306 포트가 살아있는지 검사할 때 단순히 커넥션을 한 후 close하게 되면
    MySQL은 비정상적인 접속으로 판단하여 해당 IP를 블럭킹할 수 있다.

     
    # telnet 192.168.1.2 3306
    Trying 192.168.1.2...
    Connected to 192.168.1.2.
    Escape character is '^]'.
    }너무 많은 연결오류로 인하여 호스트 '192.168.1.1'는 블락되었습니다. 'mysqladmin flush-hosts'를 이용하여 블락을 해제하세요
    Connection closed by foreign host.
     


    MySQL은 비정상적인 접속에 대한 요청수를 카운트를 하는데 max_connect_errors 변수에서 지정한 값을
    넘으면 블럭킹을 한다. 기본 값은 10이며, 정기적인 포트 점검이 필요한 경우 이 수를 높여라.

    다음은 MySQL Manual에 나온 글이다.

     
    A.2.5 Host 'host_name' is blocked

    If you get the following error, it means that mysqld has received many connect requests from the
    host 'host_name' that have been interrupted in the middle:

    Host 'host_name' is blocked because of many connection errors.
    Unblock with 'mysqladmin flush-hosts'

    The number of interrupted connect requests allowed is determined by the value of the
    max_connect_errors system variable. After max_connect_errors failed requests, mysqld assumes that
    something is wrong (for example, that someone is trying to break in), and blocks the host from
    further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS
    statement. See section 5.2.3 Server System Variables.

    By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting
    the server like this:

    shell> mysqld_safe --max_connect_errors=10000 &

    If you get this error message for a given host, you should first verify that there isn't anything
    wrong with TCP/IP connections from that host. If you are having network problems, it will do you no
    good to increase the value of the max_connect_errors variable.
     


    * 참고
      A.2.5 Host 'host_name' is blocked
      http://dev.mysql.com/doc/mysql/en/Blocked_host.html

      5.2.3 Server System Variables
      http://dev.mysql.com/doc/mysql/en/Server_system_variables.html


List of Articles
번호 분류 제목 날짜 조회 수
837 AutoHotKey autohotkey) postmessage mouse control 13 2012.02.22 28627
836 컴퓨터잡담 DIV 라운드박스 쉽게 만들자. 1 1 file 2009.07.24 28591
835 컴퓨터잡담 트랜지스터의 종류와 특정 2013.10.12 28522
834 WindowsTip 네트워크 무선연결이 안될 때의 점검 방법 file 2013.03.12 28251
833 컴퓨터잡담 php 에서 mysql 제어하기 2009.11.28 28199
832 컴퓨터잡담 MySQL FEDERATED / InnoDB is disabled, myint64.dll 오류 어찌하오리 4 2010.04.19 27705
831 AutoHotKey [COM] 자바스크립트 / DOM / HTML 웹페이지 컨트롤 3 2011.02.12 27281
830 프로세스 Searchindexer.exe 제거하기 1 2011.03.18 27259
829 컴퓨터잡담 mysql 접속에러시 재부팅 하는 배치파일 2009.11.24 26667
828 Excel [excel] GET.CELL사용법 8 2012.07.16 26573
827 컴퓨터잡담 부팅시마다 체크디스크 실행되는 경우 설정방법 2010.08.21 26563
826 AutoHotKey [AUTOHOTKEY] FTP 제어 file 2011.02.04 25246
825 Excel 엑셀) 피벗테이블 원본데이터 영역범위 수정방법 1 2 file 2012.03.06 25071
824 컴퓨터잡담 무선공유기(AP) 채널간섭 해결하기 file 2013.08.17 25044
823 컴퓨터잡담 Excel VBA (1): 셀 선택 및 변수 및 비활성시트 컨트롤하기 3 2011.10.24 24995
822 컴퓨터잡담 자바스크립트 변수를 php로 옮기기 2010.02.03 24959
821 WindowsTip Windows-XP 의 [Prefetch] 폴더에 대하여[C:\WINDOWS\Prefetch] 2013.12.04 24630
820 컴퓨터잡담 인터넷 익스플러러 속도 향샹을 위한 팁 1 file 2012.02.25 24571
819 컴퓨터잡담 티맥스OS 무료배포도 판매 수익의 세배이상 가능하다 2 2009.07.26 24565
818 Excel 현재 Excel 파일 이름을 셀에 삽입 2 2012.09.17 24551
Board Pagination Prev 1 ... 3 4 5 6 7 ... 46 Next
/ 46

http://urin79.com

우린친구블로그

sketchbook5, 스케치북5

sketchbook5, 스케치북5

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소