Stable release of MySQL 5.6 announced

On February 5, Oracle announced the release of the stable release of MySQL version 5.6. A lot of work has been done in the new version. The main efforts were aimed at increasing productivity, scalability and flexibility. The InnoDB engine has undergone massive changes.

Key improvements include: support for full-text search, the ability to access data through the memcached API, increased productivity with intensive data recording, and increased scalability when processing a large number of simultaneous requests.

Another innovation of version 5.6 is the ability to execute DDL operations (Data Definition Language) without taking the DBMS offline and interrupting access to tables. Administrators get the opportunity to perform operations related to resetting the schema, adding or removing data columns or renaming columns without disabling the DBMS. Previously, such features were available only in NoSQL products.

Compared to version 5.5, the novelty can now work on 48-core servers, versus 32-core ones in MySQL 5.5.

A little more about the key features:

• An interface has been implemented for direct access to InnoDB tables in the style of NoSQL systems using an API that manipulates key / value pairs and is compatible with memcached.
  
• Now you can create full-text indexes in InnoDB to organize quick search on word forms among text content stored in InnoDB tables. Previously, full-text search was only available for MyISAM tables.
• Increasing the efficiency of the query optimizer, streamlining the process of selecting the resulting set of values, sorting and query execution. New optimizations Index Condition Pushdown (ICP) and Batch Key Access (BKA) allow up to 280 times to increase the throughput of some queries. The efficiency of executing queries of the form “SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,] N;” has been increased. Increased performance of “SELECT ... LIMIT N” queries that display only a part of the rows from a large sample.
• Diagnostic tools for the optimizer have been expanded, and EXPLAIN support has been added for INSERT, UPDATE, and DELETE operations. EXPLAIN results can now be displayed in JSON format. The new optimizer tracing mode allows you to track each decision made during query optimization.
• Additional optimizations for the execution of subqueries, in which nested queries of the form “SELECT ... FROM table1 WHERE ... IN (SELECT ... FROM table2 ...))” are translated into a more optimal representation at the stage before the query is executed directly, for example, they are replaced by a more efficient JOIN.
• Expanding the implementation of the PERFORMANCE_SCHEMA diagnostic system, which provides low-level tools for monitoring query execution and various events during the operation of a DBMS. PERFORMANCE_SCHEMA allows you to evaluate bottlenecks in detail when performing lengthy queries, as well as present summary statistics grouped by queries, threads, users, hosts and objects.
• Implementation of the InnoDB engine has been improved, there has been an increase in performance when performing transactions and with activity with a predominance of data reading operations - in some situations, the acceleration reaches 230%.
• Lazy replication mode, which allows you to replicate data not immediately, but with a certain delay, which allows you to protect against operator errors (for example, accidentally deleting the contents of tables).
• Increase the maximum size of change log files (InnoDB Redo Log) from 4 GB to 2 TB.
• Security improvement: support for specifying authentication parameters in the .mylogin.cnf file in encrypted form; Adding the sha256_password plugin to store password hashes using the SHA-256 algorithm; adding a field to the mysql.user table with the password expiration time; new SQL functions VALIDATE_PASSWORD_STRENGTH () to evaluate password strength.
• Support for starting the server in read-only mode (option --innodb-read-only, only for InnoDB).
• Support for specifying fractional seconds in TIME, DATETIME, and TIMESTAMP functions to indicate microseconds.
• Discontinued support for the options --log, --log - slow-queries, --oneone thread, --safe-mode, --skip-thread-priority, --table- cache ".

You can familiarize yourself with the innovations in more detail on the corresponding page.

In connection with this release, the rivalry between MySQL and MariaDB is becoming more interesting, which has recently been rapidly displacing MySQL from distributions. At the moment, this has happened in Fedora and OpenSUSE. MySQL 5.6 Reference Manual Download

Page