MySQL 5.6
MySQL5.6 New Features
In MySQL5.6, the features listed below have been mainly enhanced.
InnoDB performance improvements and added features
InnoDB has been the default storage engine instead of MyISAM since MySQL 5.5. MySQL 5.6 adds new features and performance improvements to InnoDB.
Transaction
A "START TRANSACTION READ ONLY" syntax has been added, with only referencing taking place when the transaction command is first executed, that transaction will now be recognized as a reference-only transaction. The result is that internal preparations, that follow the update procedure, reduce overhead and an increase in performance can be expected.
Online DDL
Execution of DDL and changing schemes can now be done online and the downtime for maintenance, etc. has been reduced. (CREATE/DROP INDEX, AUTO_INCREMENT change in variable, ADD/DROP FOREIGN KEY, RENAME COLUMN, table ROW FORMAT/KEY_BLOCK_SIZE change, column NULL/NOT_NULL change, add column・deletion・reorganize)
Log file size
The maximum size for a log file has been enlarged. The maximum size of a log file can now be made to 512GB. Due to the crash recovery process, that has already been accelerated, it is possible to use large size log files without any problems.
Page size
The parameter "innodb_page_size" has been added, conventional fixed 16KB page size can now be changed when creating instance. In an environment where improved overall throughput of smaller pages is expected, performance can be expected to improve.
Disk write
The parameter "innodb_flush_neighbours" has been added, the writing of nearby pages can be disabled. In SSD environment, disabling this leads to increased processing efficiency.
Data transfer
Data of InnoDB can now be transferred to a different MySQL server by table unit. It can also be utilised to back up within the same server.
Buffer pool
Data that has been cached in the buffer pool can now be dumped as well as restored. When restarting the MySQL server an automatic execution setting and operation at a random time is also possible. Due to the extremely small size of the data to be dumped and only table space and page information, there is no need to worry about free disk space.（"innodb_buffer_pool_dump_at_shutdown"、"innodb_buffer_pool_load_at_startup"、"SET GLOBAL innodb_buffer_pool_dump_now"、"SET GLOBAL innodb_buffer_pool_load_now"）
Deadlock
The parameter "innodb_print_all_deadlocks" has been added. When a deadlock arises, information can be recorded to an error log. Also, deadlock detection algorithms have been modified and detection has been accelerated.
Performance and Usability Improvements of Optimizer
Subquery
Subquery, which was MySQL's weak point, has under gone performance enhancements and execution speed has accelerated substantially.
BNLJ
Block Nested Loop Join (BNLJ) in addition to INNER JOIN, is compatible with OUTER JOIN, indexes which can not use the JOIN process have furthermore increased potential for acceleration.
BKA, MRR
Batch Key Access (BKA) and Multi Range Read (MRR) algorithms have been added. Disc access, which has many JOIN processes, has potential for acceleration.
ICP
An Index Condition Pushdown (ICP) algorithm has been added. WHERE phrase conditions and such are narrowed down by the index and are all carried out at the storage engine, with MySQL internal server overhead reduction in mind. (InnoDB, MyISAM, NDBCLUSTER)
LIMIT clause
File sort of the LIMIT phrase (ORDER BY non_indexed_column LIMIT x) has been optimized, the record of x is installed in sort buffer and an increase in processing speed is possible.
FROM clause
FROM phrase that includes View/sub query has been accelerated.
Explain
In addition to SELECT, DELETE, INSERT, REPLACE, and UPDATE, Explain can now be used to confirm a program. Also in addition to conventional text format, JSON format can acquire Explain and together with MySQL Workbench, visual confirmation is possible.
Trace
On the occasion that Optimizer creates a program, the decisions that were made can now be traced by the output details of the JSON formula.
NoSQL API（InnoDB memcached plugin）
The InnoDB memached plugin utilises a memached-compatible protocol that was developed to access the InnoDB database without using SQL. The accelerated processing speeds are a trait. In an additional process of simple data between SQL's INSERT command and memached protocol's set command, it is said that memached protocol can process nine times more. Also to make it possible for SQL and memached to be compatible towards the same data, complicated operations such as JOIN will use only SQL in necessary situations.
Replication Performance and Fault Tolerance Improvements
Binary log
By setting the "binlog_row_image" parameter to "minimal", the size of the binary log can now be reduced. In minimal configuration, only the previous image of the altered image line will be retained. With this in a replication environment, memory usage, network transmission, and disk usage are reduced and consequently lead to improved performance.
Group commit
The information of several transactions can now be all flushed with group commit by a one time I/O. From this, "sync_binlog=1" the overhead initialization time has been reduced.
Multi thread slave
By using a multi thread slave, processes on the slave side are able to be executed by multi thread and an improvement in slave's latency can be expected. However, because MySQL5.6 has limitations, real applications will start on version 5.7.
GTID
Being able to uniquely show as an identifier GTID (global transaction ID) has been added. From this, on an occasion such as promoting a slave to a new master, the confirmation of position information becomes unneeded and the application management of several replications become simplified.
Crash safe slave
By recording position information on the table of InnoDb, it is now possible to maintain consistency with altering data and position information. From this, a crash safe slave is implemented.（"relay_log_info_repository"、"relay_log_recovery"）
Checksum
A check sum has been added to the binary log, it is added by default. （"binlog_checksum"）
Delay replication
A replication from master to slave can be intentionally delayed. The delay can be set by seconds. In a case where multiple slaves exist, a specified slave can be delayed and various applications such as the restoration of lost data or an intentional system test can be idealized.
Server UUID
The unique ID Universally Unique Identifier (UUID) is now automatically assigned to each server. From SHOW SLAVE STATUS of Master_UUID, a master can be uniquely identified. It can be applied to a server monitored by a remote or a server using a virtual IP address.
Information log event
To strengthen replication tracking and debug, when using RBR, information having to do with the original SQL text can now be added to the binary log.（"binlog_rows_query_log_events"）
NIC
As a countermeasure to the depletion of bandwidth from the transmission of the binary log, in cases when a slave server holds multiple NIC, a NIC for replication use can now be explicitly configured. From this, the transmission used to connect to a master can now be split, and a stable replication operation is implemented.
Remote binary log back up
For improved practicality, mysqlbinglog command is used and copying a binary log of a remote server is now possible. A copy configuration within its own node is possible, in case of disc damage it can also be used to back up the binary log.
Performance schema improvement
The performance schema implemented from MySQL5.5 onwards has been greatly improved so that the average DB manager can apply it to monitoring and tuning. MySQL5.6 performance schema is effective by default. performance_schema selecting every kind of table within the schema ("_current","_summary"etc") and specifying an SQL that takes time to execute, efficiently confirming operation statistics is possible.
