[MySQL 모니터링 및 엔진 최적화]InnoDB 스토리지 엔진 최적화

InnoDB의 옵션의 개요

 

 

InnoDB의 메모리 관련 옵션

 - Innodb_buffer_pool_size

    가장 중요한 옵션

    데이블의 데이터와 인덱스를 캐싱하기 위해서 사용

    사이즈가 클수록 성능이 향상됨

    OS Cache보다 훨씬 효율적으로 메모리를 사용하며 Write성능에 큰 영향을 미침

    서버메모리 용량의 70~80%정도로 설정하는 것이 적당

    기본값은 8MB이며 반드시 재설정 필요

 - Innodb_additional_mem_pool

    DataDictionary(테이블 스키마 등)를 저장하기 위해서 사용. 필요한 경우 자동으로 증가

 

InnoDB의 로그 관련 옵션

 - Innodb_log_file_size

    InnoDB redo로그 파일 크기

    Write 성능에 매우 큰 영향을 미침

    설정 파일 크기에 따라 복구 시간이 증가될수 있어서 256M 사용권장

 - Innodb_log_files_in_group

    로그 그룹안에 포함될 로그 파일 수

    기본적으로 2이며 3을 권장함

 - Innodb_log_buffer_size

    매우 큰 BLOB를 사용하지 않는한 2-8M의 기본값 사용

 

InnoDB log flush 주기 조절

 - Innodb_flush_log_at_trx_commit(기본값은1)

    0으로 설정하면 1초에 한번씩 디스크에 기록하고 씽크 - MySQL,시스템다운시 1시간 데이터 누락가능성 있음

    1로 설정하면 commit시 디스크에 기록하고 씽크 - 어떤 다운시에도 데이터 유지

    2로 설정하면 commit를 할때마다 디스크에 기록하고 싱크는 1초에 한번만함 - 시스템 다운시 1초간 데이터 누락

 

InnoDB의 로그 사이즈 재조정

 - 일반적인 옵션처럼 수치만 변경해서 조절할 수 없음

    MySQL종료

    Data 디렉터리 안의 ib_log* 파일 삭제

    설정의 innodgb_log_file_size 수정

    MySQL 재시작

 

InnoDB의 flush 방법 설정

 - InnoDB가 OS의 FileSystem과 연동하는 방식 설정

 - 윈도우에서는 unbufferedIO가 늘 사용됨

 - UNIX에서는 fsync(), O_SYNC/O_DSYNC를 파일 flush를 위해 사용가능

 - 리눅스에서는 O_DIRECT를 사용하여 unbufferedIO를 사용할 수 있다 (double buffering을 막아줌)

 

InnoDB의 테이블 별 테이블 스페이스

 - Innodb_file_per_table 옵션이 설정 가능

 - 테이블 별로 테이블스페이스를 설정함

 - 테이블 별로 설정해도 공통 테이블스페이스는 필요함

 - 분리시 데이터를 여러개의 디스크로 분산 가능

 - 테이블을 drop하면 디스크의 공간이 반환됨

 - 테이블이 많을 경우 MySQL기동/종료시 속도가 빨라짐

 

그 밖의  InnoDB의 옵션들

 - Innodb_thread_concurrency : 기본값은 8, 동시 사용 쓰레드수로 변경하지 않음

 - FOREIGN_KEY_CHECKS/UNIQUE_CHECKS

    데이터를 입력시 Foreign키와 Unique를 검사하지 않음

    대용량 데이터 입력시 사용함(AUTOCOMMIT을 0으로 하는것도 추천)

 - innodb_fast_shutdown

    종료시 내무 메모리 구조 정리 작업과 버퍼 정리 작업을 건너뜀. 무결성에는 영향없음

  

 

ㅇㄹㅇ

[MySQL 모니터링 및 엔진 최적화]MyISAM 스토리지 엔진 최적화/모니터링

MyISAM의 mysqld옵션

 

 - 전체 mysqld 세팅

    key_buffer_size(기본 8Mb) : 인덱스 캐시, 올리게 되면 성능 향상

    데이터 row에 대한 캐시는 OS에서 핸들링

 

 - 쓰레드별 세팅 : 일반적인 동작에 관계 없음

    Myisam_sort_buffer_size(기본 8Mb)

    Myisam_repair_threads(기본1) - 벌크 임포트와 myisam테이블 복구에 사용

 

 

Key와 관련된 Status 환경 변수

 

 - MyISAM 엔진은 key캐시를 조절하는 것이 성능과 직결됨

 

 - 일반적인 경우 다음과 같은 상황이 바람직함

     낮은 key_reads(물리 디스크를 읽는 것우)

     매우 낮은 key_reads/key_read_request비율(0.03이하)

 

 - 다음방법으로 캐시 사용을 확인

     key_block_used와 key_block_unused는 얼마나 많은 쿼리캐시 공간이 사용중인지 나타냄

     key_cache_block_size로 블록 사이즈를 결정

 

 

key_buffer_size 최적화

 - 값을 높여주면 더많은 메모리를 사용해서 성능이 높아지나 너무 높이게 되면 데이터로딩시 OS캐쉬의 이용을 할 수 없어 성능이 낮아질수 있음

 - 전체메모리의 25%정도로 설정로 보통 512M이하로 설정

 - SHow_status like 'key%' 로 key 사용상황 점검

 

 

대용량 데이터 로딩 및 수리

 - MyISAM_sort_buffer_size

     인덱스 생성에 사용되는 메모리의 양, 대용량 데이터 입력시 성능을 높히기 위해서 할당함

 - Myisam_repair_threads

     1이상으로 설정할 경우 병렬로 인덱스 생성이 가능, 코어캣수만큼 설정, repair시에만 사용됨

 

 

MyISAM모니터링

 

 - MyISAM은 키캐시에 대부분의 성능이 좌우됨

 

 - 키 캐시 쓰기 요청

 - 키 캐시 쓰기

 

 

