[MySQL 모니터링 및 엔진 최적화]일반적인 MySQLD 옵션

MySQL의 기본 설정 파일

 

 - 총 5개의 기본설정 파일(windows환경에서는 확장명이 ini임)

    my-small.cnf : 64MB이하의 메모리를 시스템 설정

    my-medium.cnf : 128MB 시스템 설정

    my-large.cnf :  512MB이하 시스템

    my-huge.cnf : 1-2GB 시스템

    my-innodb-heave-4G.cnf : 4GB이상 메모리상에서 InnoDB를 사용하는 경우(주로사용)

 

 - 저장 위치

   Linux/Unix : /etc/(1순위), 설치경로(2순위), 데이터디렉토리(3순위) 뒤쪽의 것이 오버라이트함

   Windows : Windows디렉토리(1순위), 설치경로(2순위), 데이터디렉토리(3순위) 뒤쪽의 것이 오버라이트함

 

 

MySQL의 파라메터 조정

 

 - 옵션중 일부는 동작중 변경 가능

    SESSION : 조정된 값이 현재 커넥션에서만 영향을 미침

    GLOBAL : 조정된 값이 전체 서버에 영향을 미침

    BOTH : 값을 변화시킬때 SESSION/GLOBAL을 반드시 명기해야함

 - 동작중 변경값은 기동이 종료되면 소실됨

 - 모든 옵션의 현재값은 아래 커맨드로 확인가능

SHOW SESSION/GLOBAL VARIABLES

 

 

MySQLD 옵션의 변경

 

 - 변경된 옵션은 전체 서버에 적용됨

 - 대부분의 옵션은 동작중에 아래와 같이 변경 가능(대부분 super권한을 가져야함)

mysql>SET GLOBAL parameter = value

 - 세션별로 변경가능한 옵션은 아래와 같이 적용 가능

mysql>SET SESSION parameter = value

 

 

주요 GLOBAL옵션

 

 - table_cache(기본 64)

    사용하는 테이블에 대한 핸들러를 캐시에 저장,

    동시테이블 사용량이 높으면 높임

 

 - thread_cache(기본 0)

    재사용을 위해서 보관해야할 쓰레드수,

    클라이언트가 커넥션풀을 사용할 경우 의미없음

 

 - max_connections(기본100)

    허용가능한 최대 접속수

    함부로 늘려서는 안됨(각 커넥션의 사용할 메모리 양의 총합이 동접 증가수만큼 늘어날 수 있으므로)

 

 

커넥션 관련 옵션

 

 - Connect_timeout

     connection접속후 요청 후 대기 시간

 - net_buffer_length

     MySQL이 전송하는 초기 메시지의 크기, 기본값 사용 권장

 - max_allowed_packed

     서버/클라이언트간 최대 전송 가능 패킷 크기(디폴트 2M)

     TEXT나 BLOG컬럼이 있는 것우 또는 리플리케이션을 사용하는 경우에는 최소 16M권장

 - back_log

     커넥션이 대량으로 몰리는 경우 대기 가능한 커넥션의 수, 기본값 사용 권장

 

 

커넥션 관리

 - max_connections : 최대 접속수

 - inactive_timeout : 유저와 상호작용을 하는 커넥션 타임아웃, 클라이언트 툴 등에서 접속중 유효시간

 - wait_timeout : 일반적인 서버,클라이언트 환경에서 타입아웃시간.(close를 안하는 경우 문제가 될수 있으므로 적절하게 조절해야함)

 - net_read_timeout/net_write_timeout : 클라이언트의 네트워크를 통한 읽기/쓰기 타임아웃, 기본값 사용 권장

 - net_retry_count/max_connect_error : 통신이 잘못되었을때 몇번만에 블럭되는지 지정, 블럭이 되면 FLUSH HOST명령전에는 접근불가. 최대한 높은 값으로 설정해놓음

 

 

Table Cache 최적화

 - table_cache값을 올리면 OS상의 file descriptor의 수를 증가해줘야함

 

