월: 2015 5월

MySQL 5.6 New Feature pt. 1

5.6에서 새로워진 내용
http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html

1. 옵티마이저 향상
1) 인덱스 조건 전달(Index Condition Pushdown, ICP)
http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
세컨더리 인덱스 조인시 이전에는 첫째 인덱스 튜플로 스토리지엔진
(데이터, 인덱스 및 물리적인 I/O 작업을 진행)으로 전달하여
풀 테이블 row을 받고 DB엔진(쿼리파싱, 실행계획 생성, 수행)에서
2차 조건으로 필터(accept or reject) 했으나
ICP사용 시 WHERE 2차 조건까지 스토리지엔진으로 넘겨
조건에 부합하는 row만을 DB엔진으로 전달하여 나머지 필터링을 진행
InnoDB, MyISAM, NDBCLUSTER 엔진, range, eq_ref, ref, ref_or_null 인 실행계획에서 동작함
(5.7에서 파티션테이블에서 미동작하는 부분 수정)
>> 베이스 테이블에 대한 I/O 오버헤드와 내부 통신을 위한 오버헤드가 감소

2) 다중 범위 읽기 (Multi-Range Read, MRR)
http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html
세컨더리 인덱스(보조키/넌-클러스터드 인덱스)을 읽을 때 많은 랜덤 디스크 액세스가 발생
세컨더리 인덱스 값 순의 작은단위 랜덤읽기로 정렬되지 않은 row들을 받아오는 대신,
쿼리에서 사용하는 하나 이상의 인덱스 범위를 스캔하여
관련된 디스크 블록들을 랜덤 읽기 버퍼(read_rnd_buffer_size)로 PK(rowid)기준으로 정렬
큰 단위의 순차적인 I/O 요청으로 처리하여 디스크를 읽음
>> 범위 인덱스 스캔과 인덱스 컬럼들에 대해 equi-joins 이 수행될 때 속도 향상

3) File Sort 최적화 (File Sort Optimization)
http://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html
ORDER BY {비인덱스컬럼} LIMIT n 쿼리 사용 시
n 사이즈(offset 사이즈 포함)가 sort_buffer_size 에 수용 가능할 때
기존의 merge file을 사용하는 정렬 대신 Priority Queue 를 생성하여
정렬된 순으로 queue를 채워넣어 정렬 속도 향상 (알고리즘 수정)
>> 비인덱스컬럼 ORDER BY … LIMIT 구문에서 filesort 정렬 속도 향상

4) BNL(Block Nested-Loop) 확장 & BKA(Batched Key Access) 조인 알고리즘 도입
http://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html
BNL(Block Nested-Loop)알고리즘의 확장-기존에는 Inner join 시에만 국한, outer join, semi-join, nested outer join으로 확장
BKA 알고리즘 신규-inner join, outer join, semi-join, nested outer join
조인시 BKA 알고리즘이 동작하면 index lookup 을 위한 키를 빌드하고
MRR 인터페이스를 사용하여 키 매칭
mrr=on,mrr_cost_based=off, batched_key_access=on 으로 옵티마이저 스위치를 설정
더 효율적으로 테이블 스캐닝을 진행
>> 조인 시 랜덤 I/O 발생 감소

5) 옵티마이저의 서브쿼리 수행 전략 최적화
http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
a. 서브쿼리 수행 향상을위한 세미조인
http://roylyseng.blogspot.kr/2012/04/semi-join-in-mysql-56.html
b. IN절 서브쿼리 한번만 Materialization, hash 베이스 참조
http://oysteing.blogspot.kr/2012/07/from-months-to-seconds-with-subquery.html
c. FROM절 사용 확정되기까지 서브쿼리 Materialization 연기
http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html#from-clause-subquery-optimization
>> 서브쿼리 성능 향상

#MySQL 5.5 기본 옵티마이저 스위치
index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on

#MySQL 5.6 기본 옵티마이저 스위치
index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on
(*NEW*)index_condition_pushdown=on
(*NEW*)mrr=on
(*NEW*)mrr_cost_based=on
(*NEW*)block_nested_loop=on
(*NEW*)batched_key_access=off
(*NEW*)materialization=on
(*NEW*)semijoin=on
(*NEW*)loosescan=on
(*NEW*)firstmatch=on
(*NEW*)subquery_materialization_cost_based=on
(*NEW*)use_index_extenstions=on