[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 환경변수

커넥션 관련 환경 변수

 - max_used_connections : 피크 타임의 동시 접속수 (튜닝시 중요)

 - bytes-receved, bytes-sent : 모든 클라이언트와 전송량

 - connection : 시도된 커넥션의 총합

 - aborted_connects : 접속이 끊어진 커넥션의 총 합 (높을 경우 어플리케이션 커넥션정보 확인필요)

 

쓰레드 관련 환경 변수

 - threads_connected : 현재 열려 있는 커넥션 수

 - threads_cached : 재사용 가능한 동작중이지 않은 커넥션 수

 - threads_created : 서버 시작후 현재까지 만들어진 쓰레드 수

 - threads_running : sleeping가 아닌 동작중인 쓰레드

 - slow_launch_threads : 쓰레드 생성시 시간이 2초이상이 걸린 쓰레드의 수 (0에 가까워야함 높아지면 부하가 높아진다는 뜻)

 - threads_created/connections - 캐시 적중률(적중률이 낮으면 cache사이즈를 증가시켜주는것이 좋음)

 

 

핸들러 관련 환경 변수

 - 모든 handler_xxx 환경 변수들은 내부의 테이블 핸들러의 동작 상황에 대한 정보를 제공

 - 핸들러 관련 환경 변수에 대한 일반적인 해석

 - handler_read_first가 높은 경우 -> 많은 풀 인덱스 스캔이 이루어짐 (메모리)

 - handler_read_next가 높은 경우 -> 풀 인덱스 스캔과 레인지 스캔이 이루어짐 (메모리)

 - handler_read_random가 높은 경우 -> 많은 풀 테이블 스캔과 레인지 스캔이 이루어짐 (디스크)

 - handler_read_key가 높은 경우 -> 인덱스를 읽은 경우가 많음(메모리), 좋은 수치

 

 

성능 관련 문제를 보여주는 항목들

 - MySQL의 느린 응답을 나타내는 항목

     slow_queries, slow_luanch_thread

 

 - 부하가 심하다는 것을 나타내는 항목

     thread_created가 큰경우,

     max_used_connections가 큰경우,

     opend_tables가 큰경우(table_cache를 올리는 것이 좋음),

     handler_read_key가 높은 경우

 

 - 락 경쟁과 관련된 항목(MyISAM에서 중요함)

      table_locks_waited VS table_locks_immediate (락획득시 대기/비대기 수치)

      업데이트가 많아지면 Lock경쟁이 높아진다 -> InnoDB로 변경하는 것이 해결책

 

 

쿼리 관련 문제를 보여주는 항목들

 

 - Created_tmp_disk_tables 환경 변수가 큰 경우

    메모리에 적용할 수 없는 큰 임시 테이블이 많이 만들어졌다는 의미

    -> tmp_table_size를 올려줘서 해결(사용에 주의)

 

 - Select_xxx 환경변수의 값이 큰 경우

    select쿼리가 최적화되지 못했음을 의미

    -> select_full_join과 select_range_check는 일반적으로 더 많은 인덱스를 작성해줘야함

 

 - Sort_xxx 환경변수 값이 큰 경우

    sort_merge_passes가 큰 경우 ordering하는 작업비용이 크다는 의미

    -> sort_buffer_size를 늘리거나 인덱스를 추가

[MySQL 모니터링 및 서버 최적화]서버 모니터링

퍼포먼스 모니터링

 

 - OS자체 도구
   리눅스/유닉스 :vmstat, iostat, mpstat
   윈도우 : 작업관리자 성능 탭
  
 - MySQL자체 도구
   기동 후 메모리 상의 성능 수치를 추적
   SHOW STATUS 커맨드로 확인 가능
   Cricket, SNMP 또는 자체 제작 스크립트 사용 가능
   MySQL Administrator 사용 가능(GUI형태)
  
 - 쿼리는 MySQL 로그를 통해서 추적
   General Log :
     일반적으로 사용안함(IO증가량이 높음, 5.0이전은 설정변경시 재기동 필요),
     모든 사용자의 입력을 로깅,
     액션전에 저장되어 유용
   Slow Query Log :
     느린 쿼리에 대한 로그

 

MySQL에서 Thread 모니터링
 - MySQL은 Thread기반 서버
     SHOW FULLPROCESSLIST
     STATE컬럼을 통해 각 쿼리가 현재 어떻게 수행되고 있는지를 확인가능
 - 성능관련 문제는 아래 작업을 통해 확인 가능
     Processlist 모니터링
     SHOW STATUS의 내용을 확인
 - 급박한 문제는 쓰레드 KILL을 통해 제거 가능
     잘못된 쓰레드는 Processlist로 확인
     해당 프로세스 ID를 KILL

 

 

MySQL STATUS

 - 동작 상태에 대한 항목 수집

 - 서버의 현재 동작 상태를 모니터링

 - 서버를 최적화 할때 가이드로 이용

 - Status항목은 아래 두가지 방법으로 확인 가능

설치경로/bin/mysqladmin extended-status
MySQL Administrator - GUI형태로 제공

기본적인 STATUS 모니터링

 - mysqladmin은 기본적인 관리툴 : 10초마다 갱신

설치경로/bin/mysqladmin -i 10 extended-status 

 

 

3rd Party 모니터링 툴 - MyTOP

 - MySQL의 정보를 TOP과 같이 보여주는 툴

 - 전체 쓰레드 리스트를 보여줌

 - Database나 Host별 필터링 가능

 - 커넥션에 대한 kill을 쉽게 수행

 - QPS를 쉽게 확인 가능

 

3rd Party 모니터링 툴 - innotop

 - InnoDB엔진에 대한 Status는 "show engine innodb status"로 확인 가능

 - 그 결과를 해석하기 편하게 정리해주는 툴

 - http://www.xaprb.com/blog/2006/07/02/innotopmysql-innodb-monitor/

 

 

기본 STATUS 항목

 - 서버의 시작후 동작시간은 uptime으로 확인 가능

 - com_xxx항목은 서버시작 이후 xxx관련 명령이 얼마나 수행되었는지 나타냄

 - Questions는 서버로 전송된 총 쿼리 수를 나타냄

 - 이러한 전체 동작 양상과 select와 U/D/I 비율 등을 추출할 수 있음

 

 

 

 

[MySQL Stored Procedure]Stored Program의 보안

Stored Programs에 사용시 필요한 권한
 - CREATE ROUTINE, ALTER ROUTINE, EXECUTE

 

실행모드 옵션 - DEFINER
 - SQL SECURITY DEFINER
   루틴실행이 DEFINER 지정 유저 권한으로 실행됨
   생성자가 SUPER권한을 가졌으면 다른 계정 지정 가능
 - 테이블의 권한이 없어도 프로시져를 통해서 접근이 가능

 

실행모드 옵션 - INVOKER
 - 루틴을 호출하는 사람의 권한으로 실행
 - Invoker 권한으로 실행시에는 권한이 없을 경우 에러 처리 필요

 

Stored Program의 성능은?
 - 연산작업은 처리하지 않는다
 - 통계작업 등은 네트워크 트래픽을 많이 사용하는 경우에는 성능이 좋다
 - Self Join시 단계적 로직을 이용하여 Self Join을 피해서 처리하면 성능향상
 - Update문 안에 SELECT가 있을경우 커서를 이용해서 분리시 성능향상 가능

[MySQL Stored Procedure]View

CREATE VIEW Syntax

  CREATE [OR REPLACE]
  [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
  [DEFINER={USER|CURRENT_USER}]
  [SQL SECURITY {DEFINER|INVOKER}]
  VIEW view_name [(column list)]
  AS select_statement
  [WITH [CASCADE|LOCAL] CHECK OPTION]

 
ALGORITHM 종류
 - UNDEFINED : MySQL이 자체적으로 선택, 대부분 MERGE
 - MERGE : 뷰와 포함된 쿼리를 효율적인 방식으로 Merge해서 사용
 - TEMPTABLE : 뷰와 연결된 임시테이블을 만들고 사용, 인덱스를 전혀 사용 못함

 

 

뷰와 테이블의 차이
 - 뷰는 정의시점에 고정, 테이블에 컬럼이 추가되어도 반영되지 않음
 - SELECT문에서 system이나 user변수 사용 못함
 - 뷰 정의시 임시테이블 참고 못함
 - 트리거를 뷰에 연결시킬 수 없다
 - 뷰정의시 ORVER BY를 포함할 수 있으나 VIEW사용시 ORDER BY를 지정하면 정의된 ORDER BY는 무시됨

[MySQL Stored Procedure]Trigger

트리거 생성 문법

   CREATE DEFINER = {user|CURRENT_USER}] TRIGGER trigger_name
     {BEFORE|AFTER}
     {UPDATE|INSERT|DELETE}
   ON table_name
   FOR EACH ROW
   trigger_statements

 - Definer의 경우 슈퍼권한이 있을 경우 다른 계정이 지정가능
 
컬럼값의 참조
 - NEW : 새로 입력된 값(Insert,Update에서 사용가능)
 - OLD : 삭제된 데이터를 지칭(Delete,Update에서 사용가능)

 

 

BEFORE, AFTER 트리거
 - AFTER의 경우 값의 변경이 불가능

 

Trigger 사용
 - 중요테이블 로깅, 데이터 입력력 트랙킹, 입출력 데이터 검증
 

[MySQL Stored Procedure]Stored Function

Stored Function이란?
 - 하나의 값을 반환하는 stored program
 - OUT, INOUT변수가 아닌 RETURN으로 값을 반환
 - 내장함수와 동일한 형태로 DML에서 사용 가능
 - 복잡한 코딩을 줄여줄 수 있다

 

 

Stored Function만들기
 - Syntax

   CREATE FUNCTION function_name (paramters[,...])
   RETURNS datatype
   [[NOT]DETERMINISTIC]
   [{CONTAINS SQL|NO SQL|MODIFIES SQL DATA|READS SQL DATA}]
   [SQL SECURITY {DEFINER|INVOKER}]
   [COMMENT string]
   function_statement

 - Return절은 필수임
 - 파라메터는 IN으로 처리됨

 

 

Return 문
 - 리턴문은 하나만 있는 것이 좋다(조건문으로 분기시 변수 사용)

 

 

DETERMINISTIC과 SQL절

 - 바이너리 로그를 사용할 경우 반드시 고려해야 함
   바이너리 로그를 사용하는 경우 해당 FUNCTION이 동일한 결과(DETERMINISTIC)를 가지는지 확인해야함(시스템시간등의 사용 유의)
   모든 Stored program을 위한 기본값은 NOT DETERMINISTIC CONTAINS SQL이므로 반드시 명시적으로 표시가 필요
   NOW() 함수 또는 시간을 기반으로 하는 함수와 하나의 랜덤값을 발생시키는 함수는 NON DETERMINISTIC이 아님(사용해도 무방)
   이를 막기 위해서 DETERMINISTIC, NO_SQL또는 READ_SQL_DATA키워드를 정의하거나 log_bin_trust_routine_creater옵션을 1로 설정해야 실행됨

Stored Function 생성시 주의할점

 - 옵티마이저를 사용하지 않으므로 성능 테스트 필요

 - 자주 사용되는 쿼리에는 사용하지 않음

[MySQL Stored Procedure]트랜잭션 관리

Isolation Level

 - READ UNCOMMITED
   Dirty read허용
   속도는 빠르지만 commit 되지 않은 ROW를 다른 세션에서 볼 수 있음

 - READ COMMITED
   Commit된 row만 읽을 수 있음

 - REPEATABLE READ
   트랜잭션이 시작된 시점 기준의 값을 볼 수 있음

   Default 설정

 - SERIALIZABLE
   각 트랜잭션이 독립적으로 동작
   SELECT시에도 락을 사용
 

트랜잭션 관련 Command
 - START TRANSACTION : 시작, AUTO_COMMIT를 0으로 설정
 - COMMIT : 트랜잭션의 변경 내용을 저장, LOCK해제
 - ROLLBACK : 트랜잭션의 변경 취소
 - SAVEPOINT savepoint_name : 저장단계 지정
 - ROLLBACK TO SAVEPOINT savepoint_name : 단계로 롤백
 - SET TRANSACTION : Isolation레벨 지정
 - [LOCK|UNLOCK] TABLE : 테이블에 락을 지정/해제
 
유의사항
 - 아래 구문들은 트랜잭션 처리가 되지 않음

   ALTER [FUNCTION|PROCEDURE|TABLE]
   CREATE [DATABASE]FUNCTION|INDEX|PROCEDUER|TABLE]
   DROP [DATABASE]FUNCTION|INDEX|PROCEDUER|TABLE]
   LOCK TABLES/UNLOCK TABLES
   RENAME TABLE/TRUNCATE TABLE
   BEGIN, SET AUTOCOMMIT=1, START TRANSACTION
   LOAD MASTER DATA

 

 

