DBAs and developers guide for MySQL5.6

To build next-generation Web applications and services, MySQL5.6 has improved the core functional areas of the database, including: improves the performance and scalability of the InnoDB engine, improves the query execution time of the optimizer, and diagnoses application availability. It supports online DDLSchema modification to enhance developers' flexibility and supports the use of Memcached.

To build next-generation Web applications and services, MySQL 5.6 has improved various functional areas of the Database Core, including: improves the performance and scalability of the InnoDB engine, improves the query execution time of the optimizer, and diagnoses application availability. It supports online DDL/Schema modification to enhance developer flexibility and supports the use of Memcached.

Build next-generation Web applications and services

To put it simply, MySQL 5.6 has improved various functional areas of the core database, including:

• Better performance and scalability
  1. Improve the transaction throughput of the InnoDB Engine
  2. Improves the query execution time and diagnosis of the optimizer.
• Better application availability and support for online DDL/Schema Modification
• Enhances developer flexibility and supports accessing InnoDB through Memcached APIs to implement NoSQL Functions
• Improved replication to meet high-performance and self-repair distributed deployment requirements
• Improved Performance Schema to better support various new hardware devices
• Improve Security
• And other important improvements

As a guide to MySQL 5.6 for DBAs and developers, this article introduces these important new features and provides some practical use cases.

Better performance and scalability: InnoDB Storage engine improvement

From the O & M perspective, MySQL 5.6 provides better performance and scalability for continuous linear growth in the system with multi-processor and high-CPU concurrent threads. The reason is that Oracle's InnoDB Storage engine removes the legacy thread contention and mutex locks, improving efficiency and concurrency. These improvements allow MySQL to take full advantage of the advanced multithreaded processing capabilities of x86-based COTS (commodity-off-the-shelf) hardware.

Internal read/write and read-only load test data show that the linear expansion capability of MySQL 5.6 is significantly higher than that of MySQL 5.5. Displays the linear growth of TPS in the number of read/write transactions per second when the number of concurrent CPU threads in MySQL 5.6 increases to 60.

For more information about the continuous linear growth of read-only TPS, see

Better transaction throughput

MySQL 5.6 improves the performance and scalability of highly concurrent, transactional, and read-intensive loads. In these cases, performance improvement is mainly reflected in the performance and scalability of application services as concurrent users grow. InnoDB restructured the architecture to reduce mutex contention and bottlenecks and provide a more consistent access path for the underlying data. These improvements include:

• Split Core mutex to remove single point of contention
• Flushing operations use new threads
• New multithreading purge
• New adaptive hash algorithm
• Less buffer pool contention
• Collects statistics from the persistent optimizer at more frequent and predictable intervals to generate better and more consistent query execution.

SysBench read/write performance tests show the results of these improvements:

On Linux, the TPS throughput of MySQL 5.6 is 5.5 higher than that of MySQL 150%, And the TPS throughput of MySQL 2008 is about 47% higher than that of MySQL.

Better read-only load Throughput

InnoDB has made new optimizations for read-only transactions, removing the transaction overhead. It can greatly improve performance for web-based queries and report applications. These optimizations are enabled by default when autocommit = 1. You can also enable them using the START_TRANSACTION_READ_ONLY statement:

SET autocommit = 0; START_TRANSACTION_READ_ONLY; SELECT c FROM T1 WHERE id = N; COMMIT;

The optimized result is as follows:

On the Linux platform, the read-only TPS throughput of MySQL 5.6 is 5.5 higher than that of MySQL 230%, And the throughput of MySQL 2008 is about 65% higher than that of MySQL.

The running platform configuration for the above performance test is as follows:

• Oracle Linux 6
• Intel? Xeon (R) E7540 x86_64
• MySQL leveraging:
  1. 60 of 96 available CPU threads
  2. 2 GHz with 512 GB RAM

Test Suite SysBench is a free tool for Performance Testing specific application cases, http://dev.mysql.com/downloads/benchmarks.html

If you are interested in MySQL 5.6 performance and performance tests of various features, refer to Mikael Ronstrom's blog and Dimitri Kravtchuk's blog. They shared the test results, the test cases and configurations used for testing are provided.

Better support for SSD)

Common hard disks often become bottlenecks of various systems because of their physical limitations, making it difficult to achieve good scalability in high concurrency. Therefore, many web applications that need to support high concurrency will deploy their MySQL on the SSD to obtain services that are reliable and have a similar access speed and memory. MySQL 5.6 includes several important improvements to support SSD devices:

• Supports 4 K and 8 K page sizes to adapt to SSD Standard Storage Algorithms
• The portable. ibd (InnoDB data) file allows the InnoDB "hot" table to be moved from the default data directory to the SSD or network storage device
• The InnoDB undo log can use an independent tablespace. It supports moving undo logs from the system tablespace to one or more independent tablespaces. For example, in the read-intensive I/O mode of the undo log, you can move the undo log to the SSD, while the system tablespace is still on the common hard disk.

Learn more SSD optimization technologies

Better query execution time and diagnosis: optimizer Improvement

The MySQL 5.6 optimizer is restructured to improve efficiency and performance. The main improvements include:

Subquery Optimization

Through Semi-join and materialized technologies, the MySQL optimizer improves the performance of subqueries and simplifies the complexity of writing queries by developers. In particular, subqueries in the From clause are materialized only when the subquery content is required to improve performance. Meanwhile, when necessary, the optimizer, add an index to the derived table to speed up reading records. Testing with the DBT-3 benchmark Query #13 statement shows a significant performance improvement over previous versions.

select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)from customer, orders, lineitemwhere o_orderkey in (                select l_orderkey                from lineitem                group by l_orderkey                having sum(l_quantity) > 313  )  and c_custkey = o_custkey  and o_orderkey = l_orderkeygroup by c_name, c_custkey, o_orderkey, o_orderdate, o_totalpriceorder by o_totalprice desc, o_orderdateLIMIT 100;

For more details, see From Months to Seconds with Subquery Materialization.

File Sorting Optimization for small Limit

For queries with order by and smaller LIMIT values, the optimizer can now generate an ordered result set through a single-time table scan. This type of query is common in Web applications and is used to display a few records in a large result set, as shown in the following example:

SELECT col1,... FROM t1... order by name LIMIT 10; the internal test shows that this optimization can increase the performance BY up to four times, greatly optimizing the user experience and response time. For more details, refer to blog

Index push-down (ICP)

By default, the optimizer pushes the where condition to the storage engine for evaluation, table scanning, and returned ordered result sets to the MySQL server.

CREATE TABLE person (      personid INTEGER PRIMARY KEY,      firstname CHAR(20),      lastname CHAR(20),      postalcode INTEGER,      age INTEGER,      address CHAR(50),      KEY k1 (postalcode,age)?   ) ENGINE=InnoDB;SELECT lastname, firstname FROM person   WHERE postalcode BETWEEN 5000 AND 5500 AND age BETWEEN 21 AND 22;

The internal test shows that the query of this type of table can improve the performance by up to 15 times through ICP optimization.

Batch Key Access (BKA) and multi-range read (MRR)

The optimizer now provides all primary keys to the storage engine in batches so that it can more effectively access, sort, and return data, reducing query execution time.

Tests of DBT-3 Query 13 and other disk-intensive Query statements show that BKA and MRR can increase performance by up to 280 times. For more details, see Batched Key Access Speeds Up Disk-Bound Join Queries.

Optimizer diagnosis improvement-MySQL 5.6 optimizer in diagnosis and debugging

• EXPLAIN for INSERT, UPDATE, and DELETE operations,
• EXPLAIN plan output in JSON format with more precise optimizer metrics and better readability
• Optimizer Traces for tracking the optimizer demo-- making process.

Learn about all of MySQL 5.6 Optimizer improvements and features, along with all technical documentation

If you want to learn implementation details, how to use and use examples, read the blog of the MySQL optimizer engineering team.

Application availability improvement: Online DDL/Schema Modification

Today, Web-based applications need to evolve rapidly to meet business needs. SLA is measured in minutes, days, or weeks. Therefore, when the application needs to quickly support new product lines or upgrade existing products, the backend database Schema needs to be smoothly upgraded. MySQL 5.6 adds the following DDL syntax to alter table to improve the flexibility and sensitivity of online Schema.

• CREATE INDEX
• DROP INDEX
• Change AUTO_INCREMENT value for a column
• ADD/DROP FOREIGN KEY
• Rename COLUMN
• Change row format, KEY_BLOCK_SIZE for a table
• Change column null, NOT_NULL
• Add, drop, reorder COLUMN

DBAs and developers can add/delete indexes online and perform standard InnoDB table modifications without stopping services. This greatly facilitates developers to flexibly modify the Schema to meet new business needs.

