What are the features of MySQL 5.7?

/ by 


InnoDB

Online buffer pool resize:

  • Configure innodb_buffer_pool_size offline (at startup) or online, while the server is running 
  • The operation is performed in chunks
  • Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option      
  • Buffer pool size always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
  • Default innodb_buffer_pool_chunk_size is 128M


Online RENAME INDEX operation:
  • ALTER TABLE RENAME INDEX requires only meta-data change.
Reference:






Increasing VARCHAR size:
  • Increasing VARCHAR size only requires Meta data change now.
  • Online alter table modify when increasing varchar from less than 255 bytes to up to 255 bytes.
Reference:
Optimized buffer pool flushing:
  •  Optimize buffer pool list scans and related batch processing code.
  •  Concept of Hazard Pointer-reduced the time complexity of scan.
Reference:
General Tablespace support:
  • Ability to create and use independent multi-table general table spaces.
  • This feature will provide a way to group tables together into tablespaces at a location and filename of your choice.
  • Tables using row formats of redundant, compact and dynamic can be combined together into the same general tablespace.
  • Compressed tables with the same key_block_size can also be combined together.
Reference:




Optimization for temporary tables:
  • Temporary table undo logs are introduced. Undo logs are used for roll back while the server is running. Temporary table undo logs are resides in the temporary tablespace.
  • Innodb intrinsic tables – which have relaxed MVCC and ACID semantics – These tables are a special type of temporary InnoDB table that do not perform any UNDO or REDO logging (REDO logging is disabled for all temporary tables).
  • Innodb_temp_data_file_path – specifies the path, file name and file size for innodb temporary table space data files.
  • Temporary tables are not visible across connections.
  • Temporary table lifetime is limited to connection lifetime.

Reference:





InnoDB temp directory:
  • InnoDB tempdir is now configurable.
  • InnoDB tempdir option is used to define an alternate directory for temporary sort files created during online ALTER TABLE operation that rebuild the table.
  • It is used to avoid overflowing a temporary file directory located on a tmpfs file system.
  • Overflow could occur as a result of large temporary sort files created during online ALTER TABLE operation that rebuilds the table.

Reference:





Crash recovery:
  • MySQL 5.7 provides improved crash recovery features.
  • MySQL 5.7 introduces a new redo log record type MLOG_FILE_NAME for identifying those non-predefined files that were changed since the latest log checkpoint.
  • To avoid growing the volume of the redo log, only one record will be emitted for each tablespace that was modified since the latest checkpoint.

Reference:





Read – only transaction scalability:
  • Improved transaction life cycle management. Transaction list was split into three:
  • Read-only transaction list.
  • Read-write transaction list.
  • Auto-commit non-locking read-only (AC-NL-RO) transaction list – this is a virtual transaction list.
  • Explicit read-only transaction list eliminated altogether.
  • By default transactions are not put on any list unless they are explicitly tagged as read-write then they are added to the read-write transaction list.

Reference:





Read – write transaction scalability:
  • The multi-version concurrency control (MVCC) in InnoDB requires that each MVCC-using transaction be assigned a read view.
  • The read view is created by traversing the trx_sys->rw_trx_list, which is a linked list of active read – write transactions. This will be improving the innodb performance for both read-only transaction and read-write transaction.

Reference:






Multiple page-cleaner threads:
  • Currently there is a single page_cleaner thread which is tasked with flushing dirty pages from the buffer pools.
  • In case of multiple buffer pool instances, each buffer pool has its own free list, flush list and LRU list.
  • The page_cleaner thread works on LRU and flush list. The idea is to have one page_cleaner per buffer pool instance.

Reference:



<! -- Google Ads Start -->





Page compression:
  • MySQL 5.7 introduced transparent innodb page compression.
  • From a high level, transparent page compression is a simple page trans formation.
  • Write: Page -> Transform -> Write transformed page to disk -> Punch hole
  • Read: Page from disk -> Transform -> Original Page
  • To use this new Transparent Page Compression Feature the operating system and file system must support sparse files and hole punching.

Reference:






32K and 64K pages:
  • Innodb supporting page size of 32K and 64K.
  • The innodb_page_size setting is global for all tablespaces within a single MySQL server instance.
  • MySQL do not support compression when innodb_page_size is set to 32K and 64K.

Reference:






Page fill factor:
  • There are 2 fill factor thresholds for ordinary logically.
  • merge_threshold : If the data amount in the page becomes below the threshold when deleting row or updating to shorter row, attempts to merge with neighbor page.
  • split_threshold : If the data amount in the page becomes above the threshold when inserting row or updating to longer row, split the page into 2 pages.
  • Page fill factor is now configurable.  If the threshold is set lower, the merged page farther from 100% and might not cause split page soon. Reducing opportunity for page merge/split benefit performance.

Reference:






Full text search:

  • Some conditions push down from server layer to InnoDB.
  • Ranking.
  • ‘Select limit’ with no ‘where’ clause.
  • Order by match.
  • ‘Select limit’ with ‘where’ clause.
  • Simplifying post search result processing.

Reference:                 






Plugin parser:
  • InnoDB supports plugin parser in fulltext index. 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.

Reference:






Meta data update:
  • The column INFORMATION_SCHEMA.TABLES.update_time is always NULL for InnoDB tables. It is supposed to contain the timestamps of last update (or insert or delete).

Reference:





Atomic TRUNCATE TABLE:
  • Make internal innoDB TRUNCATE TABLE statement to be atomic for single tablespace.
  • To make Internal InnoDB TRUNCATE TABLE statement to be atomic, reinitialize the original tablespace header with the same space id and then physically truncate its .ibd file during the truncation for single tablespace.

Reference:






InnoDB row format:
  • Default innodb_default_row_format can be now configured.
  • Internal InnoDB temporary tables created by the server to process queries use the DYNAMIC row format, regardless of the innodb_default_row_format setting.

Reference:






InnoDB drop tables:
  • Enabling the new innodb_background_drop_list_empty debug configuration option helps avoid test case failures by delaying table creation until the background drop list is empty.



Reference:







InnoDB buffer pool dump:
  • To reduce the warm up period after restarting the server, InnoDB saves a percentage of the most recently used pages for each buffer pool at server shutdown and restores these pages at server startup. The percentage of recently used pages that is stored is defined by the innodb_buffer_pool_dump_at_shutdown configuration option.



Reference:





Default changes:
  • innodb_default_row_format=DYNAMIC
  • innodb_buffer_pool_dump_pct=25
  • innodb_file_format=Barracuda
  • innodb_large_prefix=1
  • innodb_checksum_algorithm=crc32
  • innodb_buffer_pool_load_at_startup=1
  • innodb_buffer_pool_dump_at_shutdown=1
  • innodb_purge_threads=4
  • innodb_page_cleaners=4

Triggers
Multiple triggers per event per table:
  • MySQL supports multiple triggers per table.
  • Till now, the server supports only one trigger for every action (INSERT, UPDATE, DELETE) and timing (BEFORE or AFTER). In other words, there might be at most one trigger for every pair (action, timing).



Reference:







Triggers are not processed for NOT NULL columns:
  • The problem is that if a column is declared as NOT NULL, it is not possible to do INSERT NULL (or UPDATE to NULL) even though there is associated trigger, setting NOT-NULL value.

Reference:






Sys Schema


Sys schema bundled by default:
  • The sys schema is integrated within the MySQL fully. It is installed by default and added on upgrade appropriately.
  • It is used for configuring and querying the performance schema.

Reference:




Replication


  • Online CHANGE MASTER TO:
  • BOTH IO AND SQL THREAD ARE STOPPED
    • When both the slave threads are stopped, there will be no change in behavior. The CHANGE MASTER command will behave as it does now.
  • IO THREAD IS STOPPED, SQL THREAD IS RUNNING          
    • In order to switch the I/O thread over to read from another master it is currently necessary to stop the SQL thread as well. This worklog implements support for re-directing the I/O thread to another master without having to stop the SQL thread first, wherever possible.
  • SQL THREAD IS STOPPED, IO THREAD IS RUNNING. 
    • In order to CHANGE MASTER TO RELAY_LOG_FILE/RELAY_LOG_POS/MASTER_DELAY, we currently have to stop the SQL thread as well. This worklog will allow these CHANGE MASTER options without having to stop the IO thread, if possible.

