MySQL 5.6: Improvements in the Nutshell

Preparing for my talk for Percona MySQL University in Raleigh,NC, Tuesday 29th of January I have created the outline of improvements available in MySQL 5.6 which I thought was worth sharing to give a feel for how massive work have been done for this release in variety of areas. I’m sure the list is not complete so If I miss something significant please let me know through the comment and I’ll update the page

Scalability
- Scalable Read Only Transactions
- Concurrent Innodb data file extension
- Non-Recursive Deadlock Detection
- Faster Locking Primitives
- Improved Innodb Thread Concurrency
- Multiple background Purge Threads
- Improved Purge lag control (now works)
- Split of “Kernel Mutex”
- Data Dictionary Cache
- Improved Adaptive Flushing
- Page Cleaner/Separate Flush Thread
- Group Commit for Binary Log
- Fight Cache Coherence and False Sharing issues
- Reduced Innodb Memory Fragmentation
- Reduced Locking for Partitioned tables
- Reduced Contention for LOCK_open
- Support for multiple table_open_cache instances
- Large (over 4GB) redo logs support

Optimizer and Execution
- Index Condition pushdown (ICP)
- Multi-Range-Read (MRR)
- Faster ORDER BY nidxcol LIMIT N
- Persistent Statistics for Innodb
- Improvements to Innodb Compression
- Fast Page Checksums (CRC32)
- 4K and 8K Page sizes for Innodb
- Improvement to Buffer Pool Flushing
- Subquery Optimizations
- More efficient Optimizer

Replication
- Optimized ROW Based Replication
- Multi-Threaded Slave
- Global Transaction Identifiers
- Crash Safe Slave and Binlog
- Replication Event Checksums
- Time Delayed Replication
- Server UUID
- Improved Logging for Row based Replication
- Replication Utilities for Failover and Admin

Transparency
- Many new INFORMATION_SCHEMA Tables
- – INNODB_METRICS
- – Meta Data Information Tables
- – Buffer Pool Information Tables
- Improved PERFORMANCE_SCHEMA
- – Reduced Overhead
- – Simplified Configuration
- – Table Access instrumentation
- – Statements instrumentation
- – Stages Instrumentation
- – Aggregations by User, Host etc
- – Network IO Instrumentation
- – Show Host Cache Contents
- – Improved File I/O Instrumentation
- Improved EXPLAIN
- – Explain for UPDATE/DELETE queries
- – JSON output with more information
- Optimizer Tracing
- Deadlock Logging
- GET DIAGNOSTICS

Operational Improvements
- Separate Tablespaces for Innodb Undo Logs
- Fast Restart – Preloading Innodb Buffer Pool
- Online DDL
- Import/Export for Partitioned Tables
- Remote Binlog Backup
- Innodb Transportable Tablespaces
- New configuration files defaults
- User Defined DATA DIRECTORY for Innodb Tables
- Connection Attributes

New Functionality for Developers
- MemcacheD API in Innodb
- Explicit Partition Selection in queries
- Full Text Search index for Innodb
- Microsecond TIME precision
- Precise spatial operations in GIS

Security
- Password hashes instead of plain passwords in Query Logs
- SHA256 hashing with Salt for Authentication
- Use obfuscated password storage for command line tools
- Policy Based password validation
- Plugin authentication support for Replication

The post MySQL 5.6: Improvements in the Nutshell appeared first on MySQL Performance Blog.