MySQL 5.6

5.6 업그레이드 후 5.5 바이너리포맷 컬럼 import 오류

5.5 를 in-place 로 업그레이드 한 이후 테이블 리빌드 하지 않아 time 계열 컬럼이 5.5 바이너리 포맷이라면,
테이블스페이스의 import 시 *.cfg 에 의한 매칭 실패 오류가 나고

mysql> ALTER TABLE tab IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Column created_at precise type mismatch.)

무시하고 cfg 삭제 후 진행하면 import 는 완료되나 테이블 접근할 때 테이블 스페이스 범위를 벗어나는 메모리 페이지 참조로 크래시가 발생함
null alter 로 바이너리 포맷을 맞춰준 뒤 수행 필요

InnoDB: Error: trying to access page number 919442480 in space 1151,
InnoDB: space name test_db/tab,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2016-02-25 10:22:17 7f0e10836700 InnoDB: Assertion failure in thread 139698383316736 in file fil0fil.cc line 5609
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
01:22:17 UTC – mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=134217728
read_buffer_size=2097152
max_used_connections=13
max_threads=2000
thread_count=5
connection_count=5
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 8348665 K bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.

Thread pointer: 0xd64a4700
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7f0e10835e18 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x35)[0x8e1555]
/usr/libexec/mysqld(handle_fatal_signal+0x494)[0x6678e4]
/lib64/libpthread.so.0(+0xf790)[0x7f19ae2ce790]
/lib64/libc.so.6(gsignal+0x35)[0x7f19acd6e625]
/lib64/libc.so.6(abort+0x175)[0x7f19acd6fe05]
/usr/libexec/mysqld[0xa9e7bf]
/usr/libexec/mysqld[0xa6caad]
/usr/libexec/mysqld[0xa6d21b]
/usr/libexec/mysqld[0xa5a864]
/usr/libexec/mysqld[0xa46916]
/usr/libexec/mysqld[0x9f1c16]
/usr/libexec/mysqld[0x94f058]
/usr/libexec/mysqld[0x947e09]
/usr/libexec/mysqld(_ZN7handler14ha_index_firstEPh+0x66)[0x5a7a06]
/usr/libexec/mysqld(_Z15join_read_firstP13st_join_table+0x81)[0x6c3411]
/usr/libexec/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x15d)[0x6c40cd]
/usr/libexec/mysqld(_ZN4JOIN4execEv+0x2fa)[0x6c499a]
/usr/libexec/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x250)[0x708670]
/usr/libexec/mysqld(_Z13handle_selectP3THDP13select_resultm+0x1a7)[0x708f17]
/usr/libexec/mysqld[0x6e2e6d]
/usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x159f)[0x6e569f]
/usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x3c0)[0x6e9290]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1528)[0x6ea868]
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0xcf)[0x6b62bf]
/usr/libexec/mysqld(handle_one_connection+0x47)[0x6b63e7]
/usr/libexec/mysqld(pfs_spawn_thread+0x12a)[0xb0e17a]
/lib64/libpthread.so.0(+0x7a51)[0x7f19ae2c6a51]
/lib64/libc.so.6(clone+0x6d)[0x7f19ace2493d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (d5130210): select count(1) from tab
Connection ID (thread ID): 4854616
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
160225 10:22:17 mysqld_safe Number of processes running now: 0
160225 10:22:17 mysqld_safe mysqld restarted

Advertisements

사례로 살펴보는 MySQL 5.6 업그레이드

http://tmondev.blog.me/220612475498
MySQL 업그레이드 관련 티몬 개발 블로그 기재 글

올해는 MySQL이 세상의 빛을 본지 20주년이 되는 해입니다. 오라클이 Innobase(InnoDB)를 인수한지 10년, 썬 마이크로시스템즈 인수와 함께 MySQL을 품속에 넣은 것도 5년이라는 시간이 흘렀습니다.

그 동안 MySQL의 존속에 대해 우려가 많았던 것이 사실이고 여전히 회의적인 시선들이 업계에 존재하지만 MySQL은 대표적인 오픈소스(?) RDBMS의 자리를 지키고 있습니다. 2011년 MySQL 5.5, 2013년 MySQL 5.6, 그리고 작년 말 MySQL 5.7이 발표되며 개선이 이루어져왔고 테크 리딩 기업들의 MySQL 생태계 기여는 그 여느 때보다 활발합니다.

근래의 큰 뉴스 중 하나라면 앞서도 언급한 MySQL 5.7의 GA(General Availability)버전이 릴리즈된 것으로, 이와 같은 메이저 버전이 새롭게 릴리즈되면 서비스하고 있는 구 버전의 MySQL의 업그레이드에 대해 고민을 하게 됩니다. 업그레이드는 서비스의 성능Performance, 확장성scalability, 가용성Availability, 관리적Manageability 측면에서 많은 개선을 꾀할 수 있기 때문입니다.

MySQL 업그레이드로 얻을 수 있는 이점과 개선된 기능들은 다음과 같습니다.

1) 신규로 소개되거나 개선된 기능을 사용할 수 있습니다.
5.6: 멀티스키마 리플리케이션, 온라인 ALTER, Performance Schema 개선, 버퍼풀 프리로드 등
5.7: 멀티마스터 리플리케이션, 독립적인 멀티스레드 슬레이브, SYS Schema 등