Reference:






SLAVE_TRANSACTION_RETRIES:
  • MySQL 5.7 supports SLAVE_TRANSACTION_RETRIES in multi-threaded slave mode.
  • Currently, only non-MTS replication slave servers attempts to retry a transaction after a temporary failure.

Reference:






Multi-source replication:
  • MySQL Multi-Source Replication enables a replication slave to receive transactions from multiple sources simultaneously.
  • Multi-source replication can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server.
Reference:





Intra-schema parallel replication:
  • When using a multi-threaded slave (slave_parallel_workers is greater than 0), this option specifies the policy used to decide which transactions are allowed to execute in parallel on the slave. The possible values are:
    • DATABASE:
      • Transactions that update different databases are applied in parallel.
    • LOGICAL_CLOCK:
      • Transactions that are part of the same binary log group commit on a master are applied in parallel on a slave.

Reference:






SHOW SLAVE STATUS:
  • Supports non-blocking show slave status.
  • The NONBLOCKING clause caused SHOW SLAVE STATUS, when run concurrently with STOP SLAVE, to return without waiting for STOP SLAVE to finish shutting down the slave SQL thread or slave I/O thread (or both).

Reference:






Semi-Sync improvements:
  • The new semi-sync master plugin creates a separate thread to receive acknowledgement from its semi-sync slaves. The thread is created while semi-sync master is enabled and destroyed when semi-sync master is disabled. It is created and destroyed automatically, therefor, not controlled by users.

Reference:






REPLICATION FILTER:
  • Supports online CHANGE REPLICATION FILTER.

Reference:






Loss-less semi-sync:
  • MySQL 5.7 supports loss-less semi-synchronous replication.
  • Loss-less semi-synchronous replication enhances the data integrity between the master and slave.

Reference:






Online GT-ID migration path:
  • Global Transaction Identifiers (GTID) can now be enabled, without stopping writes, synchronizing servers, or restarting servers.
  • This enables highly available applications, since service can continue without interruption even if the master crashes.

Reference:






Semi-sync wait – configurable number of slaves:
  • Semi-sync can now wait for a configurable number of slaves.
  • The new feature allows users to set how many slaves the transaction (its binary events) should be replicated before externalizing it to users.
  • In other words, semi sync master makes the transaction to wait until receiving acknowledgements from those many slaves.

Reference:






Sequential Consistency:
  • Supports non-blocking show slave status.
  • The NONBLOCKING clause caused SHOW SLAVE STATUS, when run concurrently with STOP SLAVE, to return without waiting for STOP SLAVE to finish shutting down the slave SQL thread or slave I/O thread (or both).

Reference:





Passive slave – GTID:
  • GTIDs are optimized for passive slaves.
  • The variable name is gtid_executed_compression_period. This will compress the mysql.gtid_executed table each time this many transactions have been processed.
  • Setting the value of the variable has no effect unless binary logging disabled.

Reference:






Improved memory management for- Binlog_sender:
  • This feature provides an optimization for the dump thread that removes unnecessary reallocation of the sender buffer.
  • The user visible effect is that the CPU will be used less by each thread the master has spawned.

Reference:






Introduction of WAIT_FOR_EXECUTED_GTID_SET function:
  • The MySQL 5.7 introduced new function WAIT_FOR_EXECUTED_GTID_SET.
  • It is a replacement for WAIT_UNTILL_SQL_THREAD_AFTER_GTIDS.
  • This function shall not care about slaves running and shall return 0 or 1.

Reference:






UNSAFE FOR  BINLOG message:
  • MySQL 5.7 introduced a new replication feature log_statement_unsafe_for_binlog.
  • If error 1592 is encountered, controls whether the generated warnings are added to the error log or not.

Reference:






GTIDs in the OK packet:
  • This is a session tracker feature.
  • This feature will add GTIDs context to the OK packet.

Reference:






Default changes:
  • binlog_format=ROW
  • sync_binlog=1
  • binlog_gtid_simple_recovery=1
  • binlog_error_action=ABORT_SERVER
  • slave_net_timeout=60