2. InnoDB 향상
1) 지속적 옵티마이저 통계(Persistent Optimizer Stats)
http://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html
본래는 서버 재기동이나 특정한 동작을 할 때 통계정보가 클리어 되었음
5.6은 MySQL이 재기동될 때 나은 InnoDB 인덱스 통계 정확도, 일관성을 제공함
innodb_index_stats, innodb_table_stats 테이블에 기록
통계정보가 서버 재기동이나 어떤 런타임 이벤트 발생시 통계가 재계산되거나 바뀌기 보다 지속적으로 유지될 수 있음
>> 정확한 통계정보로 쿼리 성능을 향상시키고 쿼리 성능 안정적 유지

#신규 서버옵션
innodb_stats_persistent=1: 통계정보 디스크 기록 ON
innodb_stats_auto_recalc=1: 테이블 내에 10% row 가 변경 될 때 persistent 통계를 갱신
innodb_stats_persistent_sample_pages=20: 인덱스컬럼에 대해 카디널러티와 통계 계산시 샘플링하는 인덱스 페이지 수(기능 ON시)
innodb_stats_transient_sample_pages=8: 인덱스컬럼에 대해 카디널러티와 통계 계산시 샘플링하는 인덱스 페이지 수(기능 OFF시)
#기타
innodb_stats_on_metadata=0: 디폴트 설정이 0으로 변경되었음, SHOW TABLE STATUS, SHOW INDEX 시 통계정보 갱신하는 것

2) INFORMATION_SCHEMA 향상
https://blogs.oracle.com/mysqlinnodb/entry/information_schema_for_innodb_system
http://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema.html
측정 테이블(Metrics table): INNODB_METRICS 테이블, InnoDB를 위한 포괄적인 모니터링 프레임워크를 제공
시스템 테이블(System Tables): InnoDB 내부 DD 질의가 가능, INNODB_SYS_TABLES, INNODB_SYS_TABLESTATS, INNODB_SYS_INDEXES, INNODB_SYS_COLUMNS, INNODB_SYS_FIELDS, INNODB_SYS_FOREIGN, INNODB_SYS_FOREIGN_COLS
버퍼풀 정보 테이블: 튜닝을 위한 버퍼풀 페이지 정보, INNODB_BUFFER_PAGE, INNODB_BUFFER_PAGE_LRU, INNODB_BUFFER_POOL_STATS.
>> 불확실했던 부분에서 추가 모니터링 가능

3) 커널 뮤텍스 분할(Split Kernel Mutex)
https://blogs.oracle.com/mysqlinnodb/entry/mysql_5_6_innodb_scalability
http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_mutex
뮤텍스는 메모리 내의 데이터구조에서 상호배타 락을 걸 때 InnoDB가 사용하는 낮은레벨 오브젝트임
사용량이 많은 시스템의 병목해소 방편으로 코어 InnoDB 동작 접근을 위한 독립 뮤텍스 제어가 가능
커널뮤텍스로 불리는 글로벌 뮤텍스 때문에 제약이 많았으나 Locking 서브시스템, 트랜잭션 서브시스템, MVCC 뷰로 구성되어있는 코어 서브시스템의 뮤텍스를 분리
>> 동시접속 사용량 많은 시스템의 뮤텍스 경합을 줄여 성능 향상

4) 멀티스레드 Purge(Multi-Threaded Purge)
https://blogs.oracle.com/mysqlinnodb/entry/mysql_5_6_multi_threaded
http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_purge
purge는 DELETE 마킹된 row의 물리적 삭제나 인덱스 값 삭제 등을 하는 가비지 콜렉션과 비슷
본래는 purge 작업이 마스터 스레드에서 핸들링 되었으나 이는 다른 InnoDB 작업들과 경합이 발생하였음
MySQL 5.5에서 purge 는 단일의 독립된 스레드로 되었으며, 5.6에서는 innodb_purge_threads 세팅으로 더 많은 스레드를 할당할 수 있음
>> purge 성능확보가 어려워 딜레이, 인스턴스 성능 하락 등 운영상에 여러 문제 발생했던 부분 개선

5) Flush 스레드 분리
https://blogs.oracle.com/mysqlinnodb/entry/introducing_page_cleaner_thread_in
http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_flush
본래 dirty 페이지를 버퍼풀에서 디스크로 flush할 때 InnoDB 마스터 스레드가 제어하였으나 다른 InnoDB 동작과 경합을 발생시켰음
로드 부하가 심할 때 마스터 스레드가 dirty 페이지를 flush 할 충분한 시간없거나 비동기 flush 진행이 어려웠음
새로이 page_cleaner 라는 백그라운드 flush 스레드가 도입되어 flush 수행을 효율적으로 진행시킴
>> 4)번과 비슷한 개선