2) 성능, 확장성과 관련된 내부 엔진 개선이 있습니다.
5.6: 글로벌 뮤텍스 분리(경합 해소), 멀티스레드 퍼지, 지속적 통계정보, 옵티마이저 향상 등
5.7: 파티셔닝 개선, 벌크 로드 향상, 재개발된 옵티마이저 비용 모델, 페이지 압축 등

3) 추가적인 개발 지원, 향상된 보안 기능이 있습니다.
5.6: 정책기반 패스워드 검증, TIME계열 데이터타입의 소수점단위 사용, Memcahced API 등
5.7: JSON 데이터타입/인덱싱/함수, 패스워드 로테이션, SSL, AES256 등

4) 버그들의 수정 및 코드 리팩토링이 이루어집니다.
마이너 버전 업데이트로 쉽게 수정되지 못했던 이슈들의 해결

업그레이드 시 이렇게 많은 부분에서 이점이 있지만 리스크 역시 존재합니다. 비호환 항목에 대한 충분한 체크가 이루어지지 않았다면 업그레이드 전까지 잘 구동되던 어플리케이션이 업그레이드 이후 에러를 내며 중단될 수 있습니다. 업그레이드 된 서버와 그렇지 않은 서버간에 구성한 리플리케이션이 단절되거나 서버나 쿼리의 성능 하락이 발생하기도 합니다. 이를 방지 내지는 최소화하기 위해 업그레이드 시 변경되는 부분에 대한 충분한 이해가 필요합니다.

MySQL 업그레이드를 한다면 어떤 부분들이 고려되어야 하는지, 주의해야 할 부분은 무엇인지, MySQL 5.5 to 5.6 업그레이드의 비호환 케이스를 가지고 알아보도록 하겠습니다.

(노트: MySQL 5.6은 2016년 1월 현재 5.6.28 버전까지 릴리즈되었으며 GA가 된지 곧 3년이 되는, 업그레이드를 위한 가장 안정화된 버전으로 볼 수 있습니다.)

1. 업그레이드 방식
업그레이드를 진행한다면 먼저 업그레이드 할 대상 서버를 선정하고 대상 서버들의 데이터 사이즈와 서버 상황에 따라 작업 방식을 선택해야 합니다. 5버전대에서 취할 수 있는 작업 방식은 다음과 같이 크게 두 가지로 나뉠 수 있습니다.

1) 덤프 업그레이드 (Logical)
첫 번째 방법은 기존의 서버에서 mysqldump 등 덤프 유틸리티를 사용하여 전체 데이터베이스(시스템 데이터베이스 제외)와 유저 권한을 SQL스크립트 파일로 덤프한 뒤, 업그레이드 버전의 서버에서 덤프 로드를 하는 것으로 안전한 업그레이드 방법입니다. 덤프를 로드할 때 업그레이드 버전의 바이너리 포맷으로 데이터들이 생성되기 때문에 별도의 테이블 리빌드 Rebuild가 필요하지 않지만 데이터 사이즈에 따라 구성하는데 너무 오랜 시간이 걸릴 수 있습니다. 데이터가 수백 기가바이트에서 테라 바이트 단위라면 덤프와 로드의 시간 소모가 엄청날 수 있고 덤프 파일을 내리는 공간 역시 별도로 확보되어야 합니다.