테이블 스캔 성능의 향상

 - 결과 값을 찾기 위해 모든 row를 탐색해야하는 경우에 사용되므로 성능에 큰 영향을 미침

 - 테이블 스캔 디스크 엑세스 감소를 위해 read_buffer를 사용

 - read_buffer_size는 기본 128Kb이고 높일 수록 테이블 스캔 성능이 올라감

 - 테이블 스캔하는 모든 쓰레드에 적용되므로 너무 높이게 되면 메모리 자원에 문제가 발생할 수 있음

 - 풀 테이블 스캔을 사용하는 쿼리 앞단에서 올리고 종료히 내려주는 방법도 사용가능

 

조인 성능의 향상

 - 조인되는 컬럼의 인덱스가 존재하지 않는 경우 조인버퍼를 사용

 - 인덱스를 추가하는 것이 바람직하나 임시적으로 join_buffer_size를 높여주는 것도 가능함

 - 두 테이블간 조인일 경우 하나의 join_buffer가 추가되지면 테이블이 추가될 수록 버퍼수도 늘어남

 

정렬 성능의 향상

 - 대량의 정보를 OrderBy하거나 GroupBy를 처리하게되는 경우 디스크 자원 사용

 - 이러한 작업을 메모리내에서 처리하기 위해 sort_buffer_size를 조절

 - 적절하게 사용하여 디스크를 사용하지 않고 메모리버퍼를 쓰게되면 25%정도의 성능향상 가능

 - sort_buffer에 데이터를 정렬한 후 실제 결과값을 처리하는 것은 read_rnd_buffer_size의 영향을 받음

 - 세션별로 설정해서 임시적으로 사용하는 것을 권장

 

Query Cache

 - SELECT 쿼리와 그 결과를 저장

 - 목적 : 빈번하게 사용되는 SELECT쿼리의 성능 향상

 - 테이블에 변화(INSERT,UPDATE,DELETE)가 일어나게 되면 해당테이블과 관련된 쿼리 캐시내의 쿼리는 초기화

 - Query_cache_size 환경 변수를 통해서 조절(기본은 비활성화)

 - SHOW STATUS LIKE 'Qcache_%' 커맨드로 쿼리 캐시 관련 항목 모니터링

 - RESET QUERY CACHE 커맨드를 통해 수동으로 캐시 삭제 가능

 

 

Query Cache 최적화

 - Query_cache_limit 를 통해서 저장할 쿼리의 사이즈를 제한할 수 있음

 - Query_cache_min_res_unit(blocksize)를 설정하여 쿼리 캐시의 조각화를 줄일 수 있음, 기본4Kb

 - 남는 블록이 있음에도 불구하고 캐시 된 쿼리가 제거된다면 Qcache_free_blocks, Qcache_lowmen_prune을 참고

 - Query_cache_hits와 Com_select를 비교하여 캐시 적중률을 파악할 수 있음

[MySQL 쿼리 및 인덱스의 이해]인덱스 사용 전략

인덱스 사용전략
 - Mysql> SHOW INDEX FROM index_name \G
 
 - 필드 해석
   Table : 테이블명
   Non_unique : 중복이면 1 아니면 0
   Key_name : 인덱스에 할당된 키 이름
   Seq_in_index : 멀티컬럼인덱스일 경우 순서
   Column_name : 컬럼이름
   Collation : 인덱스의 정렬방식 A(Asc), D(Desc-지원안함) 이나
   Cardinality : 인덱스에서 유니크한 값의 개수. 값이 낮다는 것이 중복이 많다는 것(예:성별은 남,여로 2)
   Sub_part : 컬럼이 부분적으로 인덱싱 되었을때 길이
   Packed : 인덱스의 압축여부. 압축하지 않았을때 Null
   Null : 컬럼이 Null값을 가질수 있으면 Yes, 아니면 NO
   Index_type : 인덱스의 타임(RTREE,FULL TEXT,HASH,BTREE)

 

 