6) InnoDB 테이블 캐시 선별제거(Pruning)
https://blogs.oracle.com/mysqlinnodb/entry/mysql_5_6_data_dictionary
table-definition-cache 설정한 스레스홀드에 도달시 InnoDB 인터널 DD에서 테이블 close 하고 unload 하는 기능 추가
LRU 알고리즘에 의해 eviction 진행할 테이블들이 선별
>> 보통 서버 재기동이 되기 전까지 거의 사용하지 않는 수백메가에 달하는 캐시 엔트리들을 가지고 있을 수 있는데 이를 해소

3. 새 NoSQL 스타일 memcached APIs
https://blogs.oracle.com/mysqlinnodb/entry/nosql_to_innodb_with_memcached
memcached API를 사용해서 웹서비스들이 SQL변환 필요없이 낮은 레이턴시와 읽기/쓰기 처리량을 보장받으며 InnoDB 스토리지엔진에 직접 접근
mysqld의 데몬플러그인으로 제공, SQL파서와 옵티마이저를 바이패스함, memcached 프로토콜 지원, 다중 컬럼을 value 값으로 매핑 가능
set, get, delete, flush 의 memcached 동작에 대해 local caching 가능
모든 memcached 옵션 사용 가능

4. 파티셔닝 향상
1) 명시적 파티션 선택(Explicit Partition Selection)
http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html
어떤 파티션을 사용할지 쿼리,DML,데이터로드시 선택 가능
콤마가 delimiter 로 리스트 나열 가능
SELECT * FROM employees PARTITION (p0, p2);
DELETE FROM employees PARTITION (p0, p1);
UPDATE employees PARTITION (p0) SET store_id = 2 WHERE fname = ‘Jill’;
SELECT e.id, s.city FROM employees AS e JOIN stores PARTITION (p1) AS s …;

2) 파티션 테이블의 Import/Export
http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html
새 데이터셋을 파티션테이블에 가져올 때, 파티션 export 시, 서브파티션을 보통의 테이블과 같이 manage 할 때 ALTER TABLE … EXCHANGE 사용 가능
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

5. 리플리케이션 향상
1) Row 기반 리플리케이션 향상
http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_row_image
http://d2-systems.blogspot.kr/2011/04/mysql-562-dm-optimized-row-based.html
Statement 기반에 비해 반영이 빠르고 데이터 무결성을 보장하였으나 row 변경에 대한 전 이미지, 후 이미지를 기록하여 디스크 사용량, 네트웍 사용량, INSERT, UPDATE, DELETE 대량 발생 시 빈로그 사이즈 증가 했었음
row 기반 리플리케이션에서 빈로그 기록하는 방식 변경 됨
빈로그포맷이 mixed라면 row방식 사용 시에만 적용됨
>> 빈로그 디스크 사용량, 네트웍 리소스, 서버 메모리 사용량이 감소binlog-row-image=full (모든 로그 기록, INSERT는 후 이미지, DELETE는 전 이미지 기록)
binlog-row-image=minimal (변경되거나 row 식별을 위해 필요한 컬럼만 기록)
binlog-row-image=noblob (row 식별을 위해 필요하거나 변경되는 BLOB, TEXT컬럼 빼고 나머지 BLOB, TEXT는 기록에서 제외)

2) 멀티스레드 슬레이브
https://blogs.oracle.com/MySQL/entry/benchmarking_mysql_replication_with_multi
슬레이브 서버로 리플리케이션 이벤트를 반영하기 위해 여러개의 수행 스레드를 사용
멀티스레드 슬레이브는 데이터베이스 명칭에 따라 워커 스레드를 분할, 병렬적으로 업데이트를 반영
오라클배포 벤치에 따르면 10개의 스키마에 병렬로 리플리케이션 받을 때 약 5배 QPS 증가한다고 함
>> 리플리케이션 랙 감소, 처리량은 증가