2) 바이너리 교체 업그레이드 (In-place)
두 번째는 데이터 디렉토리를 그대로 사용하면서 MySQL 서버 바이너리 파일과 라이브러리를 업그레이드 버전으로 교체 한 이후, 공식적으로 제공되는 mysql_upgrade 스크립트를 구동하여 시스템 테이블의 변경 및 업그레이드 체크를 진행하는 방법입니다. 데이터베이스 전체를 SQL덤프를 하는 방법보다 간편하고 짧은 시간 내 업그레이드가 가능하나 데이터가 구 버전의 바이너리 포맷을 유지할 수 있어 충분한 호환성 체크가 진행되어야 하고 상황에 따라 테이블 리빌드가 필요할 수 있습니다.

2. 업그레이드 계획 수립
업그레이드 절차 자체가 어렵거나 복잡하지는 않습니다. 그보다 업그레이드 이후 발생할 수 있는 이슈를 사전에 체크,준비하고 플랜을 세우는 것이 중요합니다.

1) 백업 계획 수립
In-place 업그레이드 일 경우에도 가능한 한 중요 데이터베이스에 대해 logical 덤프를 수행하며 서버가 라이브 상태라면 xtrabackup 이나 mysqlbackup 등의 핫 백업 유틸리티를 통하여 백업을 진행하는 것이 필요합니다. 백업 자체가 여의치 않다면 백업 서버를 리플리카 슬레이브로 구성하거나 아예 별도의 클론을 떠 놓는 방법을 생각해 볼 수 있습니다. MySQL 은 버전 다운그레이드가 자유롭지 않기 때문에 동원할 수 있는 모든 방안을 준비하는 것이 좋습니다.

2) 업그레이드 순서
대 서버 데이터 복제기술인 리플리케이션은 업그레이드 시 주요 개선포인트로 등장하는 MySQL 의 대표적인 기능입니다.리플리케이션 토폴로지 내에서의 업그레이드는 복제 슬레이브 부터 진행하는 것이 중요합니다. 상위 버전은 대부분 호환성을 위한 설정들이 추가적으로 가능하여 복제 슬레이브의 업그레이드가 마스터보다 선행 되더라도 리플리케이션을 유지할 수 있지만, 마스터 서버가 먼저 업그레이드 된다면 하위의 슬레이브들은 마스터와의 연결이 어려울 수 있습니다. 또한 여러 대의 복제 슬레이브가 있다면 한 번에 진행하기 보다 그룹군으로 쪼개어 업그레이드를 진행하여 전체 서비스의 영향도를 줄일 수 있습니다.

3) 단계별 테스트
프로덕션에서 업그레이드를 진행하기 전에 개발/QA, 스테이징 영역에서 업그레이드를 진행하고 충분한 어플리케이션 호환성 테스트를 진행하는 것이 필요합니다. 업그레이드 전 프로덕션의 슬로우로그나 제너럴로그 쿼리를 수집하여 업그레이드 후의 MySQL 서버에서 쿼리들을 재수행하여 실행계획 확인 및 쿼리 성능분석을 진행해 보는 것도 좋겠습니다. 이 경우 쿼리의 재수행과 로그를 바탕으로 쿼리 사용 분석을 도와주는 pt-upgrade, pt-query-digest 등의 오픈소스 툴을 활용할 수도 있을 것입니다. 프로덕션도 OLTP(Online Transaction Processing)냐 OLAP(Online Analytical Processing)성이냐에 따라 쿼리나 서버 워크로드가 다르기 때문에 특성에 맞는 슬로우 기준과 샘플링 세트를 적절히 설정하고 테스트를 진행해야 합니다.

3. 하위 호환성 체크
호환성 체크는 버전 업그레이드를 통해 실질적으로 어떤 것들이 영향 받을 수 있는지 확인하는 가장 중요한 단계입니다. MySQL 레퍼런스 문서에는 업그레이드 호환성 체크에 대한 비교적 자세한 가이드를 제공하고 있습니다. 또한 각 버전별 변경로그 Change log를 살펴보는 것도 도움이 되며, 특히 버전 간 비호환 변경 Incompatible Change: 으로 표기된 부분을 주의 깊게 확인해야 합니다.

이제, 레퍼런스에 언급되어있는 호환성 체크 항목들을 살펴보고, 간단한 테스트와 함께 업그레이드 시 문제가 발생할 만한 MySQL 5.6 의 변경점을 알아보겠습니다.

