To sum up the new features of mysql 5.6, mysql5.6

To sum up the new features of mysql 5.6, mysql5.6

I have been reading some of the mysql features intermittently, summary today, the following is a list, web site http://mariadb.org/(also similar features), http://mysql.com/

I am reading some books about new mysql features recently.

1. Changes in the default settings of server Parameters

Parameter	Old Default	New Default	Version
back_log	50	Autosized usingmax_connections	5.6.6
binlog_checksum	NONE	CRC32	5.6.6
--binlog-row-event-max-size	1024	8192	5.6.6
flush_time	1800 (on Windows)	0	5.6.6
host_cache_size	128	Autosized usingmax_connections	5.6.8
innodb_autoextend_increment	8	64	5.6.6
innodb_buffer_pool_instances	1	8 (platform dependent)	5.6.6
innodb_concurrency_tickets	500	5000	5.6.6
innodb_data_file_path	Ibdata1: 10 M: autoextend	Ibdata1: 12 M: autoextend	5.6.7
innodb_file_per_table	0	1	5.6.6
innodb_log_file_size	5MB	48MB	5.6.8
innodb_old_blocks_time	0	1000	5.6.6
innodb_open_files	300	Autosized usinginnodb_file_per_table,table_open_cache	5.6.6
innodb_stats_on_metadata	ON	OFF	5.6.6
join_buffer_size	128KB	256KB	5.6.6
max_allowed_packet	1 MB	4 MB	5.6.6
max_connect_errors	10	100	5.6.6
open_files_limit	0	Autosized usingmax_connections	5.6.8
performance_schema	OFF	ON	5.6.6
performance_schema_events_waits_history_long_size	10000	Autosized	5.6.6
performance_schema_events_waits_history_size	10	Autosized	5.6.6
performance_schema_max_cond_instances	1000	Autosized	5.6.6
performance_schema_max_file_instances	10000	Autosized	5.6.6
performance_schema_max_mutex_instances	1000000	Autosized	5.6.6
performance_schema_max_rwlock_instances	1000000	Autosized	5.6.6
performance_schema_max_table_handles	100000	Autosized	5.6.6
performance_schema_max_table_instances	50000	Autosized	5.6.6
performance_schema_max_thread_instances	1000	Autosized	5.6.6
query_cache_size	0	1 M	5.6.8
query_cache_type	ON	OFF	5.6.8
secure_auth	OFF	ON	5.6.7
sql_mode	''(Empty string)	NO_ENGINE_SUBSTITUTION	5.6.6
sync_master_info	0	10000	5.6.6
sync_relay_log	0	10000	5.6.6
sync_relay_log_info	0	10000	5.6.6
table_definition_cache	400	Autosized usingtable_open_cache	5.6.8
table_open_cache	400	2000	5.6.8
thread_cache_size	0	Autosized usingmax_connections

Http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html

Ii. Changes and enhancements to innodb features

• 1. full text search: https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

• 2. Support for online DDL: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

• 3. The. ibd file for independent tablespace can specify the Directory: http://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html when creating the table

• 4. Import and export tablespaces: http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html

• 5. added parameter innodb_page_size setting page size (4 k, 8 k, 16 k): https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html

• 6. Buffer Pool flush algorithm enhancement: http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html

• 7. Support Using nosql APIs to access innodb tables (Integrated memcached): http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html

• 8. Supports read-only transactions: http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html

• 9. undo log can be independent of the system tablespace: http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html

• 10. The maximum redo log size is increased to 512 GB. Http://dev.mysql.com/doc/refman/5.6/en/innodb-redo-log-size-increase.html

• 11. innodb supports read_only media, such as DVDs, parameters -- innodb-readonly http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html

• 12. innodb internal performance enhancement: including separating flushing operations from the main thread to reduce core mutex locks. Multiple cleanup threads can be set to reduce resource contention for large memory systems.

• 13. The Deadlock Detection Algorithm is enhanced. In non-recursive situations, the Deadlock Detection information can be recorded in the error log for easy analysis.

• 14. buffer pool push: http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html

