We recently announced our 5.7.5 Milestone Release, yet another milestone on our road to 5.7 GA. The purpose of our milestone releases is to get community feedback and to ensure high quality on 5.7 from day one. This blog post gives the reader a high level view on 5.7 so far, while also attempting to demonstrate where we are heading as many of the individual pieces make much more sense when you can see the bigger picture. You might further drill into the series of milestone blog posts (5.7.1, 5.7.2, 5.7.3, 5.7.4, 5.7.5) and even further down to the individual worklogs with their specifications and implementation details. Or perhaps you prefer to just look at the source code at github.com/mysql.
The MySQL development team is stronger than ever, and so far we have implemented (as of September 2014) 244 worklogs, added 497 MTR tests, and fixed 1263 bugs (5.7 specific). It might all be a bit overwhelming, so this little guide might be useful. Note: we’ve also released a number of early access features that I won’t discuss in this post as I’m focused on 5.7 DMRs here, but you can find an overview of the features recently made available via the MySQL Labs here, and you can find additional details on the individual features here, here, here, here, here, here, here, here, & here. OK, now on to the 5.7 DMR overview to date.
Performance & Scalability
Performance and scalability is a priority for MySQL, learning from community feedback and taking into account trends and developments in computer hardware and its architecture. So far in 5.7 we have delivered impressive read-only scalability results with InnoDB and significantly speeded up the connection handling in the server layer. We are also having good progress on InnoDB read-write scalability, fast flushing, and fast bulk data loads.
InnoDB Read-Only Scalability. We have improved the performance for Read-Only (RO) and Read-Mostly workloads. We have significantly improved how InnoDB handles RO trans-actions (WL#6047, WL#6899, WL#6906, WL#6578). We have also removed server layer contentions related to Meta Data Locking (MDL) and removed the use of “thread locks” (thd_locks) for InnoDB (WL#7304, WL#7305, WL#7306, WL#6671). See Dimitri Kravtchuk’s articles “MySQL Performance: reaching 500K QPS with MySQL 5.7“, “MySQL 5.7 : Over 1M QPS with InnoDB Memcached Plugin“, Sunny Bains’ article “Transaction life cycle improvements in 5.7.3“, and Jimmy Yang’s article “MySQL 5.7.3: Deep dive into 1mil QPS with InnoDB Memcached“.
InnoDB Read-Write Scalability. We have also improved the performance of Read-Write (RW) workloads. We have removed the “index lock contention” in InnoDB (WL#6363, WL#6326). The index lock that was used to protect the entire index tree structure is now replaced by more fine grained “block locks” in the tree. See Yasufumi Kinoshita’s article “MySQL-5.7 improves DML oriented workloads“.
InnoDB Faster & Parallel Flushing. We have reduced the number of pages scanned when doing flush list batches, speeding up page flushing (WL#7047). The time complexity of a scan is reduced from O(n*n) to O(n). We have also implemented parallel flushing by having multiple page_cleaner threads (WL#6642). This gives improved scalability and throughput on multi-core systems and avoids flushing becoming the bottleneck.
Speeding up Connection Handling. In some application scenarios (e.g. PHP applications) client connections have very short life spans, perhaps only executing a single query. This means that the time spent processing connects and disconnects can have a large impact on the overall performance. In 5.7 we have offloaded thread initialization and network initialization to a worker thread (WL#6606) and more than doubled MySQL’s ability to do high frequency connect/disconnect cycles, from 26K to 56K connect/disconnect cycles per second. See also Jon Olav Hauglid’s article “Improving connect/disconnect performance“.
Bulk Data Load Improvements. Bulk Load for Create Index (WL#7277). This work implements sorted index builds, thus making CREATE INDEX operations much faster. Prior to this work InnoDB looped through the base table and created one record in the index table for each record in the base table. After this work InnoDB reads many records from the base table, sorts the records using the index key, and then inserts the chunked set of rows into the index table.
Online Operations
Always ON properties are essential to state of the art web solutions. It is important for DBAs or DevOps to be able to tune and extend their production system while continuing to serve users. Thus, we continue to ensure that MySQL is in the front in this area. So far in 5.7 we have delivered:
Resize the InnoDB Buffer Pool Online (WL#6117). This work adds the capability to change the value of innodb_buffer_pool_size dynamically. This provides the ability to tune the buffer pool size—without incurring any downtime—as your database usage patterns evolve over time.
Automatic Truncation of UNOD Logs (WL#6965). This work implements automatic truncation of UNDO logs when separate UNDO tablespaces have been configured. InnoDB operates with several UNDO tablespaces that are periodically truncated, one at a time. While one UNDO tablespace is being truncated, the other UNDO tablespaces will still be available to service transaction management and ensure minimal impact on transaction processing. The purpose of this work is to avoid ever growing UNDO log file sizes that could occur in some usage scenarios. See also Bug#1287 reported by Scott Ellsworth.
InnoDB Online Alter Table. We have added support for online RENAME INDEX (WL#6752, WL#6555) and enlarge VARCHAR column size operations (WL#6554).
Setting Replication Filters Without Server Restart (WL#7057). With this work the slave options --replicate-* become settable via the new CHANGE REPLICATION FILTER command. 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). See also Venkatesh Duggirala’s article “Making MySQL Slave Replication Filters Dynamic“.
CHANGE MASTER Without Stopping the 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 relaxes that constraint. See also Shivji Jha’s article “Change master without stopping slave altogether“.
Optimizer Improvements
Many exciting things are going on in the optimizer area, such as Cost Model work, a new parser, a layered optimizer architecture, and a new GIS implementation. In addition we have implemented the following set of feature requests:
Improved “IN queries” With Row Value Expressions to Be Executed Using Range Scans (WL#7019). We removed the previous requirement on rewriting the WHERE condition into its equivalent AND/OR form. See Bug#31188 reported by Mark Callaghan and Bug#16247 reported by Domas Mituzas. See also Martin Hansson’s article “Range access: now in an IN predicate near you“.
“UNION ALL” No Longer Creates a Temporary Table (WL#1763). In 5.7 the optimizer avoids creating a temporary table for the result of UNION ALL queries when there is no need for it, i.e., when there is no top-level ORDER BY clause. This reduces the need for disk space and disk I/O when executing large unions, and perhaps even more important, the client will get the first rows immediately. See Bug#50674 reported by Mark Callaghan. See also Norvald H. Ryeng’s article “State of the UNION“.
Non-Sorted Fields in the Sort Buffer are Now Compacted (WL#1509). This optimization is about better utilizing the sort buffer, thus avoiding/reducing the need to go to disk while sorting. The user benefit is increased query performance.
EXPLAIN for Running Queries (WL#6369). This feature is useful if you are running a statement in one session that is taking a long time to complete; using EXPLAIN FOR CONNECTION in another session may yield useful information about the cause of the delay and thus help you optimize your schema and statements.
JSON EXPLAIN (WL#6510). We have enhanced the JSON EXPLAIN output by printing the total query cost, the cost per table, and the amount of data processed. This will make it easier for a user to see the difference between good and bad execution plans. See also Øystein Grøvlen’s article “MySQL EXPLAIN Explained“.
Make Use of Condition Filtering in the Optimizer (WL#6635). This work improves join ordering. It provides a much better prefix rows estimate by taking into account not only conditions that are used by the chosen access method, but all other relevant conditions as well. See Jørgen Løland’s articles “A New Dimension to MySQL Query Optimization” part1 and part2.
Improved ONLY_FULL_GROUP_BY SQL Mode (WL#2489). We have improved the behavior of the ONLY_FULL_GROUP_BY SQL mode and also enabled it by default in 5.7.5+. This work makes the SQL mode far less strict about selected/order expressions because the server now properly recognizes functional dependencies. This work addresses many user complaints, such as those described in Roland Bouman’s article “Debunking GROUP BY myths” and Bug#51058.
Parser Refactoring
We are in the process of refactoring the SQL parser in an incremental way. The old parser had critical limitations because of its grammar complexity and top-down parsing style which lead to poor maintainability and extensibility. So far we have done a lot of preparatory work (WL#5967, WL#7199), refactored the SELECT statement (WL#7200), and the SET statement (WL#7203). We plan to rewrite the entire parser. See the article by Gleb Shchepa “SQL parser refactoring in 5.7.4 LAB release“.
Optimizer Refactoring
Currently the phases of parsing, optimizing, and execution are all intermixed. Almost every module is spread over different parts and sections of the optimizer. As a consequence, the cost of maintaining the codebase is high and extensibility is poor. We started out on 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. For example, we see this as a prerequisite for improving prepared statements. See WL#6016, WL#6042, WL#7082, and WL#7540. See also Guilhem Bichot’s article “Re-factoring some internals of prepared statements in 5.7“.
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 query 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. So far we have done preparatory infrastructure work and removed hard coded cost constants. These are now replaced by configurable cost values that can be changed by the user, without touching the source code, and that can be adjusted by the server administrator. (WL#7182, WL#7209, WL#7338, WL#5869, WL#6068, WL#7339, WL#7276, WL#7315, WL#7316). See also Jørgen Løland’s article “The MySQL Optimizer Cost Model Project“.
InnoDB Fulltext Search
We introduced InnoDB Fulltext Search in 5.6. We have now added greater flexibility and further optimizations. For example, fulltext indexes in InnoDB now support an external parser just like MyISAM (WL#6943). The plugin can either replace the built-in parser or it can act as a front-end for it. See also Feature requests from Daniel van Eeden (Bug#68816) and Hartmut Holzgraefe (Bug#70400). We have also implemented optimizer hints that are passed down to InnoDB about a query so that InnoDB may skip part of the full text search processing, e.g. not to compute the ranking values if they are not needed (WL#7123). See Shaohua Wang’s article “InnoDB supports plugin parser in fulltext index” and Matt Lord’s article “Rankings with InnoDB Full-Text Search“.
Performance Schema
Monitoring is important to our users and customers, and essential to any data management system. Our goal is to be “best in class”. At the core of our monitoring strategy we have Performance Schema, introduced in 5.5. Performance Schema is a MySQL Storage Engine built for the special purpose of storing dynamically created events, and at the same time providing a uniform well known interface to events and their configuration. In 5.7 we continue to extend and enhance our monitoring, utilizing the Performance Schema infrastructure. We have instrumented Metadata Locking (WL#5879), Transactions (WL#5864), Memory Usage (WL#3249, WL#7777), Stored Programs (WL#5766), Prepared Statements (WL#5768). We have also exposed SHOW SLAVE STATUS information (WL#3656) and USER VARIABLES (WL#6884) in Performance Schema, all while further reducing the overhead (WL#7802) and footprint. See also Mayank Prasad’s article “Performance Schema implementation Internals: Registering instruments” and “MySQL Performance Schema : Prepared Statements Instrumentation“.
Fabric Support
Oracle announced GA for MySQL Fabric on May 27, 2014. The Server team is working on some features to improve sharding, failover, and management of server farms. We have implemented a new server method for clearing session state (WL#6797). 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. We have also implemented a new server method to bring servers off line (WL#3836). The intended usage is for upgrade purposes. Setting the server to offline mode will gracefully disconnect all connected clients except those with the SUPER privilege. “Super” users are allowed to connect and manage the system while in offline mode.
Security
We are continuously working on improving MySQL security, for example to ensure “secure by default” installations. We are also working on better data encryption, better password handling, better transport layer security, and more.
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 then connect as root using the generated random password and use the SET PASSWORD command to set a new password (WL#6962). The installation process creates no anonymous user accounts, no test database, and no demo related files (WL#6977, WL#6973).
Improved Encryption. We now support multiple AES Encryption modes (WL#6781). We have enhanced 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. See also Georgi Kodinov’s article “Understand and satisfy your AES encryption needs with 5.6.17“.
Password Rotation. We have added a timestamp to the mysql.user table on the last time the password was changed (WL#7131). This work provides the means for implementing password rotation policies. See Todd Farmer’s article “Implementing a password policy in MySQL“.
SSL Improvements. We have redefined the client --ssl option to imply enforced encryption (WL#6791). 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 instead fail. We have also added SSL support for mysqlbinlog (WL#7198).
Partitioning
Our overall plan is to move in the direction of InnoDB native partitioning, which is now in Labs. This will pave the way for better partitioning; e.g. features such as parallel query processing, full-text indexes, and foreign keys on partitioned tables. So far in 5.7 we have added support for Index Condition Pushdown (ICP) for partitioned tables (WL#7231, motivated by Bug#70001), we have added support for the [{WITH|WITHOUT} VALIDATION] clause to the EXCHANGE PARTITION (WL#5630, motivated by Bug#57708), and we have added support for transportable tablespaces for partitioned innodb tables (WL#6867, WL#6868). See also Mattias Jonsson’s article “MySQL 5.7.4 now supports Transportable Tablespaces for InnoDB Partitions“.
InnoDB Temporary Table Performance
One of the goals of 5.7 is to optimize InnoDB temp tables for better performance (normal SQL temporary tables). First, we made temp table creation and removal a more light-weight operation by avoiding the unnecessary step of persisting temp table metadata to disk. We moved temp tables to a separate tablespace (WL#6560) so that the recovery process for temp tables becomes a single stateless step by simply re-creating it at start-up. We removed unnecessary persistence for temp tables (WL#6469). Temp tables are only visible within the connection/session in which they were created, and they are bound by the lifetime of the server. We optimized DML for Temp Tables (WL#6470) by removing unnecessary UNDO and REDO logging, change buffering, and locking. We added an additional type of UNDO log (WL#6915), one that is not REDO logged and resides in a new separate temp tablespace. These non-redo-logged UNDO logs are not required during recovery and are only used for rollback operations.
Second, we made a special type of temporary tables which we call “intrinsic temporary tables” (WL#7682, WL#6711). An intrinsic temporary table is like a normal temporary table but with relaxed ACID and MVCC semantics. The purpose is to support internal use cases where internal modules such as the optimizer demand light-weight and ultra-fast tables for quick intermediate operations. Finally, we made the optimizer capable of using InnoDB “intrinsic temporary tables” for internal storage (WL#6711). Historically the optimizer has been using MyISAM for storage of internal temporary tables created as part of query execution. Now InnoDB can be used instead, providing better performance in most use-cases. While MyISAM currently remains the default, our intention is to switch to InnoDB Temp Tables as the default.
Buffer Pool—Dump and Load
InnoDB Buffer Pool Dump and Load Enhancements (WL#6504). This work improves both the dump and load scenarios. It is now possible to dump only the hottest N% of the pages from each buffer pool. The load operation is also made less disruptive to user activity because the load now happens in the background while continuing to serve clients; while also attempting not to be too aggressive and taking too much IO capacity away from servicing new client requests.
Tools
innochecksum. Improve Innochecksum tool (WL#6045). This work significantly extends the innochecksum utility’s functionality. It is now possible to specify the checksum algorithm (innodb/crc32/none), rewrite the current checksum using the specified algorithm, rewrite the checksum even if the current checksum is invalid, and specify the maximum checksum mismatch allowed before terminating the program. Innochecksum can also now operate on multiple tablespace files and on multiple files in the same tablespace. See Anil Toshniwal’s article “Improving Innochecksum“.
mysql_upgrade. Refactor mysql_upgrade tool (WL#7308). This work is a rewrite of the mysql_upgrade tool, which fixes many reported bugs while also making mysql_upgrade more robust and easier to maintain. For example, this work fixes Bug#65288 reported by Nicholas Bamber and Bug#71579 reported by Florian Weimer.
mysqlbinlog. Adding SSL support for the mysqlbinlog tool (WL#7198). This work adds SSL options and support for the mysqlbinlog client program, allowing system administrators to perform remote binlog queries (--read-from-remote-server) over secure connections. This was previously the last remaining MySQL client program without SSL support.
mysql_secure_installation. Convert mysql_secure_installation script to C/C++ (WL#6441). This program can now connect to the server directly and execute the specified commands using the C API (libmysql). This removes the need for storing the user supplied password in a temporary option file on the filesystem.
mysql_install_db. Convert the mysql_install_db script to C/C++ (WL#7688). This work makes the program usable on all platforms—particularly on Windows—while also redesigning the program to provide a better user experience, cover more functionality, and improve security.
Community Contributions
Statement Timeouts. Implementation of server-side statement timeouts (WL#6936). This work is based on a contribution submitted by Davi Arnaut (Bug#68252). 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. See Praveen Hulakund’s article “Server-side SELECT statement timeouts“.
Multiple User Level Locks. Allow multiple locks in GET_LOCK() (WL#1159). User-level locks are often used to organize mutual exclusion when accessing some resource in cases when table or row-level locks are not appropriate. This work allows for multiple user level locks per connection. The work is based on a contribution by Konstantin Osipov, see Bug#67806.
Triggers
BEFORE Triggers Are Not Processed for NOT NULL Columns (WL#6030). This work ensures that we check column constraints at the end of the SQL statement. This is in compliance with the SQL standard. In 5.6 and earlier, MySQL checks the column constraints too soon. See Bug#6295 reported by Peter Gulutzan, and Dmitry Shulga’s article “BEFORE triggers and NOT NULL columns in MySQL“.
Multiple Triggers Per Table (WL#3253). This work provides the ability to have more than one trigger for every action (INSERT, UPDATE, DELETE) and timing (BEFORE or AFTER). This is in accordance with the SQL standard. See Dmitry Shulga’s article “Support for multiple triggers per table for the same value of action/timing“.
IGNORE Clause
Define and Reimplement IGNORE (WL#6614). This work properly defines the meaning and handling of the IGNORE clause, a MySQL extension to the SQL standard. It reimplements IGNORE so that it is consistent across all supported SQL statements while also making it much easier to maintain. See also Bug#30191, Bug#49539, Bug#55421, Bug#54543, Bug#54106, Bug#49534, Bug#47788, Bug#46539, and Bug#46425.
STRICT Mode
Define and Reimplement STRICT Mode (WL#6891). We have made STRICT MODE behaviour consistent across all supported SQL statements. We have also made STRICT MODE the default for all transactional storage engines (WL#7764). The IGNORE clause downgrades errors to warnings to allow statements to skip row(s) which would have otherwise have caused the entire statement to abort. STRICT MODE does just the opposite—it upgrades warnings to errors. Similar to IGNORE, STRICT MODE has not previously been clearly and consistently defined, thus the implementation has been the source of many bugs like Bug#42910, Bug#5929, Bug#43880, Bug#48637, Bug#5912, and Bug#5913.
MySQL Client
Log Interactive Commands to Syslog (WL#6788). This work introduces a new client option, --syslog, which enables/disables the logging of attributes like sudo_user (or user), mysql_user, connection_id, db_server, DB, and query entered in an interactive session. This has been requested by some users due to auditing compliance requirements, i.e. requirements to log all interactive commands entered at the mysql command-line prompt to syslog.
Client Side Protocol Tracing (WL#6226). This work creates hooks inside the client library code which allows tracing of protocol events such as sending packets to the server, receiving server replies, and authentication handshakes. This provides a mechanism for collecting performance data about the client-server connections, from the client’s perspective.
Client-Server Protocol
Extending the Protocol’s OK Packet (WL#4797). This work extends the client-server protocol’s 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 using latin1.
Flag for Session-Specific State (WL#6885). This work makes it possible for the client to tell the server that it wants notification about session state changes. 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.
InnoDB Spatial Indexes
We have implemented spatial indexing within InnoDB. InnoDB spatial indexes can be used with all existing syntax that has been developed for MyISAM spatial indexes. In addition, InnoDB spatial indexes supports full transactional properties, as well as isolation levels. It employs predicate locks to prevent phantom scenarios. See Jimmy Yang’s article “InnoDB Spatial Indexes in 5.7.4 LAB release“.
GEOMETRY Datatype Support (WL#6455). This work adds a new InnoDB internal datatype called DATA_GEOMETRY, and maps all MySQL GEOMETRY datatypes to this new internal datatype.
R-tree Index Support (WL#6968). This work implements R-tree search and key positioning. The R-tree search and traversal is different from that of B-tree in the sense that a search criteria could be met in multiple leaf pages of different search paths. A querying bounding box can intersect or contain multiple leaf and non-leaf bounding boxes.
Support DML Operations for InnoDB R-tree Indexes (WL#6745). This work adds R-tree index support in InnoDB on the Geometry datatype (R-tree split and shrink operations). This work adopts all of MyISAM’s Minimum Bounding Box (MBB/MBR) manipulation functions and supports 2 dimension GIS datatypes, just as MyISAM does.
Support Predicate Locking for Spatial Indexes (WL#6609). This work implements predicate locks to enforce consistent reads for spatial indexes.
Store the GIS POINT Datatype as a Fixed Length Column Rather Than as a BLOB (WL#6942). This is an optimization for POINTs, a commonly used GIS datatype.
Optimizer GIS
Now that InnoDB is the default storage engine for MySQL, our user base is rapidly transitioning to InnoDB. One capability that they have been demanding is a performant and scalable GIS implementation. Along with adding spatial (R-tree) index support to InnoDB, we also decided to replace the original GIS algorithms with a more powerful, reliable, effective and efficient geometric engine. After many comparisons of the available open source geometry engines on multiple aspects, Boost.Geometry was finally chosen. See also Manyi Lu’s article “Why Boost.Geometry in MySQL?“, David Zhao’s article “Making Use of Boost Geometry in MySQL GIS“, and Matt Lord’s article “MySQL 5.7 and GIS, an Example“.
Spatial Relation Check Functions (WL#7220). This work implements MySQL GIS relation checking functions using Boost.Geometry. Many type combinations which were not previously supported are now supported, e.g. for functions like within, intersects, equals, disjoint, cross, and overlaps.
Geometry Set Operations (WL#7221). This work implements MySQL GIS geometry set operations using Boost.Geometry. Many type combinations which were previously not supported are now supported, e.g. for set operations like intersection, union, and difference.
Spatial Analysis Functions (WL#7236). This work refactors spatial analysis functions including area(), centroid(), convexhull(), distance() and envelope() using Boost.Geometry functions. The semantics of these functions are found in OGC standard specifications.
WKB Geometry Container (WL#7280). This work implements WKB containers that conform to the Boost.Range concept so as to be used as an effective adapter between existing WKB geometry data and Boost.Geometry algorithms. The aim is to avoid conversions between WKB enconded byte strings and Boost.Geometry objects, since such conversions can be expensive.
Geohash Encoding and Decoding Functions (WL#7928). This work adds functions to encode and decode geohashes. Geohash is a system for encoding latitude and longitude coordinates of arbitrary precision into a text string. These added MySQL functions will make it possible for applications to import and export data from MySQL using the Geohash format. It will also make it possible to index and search for geographic data in one-dimensional (e.g. B-tree) indexes.
GeoJSON Support for GIS (WL#7444). This work by adds functions for parsing and generating GeoJSON documents into GIS data types: ST_AsGeoJSON and ST_GeomFromGeoJSON. GeoJSON is an open standard for encoding geometric/geographical features. GeoJSON supports the same geometric/geographic datatypes that are already supported by MySQL. GeoJSON also includes the possibility to declare which coordinate reference system (CRS) is used (WKT and WKB lack this).
Replication
Non-Blocking SHOW SLAVE STATUS (WL#6402). This work adds a non-blocking option to SHOW SLAVE STATUS. The new option makes SHOW SLAVE STATUS non-blocking when run in parallel with STOP SLAVE (stopping the slave may take a long time to complete when it is waiting for an SQL thread to finish applying large events).
Add Idempotent Mode to mysqlbinlog (WL#6403). This work provides an idempotent mode of operation for the mysql server. In this mode the server will ignore errors while applying ROW based events from a binlog file. This mode is useful when a DBA wants to replay binlogs using mysqlbinlog, but against a MySQL server which may not contain all of the data, so suppressing duplicate-key and no-key-found errors can be very useful.
Add a Rewrite-DB Option to mysqlbinlog for ROW Events (WL#6404). This work adds support for a rewrite-db option to mysqlbinlog, so that a ROW based events can be applied to a different database/schema. I.E. replace “from_db” to ‘to_db’ when reading a ROW based event from the binlog.
Binlog_sender: Do Not Reallocate (WL#7299). This work 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 that the master spawns. See also Bug#31932 reported by Mark Callaghan.
Move Status Variables to Replication Performance Schema Tables (WL#7817). This work moves replication system variables to Performance Schema tables so that they can be monitored per-source and not simply as global variables. This work is a requirement for Multi-source Replication.
Make Master-Slave Syncing Option Independent of the Slave Threads (WL#7796). This work adds a new SQL function WAIT_FOR_EXECUTED_GTID_SET which makes the master-slave syncing option independent of the slave threads. If the slave thread is not running the WAIT_FOR_EXECUTED_GTID_SET(GTID_SET [, TIMEOUT]) function, then keep waiting until success (0) or timeout (1).
Optimize GTIDs for Passive Slaves — Store GTIDs in a Table (WL#6559). This work adds the option of storing GTIDs in a table instead of in the binary log. The use case may be a slave that is only used for read scale-out and is never going to become a master, thus it may not have any use for the transactions in the binary log, but it may have a use for the related GTID features (e.g. to initiate a fail over to another new master).
Waiting for More Transactions to Enter Binlog Group Commit (BGC) Queues (WL#7742). This work adds two new options to introduce an artificial delay to make the binary log group commit procedure pause. This increases the likelihood that more transactions are flushed and synced together to disk, thus reducing the overall time spent to commit a group of transactions (the bigger the groups the less number of sync operations). With the correct tuning, this can make the slave perform several times faster without compromising the master’s throughput.
Semi-Sync Replication
Make the Master Wait for More than One Slave to Acknowledge Back (WL#7169). This work implements an option to make the master wait for N slaves to acknowledge back, instead of just one, when semi-sync is enabled. 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.
Externalize Transactions Only after ACK is Received (WL#6355). This work allows for true lossless failovers when using semi-sync replication. If the master crashes, the slave is still ensured to be up to date. The implementation makes the master wait for the ACK after preparing within the storage engine and writing to the binary log, but before committing to the storage engine. See also Libing Song’s article “Loss-less Semi-Synchronous Replication on MySQL 5.7.2“.
Semi-Sync — Separate ACKs Collector (WL#6630). This work reduces semi-sync delays by using separate threads to send and receive semi-sync acknowledgements. So event and ACK streams can now be sent and received simultaneously.
Multi-Threaded Slaves (MTS)
Intra-Schema Parallel Slave Execution (WL#6314). This work implements intra-schema multi-threaded slaves. With this implementation the slave will be able to apply transactions in parallel, even within a single database or schema, as long as they have a disjoint read and write set. See also Rohit’s article “MySQL 5.7 Enhanced MTS: configuring slave for Intra-database parallelization“.
Ordered Commits (Sequential Consistency) (WL#6813). This work ensures that the commits by slave applier threads running in parallel will be done in the same order as they were on the master. This also means that the slave will never externalize a database state which was never externalized by the master. This is a requirement when the applications reading from the slave must observe the same set of states that existed on the master due to some application enforced constraint. This has become a necessity after WL#6314, which enables multiple transactions to be executed in parallel by the slave threads, some of which may be modifying a single database.
Support Slave Transaction Retries in Multi-Threaded Slave Mode (WL#6964). This work enables Multi-Threaded Slave (MTS) replication slave servers to retry a transaction after a temporary failure. Before this, only non-MTS replication slave servers attempted to retry a transaction after a temporary failure. See Bug#68465 reported by Yoshinori Matsunobu.
Other Improvements
DTrace Support (WL#7894). We have added full DTrace support to MySQL in the Server 5.6+ packages we ship for Oracle Linux 6+.
Update_time for InnoDB Tables (WL#6658). This work implements in-memory maintenance of update_time for InnoDB tables. This functionality was previously missing in InnoDB and people have been asking for it, see Bug#2681 reported by Phil Sladen.
TRUNCATE TABLE Statement Becomes Atomic (WL#6501) This work makes the internal InnoDB TRUNCATE TABLE statement atomic by reinitializing the original table-space header with the same space id and then physically truncating its .ibd file during the truncation of a single table tablespace.
Proper Connection ID Handling (WL#7293). This work avoids the attempted reuse of any connection IDs that are still being used. See also Bug#44167 reported by Jan Kneschke.
Error Reporting — Stacked Diagnostic Areas (WL#6406). This work implements support for stacked diagnostic areas as defined by the SQL standard. The GET DIAGNOSTICS statement has been extended to support GET [STACKED] DIAGNOSTICS.
Error Reporting — Most Statements Should Clear the Diagnostic Area (WL#5928). This work makes MySQL follow the SQL standard with respect to clearing the diagnostic area. This work fixes bugs like Bug#35296, Bug#43012, and Bug#49634.
Error Logging — Allow Control of Verbosity (WL#6661, WL#6755). This work gives the DBA control of how verbose the MySQL Server should be when writing to the error log (ERROR/WARNING/NOTE). This work also changes the timestamp format printed to a more standard format (UTC timestamp) and converts the existing fprintf(stderr, …) in the server layer to use the new internal error logging API.
Add Native Support for Syslog on Unixoid Platforms (WL#7793). This work adds full support for logging server output to the system’s native syslog facility. See also Bug#55370 reported by Kyle Joiner and later enhanced by Simon Mudd and Mark Alff.
GB 18030 Chinese Character Set Support (WL#4024). This work adds the MySQL character set gb18030 which supports the China National Standard GB 18030 character set.
That’s it for now. Please stay tuned for the next 5.7 milestone release, and thank you for using MySQL!