1) MySQL 5.6 주요 서버변수 변경점
중요한 설정 변수의 기본값이 변경되거나 호환성을 위해 설정이 필요한 부분들이 있습니다. MySQL 5.6은 대체로 확장성을 증대시키는 방향으로 기본 설정들이 변경되었으며 binlog_checksum 과 innodb_checksum_algorithm 등 구 버전과의 연계를 위한 호환 설정도 다수 추가되었습니다. 특히 bufferpool이나 redo log 등에 대해 세부적인 동적 튜닝이 가능해지면서 이에 대한 신규 설정이나 기본값들에 대한 숙지가 필요해졌습니다. 추가되었거나 변경된 부분을 살피고 업그레이드 서버의 워크로드에 맞는 설정 튜닝을 찾아내야 하겠습니다.

2) MySQL 5.6 주요 내부 변경점
가장 큰 변경점은 TIME, DATETIME, TIMESTAMP 에 대한 정밀도 표현(ms)이 가능해졌다는 점입니다. 신규 스키마는DATETIME(숫자1~6) 과 같이 타입선언을 하면 소수점 이하 6자리까지 사용 가능하고, 기존 스키마는 ALTER 등으로 데이터타입의 바이너리포맷이 변경되기 전까지 기존 형식대로 유지됩니다.

(중략)

4. 예상치 못한 버그들
누구나 업그레이드 시 성능 향상을 기대하겠지만 버전이 바뀌었을 때 이전에는 없던 새로운 버그가 발견되는 등의 난감한 상황이 발생하기도 합니다. 특히 최신의 버전임에 불구하고 현상이 재현되는 상태라면 다른 문제해결 방안을 찾아야 합니다.

(중략)

5. 마치며
간단하게나마 MySQL 메이저 업그레이드에 대한 필요성과 방안, 업그레이드 시 주의해야 할 점, MySQL 5.6 비호환 항목들을 살펴보았습니다. MySQL은 탄생한지 스무돌이 넘었지만 현대의 웹/모바일 패러다임에 맞는 변화를 추구하고 있으며 앞으로도 계속 새로운 기능 추가와 개선이 있을 것 입니다. 업그레이드 시에는 적절한 업그레이드 계획을 세우고, 서비스 영향도를 파악하고, 광범위한 테스트를 병행하는 것을 잊지 않아야 하겠습니다.

감사합니다.

참고
https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/
https://www.percona.com/blog/2014/09/19/mysql-upgrade-best-practices/
https://www.percona.com/blog/2010/01/05/upgrading-mysql/

Multiple-Column Indexes & ICP (Index Condition Pushdown)

1. 복합키
http://jorgenloland.blogspot.kr/2011/08/mysql-range-access-method-explained.html

CREATE TABLE orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT,
value INT,
order_date DATE,
KEY custid_value (customer_id, value)
)

customer_id = 2 and value > 1000 — 키파트 1, 키파트 2 사용
customer_id < 2 and value = 1000 — 키파트 1 사용, customer_id < 2 인 모든 레코드를 스토리지엔진에서 획득

(1)복합키 내 키파트을 좌로부터 보았을 때 =, <=>, IS NULL 비교가 들어가지 않고
>, <, >=, <=, !=, <>, BETWEEN, LIKE 를 만나면 뒤에 나열된 키파트들을 사용하지 않음
5.5, 5.6 에서 인덱스키가 사용되느냐 아니냐 하는 복합키의 키파트 선택은 동일

2. ICP
http://jorgenloland.blogspot.kr/2012/03/index-condition-pushdown-to-rescue.html#more
http://olavsandstaa.blogspot.co.uk/2011/04/mysql-56-index-condition-pushdown.html

5.6의 ICP는 (1)에서 키를 타지 않고 무시된 뒤의 키파트에 대한 처리 시
레코드들을 가지고 올 때 얼마나 덜 가지고 올지를 결정

MySQL 5.6.27의 innodb_numa_interleave 변수 추가

https://xdhyix.wordpress.com/2015/07/20/numa-swap-insanity-mysql/
에서 언급되었던 NUMA 핸들링에 대한 패치가 merge 되었음

mysqld_safe 스크립트 에서 /usr/bin/numactl –interleave all 로 프리로드 했었으나
추가된 innodb_numa_interleave 변수를 ON 으로 변경 시 에러로그에 MPOL_INTERLEAVE 후 MPOL_DEFAULT 로 변경하는 로그가 찍힘