LOCK의 종류별 발생 Case
 - UPDATE : 변경되는 모든 ROW에 락설정
 - INSERT : PK, Unique Key레코드에 락설정
 - LOCK TABLES : 전체 테이블에 락설정
 - SELECT ... FOR UPDATE : Select 결과 row에 대해 Exclusive 락 설정, Read/Write 불가능
 - SELECT ... LOCK IN SHARE MODE : Select 결과 row에 대해 Shared Lock 설정, Read는 가능

 

Deatlock
 - InnoDB는 DeadLock상황을 탐지해서 트랜잭션들을 강제로 Rollback함

 

 

LockTimeout
 - 지정한 시간동안 Lock를 획득하지 못하면 Rollback처리

 

 

Locking 전략
 - Pessimistic Locking Strategy
   트랜잭션에서 읽는 row에 미리 락을 설정
   concurrent update가 빈번하다 가정
   단순하고 견고한 코드
   트랜잭션 처리 시간이 길어지는 경우 성능 저하 유발

 - Optimistic Locking Strategy
   마지막으로 업데이트 하는 순간 Lock를 확인하고 처리
   update 빈도가 낮다고 가정

 

 

트랜잭션 디자인 가이드라인
 - 최대한 작게 유지
 - Rollback는 최대한 자제 - Retry할 수 있도록 예외 처리를 하는 것이 좋음
 - Savepoint는 사용 자제
 - Pessimistic Locking Strategy를 기본으로 하고 처리량이 중요한 경우 Optimistic 고려

 

[MySQL Stored Procedure]장애처리(Handler)

Condition Handler
 - Syntax

   DECLARE [CONTINUE|EXIT] HANDLER FOR
   [SQLSTATE sqlstate_code|MySQL error code|condition name]
   handler_actions

  
핸들러의 종류
 - EXIT핸들러
   현재 실행중인 블록 중단, outer블럭일 경우 종료됨
 - CONTINUE
   에러를 발생시킨 문장이 계속 실행됨

 