For more information about MySQL 5.6 InnoDB online DDL improvement and features, see

Agile development support: InnoDB supports NoSQL access

Currently, many web, cloud, Social and mobile applications require such services: they can perform fast Key/Value operations on data and ensure the ACID properties of such data, attackers can execute complex queries. With the NoSQL API of InnoDB, developers can have the features of traditional RDBMS and high-performance KV query capabilities.

MySQL 5.6 provides common Memcached APIs and InnoDB for simple KV operations. It contains the Memcached background plug-in mysqld. It interacts directly with the native API of InnoDB through the Memcached protocol, bypassing the query analysis phase that consumes a lot, query InnoDB data and execute data updates for compatible transactions. This API integrates the Memcached function into a persistent, crash-safe, transactional database and is compatible with the original standard Memcached library and client. Shows the implementation:

What is the difference between this and ordinary SQL Performance? The internal performance test shows that the SET/INSERT operation can increase the throughput by nine times in some scenarios:

This not only improves performance and flexibility, but also reduces complexity for developers and DBAs. The previously separated cache and database layers can now be placed in a data layer, it can also solve the problem of data consistency.

For more information, see InnoDB team blog

Learn more about the details and how to get started with the new Memcached API to InnoDB

Agile development support: expands InnoDB application scenarios

The optimization and new features of MySQL 5.6 extend the application scenarios of MySQL. developers can use only one storage engine of InnoDB to complete various tasks and simplify application development.

New full-text search (FTS)

As an alternative to MyISAM FTS, InnoDB supports creating FULLTEXT indexes for text content to accelerate word and phrase search. InnoDB full-text search supports natural language/Boolean mode, approximate search, and relevance sorting. The following is an example:

CREATE TABLE quotes(id int unsigned auto_increment primary key, author varchar(64), quote varchar(4000), source varchar(64), fulltext(quote)) engine=innodb;SELECT author AS "Apple" FROM quotes    WHERE match(quote) against (‘apple' in natural language mode);

New table space that can be migrated

InnoDB created in file-per-table mode. ibd files can now be migrated between different physical storage devices and database servers. When creating a table, developers can. the ibd file specifies a location that is not stored in the MySQL data directory. This feature allows developers to migrate "hot" tables to external network storage devices (such as SSD and HDD) to reduce server load. In addition, you can easily export/import InnoDB tables to quickly and seamlessly scale applications, as shown in the following example:

Export:

CREATE TABLE t(c1 INT) engine=InnoDB;FLUSH TABLE t FOR EXPORT; -- quiesce the table and create the meta data file$innodb_data_home_dir/test/t.cfgUNLOCK TABLES;

Import:

CREATE TABLE t(c1 INT) engine=InnoDB; -- if it doesn't already existALTER TABLE t DISCARD TABLESPACE;-- The user must stop all updates on the tables, prior to the IMPORTALTER TABLE t IMPORT TABLESPACE;

For more improvements to InnoDB, see the documentation.

Optimized replication and high availability

Replication is a key feature of MySQL's scalability and high availability. MySQL 5.6 adds a self-repair duplex replication topology and a high-performance Master/Slave service, enabling developers to build next-generation new applications.

New global transaction identifier (GTID)

GTID is used to track the transaction integrity in the master-slave replication topology, providing the foundation for self-built duplex recovery. It also facilitates DBAs and developers to find the slave database with the minimum replication latency when the master database fails. GTID is directly stored in Binlog, and similar tasks are no longer required, as in previous versions. Complex third-party plug-ins are required to complete similar tasks.

New MySQL replication tool set

MySQL 5.6 provides a set of tools written in Python to manage and monitor master-slave replication. GTID is used to implement the automatic fail-over function when the master database fails and the master database switch function during maintenance, it no longer relies on third-party high availability solutions and does not require OP manual intervention to reduce service downtime.

Download White Paper: MySQL replication: High Availability-build self-built duplex replication topology

Added multi-thread slave Database

Divides working threads according to the Schema and allows parallel updates. For systems that use different databases to separate applications, the efficiency can be greatly improved, such as multi-tenant systems ).

The test results of using multiple worker threads on 10 schemas by SysBench benchmarks show that the performance can be increased by about 5 times.

Added Binary Log Group Commit (BGC)

MySQL 5.6 writes binlogs to the master database by group during replication, instead of submitting them one by one, which greatly improves the performance of the master database. BGC also reduces lock waits and improves performance. The test results are as follows:

The throughput of MySQL 5.6 is about 5.5 higher than that of MySQL 180%. BGC eliminates the need for developers to make difficult choices between the performance of the master database and the scalability and high availability provided by MySQL replication, as they have previously done.

• New optimized row-based replication-MySQL 5.6 New Option binlog-row-image = minimal: only the data elements modified in the DML operation are copied to improve the master-slave replication throughput, reduce binlog size, network resources, and server memory usage.
• Add Crash-Safe slave database-MySQL 5.6 to store the Binlog location information in the table. When the replication fails, the slave database can automatically roll back to the last submitted state and restart the replication process without manual intervention. This not only reduces the operation burden, but also prevents data loss caused by the recovery from the wrong data file from the database. In addition, when the master database crashes, leading to a Binlog error, the server can also be automatically rolled back to the correct status.
• New replication checksum-MySQL 5.6 slave database checks whether the data is damaged and returns an error to ensure data integrity in the slave database and prevent the slave database from being damaged by wrong data.
• Added delayed replication-MySQL 5.6 allows developers to set the replication delay time to prevent unauthorized operations on the master database from spreading to the corresponding slave database. If a problem occurs in the master database before the delay is reached, With the failed able master to slave time delays, in the event of failure or mishap, slaves can be promoted to the new master in order to restore the database to its previous state. it also becomes possible to inspect the state of a database before an error or outage without the need to reload a back up.

For more information about MySQL 5.6 replication and high availability improvement and features, see the documentation and Mat Keep's Developer Zone article.

Finally, for more instructions on MySQL replication, refer to the following resources:

• White Paper: Introduction to MySQL Replication
• Using MySQL Replication for Highly Available Applications
• MySQL Replication Hands On Tutorial: Configuration, Provisioning and Management

Improved Performance Schema

MySQL Performance Schema is introduced in MySQL 5.5 to view key Performance indicators. MySQL 5.6 enhances the Performance Schema function and provides answers to common questions of DBAs and developers. Including:

• Statements/Stages
  • What are resource-intensive queries? Where is the time spent?
• Table/Index I/O, Table Locks
  • Which tables and indexes have the highest load or most contention?
• Users/Hosts/Accounts
  • Which users, hosts, and accounts are most charged for resources?
• Network I/O
  • What is the network load? What is the idle time of the session?
• Summaries
  • Aggregated statistics grouped by statement, thread, user, host, account or object.

MySQL 5.6 Performance Schema is enabled by default in my. cnf. However, the configuration has been optimized and does not occupy much resources (less than 5%, different scenarios), so it can be used online products. In addition, new atomic levels of instrumentation enable the capture of granular levels of resource consumption by users, hosts, accounts, applications, etc. for billing and chargeback purposes in cloud computing environments.

MySQL Engineering has several champions behind the 5.6 Performance Schema, and publish have published excellent blogs that you can reference for technical and practical details. to get started see Mark Leith's blog and Marc Alff's blog.

The MySQL docs are also an excellent resource for all that is available and that can be done with the 5.6 Performance Schema.

Security improvement

MySQL 5.6 introduces a major overhaul to how passwords are internally handled and encrypted. The new options and features include:

• New alternative to password in master.info-MySQL 5.6 extends the replication start slave command to enable DBAs to specify master user and password as part of the replication slave options and to authenticate the account used to connect the master through an external authentication plugin (user defined or those provided under MySQL Enterprise Edition ). with these options the user and password no longer need to be exposed in plain text in the master.info file.
• New encryption for passwords in general query log, slow query log, and binary log-Passwords in statements written to these logs are no longer recorded in plain text.
• New password hashing with appropriate strength-Default password hashing for internal MySQL server authentication via PASSWORD () is now done using the SHA-256 password hashing algorithm using a random salt value.
• New options for passwords on the command line-MySQL 5.6 introduces a new "scrambled" option/config file (. mylogin. cnf) that can be used to securely store user passwords that are used for command line operations.
• New change password at next login-DBAs and developers can now control when account passwords must be changed via a new password_expired flag in the mysql. user table.
• New policy-based Password validations-Passwords can now be validated for appropriate strength, length, mixed case, special chars, and other user defined policies based on LOW, MEDIUM and STRONG designation settings. see/doc/refman/5.6/en/validate-password-plugin.html for details and available configuration options.

