MySQL 5.5
---------------
1> MySQL Enterprise Thread Pool :: The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. As of MySQL 5.5.16, MySQL Enterprise Edition distributions include a thread pool plugin that provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections.
2> MySQL Enterprise Audit :: MySQL Enterprise Edition now includes MySQL Enterprise Audit, implemented using a server plugin named audit_log. MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines. When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.
3> Multi-core scalability :: Scalability on multi-core CPUs is improved. The trend in hardware development now is toward more cores rather than continued increases in CPU clock speeds, which renders “wait until CPUs get faster” a nonviable means of improving database performance. Instead, it is necessary to make better use of multiple cores to maximally exploit the processing cycles they make available. The focus has been on InnoDB, especially locking and memory management.
4> Default storage engine :: The default storage engine for new tables is InnoDB rather than MyISAM.
5> Semisynchronous replication :: A commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through an optional plugin component.
6> Partitioning :: It is now possible to delete all rows from one or more partitions of a partitioned table using the ALTER TABLE ... TRUNCATE PARTITION statement.
7> Metadata locking :: The server now prevents DDL statements from compromising transaction serializibility by using a new class of locks called metadata locks.
8> IPv6 support :: MySQL Server can accept TCP/IP connections from clients connecting over IPv6.
MySQL 5.6
---------------
1> Security improvements :: Stronger encryption for user account passwords, available through an authentication plugin named sha256_password that implements SHA-256 password hashing. The mysql.user table now has a password_expired column. Its default value is 'N', but can be set to 'Y' with the new ALTER USER statement. After an account's password has been expired, all operations performed in subsequent connections to the server using the account result in an error until the user issues a SET PASSWORD statement to establish a new account password.
2> InnoDB Enhancements (Online DDL) :: Several ALTER TABLE operations can be performed without copying the table, without blocking inserts, updates, and deletes to the table, or both. These enhancements are known collectively as online DDL.
3> InnoDB Enhancements (Transport Tablespaces) :: InnoDB now supports the notion of “transportable tablespaces”, allowing file-per-table tablespaces (.ibd files) to be exported from a running MySQL instance and imported into another running instance without inconsistencies or mismatches caused by buffered data, in-progress transaction
4> InnoDB Enhancements (Deadlocks) :: InnoDB uses a new, faster algorithm to detect deadlocks. Information about all InnoDB deadlocks can be written to the MySQL server error log, to help diagnose application issues.
5> InnoDB Enhancements (Undo Tablespace) :: You can move the InnoDB undo log out of the system tablespace into one or more separate tablespaces. The I/O patterns for the undo log make these new tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage.
6> InnoDB Enhancements (Memcache) :: You can code MySQL applications that access InnoDB tables through a NoSQL-style API. This feature uses the popular memcached daemon to relay requests such as ADD, SET, and GET for key-value pairs. These simple operations to store and retrieve data avoid the SQL overhead such as parsing and constructing a query execution plan. You can access the same data through the NoSQL API and SQL. For example, you might use the NoSQL API for fast updates and lookups, and SQL for complex queries and compatibility with existing applications.
7> Replication and logging :: MySQL now supports transaction-based replication using global transaction identifiers (also known as “GTIDs”). This makes it possible to identify and track each transaction when it is committed on the originating server and as it is applied by any slaves. Enabling of GTIDs in a replication setup is done primarily using the new --gtid-mode and --enforce-gtid-consistency server options.
8> EXPLAIN plan :: The EXPLAIN statement now provides execution plan information for DELETE, INSERT, REPLACE, and UPDATE statements. Previously, EXPLAIN provided information only for SELECT statements.
9> Data Types :: MySQL now permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision.
MySQL 5.7
---------------
1> Security Improvements :: MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password. Administrators can lock and unlock accounts for better control over who can log in. Installation creates no anonymous-user accounts. And created no test database.
2> Online ALTER TABLE :: ALTER TABLE now supports a RENAME INDEX clause that renames an index. The change is made in place without a table-copy operation. It works for all storage engines.
3> InnoDB Enhancements (Optimized DDL) :: DDL performance for InnoDB temporary tables is improved through optimization of CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE statements.
4> InnoDB Enhancements (Transport Partitioned tablespace) :: 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.
5> InnoDB Enhancements (Dynamic innodb_buffer_pool_size) :: The innodb_buffer_pool_size parameter is dynamic, allowing you to resize the buffer pool without restarting the server. The resizing operation, which involves moving pages to a new location in memory, is performed in chunks. Chunk size is configurable using the new innodb_buffer_pool_chunk_size configuration option. You can monitor resizing progress using the new Innodb_buffer_pool_resize_status status variable.
6> InnoDB Enhancements (Undo Tablespace) :: Can truncate undo logs that reside in undo tablespaces. This feature is enabled using the innodb_undo_log_truncate configuration option.
7> JSON Support :: MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error.
8> System and status variables :: System and status variable information is now available in Performance Schema tables, in preference to use of INFORMATION_SCHEMA tables to obtain these variable. This also affects the operation of the SHOW VARIABLES and SHOW STATUS statements.
9> sys schema :: MySQL distributions now include the sys schema, which is a set of objects that help DBAs and developers interpret data collected by the Performance Schema. sys schema objects can be used for typical tuning and diagnosis use cases.
10> Triggers :: Previously, a table could have at most one trigger for each combination of trigger event (INSERT, UPDATE, DELETE) and action time (BEFORE, AFTER). This limitation has been lifted and multiple triggers are permitted.
11> mysql client :: Previously, Control+C in mysql interrupted the current statement if there was one, or exited mysql if not. Now Control+C interrupts the current statement if there was one, or cancels any partial input line otherwise, but does not exit.
12> Multi-source replication is now possible :: MySQL Multi-Source Replication adds the ability to replicate from multiple masters to a slave. MySQL Multi-Source Replication topologies 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:
https://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html
https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html
https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html
---------------
1> MySQL Enterprise Thread Pool :: The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. As of MySQL 5.5.16, MySQL Enterprise Edition distributions include a thread pool plugin that provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections.
2> MySQL Enterprise Audit :: MySQL Enterprise Edition now includes MySQL Enterprise Audit, implemented using a server plugin named audit_log. MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines. When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.
3> Multi-core scalability :: Scalability on multi-core CPUs is improved. The trend in hardware development now is toward more cores rather than continued increases in CPU clock speeds, which renders “wait until CPUs get faster” a nonviable means of improving database performance. Instead, it is necessary to make better use of multiple cores to maximally exploit the processing cycles they make available. The focus has been on InnoDB, especially locking and memory management.
4> Default storage engine :: The default storage engine for new tables is InnoDB rather than MyISAM.
5> Semisynchronous replication :: A commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through an optional plugin component.
6> Partitioning :: It is now possible to delete all rows from one or more partitions of a partitioned table using the ALTER TABLE ... TRUNCATE PARTITION statement.
7> Metadata locking :: The server now prevents DDL statements from compromising transaction serializibility by using a new class of locks called metadata locks.
8> IPv6 support :: MySQL Server can accept TCP/IP connections from clients connecting over IPv6.
MySQL 5.6
---------------
1> Security improvements :: Stronger encryption for user account passwords, available through an authentication plugin named sha256_password that implements SHA-256 password hashing. The mysql.user table now has a password_expired column. Its default value is 'N', but can be set to 'Y' with the new ALTER USER statement. After an account's password has been expired, all operations performed in subsequent connections to the server using the account result in an error until the user issues a SET PASSWORD statement to establish a new account password.
2> InnoDB Enhancements (Online DDL) :: Several ALTER TABLE operations can be performed without copying the table, without blocking inserts, updates, and deletes to the table, or both. These enhancements are known collectively as online DDL.
3> InnoDB Enhancements (Transport Tablespaces) :: InnoDB now supports the notion of “transportable tablespaces”, allowing file-per-table tablespaces (.ibd files) to be exported from a running MySQL instance and imported into another running instance without inconsistencies or mismatches caused by buffered data, in-progress transaction
4> InnoDB Enhancements (Deadlocks) :: InnoDB uses a new, faster algorithm to detect deadlocks. Information about all InnoDB deadlocks can be written to the MySQL server error log, to help diagnose application issues.
5> InnoDB Enhancements (Undo Tablespace) :: You can move the InnoDB undo log out of the system tablespace into one or more separate tablespaces. The I/O patterns for the undo log make these new tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage.
6> InnoDB Enhancements (Memcache) :: You can code MySQL applications that access InnoDB tables through a NoSQL-style API. This feature uses the popular memcached daemon to relay requests such as ADD, SET, and GET for key-value pairs. These simple operations to store and retrieve data avoid the SQL overhead such as parsing and constructing a query execution plan. You can access the same data through the NoSQL API and SQL. For example, you might use the NoSQL API for fast updates and lookups, and SQL for complex queries and compatibility with existing applications.
7> Replication and logging :: MySQL now supports transaction-based replication using global transaction identifiers (also known as “GTIDs”). This makes it possible to identify and track each transaction when it is committed on the originating server and as it is applied by any slaves. Enabling of GTIDs in a replication setup is done primarily using the new --gtid-mode and --enforce-gtid-consistency server options.
8> EXPLAIN plan :: The EXPLAIN statement now provides execution plan information for DELETE, INSERT, REPLACE, and UPDATE statements. Previously, EXPLAIN provided information only for SELECT statements.
9> Data Types :: MySQL now permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision.
MySQL 5.7
---------------
1> Security Improvements :: MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password. Administrators can lock and unlock accounts for better control over who can log in. Installation creates no anonymous-user accounts. And created no test database.
2> Online ALTER TABLE :: ALTER TABLE now supports a RENAME INDEX clause that renames an index. The change is made in place without a table-copy operation. It works for all storage engines.
3> InnoDB Enhancements (Optimized DDL) :: DDL performance for InnoDB temporary tables is improved through optimization of CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE statements.
4> InnoDB Enhancements (Transport Partitioned tablespace) :: 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.
5> InnoDB Enhancements (Dynamic innodb_buffer_pool_size) :: The innodb_buffer_pool_size parameter is dynamic, allowing you to resize the buffer pool without restarting the server. The resizing operation, which involves moving pages to a new location in memory, is performed in chunks. Chunk size is configurable using the new innodb_buffer_pool_chunk_size configuration option. You can monitor resizing progress using the new Innodb_buffer_pool_resize_status status variable.
6> InnoDB Enhancements (Undo Tablespace) :: Can truncate undo logs that reside in undo tablespaces. This feature is enabled using the innodb_undo_log_truncate configuration option.
7> JSON Support :: MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error.
8> System and status variables :: System and status variable information is now available in Performance Schema tables, in preference to use of INFORMATION_SCHEMA tables to obtain these variable. This also affects the operation of the SHOW VARIABLES and SHOW STATUS statements.
9> sys schema :: MySQL distributions now include the sys schema, which is a set of objects that help DBAs and developers interpret data collected by the Performance Schema. sys schema objects can be used for typical tuning and diagnosis use cases.
10> Triggers :: Previously, a table could have at most one trigger for each combination of trigger event (INSERT, UPDATE, DELETE) and action time (BEFORE, AFTER). This limitation has been lifted and multiple triggers are permitted.
11> mysql client :: Previously, Control+C in mysql interrupted the current statement if there was one, or exited mysql if not. Now Control+C interrupts the current statement if there was one, or cancels any partial input line otherwise, but does not exit.
12> Multi-source replication is now possible :: MySQL Multi-Source Replication adds the ability to replicate from multiple masters to a slave. MySQL Multi-Source Replication topologies 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:
https://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html
https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html
https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html
No comments:
Post a Comment