핸들러의 조건
 - MySQL Error Code : 에러코드를 조건으로 받음

   DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1

 - ANSI-Standard SQLSTATE code : ANSI SQL 2003 표준 SQLSTATE 코드를 조건으로 받음

   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_key=1

 - Named Conditions
   사용자가 임의로 에러코드에 이름을 부여하여 코드의 가독성을 높인다

   DECLARE foreign_key_error CONDITION FOR 1216;
   DECLARE CONTINUE HANDLER FOR foreign_key_error SET duplicate_key=1

 

SQL 2003에서 빠진 부분
 - SQLCODE 또는 SQLSTATE에 대한 직접 접근
 - SIGNAL문의 사용
  

[MySQL Stored Procedure]커서의 사용

INTO절에서 SELECT
 - 오직 하나의 값만 나오게 처리해야함

 

커서의 생성
 - 하나이상의 결과를 return하기 위해서 사용
 - 커서의 선언은 모든 변수를 생성한 이후에 선언해야함
 - Syntax

DECLAER cname CURSOR FOR SELECT * from dep;

  

커서의 사용
 - OPEN : 커서를 사용하기 위해서 fetch전에 반드시 처리
 - FETCH : 커서가 다음 ROW로 이동
 - CLOSE : 커서를 꼭 닫아줘야함
 - 전체 결과를 FETCH하는 경우 LOOP를 사용하며 이때 마지막 row을 fetch할때 "no data to fetch"에러를 발생한다 이것을 피하기 위해서 error handler을 정의해서 해결

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;

 

커서의 Loop
 - 예시

 OPEN dept_csr
 dept_loop : LOOP
   FETCH dept_csr INTO l_id, l_name, l_location;
   IF no_more_departments=1 THEN
     LEAVE dept_loop;
   END IF
   SET l_count=l_count+1
   CLOSE dept_csr;
   SET no_more_departments=0;
 END LOOP;

 

Nested Cursor Loops
 - 하나의 커서 종료후 not found변수를 reset한다.
 - NOT FOUND의 경우 커서별로 지정할 수 있다(같은 블럭내에서는 하나의 not found변수만 활성화)

 

 

 

[MySQL Stored Procedure]블록, 조건절, 반복구문의 처리

Stored Program의 Block구조

 - 명시적으로 BEGIN, END구문 사용

 - BEGIN 구문전, END 구문후에 이름을 지정

 - LEAVE 를 통해서 블럭을 나갈 수 있음

   outer : BEGIN
     LEAVE outer;
   END : outer

 - Nested Blocks구조
   블럭 내부에서 선언한 변수는 외부에서 사용불가
   반대는 사용 가능

 - 조건문

   IF THEN
     statements...  
     ELSEIF THEN
     statements...    
     ELSE
     statements...    
   END IF;

 

   CASE exp
     WHEN val THEN
     statements...    
     ELSE
     statements...    
   END CASE;

 

 - 반복구문 : LOOP

   label : LOOP
     statements...  
     LEAVE label;
   END LOOP label;

 - 반복구문 : UNTIL
   exp가 참일 경우 계속 Loop, REPEAT는 최소한 한 번 수행 보장  

   label : REPEAT
     statements...
   UNTIL exp
   END REPEAT;

 

 - 반복구문 : WHILE

   exp가 참일 경우 계속 Loop, 한번도 수행되지 않을 수 있음

   label : WHILE exp DO
     statements...  
   END WHILE label;

 

[MySQL Stored Procedure]Language Fundamentals

Variables
 - Syntax
   DECLARE variable_name[,variable_name..] datatype [DEFAULT value];
 - 값 할당시에는 SET사용
 - 모든 데이터 타입 사용 가능

Literals
 - Numeric literals 가능
   10진수(200), e지수(2e5), 16진수(OxA) 사용 가능
 - Date literals
   SET datetime = '1999-12-31 23:59:59';
 - String literals
   'String', "String"로 표현
   상수안에 '," 사용시 백슬래쉬 사용
   escape문자 사용가능 \t, \n, \\
   ANSI_QUOTES모드일때에는 single quotes만 가능

Parameters - IN
 - Syntax
   CREATE PROCEDURE name ([IN|OUT|INOUT] parameter_name data_type...)
 - IN 파라미터 값을 프로시져 안으로 전달
 - 변경은 가능하지만 리턴은 불가(Call by Value 형태)

Parameters - OUT
 - 프로시져 호출시 return value 지정
 - 값이 반환 될 수는 있지만 프로시져 안으로는 전달이 안됨

Parameters - INOUT
 - 값이 입력, 출력이 가능하다

User Variables
 - Syntax
   SET @name = 'YourName';
 - 세션단위의 사용자 지정 광역변수
 - 동일세션내에서 다른 프로시져에서 호출 가능
 - 관리가 힘들다

Comment
 - '--' 또는 '/* */'으로 사용 가능

Data Types - ENUM
 - Syntax
   CREATE PROCEDURE name (in_option ENUM('Yes','No','Maybe'))
   CALL name('Yes')
  
   CREATE PROCEDURE name (in_option SET('YES','No','Maybe'))
   CALL name('Yes,No')  
 - 다른 DB와는 호환이 되지 않음

Strict Mode
 - 잘못된 값이 컬럼에 삽입되거나 업데이트 될때 Error을 반환
 - STRICT_TRANS_TABLES, STRICT_ALL_TABLES의 차이점
   STRICT_TRANS_TABLES은 트랜잭션 지원하지 않는 테이블에서 두번째 줄에서 오류시 첫번째 줄은 처리됨
   STRICT_ALL_TABLES의은 전체가 무시됨
 - 프로시저 생성시 지정된 sql_mode환경에 따라 동작함(생성후 Mode를 변경해도 프로시져는 반영안됨)
 - 프로시저 작성전에는 Strict모드로 지정하여 잘못된 데이터의 삽입,수정을 막아야함

 

 

[MySQL Stored Procedure]Stored Procedure 개요

Stored Program이란?
 - MySQL5 버전부터 지원
 - ANSI SQL 2003을 기준으로 채택(블럭구조기반)
 - 3가지 타입
   Stored procedure : 가장 일반적인 타임
   Stored functions : 프로시져와 비슷하지만 단일값만 return
   Triggers : 데이터베이스 activity발생시 반응되는 action정의

 

Stored Program 사용이유
 - 데이터베이스 보안 강화
 - 코드의 유지보수를 더욱 쉽게
 - 네트웍 트래픽 감소
 - 어플리케이션의 이동성을 강화
 
