Whats new in MySQL 5.6

MySQL 5.6 released last month downloadable for general with massive features added for better performance, scalability, high availability and seciruty.

Here are most important feature and drastic change in MySQL 5.6.

1. Online DDL/Schema changes
2. Full Text Search in InnoDB Storage Engine
3. Introduction of GTID Global Transaction Identifiers in Replication and High Availability.
4. New transportable Tablespace
5. Better optimizer diagnostics : EXPLAIN for insert, update, delete operations.

All features in a Nutshell.

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