The MySQL Development team is happy to announce our 5.7.3 development milestone release, now available for download at dev.mysql.com. You can find the full list of changes and bug fixes in the 5.7.3 Release Notes. Here are the highlights. Enjoy!
Over 1 million QPS with InnoDB Memcached Plugin
After reaching 500 thousand QPS in Read-Only on SQL queries in 5.7.2 we now follow up with over 1 million QPS with InnoDB Memcached Plugin. Read more on this at Dimitri’s blog. Thanks to Yoshinori (Facebook) for inspiration!
Over 50 thousand connects/disconnects per second
Fast processing of new connections is important for applications which do high frequency connect/disconnect (e.g. PHP applications). 5.7.3 is able to handle 56K connect/disconnect per second. In comparison 5.7.2 handles about 45K, 5.6 handles about 26K, and 5.5 handles about 17K. The work on improving connect/disconnect speed has been inspired by ideas from Facebook, for example bugs reported by Domas Mituzas (Bug#62282, Bug#62283, Bug#62284, Bug#62285, Bug#62286) and covered by WL#6606. To reach this new level we fixed a bottleneck at the LOCK_thread_count mutex by splitting it into several mutexes, each protecting a separate structure/ variable. This work also builds upon code refactoring associated with fixing Bug#56666.
Optimizer
We’ve improved “IN queries” with row value expressions to be executed using range scans. Take SELECT * FROM table WHERE ( column_1, column_2 ) IN (( ‘a’, ‘b’ ), ( ‘c’, ‘d’ )) as an example. Prior to 5.7.3 the only way to enable range scan was to rewrite the WHERE condition into its equivalent AND/OR form, for example SELECT * FROM table WHERE ( column_1 = ‘a’ AND column_2 = ‘b’ ) OR ( column_1 = ‘c’ AND column_2 = ‘d’ )”. In 5.7.3 you get this improved performance without having to rewrite the query. See Bug#31188 reported by Mark Callaghan (Facebook).
“UNION ALL” no longer creates a temporary table. Prior to 5.7.3 union queries always used a temporary table to store the result before returning it to the user. In 5.7.3 the optimizer avoids creating a temporary table for the result of UNION ALL when there is no need for it, i.e., when there is no top-level ORDER BY. This saves the cost of creating, writing to and reading from the temporary table, which may be written to disk if the result is large. This will reduce the need for disk space for large unions, and perhaps even more important, the client will get the first rows quicker (i.e. immediately). See Bug#50674 reported by Mark Callaghan (Facebook).
Non-sorted fields in the sort buffer are now compacted. This optimization is about better utilization of the sort buffer and thus avoiding/reducing the need to go to disk while sorting. The user benefit is increased speed. For example if you have queries like “SELECT * FROM t ORDER BY c1” and table t contains char or varchar fields (c2, c3), then c2 and c3 are packed. Note: This is true for sysbench –oltp-order-ranges (SELECT c FROM sbtest WHERE id between 2 and 102 ORDER BY c;) and our experiments with this query show an increase in speed from 1-10% depending upon size of range used.
InnoDB
Fulltext index on InnoDB now supports an external parser just like MyISAM. The syntax of adding an external parser is like “ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER my_parser;” A parser plugin can operate in either of two roles:
- The plugin can replace the built-in parser. In this role, the plugin reads the input to be parsed, splits it up into words, and passes the words to the server (either for indexing or for word accumulation).
- The plugin can act in conjunction with the built-in parser by serving as a front end for it. In this role, the plugin extracts text from the input and passes the text to the parser, which splits up the text into words using its normal parsing rules.
For examples see documentation on writing full text plugins. See also Feature requests from Daniel van Eeden (Bug#68816) and Hartmut Holzgraefe (Bug#70400).
Redo log write code has been refactored for better performance. This work improves the performance for any workload that makes heavily usage of log_sys::mutex and for which innodb_flush_log_at_trx_commit = 2.
Performance Schema
Metadata Lock (MDL) instrumentation. With this work we expose which session owns which meta data lock and which session is waiting for which meta data lock in performance schema tables. This feature is critical to understanding metadata lock dependencies between sessions. In production, the DBA not only needs to know if a session is waiting on a metadata lock, but also which other session is currently holding the metadata lock for the same resource.
TRANSACTIONS instrumentation . With this work we add transaction instrumentation to the Performance Schema. This instrumentation exposes all aspects of transaction operation within the server and the storage engines. The information collected includes quantitative and qualitative data including transaction duration, transaction counts, frequency of various transaction attributes such as isolation level and access modes. The information is aggregated across several dimensions, including user, account, and session.
Replication
Added SSL support for mysqlbinlog. This work adds SSL options for the mysqlbinlog client program, allowing system administrators to perform remote binlog queries (–read-from-remote-server option) over secure connections. This was the last MySQL client program without SSL support.
MAKE –REPLICATION-* FILTER SETTINGS DYNAMIC. With this work the slave options –replicate-* becomes settable through the new command CHANGE REPLICATION FILTER. These options can now be changed dynamically while the server is running, enabling users to modify replication filtering rules without requiring a server stop and restart. This work originates in a contribution from Davi Arnaut (Bug#67362). Thanks Davi!
Semisync: Make the master wait for more than one slave to acknowledge back. This work implements an option to make the master wait for N slaves to acknowledge back, instead of just one, when semisync is ON. Choosing to wait for N slaves (N > 1), adds resiliency to consecutive failures. It also improves transaction durability as one transaction gets persisted in more than two servers before the results are externalized on the master.
Fabric
New server method for clearing session state. This work was motivated by MySQL Fabric driven failover scenario. It is now possible for a client to do a reset of the existing connection, i.e. to clean the existing session context and free up resources. Resetting a connection can be performed from either the mysql command prompt or from client application using the lib mysql API.
Security
Redefine client –ssl option to imply enforced encryption. This work redefines –ssl to enforce encryption from the client side. Before, when a MySQL client specified the –ssl option a connection could still happen without encryption being enforced. Now if specified and no ssl connection is available, the connection will fail instead.
Partitioning
Support ICP for partitioned tables. Before 5.7.3 partitioned tables did not support Index Condition Pushdown (ICP). This caused a severe performance drop in cases where users have moved from a non-partitioned table (that was using ICP) to a partitioned table (which would then no longer use ICP). See Bug#70001 reported by Justin Swanhart (Percona).