Procedure의 기초

 - 기본 샘플

 DELIMITER $$
 DROP PROCEDURE IF EXISTS HellloWorld$$
 CREATE PROCEDURE HelloWorld()
 BEGIN
   SELECT 'Hello World';
 END$$
 DELIMITER ;

 

 - 변수

 BEGIN
   DECLARE my_integer INT;
   DECLARE my_dob     DATE DEFAULT '1960-06-21'
   SET my_integer=20;
 END;

 
 - 파라미터의 모드

 DELIMITER $$
 CREATE PROCEDURE my_sqrt(input_number INT, OUT out_number FLOAT)
 BEGIN
   SET out_number=SQRT(input_number);
 END$$
 DELIMITER ;

 IN - 프로시저의 기본모드, 값을 전달받음
 OUT - 값을 할당할 수 있고 호출할 프로그램에 반환
 INOUT - 값을 전달할 수 있고 변경된 값 역시 확인 가능

 

 - 조건절(IF, CASE)

   IF(price>500) THEN
     SET discount=10
   ELSEIF (price>100) THEN
     SET discount=5
   ELSE
     SET discount=3
   END IF

   
 - LOOP의 사용(WHILE/END WHILE, REPEAT/UNTIL절 가능)

   my_simple_loop : LOOP
     SET counter = counter+1;
   END LOOP my_simple_loop;

 - SELECT INTO(변수에 값을 SELECT로 넣을 경우 사용)

   DECLARE total NUMERIC(8,2);
   SELECT SUM(sale) INTO total
   FROM sales

 

 - Store Function : IN파라미터만 사용가능, OUT, INOUT는 사용 불가, SQL내에서도 호출 가능

 - Trigger : DML문에 의하여 테이블이 변경되었을때만 실행됨

 

  

[MySQL 아키텍처]MySQL 5.1의 새로운 기능

파티셔닝의 지원
 - Range파티셔닝 : 특정 Row의 지정한 범위를 기준으로 처리
 - List파티셔닝 : 일련의 값들중 특정값들을 기준으로 처리
 - Hash파티셔닝 : 정해진 파티셔닝 개수에 따라 해시 함수를 이용해서 균등하게 처리
 - Key파티셔닝 : 정의된 키를 기준으로 처리
 - Sub파티셔닝 : 상기파티셔닝된것을 다시한번 파티셔닝 하는 방법

 

 

Rnage파티셔닝
 - 지정한 값의 Row를 기준으로 처리, 주로 기간 분할에 사용
 - Range의 법위는 연속적이여야하고 겹쳐서는 안됨
 - VALUES LESS THAN 구문 이용
 - MAXVALUE는 가능한 최대값을 이용
 - Alter table로 추가적인 파티션 생성 가능
 - 칼럼이 Date나 Time을 가지고 있을때 유용

 

 

List파티셔닝
 - Range와 흡사하나 List는 값들의 목록의 일부를 지정, 사원정보를 부서코드별로 파티셔닝
 - 5.1버전에서는 List는 정수값만 가능
 - 정수값 외에는 NULL가능
 - List에 해당하지 않는 값을 INSERT시 에러 발생

 

 

Hash파티셔닝
 - 정해진 파티셔닝 개수에 따라 데이터를 균등하게 배분
 - 개수만 지정해주면 나머진 알아서 처리됨
 - PARTITION BY HASH(exp)를 사용하는데 exp는 항상 정수형 값을 반환해야함
 - 테이블이 UniqueKey를 가진 경우 HASH함수에서 사용되는 컬럼은 반드시 그 키의 일부여야함
 - LINEAR HASH 파티셔닝 지원

 

 

KEY파티셔닝
 - HASH파티셔닝과 거의 흡사하나 유저가 지정한 키에 따라 처리
 - KEY파티셔닝을 위한 해시 함수는 MySQL서버에서 제공
 - Cluster는 MD5 사용 그외는 Password()와 같은 알고리즘 사용
 - PK있으면 사용하고 없으면 not null로 정의된 Unique Key를 사용
 - Key값이 정수형이나 Null이 아니어야하는 제한 없음

 

 

Sub파티셔닝
 - 파티션으로 분리된 파티션을 다시 분리
 - 모든 파티션은 동일한 개수의 서브 파티션을 지정해야함
 - 5.1.8이후 서브파티션의 이름은 유니크 해야함

 

XML 기능
 - 5.1.5버전 부터 XPath를 위한 기능 추가
 - 아직까지는 미약함

 

 

이벤트 스케줄러
 - 데이터베이스 배치 등의 반복잡업을 위한 스케줄러
 - 이벤트는 일회성 이벤트와 반복 이벤트로 구분됨
 - 이벤트 스케줄러 쓰레드가 실행

 - DB가 비정상종료시 스케줄도 무효가 되므로 사용에 유의

 

 

Row 기반 리플리케이션
 - 기존 Statement기반과 함께 추가적으로 Row기반이 추가됨
 - Row에 대한 이미지를 전송되어 처리
 - 세션별로 Statement, Row 구분 처리가능
 - Mixed 방식으로 비용이 낮은 것으로 처리 가능
 - 트랜잭션에 대한 안정성 보장(시간 등의 경우 서버별 시간차이발생 가능)
 - 슬레이브에서는 Lock를 적게 사용
 - 작은단위 트랜잭션의 경우 더 빠르게 처리가능

 

 

데이터베이스화된 로그
 - 로그를 DB화 시켜 저장시킬수 있게 되어서 기동중 On/Off를 처리 가능
 - 로그 처리 방법이 편해질 수 있음

 - 단 DB부하가 높아질 수 있어 사용에 주의

 

 

디스크 기반 MySQL 클러스터
 - 메인메모리 기반에 디스크 기반의 클러스터 기능 추가
 - 인덱싱되지 않은 데이터의 경우 디스크에 저장 가능
 - 리플리케이션 기능이 추가됨

 

[MySQL 아키텍처]Lock & Transaction

트랜잭션이란 무엇인가?

 - ACID 기준으로 정의된다.
  Atomic - 원자성, 하나의 작업단위로 하나의 명령으로 수행되거나 모두 수행되지 않아야 함
  Consistency - 일관성, 트랜잭션전 후에 모두 일관된 데이터여야 함
  Isolation - 분리성, 트랜잭션은 다른 트랜잭션과 완전하게 분리되어야 함
  Durablity - 완전성, 처리완료된 트랜잭션은 영구적으로 보존되어야 함

 

 

MySQL에서의 트랜잭션
 - AUTOCOMMIT옵션은 트랜잭션을 어떻게 묶을까 결정됨(기본은 설정)
 - AUTOCOMMIT=1(Default)
   예외는 BEGIN 또는 BEGIN WORK로 시작하고 COMMIT나 ROLLBACK를 완료해야함
 - AUTOCOMMIT=0
   각각의 COMMIT또는 ROLLBACK는 하나의 트랜젹션을 완료하며 동시에 새로운 트랜잭션을 시작

 

스토리지 엔진별 트랜잭션 지원
 - InnoDB : Row레벨 락킹, Select는 락없이 수행, 테이블별 테이블스페이스 사용가능
 - BDB : 페이지 레벨 락킹, 테이블별 하나의 파일
 - MyISAM : 지원안함
 - NDB Cluster : Row레벨 락킹, 클러스터 테이블

 

 

락킹이란 무엇인가?
 - 데이터에 대한 동시접근을 제어
   UPDATE는 READ를 제한함, 락의 종류에 따라 성능,일관성 차이
 - 각각의 스토리지 엔진은 락킹에 관련한 각각의 알고리즘 보유

 

 

락의 종류
 - 테이블레벨 락킹(MyISAM,Memory) : 업데이트 중 테이블의 접근 불가
 - 페이지레벨 락킹(BDB) : 업데이트 중 동일 테이블 해당 페이지 접근 불가
 - Row레벨 락킹(InnoDB, NDB) : Commit전에는 해당 Row에 접근 불가

 

 

