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

– 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

– 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

– – Meta Data Information Tables
– – Buffer Pool Information Tables
– – 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

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

– 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

Share this post

Comments (16)

  • Nirbhay Choubey

    some more – connection attributes, expire password at next login.

    January 27, 2013 at 11:12 pm
  • Marc Alff

    The content of the host cache is also visible in the performance_schema now.

    Configuration of the performance_schema is easier, with many sizing parameters that can be sized automatically.

    January 28, 2013 at 3:27 am
  • Shlomi Noach

    * User defined DATA DIRECTORY per InnoDB table
    * Reduced partition locks (only locks relevant partition as opposed to going through the locking mechanism of all partitions)

    January 28, 2013 at 6:26 am
  • marc castrovinci

    Can’t remember the last time I was so excited for a release. I’m really looking forward to the optimized order by/limit and GTID.

    January 28, 2013 at 7:57 am
  • Peter Zaitsev

    Thank you for comments. I’ve now updated the post with your comments.
    The password expiration is one of the features of password policies.

    January 28, 2013 at 9:17 am
  • Daniël van Eeden

    I tried to get “Obfuscated passwords in Query Logs” to work today. But it didn’t appear to work.

    January 28, 2013 at 9:37 am
  • Peter Zaitsev


    Thanks for comments. Well it brings the question of cases are covered about it. The feature is mentioned for example here:

    I did not dig much further into it as there is a little value in security by obfuscation – I expect the “password recovery tools” will appear for MySQL rather shortly

    January 28, 2013 at 9:43 am
  • Dimitri

    Peter, more about performance/scalability:
    – improved adaptive flushing (really improved + transparent for tuning and monitoring)
    – fixed purge lag tuning (no more danger, designed right now + transparent, I’ll yet blog about)
    – fixed LOCK_open contentions and introduced table open cache instances
    – over 4GB REDO logs
    – FILE I/O instrumentation in Performance Schema (not only waits and access, but also TIME of I/O operations!)

    yes, the list is really huge, pretty sure we’ve still missed something 😉


    January 28, 2013 at 10:49 am
  • Peter Zaitsev

    Thanks Dimitri,

    I have added your comments. I think purge lag tuning is more of the bug fix than new feature… though you can see the Group Commit as such too.

    January 28, 2013 at 12:18 pm
  • Dimitri

    Hi Peter,

    stay tuned, I hope purge lag tuning will see a new life in 5.6,
    let’s see.. 😉


    January 28, 2013 at 12:39 pm
  • Daniël van Eeden


    If I understand it correctly this isn’t just obfuscation. Instead of logging PASSWORD(‘password’) the resulting (one-way) hash will be logged.

    This could be done for the binlog manualy by doing
    SET @pwd := PASSWORD(‘password’);

    The new feature should make this automatic and will be applied to all logging options.

    January 28, 2013 at 1:48 pm
  • Peter Zaitsev


    Thanks for catching it. Indeed it is perhaps different for logging and the command line password, which is indeed obfuscated. For logging of password we can indeed just log one way hash

    January 28, 2013 at 1:57 pm
  • marc castrovinci


    Nice trick for keeping the password out of the log.

    It will end up in .mysql_history though, which is always an area of concern. If an intruder did get on the DB server, there is no difference of gaining the password from log files or history files.

    Really need to write a script to scrub out the .mysql_history file in addition.

    January 29, 2013 at 5:37 pm
  • Nirbhay Choubey


    A solution for this issue is in place too.
    Check this out :

    January 30, 2013 at 1:22 am
  • Justin Swanhart

    Is the password in the log just the hash from the user table? If so, you can modify the MySQL client to salt that hash and it is just as bad having the hashed password as the original. Just like the “encryption” in the new command line utils which is no better than ROT13.

    btw, innodb_autoextend_increment affects innodb_file_per_table tablespaces. I don’t see that on the list.

    January 30, 2013 at 1:05 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.