인덱스와 관련된 로그
  - 로그를 통해서 추가적인 정보획득 가능
  - "--log_queries-not-using-indexes" 슬로우 쿼리 로그에 인덱스를 사용하지 않은 쿼리 로그를 남김
  - 슬로우 로그사용시 "-log-slow_queries"옵션을 통해서 활성화 해야함

 

MyISAM의 인덱스 구조
 - PrimaryKey : Leaf 노드에는 ROW Number가 들어 있다
 - Secondary Index : Leaf 노드에는 ROW Number가 들어 있다.
 - Key Cache :
    인덱스를 메모리에 저장.
    인덱스를 디스크에서 읽을때보다 10배빠름.
    없을경우 시스템 캐쉬사용.
    전체물리메모리의 512M-1G정도 지정.
   

InnoDB의 인덱스 구조
 - 내부적으로 Clustered인덱스라는 구조를 통해 저장. 인덱스의 순서에 따라 물리적 데이터 저장.
 - 프라이머리키가 존재하면 : Clustered인덱스
 - 프라이머리키가 없으면 : 유니크 인덱스를 자동으로 지정
 - 프라이머리키, 유니크 없으면 : 자체적으로 rowID라는 6바이트 유니크 컬럼을 생성해서 입력순서에 따라 생성. show index로 보이지 않음
 - Clustered 인덱스 : Leaf노드에 데이터가 저장됨
 - 다른 모든 인덱스는 Leaf노드에 Clustered인덱스 주소를 가짐
 - 어떠한 컬럼이 Clustered 인덱스가 되느냐가 중요 (크기가 크지 않으며 자주사용되는 컬럼을 이용)

 

 

인덱스 선정 절차
 - 해당 테이블 엑세스 유형 조사
 - 대상 컬럼의 선정 및 분포도 분석
 - 반복 수행되는 엑세스 경로의 해결
 - 인덱스 컬럼의 조합 및 순서 결정
 - 시험생성 및 테스트
 - 수정한 필요한 애플리케이션 조사 및 조사
 - 일괄 적용

 

 

인덱스의 선정기준
 - 분포도가 좋은 컬럼은 단독적으로 생성
 - 자주 조합되어 사용되는 경우 결합인덱스
 - 각종 엑세스 경우의 수를 만족할 수 있도록 인덱스간의 역활분담
 - 가능한 수정이 빈번하지 않는 컬럼
 - 기본키 및 외부키(조인의 연결고리 컬럼)
 - 결합 인덱스의 경우 컬럼 순서 주의
 - 반복수행 되는 조건은 가장 빠른 수행속도를 내게 할 것

 

 

인덱스의 활용시 고려사항
 - 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음
 - 지나치게 많은 인덱스는 오버헤드 발생
 - 넓은 범위를 인덱스 처리시 많은 오버헤드발생
 - 옵티마이저를 위한 통계데이터를 주기적으로 갱신
 - 인덱스의 개수는 적절히 생성
 - 분포가 양호한 컬럼도 처리범위에 따라 분포도가 다를수 있음
 - 인덱스 사용원칙을 준수해야 인덱스가 사용되어진다
 - 조인시 인덱스 사용여부에 주의
 

Index Cardinality
 - SHOW INDEX에서 확인 가능
 - 가능하면 높은 값을 유지하도록 하는 것이 좋음
 - 높은 컬럼에 인덱스가 필요한 경우 복합 인덱스를 고려
 

문자형 인덱스 VS 숫자형 인덱스
 - 문자형 인덱스 성능 < 숫자형 인덱스 성능
 - 숫자형 컬럼이 작은 싸이즈

최적의 컬럼 타입 선택을 위한 툴 - Procedure Analyse
 - 사용법
   SELECT * FROM 데이블명 PROCEDURE Analyse(처리할컬럼수)

 

 

다중컬럼사용시 빈번한 실수
 - Index(columnA, columnB) 일 경우 WHERE columnB='ABCD' 일경우 인덱스 사용안함
 - WHERE columnA LIKE '%rane' 일 경우 와일드카드가 앞에 있으므로 인덱스 사용안함
 - ORDER BY columnB, columnA 일 경우 인덱스 사용안함