락의 특성 비교
 - 테이블레벨 락킹 장점
   오버해드가 적음,매우 빠른 읽기,데드락이 없음,읽기가 많고 키 기반 업데이트 어플리케이션에 적합
 - 테이블레벨 락킹 단점
   트랙잭션 지원불가, UDATE가 많으면 성능 저하

 - Row레벨 락킹의 장점
   동접이 많아도 충돌이 없음, 롤백을하는 경우 변경이 적음, 좀 더 긴시간 락 설정 가능, 변화가 많은 어플리케이션에 적합
 - Row레벨 락킹의 단점
   메모리양의 따라 성능 영향받음, 많은 ROW작업시 느림, GROUP BY와 테이블스캔 성능이 낮음, 데드락 발생 가능성 있음

 

 

MyISAM의 트랜잭션과 락킹
 - 트랜잭션 지원안함, 즉각 커밋
 - WRITE 또는 READ 락 사용
 - LOCK가 이미 걸려 있으면 LockQueue이용
 - WRITE가 우선권
 - 많은 경우 락킹없이 INSERT와 SELECT 사용 가능(충돌이 없다고 판단 되면 동시 수행)

 

 

MyISAM의 테이블락킹
 - LOCK TABLES를 통해서 수동으로 걸수 있음
 - LOCK가 걸린 테이블은 UNLOCK TABLES를 쓰거나 쓰레드가 죽지 않는한 유지
 - READ Lock가 걸리면 어떠한 내용도 쓸수 없음(읽기는 가능)
 - READ LOCAL LOCK은 충돌이 없을 경우 INSERT LOCK이 있어도 처리 가능
 - WRITE LOCK는 LOCK를 걸고 있는 쓰레드만이 테이블에 접근 가능(UPDATE시)

 

 

InnoDB의 트랙잭션 MVCC(Multiversion Concurrency Control)
 - 테이블 스페이스는 동일 데이터에 대한 여러가지 버전 보유
 - 각각의 트랜잭션이 각각의 테이블 스페이스를 가지고 처리(isolation)
 - Roll Pointer은 예전 데이터를 가리킴(Select시에는 이전 데이터를 보게됨)
 - 로그파일용량을 넘어서는 트랜잭션 처리를 위해서 테이블스페이에 기록할 경우도 있음

 

 

InnoDB의 Isolation레벨
 - Read Uncommitted(Dirty Read) : Isolation이 깨진 상태
   쿼리는 진행되거나 커밋된 모든 트랜잭션의 결과를 볼수 있음
   읽기는 락을 걸지 않으며 쓰기에 경우에만 row에 락을 설정
   최고의 성능

 - Read Committed
   읽기 쿼리는 그 커밋된 트랜잭션의 결과만을 볼 수 있음

 - Repeatable Read
   InnoDB의 표준
   새로 시작된 트랜잭션은 그 트랜잭션이 시작되기 전에 커밋된 트랜잭션의 결과만을 보게됨
   트랜잭션안에 포함된 모든 읽기 쿼리는 동일함
   팬텀리드의 문제가 있을 수 있음

 - Serializable
   모든 읽기 쿼리도 해당 row에 락을 설정
   실제하지 않는 유령값이 존재할 수 없음
   Tow phase commit - 커밋이 수행될 것임을 확인할 수 없음

 

 

[MySQL 아키텍처]아키텍쳐

MySQL의 내부구조
 - Connection Pool 이 접속 제어
 - SQL Support, Parser, Optimizer, Caches&Buffers 등의 앞단에서 쿼리를 처리
 - Storage
 - MySQL은 Pluggable Storage Engines를 사용 - 다양한 스토리지 엔진을 사용할수 있다
   예) InnoDB, MyISAM, 등.
 - 어플리케이션에 따라서 스토리지 엔진을 선택하여 사용할 수 있다.

 

 

Connection Pool
 - 실제적으로는 Thread Pool 임(Thread Reuse 가능)
 - 유저의 접속시 쓰레드를 할당함. 프로세스기반의 오라클보다 성능상 이익
 - 각각의 쓰레드에 메모리 캐쉬를 할당함
 - 유저의 접근 인증을 처리함
 - 트래픽이 많은 웹사이트와 어플리케이션에 유리함.

 

 

SQL Support
 - SELECT, DML, DDL, 스토어드 프로시져, 트리커, 뷰 등을 지원
 - SQL에 대한 함수 지원

 

 

Parser/Optimizer
 - 유저의 오브젝트 접근 권한 확인
 - SQL을 데이터베이스 내부 언어로 변환
 - 사용자의 SQL요청의 최적화된 처리를 결정

 

 

메모리 캐시
 - 빈번하게 사용되는 인덱스와 데이터를 메모리에 캐시
 - 쿼리 캐시는 Select문과 결과의 조합을 캐시하여 매우 빠른 응답을 지원
 - 데이터베이스 메타데이터 메모리 캐쉬를 제공
 - 서버전체, 엔진별, 유저별 캐시를 포함
 

관리도구
 - 데이터베이스 관리(MySQLAdmin), 쿼리 브라우징(Query Browser), 데이터 베이스 디자인(WorkBench), 마이그레이션등을 위한 GUI제공
 

엔터프라이즈 서비스
 - 인증,오브젝트별 권한관리, 데이터 암호화의 보안 제공
 - 백업,복구,복제,클러스터링 등의 제공

 

 

플러그인 스토리지 엔진 구조
 - 사용중인 어플리케이션에 맞은 엔진 선택
 - 단일 어플리케이션 안에서 여러 스토리지 엔진을 쉽게 조합하여 사용가능

 

 

스토리지엔진의 종류별 특징
 - 표 참고

MySQL 스토리지엔진별 특징

MyISAM - 기본적인 특징
 - 비활성화 할수 없는 기본 스토리지 엔진
 - 데이터 저장에 실제적인 제한이 없음(파일시스템의 제한과 동일)
 - 데이터를 매우 효율적으로 저장
 - 빈번한 데이터 사용(Read)의 부하를 잘 소화
 - B-tree,R-Tree, Full-Text 인덱스 지원
 - 특정 인덱스에 대한 메모리 캐시지원(데이터는 캐시하지 않음)
 - 데이터 압축 옵션 제공(압축하게 되면 ReadOnly가 되지만 디스크효율이 높아짐)
 - 지리적 데이터 지원
 - 테이블 레벨의 락
 - 트랜적션 미지원
 - 백업 및 특정 시점으로의 복구 지원

 - 적합한 사용처 : 트레픽이 많은 웹사이트(Read트래픽), 데이터 웨어하우스

