The MySQL Development team is happy to announce our 5.7.4 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.4 Release Notes. Here are the highlights. Enjoy!
Error Logging
Use of error logging API in InnoDB (WL#6755) This work by Anil Toshniwal completes the integration of the new error log formatting in 5.7. It was done for the server layer in 5.7.2 and now it has been done for InnoDB. There is one common API for all error logging. Messages to the error log now have the same format with connection ID and UTC timestamp with microsecond resolution. The user is in control of verbosity as messages to error log respect the “error”, “warning”, and “note” verbosity levels. This functionality has been requested by Giuseppe Maxia for some time, see Old and new MySQL verbosity .
Character Sets
GB 18030 Chinese character set (WL#4024). This work by Bin Su adds the MySQL character set gb18030 which supports the China National Standard GB 18030 character set. The new associated collations are gb18030_bin and gb18030_chinese_ci.
Parallel Flushing
Multiple page_cleaner threads (WL#6642). This work by Yasufumi Kinoshita implements parallel flushing. In MySQL 5.6, InnoDB has a single dedicated thread (page_cleaner) that is responsible for flushing dirty pages from all buffer pool instances. In 5.7.4, InnoDB now supports multiple page_cleaner threads, one coordinator and many workers, configurable from 1 to 64. The main motivation is improved scalability and throughput on multi-core systems. There is no fixed relationship between page_cleaner threads and buffer pool instances.
Transportable Tablespaces for Partitioned Tables
In 5.7.4, InnoDB supports the Transportable Tablespace feature for partitioned InnoDB tables and individual InnoDB table partitions. This enhancement eases backup procedures for partitioned tables and enables copying of partitioned tables and individual table partitions between MySQL instances. This feature has been requested by our users, as can be seen in Bug#70196 (by Valeriy Kravchuk) and Bug#71784 (by Xiaobin Lin). The feature has been implemented by Mattias Jonsson in terms of three worklogs:
Refactor the Tablespace class (WL#7342). This work refactored the Tablespace class into a separate Datafile class and a SysTablespace class that derive from Tablespace. This work is pure refactoring done to facilitate upcoming tablespace functionality and is not visible to the end user.
Support transportable tablespaces for partitioned innodb tables (WL#6867). This work extends the 5.6 transportable tablespaces by also supporting partitioned tables. This work implements: FLUSH TABLES ptable FOR EXPORT; ALTER TABLE ptable DISCARD TABLESPACE; ALTER TABLE ptable IMPORT TABLESPACE; By supporting partitioned tables it will ease the backup procedure for partitioned innodb tables, and also make it possible for MEB to make use of Transportable Tablespaces like it does for non partitioned innodb tables.
Support transportable tablespaces for single innodb partition (WL#6868). This work allows the user to select a subset of all [sub]partitions to DISCARD/IMPORT tablespaces for. This allows selective backup/restore/archive/transport of a set of partitions instead of all partitions. FLUSH TABLES FOR EXPORT will flush all partitions as before.
Statement Timeout
Implementation of server-side statement timeout (WL#6936) This work is based on a contribution submitted by Davi Arnaut (Bug#68252). The contribution has been adopted and included in MySQL 5.7.4 by Praveenkumar Hulakund. The work implements a server-side time limit for the execution of top-level read-only SELECT statements. After the specified amount of time, the statement will be aborted without affecting the session (connection) or the transaction contexts. The execution time limit can be set globally for the server, per session, or for individual SELECT statements. The execution time limit is given as milliseconds. Zero (0) is a special value meaning no time limit. With respect to Davi’s contribution we expanded it to cover Windows and Solaris, and restricted it by removing option for USER.
Meta Data Locking – Improved Scalability
In 5.7.4 we have removed Meta Data Locking (MDL) as the bottleneck for DML access to a single table. Removing this bottleneck accounts for 10% increased throughput in OLTP_RO/POINT_SELECT SysBench tests on higher core counts. This work clears the way for the next step in scaling the server layer, namely to remove the use of “thread locks” for InnoDB tables. The feature has been implemented by Dmitry Lenev in terms of three worklogs:
Improve MDL performance and scalability by implementing “fast-path” for DML locks (WL#7304). This work implements a “fast-path” for metadata locks acquired by DML statements. Since typical user workload consists of DML statements it makes sense to improve performance/scalability by optimizing the MDL subsystem for such type of statements.
Improve MDL scalability by using lock-free hash (WL#7305). This work implements lock-free hashing in the MDL subsystem. Scalability of the MDL subsystem for workloads where hashing (now done using the MurmurHash library) becomes a bottleneck can be improved by changing MDL_map to use a lock-free hash instead of our normal hash implementation protected by a mutex. More importantly such a transition opens the way to making MDL acquisition for DML statements lock-free.
Improve MDL performance and scalability by implementing a lock-free lock acquisition for DML (WL#7306). This work makes MDL acquisition for DML statements lock-free by replacing a complex construction–involving locking a mutex, object manipulation, and unlocking a mutex–with an atomic compare-and-swap (CAS) operator updating a single object member.
Password Rotation
Add timestamp in mysql.user on the last time the password was changed (WL#7131). This work by Atanu Ghosh provides the means for implementing password rotation policies. We have added a TIMESTAMP column inside mysql.user table and we update it when the password is updated. We have then added another column in mysql.user, holding the number of DAYS after which the password must expire. Password rotation policies can be set globally, and at the user level. See also Todd’s blog post Password expiration policy in MySQL Server 5.7.
Secure Deployments
MySQL deployments installed using RPM packages are now secure by default. The installation process creates only a single root account, ‘root’@’localhost’, automatically generates a random password for this account, and marks the password as expired. The MySQL administrator must connect as root using the generated random password and use the SET PASSWORD command to set a new password. (The random password is found in the $HOME/.mysql_secret file.). Installation creates no anonymous-user accounts, and no test database. The feature has been implemented by Harin Vadodaria in terms of three worklogs:
Random passwords generated for unattended installs (WL#6962). This work ensures that – by default – a random password is generated whenever mysql_install_db is executed. The password is stored in a specified file under the same context as the installation process is running and all affected accounts are marked with PASSWORD EXPIRE so it isn’t possible to hijack the account later by snatching the password. The user might choose to skip generation of random password, but this is no longer the default behavior. This provides a means for the user to explicitly create an installation where root’s password is empty.
Remove all anonymous accounts from all default deployments (WL#6977). This work ensures that – by default – that there are no anonymous user accounts created upon mysql_install_db execution. The user might choose to generate anonymous users, but this is not the default behavior.
Offer deployment without test db, test scripts or demofiles (WL#6973). This work ensures that – by default – that there are no test db, test scripts, or demofiles created upon mysql_install_db execution.
AES Encryption Modes
Support multiple AES Encryption modes (WL#6781). This work by Georgi Kodinov enhances the security strength of Advanced Encryption Standard (AES) encryption/decryption functions (AES_ENCRYPT/AES_DECRYPT) by adding support for larger key sizes and different block modes. Defaults are as before, but now the user can specify more secure options. This feature has also been backported to 5.6.17. Historically, and still used as defaults in 5.6 and 5.7, we are using a relatively small key size (128 bits, corresponding to “SECRET” according to NSA) and block mode (ECB, encrypting equal blocks with equal code blocks) to calculate the cipher. See also blog post Understand and satisfy your AES encryption needs with 5.6.17 by Georgi Kodinov.
Performance Schema
Earlier in the 5.7 development cycle we have delivered instrumentation for memory usage, stored programs, meta data locking, and transactions. In 5.7.2 we also added a performance schema table for SHOW SLAVE STATUS. In 5.7.4 we deliver instrumentation for prepared statements.
Prepared statements instrumentation (WL#5768). This work by Marc Alff and Mayank Prasad provide for performance schema tables to inspect prepared statements in use by the server. When a SQL PREPARE statement is executed, a new row representing the prepared statement is created in table prepared_statements_instances. The matching row is updated for each SQL EXECUTE statement, and deleted by the SQL DEALLOCATE PREPARE statement.
Extract digest (WL#7152). This work by Marc Alff has moved the digest related code from the performance schema implementation to the SQL layer of the server. Thus, the APIs to compute a query digest and digest text are now available in the SQL layer.
Marc Alff has also fixed performance schema related overhead in the connect and disconnect phases (see related Bug#70018), reduced memory footprint of internal buffers, and and improved the pfs_lock implementation. Furthermore, performance schema instrumentation has been added to capture GTIDs for transactionevents.
Client-Server Protocol
“Extending protocol’s OK packet” (WL#4797) This work by Vamsikrishna Bhagi extends the client-server protocols OK packet to allow the server to send additional information, e.g. server state changes. By default the server now sends information about the effect of “SET character_set” and “USE database” commands. This avoids situations like, for example, after “SET NAMES big5” the server assumes that the client will send big5 encoded data, while the client character set is still latin1.
Flag that a session-specific state exists (WL#6885). This work by Bharathy Satish makes it possible for the client to tell the server that it wants notification about session state changes, by setting the session variable “session track state change=ON”. This will make the server track all types of session states, such as adding or deleting a prepared statement, changing a server system variable, changing a user variable, creating or dropping a temporary tables, or changing the current database. Modifying any such attributes will cause the server to send a boolean tracker in the OK packet when the variable is ON. One possible usage for this feature is to be able to detect if it is possible to migrate a user session context to another physical connection within a load balanced or clustered environment.
GCC on Solaris
Switching from Sun Studio to GCC on Solaris. This work by Tor Didriksen switches the compiler from Sun Studio to GCC in order to compile Boost.Geometry, which is an external Geometry library that we now use internally for spatial functions.
Work towards a new Cost Model
We want to improve the cost based optimizer and replace existing heuristics with cost based decisions. We want to produce better cost estimates which also take into account new hardware architectures (larger buffers, caches, SSDs, etc.). Better cost estimates will lead to better decisions by the optimizer and thus to better performance. We have started to refactor the existing cost model code and to remove hard-coded constants. This will make the code more maintainable and make it possible to tune and configure the cost model for your particular hardware configuration, as well as laying the groundwork for storage engines to provide costs that factor in whether the data requested resides in memory or on disk. In the 5.7.4 DMR, we deliver three new APIs which facilitate a step by step transition towards the long term goal (by Olav Sandstå):
Optimizer Cost Model API (WL#7182) This work introduce a cost model API and a cost model module to be used by the optimizer. Essentially, where we before used hard coded constants we now call a function in this API. The next step will be to make the cost constants used in the optimizer cost model configurable, as hardware profiles can vary drastically (for example an SSD will have much lower costs associated with random I/O than a SATA HDD).
Handler interface changes for new cost model (WL#7209) This work introduced a new handler API for the new cost model. This is the API which provides dynamic cost estimates from the Storage Engine, e.g. estimates for doing key lookups, table scans, range scans, etc. In the future we would like to take into account whether a requested row is available in memory or or not, and other potential factors. The new API lives side by side with the old API to avoid compatibility issues. An important change is that cost estimates are now returned as Cost_estimate objects instead of double values. The optimizer cost model has been changed to use the new API functions. This work also provides parts of the work needed for eliminating the use of hard coded cost constants in the optimizer and handler code.
Interface for improved records per key estimates (WL#7338) This work implements the API and the data structures for storing records per key estimates (index statistics) as float instead of using integer values. This new interface is not yet in use.
Work on a Layered Optimizer Architecture
Currently, the phases of parsing, optimizing and execution are intermixed. Almost every module is spread over different optimizer parts. As a consequence, the cost of maintaining the codebase is high and extensibility is poor. We started out an optimizer refactoring project with the goal of clear separation of these phases. With a refactored code base, the optimizer will be able to evolve much faster. In the 5.7.4 DMR, we deliver the following two steps towards this goal:
Consolidate access method decisions in a single function (WL#6016) is a prerequisite for splitting JOIN_TAB in two parts: optimizer’s and executor’s. This work by Evgeny Potemkin settles the join plan at a single point, and avoids changing access methods in the middle of optimization. We also cleaned up the code for use of access methods, and such a clean up reduces the risk for bugs significantly.
Move permanent transformations from Join:optimize to Join::prepare (WL#7082) is the first step towards a fully functional prepared statement. In this work by Guilhem Bichot we consolidate permanent transformations into the prepare phase, instead of having them spread in both the prepare and optimizer phases. In the future, we want to prepare once and only once for each prepared statement, thus allowing us to offer the full performance advantages that prepared statements offer in other database servers.
Replication
Binlog_sender: do not reallocate (WL#7299). This work by Luis Soares implements an optimization on the dump thread that removes unnecessary reallocation of the send buffer. The user visible effect is that CPU usage will be lessened for each dump thread the master has spawned. See also Bug#31932 reported by Mark Callaghan.
Semisync: Semisync separate acks collector (WL#6630). This work by Libing Song reduces semisync delay by using separate threads to send and receive semisync acknowledgements. So event and ACK streams can now be sent and received simultaneously.
Change master without stopping SQL thread (WL#6120). In order to add/alter an option using the CANGE MASTER TO command, it was previously necessary to issue a STOP SLAVE command before the CHANGE MASTER TO command. This work by Shivji Jha relaxes this constraint.
Improve MTR test coverage for InnoDB
In 5.7.4 we made further progress with the test suite migration project. The two main replication suites, rpl and binlog, are completely migrated. The only remaining suite is main. This is very large and will be handled in smaller chunks. In 5.7.4 we have migrated tests for partitioning (WL#7405), authentication (WL#7410), charsets (ctype*) (WL#7404) and storage engines (federated, blackhole etc) (WL#7402) in main suite.
Other Refactoring
Decouple THD and st_transactions (WL#7193). This work by Dmitry Shulga prepares for a proper decoupling of the connection context from the transaction context in the server. The long term goal here is to let transactions live outside the scope of a connection. This work paves the way for our support of XA JOIN and XA RESUME operations, as the transaction will then be able to live beyond the scope of the initial connection. This work is also a critical step toward allowing transactions to be migrated from one physical connection to another in a load balanced or clustered MySQL environment.
Refactor atomics implementation (WL#7607). This work by Jon Olav Hauglid implements atomic operations by using self-contained static inline functions. The old implementation was hard to read and reason about, as it consisted of layer upon layer of macros and with macro definitions split between multiple files.
Deprecation & Removal
Remove innodb_*monitor tables in 5.7 (WL#7377). This work removes the InnoDB built-in magic tables: innodb_monitor, innodb_lock_monitor, innodb_tablespace_monitor, innodb_table_monitor, and innodb_mem_validate. Historically, CREATE TABLE on one of these names has set flags inside InnoDB to enable additional debug output to the error log. DROP TABLE has cleared the flags and stopped the output.
a) The innodb_monitor and innodb_lock_monitor tables have been replaced by global variables which can be switched ON/OFF.
b) The functionality covered by innodb_tablespace_monitor, innodb_table_monitor, and innodb_mem_validate have been removed in 5.7.4. Some of this functionality has already been replaced within Performance Schema, and other parts will be replaced in subsequent DMRs.
Remove innodb_use_sys_malloc & innodb_additional_mem_ pool_size system variable in 5.7 (WL#7628). This work removes these two variables in 5.7.4, they have been deprecated since 5.6.2. We decided to do this now because it simplified InnoDB scalability efforts, i.e. the InnoDB home grown memory manager was not as good as jemalloc or tcmalloc. The latter two are more scalable.
Remove IGNORE for ALTER TABLE in 5.7 (WL#7395). In 5.7.4 we have removed IGNORE for ALTER TABLE. We made this decision after Morgan’s blog post seeking community advice on this topic. We decided to remove IGNORE because it caused problems with foreign keys (rows removed in parent table), prohibits true online CREATE UNIQUE INDEX, and caused problems for replication.
Remove ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL MODES and make their functionality part of STRICT MODE in 5.7 (WL#7467). These SQL modes are folded into STRICT MODE in 5.7.4. We are also considering Making strict sql_mode the default for transactional engines in 5.7.
Remove outdated command line programs in 5.7. This work removed these command line programs in 5.7.4: mysqlaccess (WL#7620, Bug#69012), mysql_convert_table_format (WL#7034, Bug#69014), mysql_fix_extensions (WL#7036, Bug#69015), mysql_find_rows.sh (WL#7621, Bug#27482), mysql_setpermission (WL#7033, Bug#69016), and msql2mysql (WL#7035, Bug#69017). See also Morgan’s blogpost: Plan to remove unused command line programs (10 out of 43).