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


  1. MYSQL 미 해결 과제 : Can't connect to MySQL server on 'localhost'(10055)

  2. php로 이미지를 mysql디비 저장하고 보여주는 소스

  3. mysql 날짜타입에 기본값으로 현재시간넣기

  4. mysql 재시작, 사용자 추가 명령어

  5. MySQL TABLE 손상시 대응방법(db 내용이 안 나올때)

  6. php 에서 mysql 제어하기

  7. mysql 접속에러시 재부팅 하는 배치파일

  8. MYSQL 최적화

  9. MYSQL Qcache 값 조정~~~

  10. mysqli로 DB 검색 조회

  11. 서버 최적화 시키기(APACHE, PHP, MYSQL, XE 압축&캐싱&버퍼)

  12. 우린친구블로그 서버 다운해결방법과 MYSQL 실행 안될 때 복구방법

  13. 램드라이브를 이용한 mysql 번개속도로 개선방법

  14. [mysql] Created tmp disk tables 디스크 쓰기방지 대안

  15. innodb_use_sys_malloc에 따라 The InnoDB memory heap is disabled mysql error 메시지

Board Pagination Prev 1 Next
/ 1

http://urin79.com

우린친구블로그

sketchbook5, 스케치북5

sketchbook5, 스케치북5

나눔글꼴 설치 안내


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

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

설치 취소