Learn about these and all of MySQL 5.6 Security improvements and features, along with all technical documentation.

Other improvements

• New Default Configuration Optimization-MySQL 5.6 modifies the default value of server configuration items for the current system architecture to improve the server performance under the default configuration. These new values are suitable for common scenarios, saving the trouble of manual changes.

  Modify the configuration items, automatically set the configuration items, and the configuration items that can be set at startup. Check the default configuration of the server.

• Improve the TIME/TIMESTAMP/DATETIME data type:
  • TIME/TIMESTAMP/DATETIME-comparison and data selection of TIME/date in microseconds are now allowed
  • TIMESTAMP/DATETIME-values can be assigned to the current TIMESTAMP/automatically updated value, or both are the default values of the TIMESTAMP and DATETIME columns.
  • TIMESTAMP-the default value is null. If the TIMESTAMP column is not explicitly specified, NOW () is no longer used as the default value. If it is not null and the default value is not explicitly specified, it is treated as no default value.
• Better Condition Handling-GET DIAGNOSTICS
• MySQL 5.6 enables developers to easily check for error conditions and code for exceptions by introducing the new MySQL Diagnostics Area and corresponding get diagnostics interface command. the Diagnostic Area can be populated via multiple options and provides 2 kinds of information:
  • Statement-which provides affected row count and number of conditions that occurred
  • Condition-which provides error codes and messages for all conditions that were returned by a previous operation

The addressable items for each are:

The new get diagnostics command provides a standard interface into the Diagnostics Area and can be used via the CLI or from within application code to easily retrieve and handle the results of the most recent statement execution:

Mysql> drop table test. no_such_table; ERROR 1051 (42S02): Unknown table 'test. no_such_table 'mysql> get diagnostics condition 1-> @ p1 = RETURNED_SQLSTATE, @ p2 = MESSAGE_TEXT; mysql> SELECT @ p1, @ p2; + ------- + -------------------------------- + | @ p1 | @ p2 | + ------- + -------------------------------------- + | 42S02 | Unknown table 'test. no_such_table '| + ------- + -------------------------------------- +

Options for leveraging the MySQL Diagnotics Area are detailed in the MySQL Diagnostics documentation. get diagnotics is well known ented in the Get Diagnostics documentation.

• Improved IPv6 Support
  • MySQL 5.6 improves INET_ATON () to convert and store string-based IPv6 addresses as binary data for minimal space consumption.
  • MySQL 5.6 changes the default value for the bind-address option from "0.0.0.0" to "0: 0" so the MySQL server accepts connections for all IPv4 and IPv6 addresses. you can learn more in the MySQL Documentation
• Partition Function Improvement
  • Improve the Performance of tables that contain a large number of partitions-MySQL 5.6 has greatly improved the performance and scalability of tables that contain a large number of partitions, especially the insert operation, it can efficiently operate on hundreds of shards.
  • Import/export tables to/from partitioned tables-MySQL 5.6 supports the use of alter table... the exchange partition statement modifies table partitions or subpartitions. Rows of partitions or subpartitions can be moved to unpartitioned tables, or reverse operations can be performed. For more details, see MySQL Documentation.
  • Explicitly select a partition-MySQL 5.6 displays the specified partition or sub-partition and retrieves data rows that meet the WHERE condition in these partitions. Similar to automatic partition pruning, the retrieved partition is specified by the statement provider. This operation supports query statements and other DML statements (SELECT, DELETE, INSERT, REPLACE, UPDATE, load data, and load xml ). For more information, see MySQL Documentation.
• Improved GIS: Precise spatial operations-MySQL 5.6 provides geometric operations via precise object shapes that conform to the OpenGIS standard for testing the relationship between two geometric values. Learn more by referencing the MySQL Documentation

Summary

MySQL 5.5 was once called the best MySQL version that has been released. Now MySQL 5.6 has improved its performance, scalability, transaction throughput, and availability, to meet the requirements of web, cloud, and embedded application scenarios. MySQL 5.6 is now officially released. you can download the fully-functioning in is now Generally Available and you can download the fully-functioning, production-ready product from the MySQL Developer Zone provides a complete download function for MySQL at the product level.

As mentioned above, this article only describes the main features of MySQL 5.6. for complete changes, see MySQL Documentation.

Link: http://dev.mysql.com/tech-resources/articles/mysql-5.6.html

Original article address: DBA and developer guide for MySQL 5.6. Thank you for sharing it with me.