https://mysqlserverteam.com/whats-new-in-mysql-5-7-so-far/
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.
Read more here:
https://mysqlserverteam.com/whats-new-in-mysql-5-7-so-far/
Curated by Liquid Layer Networks
At Liquid Layer Web Hosting [LiquidLayer.net], we’ve made a shared web hosting platform that’s both feature-rich and easy to make use of. Our programmers have built up a custom Linux cloud web hosting platform plus an innovative Control Panel that perfectly takes advantage of its capabilities. After long hours of programming and bug fixing on our end, we are now capable to guarantee that all of our shared web hosting services are safe, virus-free, full of capabilities and very easy-to-work-with. In addition, they feature 99.9% server uptime as well as 99.9% network uptime warranties.
Powered by:
HostCheetah Networks
Global Web Hosting, Domain Registration, and Internet Services
https://hostcheetah.net | http://hostcheetah.uk
PC Helper | Est 1996 - Web Hosting | US, AU, UK, Finland, Bulgaria | 
https://pchelper.com