Iii. Partition Restrictions (Restrictions and Limitations on Partitioning)

• 1. the maximum number of partitions is increased to 8192, including partitions and subpartitions. (Prior to MySQL 5.6.7, the maximum possible number of partitions for a given table not usingNDBStorage engine was 1024)

• 2. Supports PARTITION (or sub-PARTITION) EXCHANGE with non-PARTITION tables: alter table... EXCHANGE PARTITION

• 3. query supports explicit PARTITION statements such as SELECT * FROM t PARTITION (p0, p1) WHERE c <5: http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html

• 4. Simplified partition lock enhanced performance: http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-locking.html

Iv. Enhancement of the replication function

• 1. Added GTID replication: http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html

• 2. Add binlog_row_image: The http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html only records changes to the fields used in the row format (instead of logging all columns as before), saving space and other resources.

• 3.master.info and relay-log.info support storage in tables.

• 4. mysqlbinlog command supports binlog backup (binlog is still binary after backup rather than text) http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-backup.html

• 5. Support for delayed replication: add the MASTER_DELAY option to change master.

• 6. You can specify one slave with multiple network interfaces: Add MASTER_BIND to change master.

• 7. Added log_bin_basename: http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html relay_log_basename: http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html

• 8. Supports schema-level multi-threaded replication.

• 9. binlog supports crash-safe.

5. Query Optimizer

• 1. limit optimization: http://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

• 2. MRR optimization (Multi-Range Read): http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html

• 3. http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html optimization (Index Condition Pushdown)

• 4. The explain statements support insert, delete, update, and replace statements, and support json format.

• 5. New Connection algorithm BKA (Batched Key Access): http://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html

• 6. subquery optimization: including materialized and semi-join optimization.

• 7. developer-oriented optimizer tracking feature.

6. Data Types

• 1. The time, datetime, and timestamp types can be subtle (six digits after the decimal point). Table slowlog will not be truncated.

• 2. The datetime type supports DEFAULT CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP.

• 3. added the explicit_defaults_for_timestamp system variable to specify the default value of timestamp.

• 4. year (2) is limited and will be converted to year (4 ).

7. Changed Parameters

• 1. -- remove log and log, and replace it with -- general_log and -- general_log_file = file_name.

• 2. log_slow_queries is removed, and only slow_query_log is used.

• 3. Remove the -- one-thread system parameter and replace it with -- thread_handling = no-threads.

• 4. -- remove the safe-mode system parameter.

• 5. -- remove the skip-thread-priority system parameter.

• 6. -- replace table-cache with table_open_cache.

• 7. -- init-rpl-role, -- rpl-recovery-rank, rpl_recovery_rank, and Rpl_status are removed.

• 8. The engine_condition_pushdown system variable is removed. The engine_condition_pushdown variable is incorporated into the optimizer_switch variable.

• 9. Remove have_csv, have_innodb, have_ndbcluster, and have_partitioning with show engines.

• 10. SQL _big_tables is removed and replaced by big_tables.

• 11. SQL _low_priority_updates is replaced by low_priority_updates.

• 12. SQL _max_join_size is replaced by max_join_size.

• 13. max_long_data_size is replaced by max_allowed_packet.

• 14. The flush master and flush slave statements are removed and replaced by the reset master and reset slave statements.

• 15. Remove the slave start and slave stop statements and replace them with start slave and stop slave.

• 16. Remove the show authors and show contributors statements.

• 17. Remove OPTION and nd ONE_SHOT from the set statement.

Http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html

8. MySQL Performance Schema Enhancement

• 1. Statements/execution stages-find the resource consumption hotspot SQL

• 2. Table and Index I/O: Find out which tables and indexes cause high load.

• 3. Table Locks: Which tables cause competition

• 4. Users/Hosts/Accounts: Find the Users/Hosts/Accounts that consume the most resources

• 5. Network I/O: Network or application? How long is the session idle?

• 6. Aggregate and summarize through thread, user, host, account, and object.

All of the above features can be found on the mysql official website,

 

References

Http://blog.csdn.net/yueliangdao0608/article/details/21230627