Security

Username:
  • Length of a username increased to 32 characters.

Reference:






Support for IF [NOT] EXISTS clause in CREATE / DROP USER:
  • MySQL should support IF [NOT] EXISTS clauses for CREATE, DROP and ALTER USER commands. 
  • This will allow for distribution of accounts using replication without triggering replication failures in the event of non-synchronized accounts on master and slave.
  • It also simplifies user scripting of account management operations.

Reference:






LOCK / UNLOCK user accounts:
  • MySQL supports locking and unlocking user accounts using the ACCOUNT LOCK and ACCOUNT UNLOCK clauses for the CREATE USER and ALTER USER statements.
  • When used with CREATE USER, these clauses specify the initial locking state for a new account. In the absence of either clause, the account is created in an unlocked state.
  • When used with ALTER USER, these clauses specify the new locking state for an existing account. In the absence of either clause, the account locking state remains unchanged.

Reference:






Password expiration policy:
  • MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration.
  • The mysql.user table indicates for each account when its password was last changed, and the server automatically treats the password as expired at client connection time if it is past its permitted lifetime.
  • This works with no explicit manual password expiration.

Reference:






Old_password removed:
  • Pre-4.1 passwords are deprecated and support for them is removed in MySQL 5.7.5.
  • Consequently, OLD_PASSWORD () is deprecated and is removed in MySQL 5.7.5.

Reference:






Tablespace encryption:
  • InnoDB supports data encryption for InnoDB tables stored in file-per-table tablespaces.
  • This feature provides at-rest encryption for physical tablespace data files.

Reference:






Server option:
  • Added server-side option to require secure transport.
  • Server option --require_secure_transport added.

Reference:






AES Encryption modes:
  • MySQL 5.7 supports multiple encryption modes.
  • MySQL implements AES encryption/decryption functions AES_ENCRYPT/AES_DECRYPT.
  • This new feature enhance the security strength of the these functions by supporting larger key sizes and different bloc modes.

Reference:






No-Login Authentication Plugin:
  • The mysql_no_login server-side authentication plugin prevents all client connections to any account that uses it.
  • Use cases for such a plugin includes accounts that must be able to execute stored programs and views with elevated privileges without exposing those privileges to ordinary users, and proxy accounts that should never permit direct login.

Reference:






Optimizer

Optimizer cost model:
  • Improved optimizer cost model, leading to more consistently better query plans.
  • The MySQL optimizer cost model project – improved the query cost model:
  • Cost model for where conditions.
  • More accurate index statistics.
  • Configurable cost constants.

Reference:












Query Parser:
  • Query parser has been refactored and improved.
  • MySQL server uses a bison-generated parser to process the input SQL language.
  • This parser transformed into the pure stateless bottom-up parser step-by-step.
  • Self-contained parse tree that has minimal dependency on the rest of the server.

Reference:






UNION ALL – temporary tables:
  • In MySQL 5.7 UNION ALL does not use a temporary table.
  • This will save the cost of creating, writing and reading from the temporary table, which may be put on disk if the result is large.
  • This will also reduce the need for disk space for large unions and the client will get the first rows quicker.

Reference:






EXPLAIN FOR CONNECTION:
  • MySQL 5.7 introduces new feature to obtain the execution plan information for a named connection.
  • EXPLAIN FOR CONNECTION returns the EXPLAIN information that is currently being used to execute a query in a given connection.

Reference:






Sub-queries in FROM clause:
  • Sub-queries in FROM clause can now be handled same as a view (derived_merge).
  • The optimizer can handle derived tables (sub queries in the FROM clause) and view references using two strategies:
  • Merge the derived table or view into the outer query block
  • Materialize the derived table or view to an internal temporary table

Reference:






New condition filtering optimization:
  • Optimizer now supports a new condition filtering optimization.
  • Given a query with a join between two or more tables, the MySQL optimizer’s mission is to find the best combination of join order and access method so that the response time becomes as low as possible. The optimizer does this by calculating the cost of each combination and then picks the cheapest one by calculating the cost of each combination and then picks the cheapest one.

Reference:






Functional Indexes:
  • MySQL 5.7 support for STORED and VIRTUAL generated columns.
  • The virtual columns are no longer materialized in the table.
  • Since they are truly “virtual”, adding and dropping virtual columns does not require a table rebuild. 
  • Creating an index on a virtual column (only secondary indexes are allowed) will essentially “materialize” the virtual column in the index records as the computed values are then stored in the secondary index, but the values are not stored in the primary key (clustered index). 

Reference:






Row value constructor:
  • Queries using row value constructor are now optimized.
  • Added support for row value constructors in predicates to range optimizer.
  • Optimizer is able to apply the range scan access method to queries.

Reference:






New Query hints:
  • New query hints using comments like / *+ */ syntax.
  • Comment syntax /*+ */ is used for the new hints.
  • Multiple hints can be specified within the same comment.
  • A query block can have only one comment containing hints, and that comment must directly follow the SELECT, UPDATE, INSERT, REPLACE, or DELETE keyword

Reference:






Server-side query rewrite framework:
  • With query rewrite framework, you can choose whether to rewrite the queries before and/or after parsing.
  • The greatest benefit compared to post-parse rewrite is the efficiency, especially the lack of overhead for those queries that are actually rewritten.

Reference:






Dynamic range access:
  • Dynamic range access (aka “Range checked for each record” since that is what EXPLAIN will say in the ‘Extra’ column) is one of the big mysteries to MySQL users. The reason is that it is used for queries that are less common, such as queries with non-equality join predicates. MySQL 5.7 enhanced the dynamic range access features.

Reference:






Range optimizer:
  • Memory used by the range optimizer is now configurable.
  • The limit on memory consumption for the range optimizer. A value of 0 means “no limit.”
  • The variable is: range_optimizer_max_mem_size

Reference:






Optimized file sort:


File sort is now optimized to pack values in the sort buffer.Read the rows that match the WHERE clause.For each row, record a tuple of values consisting of the sort key value and the additional fields referenced by the query.When the sort buffer becomes full, sort the tuples by sort key value in memory and write it to a temporary file.After merge-sorting the temporary file, retrieve the rows in sorted order, read the required columns directly from the sorted tuples

Default changes:
  • Internal_tmp_disk_storage_engine=INNODB
  • eq_range_index_dive_limit=200
  • sql_mode=ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
  • optimizer_switch=condition_fanout_filter=on, derived_merge=on
  • EXTENDED and PARTITIONSkeywords for EXPLAIN enabled by default.

Backup Utility

Mysqlpump:

MySQL 5.7 introduced a new client utility called mysqlpump that performs logical backups, producing set of SQL statements that can be run to produce original schema objects and table data.The goal of mysqlpump is to have a modern utility that is extendable and has native support for parallelization. Mysqlpump executes all of the operations related to dumping multiple databases and the objects inside them in parallel in order to drastically reduce the total time needed to perform a logical backup.

  • Index condition pushdown optimization now supported.
  • HANDLER command is now supported.
  • WITHOUT VALIDATION option now supported for ALTER TABLE  . . . EXCHANGE PARTITION.
  • Support for transportable table space.
  • Partitioning is now storage engine native for InnoDB.
Reference:

Other Feature Changes:


  • A number of tables in the mysql schema have moved from MyISAM to InnoDB
  • Server error log format improved to be consistent
  • Better handling of connection id rollover
  • Extract query digest moved from performance_schema into the server directly
  • Improved scalability of meta data locking
  • Increased control over error log verbosity
  • Stacked Diagnostic Areas
  • The server now supports a "SHUTDOWN" command
  • Removed support for custom atomic implementation
  • Removed "unique option prefix support" from server and utilities, which allowed options to be configured using multiple names.
  • Removed unsafe ALTER IGNORE TABLE functionality. Syntax remains for compatibility
  • Removed unsafe INSERT DELAYED functionality. Syntax remains for compatibility
  • Removed of outdated sql-bench scripts in distributions
  • Removal of ambiguous YEAR(2) datatype
  • Defaults change: log_warnings=2
  • Defaults change:table_open_cache_instances=16.











1 comment:

Subscribe to: Post Comments (Atom)