MyISAM - 테이블의 구성
 - 하나의 테이블은 .frm .MYI .MYD 총 3개 파일 사용
 - Merge테이블은 .frm이라는 테이블 정의 파일과 .mrg라는 테이블 목록 파일로 이루어짐
   예) 동일한 구조의 '월,화...일' 테이블이 있고 '일주일'이라는 통합 테이블이 있는 경우
 - .MYI 인덱스 저장파일, 파일의 크기는 1024byte의 배수로 증가
 - .MYD 데이터 저장파일, 레코드 길이의 배수로 증가
 - .MRG merge테이블에 포함되는 테이블의 목록을 담고 있는 텍스트 파일(트릭사용가능)

 

MyISAM - Merge
 - 여러 동일한 구조의 테이블을 하나의 테이블로 사용
 - ALTER TABLE로 추가하거나 삭제 가능
 - 테이블에 포함되는 모든 테이블에 대한 권한 필요
 - SELECT, INSERT, UPDATE, DELETE 작업을 지원
   (INSERT 작업시에는 INSERT_METHOD를 지정해야함)

 

InnoDB - 기본적인 특징
 - ACID 트랜잭션 지원
 - 테이블스페이스당 64TB 저장 지원
 - MyISAM보다 데이터 저장 비율이 낮음
 - 다른 엔진들에 비해서 느린 데이터 로드 속도
 - MVCC/Snapshot read 지원
 - B-tree, clustered 인덱스 지원
 - 데이터와 인덱스 메모리 캐시 지원
 - 외부키 지원
 - 데이터 압축 옵션을 제공하지 않음
 - row레벨 락을 지원 하며 isolation 레벨 지원
 - 자동 에러 복구 기능
 - 백업 및 특정 시점으로 복구 지원

 

InnoDB - 적합한 사용처
 - 온라인 트랜잭션을 지원하는 어플리케이션

 

InnoDB - 구조

InnoDB 구조

 - Buffer pool이 MySQL의 성능을 좌우
 - Additional Memory Pool - 메타데이터 저장
 - 다수의 로그 파일 - 로그작성시 병목 제거

 

 

MySQL의 기본적인 DataFile 구조
 - Data - Hostname.err(에러로그), Hostname-slow.log(슬로우쿼리로그), Ibdata1 - innodb기본 테이블 스페이스, Ib_logfile0x(innodb redo log파일)
 - Data/mysql - 기본적으로 존재
 - Data/test - 기본 생성되는 DB 유저 관련 데이터와 인증관련 데이터
 - Data/유저생성MyISAM - 테이블명.frm(테이블정보파일), 테이블명.MYI(인덱스), 테이블명.MYD(데이터)
 - Data/유저생성InnoDB - 위와 동일, 테이블명.ibd(file per table옵션일 경우 테이블별로 생성되는 스페이스)

 

InnoDB - 테이블의 구성
 - 최소한 하나의 테이블 스페이스와 redo로그 파일
 - MyISAM과 호환을 위해 DB별로 디렉토리 생성
 - 테이블별 .frm파일 존재
 - 테이블별 테이블 스페이스 생성시 .idb라는 테이블 스페이스 파일 생성
 - redo로그는 기본적으로 두개의 파일 생성
 - 테이블별로 테이블스페이스를 생성하더라도 Undo로그는 테이블이 고유하는 기본 테이블 스페이스에 생성되어 테이블 스페이스 복사로 복제는 불가능

 

 

InnoDB와 MyISAM의 성능상 차이
 - InnoDB가 디스크 공간은 많이 사용
 - InnoDB가 PK기반 Select는 우수함
 - InnoDB가 Index Rebuild속도가 느림
 - InnoDB가 Count(*)은 MyISAM에 비해 느림
 - InnoDB는 Fixed Length row가 없음
 

InnoDB의 제약
 - 최소테이블스페이스 크기 10M(최대 64TB)
 - SHOW TALBE STATUS 결과는 근사치결과
 - 풀 텍스트 인덱스 지원 안함
 - 데드락은 자동으로 검출되며 그런경우 트랜잭션 자동 롤백

Cluster(NDB) - 기본적인 특징
 - 트랜잭션 지원
 - 모든 데이터와 인덱스 메모리에 저장
 - 매우 빠른 데이터 로드 속도
 - MVCC/Snapshop read를 지원
 - B-tree인덱스 지원
 - 프라이머리 키 사용시 최상의 속도
 - 99.999% uptime제공
 - 클러스터간 어떤것도 공유하지 않는 구조(Shared Nothing)
 - SQL API와 함께 고속적근 API제공(이걸 사용해야 최적의 성능)
 - 온라인 백업과 특정 시점 복구 지원

 

Cluster(NDB) - 적합한 사용처
 - 고가용성이 필요한 어플리케이션
 - 고속의 데이터/키 룹업이 필요한 어플리케이션
 - 대량의 사용자의 인증서버
 
Archive - 기본적인 특징
 - 5.0버전에 도입
 - 자동적인 데이터 압축지원
 - 다른 스토리지 엔진 대비 20%저장공간
 - 저장용량 제한 없음
 - 가장 빠른 로드 속도(벌크 인서트 최적화)
 - MVCC/Snapshot read 제공
 - 인덱스 미지원
 - 빠른 Insert 속도를 위한 입력 버퍼 지원
 - row레벨락 지원
 - 백업 및 복구 지원

 

Archive - 적합한 사용처
 - 해를 두고 계속되는 데이터를 위한 데이터 워어하우스
 - 데이터 저장 어플리케이션
 - 데이터 감사

 

Federated - 기본적인 특징
 - 5.0에 새롭게 도입
 - 원격의 물리적 데이터베이스에 대한 논리적 데이터베이스로 사용(DB Link)
 - 하나의 데이터베이스에서 다른 타겟오브젝트로의 '포인터'역할
 - 원격접근을 위한 미들웨어 필요 없음
 - 실행속도는 네트워크 요소에 좌우됨
 - SSL보안 처리 가능
 - 모든 SQL 오퍼레이션 지원

 

Federated - 적합한 사용처
 - 분산데이터베이스 환경

 

 

Memory - 기본적인 특징
 - 완전하게 메모리에 존재
 - 갑작스런 종료시 데이터 소실
 - 너무 많은 메모리 사용제한을 위해서 MAX_ROW를 설정하는 것이 현명함
 - Auto_increment 지원
 - B-Tree 인덱스 지원
 - varchar칼럼 지원(내부적으로는 char와 동일한 형태로 지원)
 - 대량의 데이터 로드시 MyISAM에 비해 30% InnoDB에 비해 50% 성능향상

 

Memory - 테이블 제약
 - fixed length레코드만 지원
 - BLOG나 Text사용불가
 - Hash인덱스는 하나의 row를 찾기 위해 모든 키를 사용
 - Hash인덱스는 Where 조건절을 사용할 경우 '='이나'<>'인 경우만 사용가능(하지만 매우빠름)

리플리케이션 아키텍쳐
 - 마스터 서버 : Binlog활성화, Slave에서의 접근을 위한 유저 생성
 - 슬레이브 서버 : 두개의 쓰레드가 추가 생성
   IO-Thread : Binlog모니터링 후 RelayBinlog에 복제
   SQL-Thread : RelayBinlog의 내용을 실행
 - 구성이 쉽다
 - 동작 자체가 명쾌하다
 - ASync 동작

 

