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