3) 크래시-세이프 슬레이브
http://dev.mysql.com/doc/refman/5.6/en/slave-logs.html#replication-implementation-crash-safe
http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_relay-log-recovery
http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_relay-log-info-repository
http://mysqlmusings.blogspot.kr/2011/04/crash-safe-replication.html
슬레이브는 자동적으로 실패로 부터 리커버리, DML 업데이트 리플리케이션을 재개
수동으로 SQL_SLAVE_SKIP_COUNTER로 마지막 성공적으로 수행했던 트랜잭션으로 롤백시키거나 또는 트랜잭션 스킵을 시킬 필요 없음
구글패치를 수정하여 반영, 포지션 업데이트를 데이터 트랜잭션 내부에 포함
디폴트는 relay-log-recovery=0, 크래시-세이프 쓰려면 relay-log 를 TABLE 로 기록해야함 (relay-log-info-repository)
리플리케이션 리파지토리가 추가되었음(마스터정보 리파지토리, 릴레이정보 리파지토리)
>> 데이터 무결성이 향상, 전략적인 데이터관리 활동에 더 집중 가능

4) 리플리케이션 데이터 체크섬
http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html http://www.percona.com/blog/2013/02/15/replication-checksums-in-mysql-5-6/
CRC32 체크섬이 들어가 슬레이브로 복제되는 데이터의 무결성을 확보
바이너리로그, 릴레이로그, 개별 리플리케이션 이벤트에 구현
>> 메모리, 디스크, 네트웍실패, 데이터베이스 자체 등에서 기인하는 에러 검출
binlog-checksum=CRC32: 빈로그에 체크섬 기록, 기본값 CRC32
master-verify-checksum=0: 마스터 빈로그 체크섬 검증, 기본값 0
slave-sql-verify-checksum=0: 슬레이브 릴레이로그 체크섬 검증, 기본값 0

5) 시간지연 리플리케이션
http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html
마스터에서 각각의 슬레이브로 리플리케이션될 이벤트를 시간지연 지정(ms ~ 68yrs)
SQL_THREAD 수행을 홀딩하는 것으로 슬레이브당 구현되어짐
>> 마스터에서의 수행오류(drop 테이블등)를 보호, 리플리케이션 랙 상황에서 어플리케이션 테스트 시 유용, 배포유연성 확보

6) Row 기반 리플리케이션 로그 이벤트 정보 추가
http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html
Row 기반 리플리케이션 시 debug를 위한 추가 정보를 기록하는 기능옵션 추가
binlog_rows_query_log_events 등
>> auditing과 디버깅 향상

7) mysqlbinlog 리모트 빈로그 백업
http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_raw
raw, read-from-remote-server, stop-never 등의 플래그를 추가하여 오리지널 바이너리 형태의 빈로그를 리모트 백업 서버로 써낼수 있음
각각의 서버로 SSH 엑세스 필요없이 가능
>> 바이너리 로그로 부터 실시간 백업을 생성 오퍼레이션 효율 향상

8) 서버 UUID
http://dev.mysql.com/doc/refman/5.6/en/replication-options.html#sysvar_server_uuid
http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html
자동적으로 Universally Unique Identifier (UUID)를 각각의 서버에 생성
readonly 로 datadir/auto.cnf 에 기록
GTID 사용 및 마스터에서 슬레이브들의 인식, 리플리케이션 모니터링 툴에서 마스터 와 슬레이브 서버 정보를 획득 시 사용
슬레이브 서버를 clone 만들 때 주의
GTID = source_id(server_uuid):transaction_id

6. PERFORMANCE_SCHEMA 확장 개선
http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html
1) 오버헤드 감소: performance_schema 코드 튜닝, 사용시 오버헤드가 감소
2) 테이블/인덱스 I/O 계측: 테이블/인덱스 단위로 어플리케이션이 I/O 로드 이해하고 분석, 튜닝(setup_object)
3) 테이블 락 계측: 테이블로 어플리케이션의 데이터엑세스 패턴에 의해 유발되는 오브젝트관련 병목 인지(setup_object)
4) 세션/유저레벨 계측: 테이블로 특정한 유저,스레드,서버 어플리케이션 부하 모니터링(setup_actors)
5) 글로벌 수치 요약: 전역적으로 스레드/오브젝트/계측 포인트로 집계
6) 테이블 락 대기 요약: 테이블 락 데이터를 집계, 어떤 테이블이 어플리케이션으로부터 자주 병목이 생기는지 확인
8) 구문레벨 계측: SQL 구문당 수치를 수집하고 유저세션이나 글로벌 구문분석/수행 수치 집계
9) 커넥션 에러 중앙화: 커넥션 관련 에러들의 사유(SSL, DNS, auth플러그인 등)와 함께 커넥션 에러들에 대해 중앙화된 로깅
>> 내부 동작에 대한 명확한 모니터링