2015-10-20 11:24:08 24314 [Note] InnoDB: Initializing buffer pool, size = 38.0G
2015-10-20 11:24:08 24314 [Note] InnoDB: Setting NUMA memory policy to MPOL_INTERLEAVE
2015-10-20 11:24:10 24314 [Note] InnoDB: Setting NUMA memory policy to MPOL_DEFAULT
2015-10-20 11:24:10 24314 [Note] InnoDB: Completed initialization of buffer pool

/usr/bin/numactl –interleave all 프리로드 스크립트를 삭제하고 새 변수를 사용하여 확인

# ./numa-maps-summary.pl < /proc/24314/numa_maps
N0 : 661167 ( 2.52 GB)
N1 : 566951 ( 2.16 GB)
active : 396 ( 0.00 GB)
anon : 1226437 ( 4.68 GB)
dirty : 1226437 ( 4.68 GB)
kernelpagesize_kB: 312 ( 0.00 GB)
mapmax : 261 ( 0.00 GB)
mapped : 1796 ( 0.01 GB)

# ./numactl –hardware
available: 2 nodes (0-1)
node 0 cpus: 0 1 2 3 8 9 10 11
node 0 size: 24532 MB
node 0 free: 21129 MB
node 1 cpus: 4 5 6 7 12 13 14 15
node 1 size: 24576 MB
node 1 free: 21762 MB
node distances:
node 0 1
0: 10 21
1: 21 10

MySQL 5.6 New Feature pt. 2

MySQL 5.6 내부적인 향상점
https://www.percona.com/blog/2013/01/27/mysql-5-6-improvements-in-the-nutshell/

1. 확장성
Scalable Read Only Transactions
InnoDB가 readonly 트랜잭션 판단(싱글트랜잭션으로 간주되는 autocommit=1 등)하고 transaction ID(TRX_ID) 세팅하는 오버헤드 피함
Concurrent Innodb data file extension
Non-Recursive Deadlock Detection
Faster Locking Primitives
Improved Innodb Thread Concurrency
동시 스레드 부분에서 더 세부적인 설정
Large (over 4GB) redo logs support
2TB까지 리두로그 사이즈를 증가(ib_logfile)
사용량이 많은 DB에서 성능 향상
4k, 8k Pages
innodb_page_size 옵션 SSD에서 성능향상 기대
낮아질 수록 반으로 데이터 길이 한계가 줄어듬
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_page_size
https://blogs.oracle.com/mysqlinnodb/entry/innodb_5_6_4_supports
Group Commit for Binary Log
http://mysqlmusings.blogspot.kr/2012/06/binary-log-group-commit-in-mysql-56.html
http://dimitrik.free.fr/blog/archives/2012/06/mysql-performance-binlog-group-commit-in-56.html
Fight Cache Coherence and False Sharing issues
Reduced Innodb Memory Fragmentation
Reduced Locking for Partitioned tables
Reduced Contention for LOCK_open
Multiple table_open_cache instances
테이블 오픈 시 경합 해소
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cache_instances
http://dimitrik.free.fr/blog/archives/2012/09/mysql-performance-table-open-cache-in-56.html

2. 투명성
Improved EXPLAIN
Handling INSERT/UPDATE/DELETE queries
JSON output with more information
DML모두 플랜 가능, JSON 포맷으로 출력
Optimizer Tracing
http://jorgenloland.blogspot.kr/2011/10/optimizer-tracing-query-execution-plan.html
Deadlock Logging
가장 최근 뿐만이 아니라 모든 데드락을 에러로그에 로깅
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks
GET DIAGNOSTICS
진단, SP에서 에러코드등 받을 수 있음
http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

3. 사용성 개선
Separate Tablespaces for Innodb Undo Logs
언두로그 경로 분리(다른 디스크로)
http://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html
Innodb Buffer Pool Preloading
서버 리스타트 시 버퍼풀 웜업
http://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html
Online DDL
온라인 ALTER 등 가능
http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
Innodb Transportable Tablespaces
테이블 단위의 파셜백업 가능
http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html
User Defined DATA DIRECTORY for Innodb Tables
Connection Attributes (퍼포먼스 스키마)
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-connection-attribute-tables.html

4. 개발
Microsecond TIME precision
DATETIME(6)등 사용
http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

5. 보안
Password hashes instead of plain passwords in Query Logs
SHA256 hashing with Salt for Authentication
Support obfuscated password storage for command line tools
Policy Based password validation
플러그인 사용
http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html
Plugin authentication support for Replication

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플러그인 등)와 함께 커넥션 에러들에 대해 중앙화된 로깅
>> 내부 동작에 대한 명확한 모니터링