MySQL 5.7 features

MySQL 5.7 features

MySQL 5.7 is the best release ever of the world’s most popular open source database and provides a new, advanced feature set designed to enable those who are building the next generation of web-based and embedded applications and services.  — Ref. https://www.mysql.com/why-mysql/white-papers/whats-new-mysql-5-7/

• Multi-source Replication
• 3x Faster Performance
• New Optimizer
• Native JSON Support
• GIS Spatial Extensions
• And other important enhancements

Replication Enhancements:

• One of the top features in MySQL 5.7 is multi-source replication. With multi-source replication you can point multiple master server’s to slave so limitation of slave having only one master is lift off.
• SHOW SLAVE STATUS is non-blocking since MySQL 5.7. SHOW SLAVE STATUS returns immediately without waiting for STOP SLAVE to finish which can be blocked by long running SQL query from replication SQL_THREAD.
• Now you can have all the information about SHOW SLAVE STATUS from performance schema database tables.
• With the new CHANGE REPLICATION FILTER command now you can modify replication filters rules without bouncing MySQL servers.
• Since MySQL 5.7 you can perform CHANGE MASTER TO without stopping the slave via the STOP SLAVE command.
• There is now a different method for parallel replication. With new implementation the slave can apply transaction in parallel with single database/schema too. Check slave_parallel_type for details.
• Global Transaction Identifiers (GTID) is a feature that automatically tracks the replication position in replication stream, and since MySQL 5.7 gtid_mode is dynamic variables, which means you can enable/disable GTID in replication topology without synchronizing and restarting entire set of MySQL servers. As a side note, online GTID deployment feature is added in mysql Server 5.7. With this feature you can deploy GTID on existing replication setups without marking master read_only and stopping all slaves in replication chain.

Security Enhancements:

• Since MySQL 5.7 there is a password expiration policy in place. Any user that connects to a MySQL server goes through a password expiration life cycle and must change the password. More from the manual here.
• Database administrators can nowo lock/unlock user accounts. Check details here.
• As of MySQL 5.7, installation only creates only one ‘root@localhost’ user account with random password and marks the password expiration cycle. So, installation no longer creates anonymous-user accounts and along with that there is no test database. For root user account password, MySQL generates it during data directory initialization and marks it as expired and will write a message to stdout displaying the password.

Performance Improvements:

• Bulk data load is improved on InnoDB in MySQL 5.7. InnoDB performs a bulk load when creating or rebuilding indexes. This method known as sorted index build and enhance create index operation and it also impacts FULLTEXT indexes.
• Currently there is a single page cleaner thread responsible for flushing dirty pages from the buffer pool(s). In MySQL 5.7 InnoDB parallel flushing was implemented to improve flushing where separate background thread for each buffer pool instance for flush list, LRU list.

InnoDB Enhancements:

• Now you can resize InnoDB buffer pool online. Since MySQL 5.7 innodb_buffer_pool_size is a dynamic variable which provides the ability to resize buffer pool without restarting MySQL server.
• From MySQL 5.7, online ALTER TABLE also supports RENAME INDEX clause to rename an index. This change will take in place without table copy operation.
• InnoDB supports Transportable Tablespace feature for partitioned InnoDB tables.
• Innochecksum utility is enhanced with new options.
• As of MySQL 5.7, InnoDB supports “spatial indexes” and it also supports online DDL operation to add spatial indexes i.e. ALTER TABLE .. ALGORITHM=INPLACE.
• Improved InnoDB buffer pool dump/reload operations. A new system variable, innodb_buffer_pool_dump_pctallows you to specify percentage of most recently used pages in each buffer pool to read out and dump.

Triggers:

• As per SQL standard, MySQL 5.7 now supports multiple triggers per table for trigger event (DML) and timing (BEFORE,AFTER) i.e. multiple triggers are permitted now for each event e.g. multiple triggers on INSERT action.

Optimizer Improvements:

• EXPLAIN FOR CONNECTION will let you run explain statements for already running queries. This may yield important information towards query optimization.
• In MySQL 5.7 the optimizer avoids the creatation temporary table for result of UNION ALL queries and this will help to reduce disk I/O and disk space when UNION yields large result set.
• JSON format for EXPLAIN first introduced in MySQL 5.6 which produces extended information. JSON format for EXPLAIN is enhanced in version 5.7 by printing total query cost which makes it easier to see the difference between the good and bad execution plans.
• MySQL 5.7 now supports generated columns also known as virtual columns as new feature.

MySQL Test Suite Enhancements:

• The MySQL test suite now uses InnoDB as its default storage engine. Along with that many new tests added and existing tests enhanced including test suite for replication with GTID.

Few More

– show grants for a particular user doesn’t show the password hash key any more and the mysql.user table structure has been changed, dropped the password column and changed it as authentication_string.

– New query hints + query rewrite features.
– InnoDB supports tablespaces, undo truncation and punch hole compression. Barracuda is now the default row format.
– The optimizer cost model has been improved.
– The optimizer also better handles derived tables in the from clause, and uses less memory to sort variable length data.
– The performance of semi-sync replication has greatly improved.

– creating the implicit or internal on-desk temporary tables using InnoDB SE instead of MyISAM by setting the variable: internal_tmp_disk_storage_engine=InnoDB

Posts to Review:

MySQL RAND Function MySQL Optimization Part 1 mysqli_connect() – function php mysql db connect Replication with Different Master and Slave Storage Engines