다양항 형태의 리플리케이션
 - 하나의 마스터 다수의 슬레이브 : 리드 부하분산 및 백업
 - 양방향마스터 : 서로서로 마스터 슬레이브 관계, 양방향 장애 대응
 - 링형 : 특별한 목적이 있지 않는 한 피해야함
 - 피라미드형 : 마스터, 슬레이브/마스터, 슬레이브 형태의 다단계 구조, 넓은 지역을 커버하기 위한 구조(다중 IDC)

 

[MySQL 쿼리 및 인덱스의 이해]기타 SQL

High_Priority
 - 높은 우선순위로 결과를 받아오도록 질의함
 - mysql> SELECT HIGH_PRIORITY * FROM USER

 

 

Query_Cache
 - 쿼리결과를 캐슁
 - 해당 테이블이 변경(Insert,Update)가 처리되면 모든 캐쉬 Flush

 

 

Low_Priority
 - INSERT 시 옵션을 주면 큐의 모든 Select가 완료될때까지 기다림
 - 슬레이브(백업)서버의 경우 서버옵션으로 지정해놓으면 동기화는 느려도 Select는 빨라짐

 

 

대용량 INSERT
 - MultiRow를 사용하면 성능이 높아짐
 - MyISAM의 경우 8배, InnoDB에서는 30배 성능 향상
 - 대용량 처리시 disable_key를 사용해서 인덱스 생성을 멈추고 완료후 활성화
 

DELETE 성능향상
 - DELETE QUICK : MyISAM에서만 사용가능. 내부적인 인덱스 정리작업 처리안함
 - 모든 데이터를 삭제할 경우에는 Drop Table 처리
 - MyISAM 경우 Truncate Table를 사용가능(내부적으로 Drop Table처리)

 

Count(*)은 위험하다
 - Count를 위해서 별도의 테이블을 만드는 것이 좋은 해결책
 

Count(*) 쿼리가 필요할까?
 - Google같이 대략적인 COUNT로 해결
 - Limit를 사용하여 1000개 이하는 정확한 수를 1000개 이상은 1000+라고 표시

 

 

Count(*)의 속도 높히기
 - MyISAM는 전체 테이블 카운터를 가지므로 빠른 결과
 - InnoDB는 풀스캔처리
 - Count결과에 영향을 주지 않는 Join을 제거
 - 인덱스를 사용하도록 처리(UsingIndex)가 explain의 extra의 결과에 나오도록 처리

 

 

높은 Limit값 다루기
 - SELECT * FROM users ORDER BY last_online DESC limit 100000,10
 - 처음10만row 검색후 버리게 되므로 비용이 높다
 - DDos공격대상
 - 검색엔진 봇은 높은 Page를 자추 찾아들어감
 - 빠르게 동작하는걸 보장하지 못할경우 제한하는 방법 : Google 등

 

 

높은 Limit : 가능한 결과를 미리 생성
 - SELECT * FROM sites ORDER BY visits DESC LIMIT 100,10
 - 아래와 같이 레이팅을 처리하는 테이블을 추가하는 방법
 - SELECT * FROM sites_rating WHERE position BETWEEN 101 and 110 ORDER BY position
 - 실시간이 필요 없는 경우 통계테이블을 추가하면 성능 향상이 있음

 

 

배치작업에서의 LIMIT의 사용
 - 배치작업에서는 Limit n,10000 보다 Where id Between n and n+99999 형태로 변경
 - 정확한 개수는 필요 없을 경우
 

파생테이블(Derived Table)사용하기
 - 파생테이블이란 : WHERE id IN (SELECT ID FROM tb)
 - 부분적으로 인덱스를 사용할 수 있도록 함
 - 파생테이블 두개를 조인하게 되면 MySQL에서는 풀조인을 하므로 주의

 

 

FROM절의 서브쿼리
 - SELECT ... FROM (SELECT ...) WHERE ...
 - 서브쿼리를 완전 별개로 처리함. 즉 Temp Table로 사용. Temp Table은 인덱스 사용암함
 - Join으로 분할 해야함

 

 

Sorting시 File Sort 피하기
 - SELECT * FROM TBL WHERE A IN (1,2) ORDER BY B LIMIT 5
 - MySQL에서는 정렬시 인덱스 사용못함(모든 컬럼에서 = 비교를 하는 경우를제외)
 - 정렬작업을 위해서 File Sort함
 - 데이터 양이 많을 경우 시간이 오래 걸림
 - UNION으로 쪼개면 인덱스만 사용하게 됨
 - (
   SELECT * FROM TBL WHERE A=1 ORDER BY B LIMIT 5
   UNION
   SELECT * FROM TBL WHERE A=2 ORDER BY B LIMIT 5
   ) ORDER BY B LIMIT 5

 

[MySQL 쿼리 및 인덱스의 이해]Status값을 활용한 Query성능 평가

MySQL STATUS
 - 수집된 정보를 메모리상에 관리 : SHOW STATUS를 통해서 모니터링 가능
 - 각항목을 확인하여 서버동작 상황 모니터링
 - 성능을 최적화할 때 기본적인 가이드 제공
 - 아래 두가지 방법으로 확인 가능
   mysqladmin -i 10 extended-status //10초마다 로그출력
   MySQL Administrator에서 GUI로 확인 가능

 

기본적인 STATUS 모니터링
 - mysql> show [session,global] status
 - uptime : 서버가동시간
 - com_xxx : 서버시작이후 xxx관련 명령이 얼마나 수행되었나(기본은 세션별)

 

 

MySQL Handler Status값의 의미
 - mysql> show session status like 'Handler_read%';
 - MYSQL이 수행하는 논리적 row 단위 operation은 handler변수에 의해 카운트 됨
 - 값의 의미

   Handler_read_first : Index의 첫번째 Node Access, FULL_INDEX_SCAN
   Handler_read_key : 특정값으로 Tree Search를 통해 index Node를 선택하는 경우, INDEX_SEEK
   Handler_read_next : 인덱스의 leaf노드들의 링크를 Range스캔할 경우
   Handler_read_prev : 위와 반대로 역순 Range스캔
   Handler_read_random : Sorting같은 Buffer에 저장된 row에 대한 Access
   Handler_read_random_next : 데이터Block나 Temp Table에서 순차적으로 row를 읽는 경우,FULL_TABLE_SCAN

 - Index를 통한 RowAccess : first + key + next + read_prev
 - Table scan을 통한 RowAccess : random + random_next
 - 가급적 Index를 통한 Access가 일어나도록 유도하는 것이 바람직함

 

 

SHOW XXX_STATISTICS의 사용
 - 기본적으로 MySQL에서 제공하지 않는 User,Table, Index와 관련된 정보를 보여줌
 - Google에서 제작한 패치
 - SHOW USER_STATISTICS
 - SHOW TABLE_STATISTICS
 - SHOW INDEX_STATISTICS

 

 

MYSQL XXX-STATISTICS의 사용
 - mysql> show index_statistics like '%SERVERDB%'