new awesome features in mysql 5.7
TRANSCRIPT
New Awesome Features in MySQL 5.7
— by royalwzy
Feedbacks
Personal Profile
More than 5 years experience in database use and management Proficient in synchronizing data between heterogeneous database
Skilled in designing and optimizing database Install and configure MySQL cluster and high-availability cluster environment
Synchronize and merge data from Oracle to MySQL
mysqlexp mysqlclone
Oracle 10g/11g OCM MySQL 5.6 Database Administrator
MCITP/MCTS DBA(MSSQL2008)
RHCE Java Programmer
Oracle ACEA, Oracle Young Expert ACOUG/SHOUG/OCMU core member ITPUB Moderator and Community Expert Senior lecturer in "ENMO" and “UPLOOKING" Active in OTN & ITPUB and other forums My blog:royalwzy.com
MySQL5.7 Released Versions
❖ MySQL 5.7.9 was GA on 2015-10-21
❖ MySQL 5.7.10 was GA on 2015-12-07
❖ MySQL 5.7.11 was GA on 2016-02-05
New Features in MySQL 5.7
❖ Performance Improvements❖ Performance Schema❖ SYS Schema
❖ Optimizer Improvements❖ Optimizer Cost Model❖ JSON Explain❖ New and Improved Optimizer Hints❖ Query Rewrite Plugin
❖ Replication Improvements❖ Multi-Source Replication❖ Transaction Based Parallel Replication❖ Online Replication Changes❖ Semi-Sync Replication Enhancements❖ Enhanced Monitoring❖ Group Replication
❖ InnoDB Improvements❖ InnoDB Online Operations❖ InnoDB General Tablespaces❖ Transparent Page Level Compression ❖ InnoDB Native Partitioning❖ InnoDB Native Full Text Search❖ InnoDB Cache Retention
❖ Security Improvements❖ AES 256 Encryption❖ Password Rotation Policies❖ MySQL Secure Installation❖ Comfortable User Account Management
❖ Native JSON Support❖ Generated Columns❖ High Availability Improvements
❖ GIS Improvements❖ GeoJSON❖ GeoHash❖ Spatial Aware Client Tools
Performance Improvements
❖ 3x Faster than MySQL 5.6: SQL Point Selects
Performance Schema Enhancements
❖ Metadata Locking
❖ Stage Tracking
❖ Transactions
❖ Memory Usage
❖ Stored Programs
❖ Prepared Statements
SYS Schema
❖ MySQL sys schema is included by default in 5.7.7
❖ What's its role? ❖ Who is taking up all the resources on my database server?
❖ Which hosts are hitting my database server those most?
❖ Which objects are accessed the most, and how?
❖ Which statements are using temporary tables on disk?
❖ Which tables take the most space in my InnoDB buffer pool?
❖ Where is all the memory going on my instance?
❖ Where is the time being spent the most within my instance?
Optimizer Improvements
❖ New MySQL Parser & New MySQL Optimizer
Optimizer Improvements
❖ The Optimizer Cost Model
❖ JSON Explain
❖ Optimizer Hints
❖ Query Rewrite Plugin
Optimizer - The Optimizer Cost Model
❖ The cost based optimizer has been improved to make dynamic, intelligent, and ultimately better cost based decisions
❖ These estimates are stored in the server_cost and engine_cost tables within the mysql system database.
❖ Dynamically reload or execute FLUSH OPTIMIZER_COSTS statement
Optimizer - The Cost Model Project
❖ Relate cost to more things
❖ Make the cost model aware of the underlying hardware
❖ Make use of the cache information
❖ Improve the precision of the statistics
❖ Prioritize histograms
❖ Add networking cost to the model
Optimizer - JSON Explain
❖ Use EXPLAIN FORMAT=JSON to extend query execution plan
❖ See Visual Explain in Workbench
Optimizer - JSON Explain
Optimizer - Optimizer Hints
❖ The disadvantage of optimizer_switch:❖ It needs an extra step to change it's value
❖ It affects the entire statement
❖ New parser rules to support hint syntax
❖ Server side statement timeouts
Optimizer - Hints Rules
❖ 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
❖ Incorrect and/or invalid syntax within a hint will cause a warning
❖ The first conflicting hint will take effect
❖ Multilevel hints are supported
Optimizer - Query Rewrite Plugin
❖ Why Query Rewrites?
❖ How Query Rewrites?
❖ Install Query Rewrite Plugin
❖ A glance with a demo
Replication Improvements
❖ Multi-Source Replication(Flexibility)
❖ Transaction Based Parallel Replication(Slave Throughput)
❖ Online Replication Changes(Convenience)
❖ Semi-Sync Replication Enhancements(Consistency)
❖ Enhanced Monitoring(Monitoring)
❖ Group Replication(HA)
Replication - Replication Components
SESSION DUMP I/O SQLNETWORK
Master Database
Salve Database
Binary Log
Relay Log
❖ Session thread
❖ Dump thread
Master Log
Binary Log
❖ I/O thread
❖ SQL thread
❖ Binary log
❖ Master log
❖ Relay log
Replication - Why Use Replication
❖ Read Scale-Out
Replication - Why Use Replication
❖ Write Scale-Out
❖ MySQL Fabric helps sharding you data with MySQL
Replication - Why Use Replication
❖ Switching Masters During Failover
❖ Use STOP SLAVE & CHANGE MASTER TO MASTER_HOST='Slave1'
Replication - Why Use Replication
❖ Replicating Different Databases to Different Slaves
MySQL Slave1
Master Database
MySQL Slave2
MySQL Slave3
Replication - Multi-Source Replication
❖ Complex queries for analytics purposes
❖ Data HUB for inter-cluster replication
❖ Merge table shards
❖ Integrated backup
Replication - Multi-Source Replication
❖ A server(slave) can replicate from multiple sources(masters)
❖ Multiple channels(channel:connection thread, relay log, applier threads) that can be stopped/started individually
❖ Virtually no limit on the number of sources(capped to 256, but can be changed if server binary is rebuilt)
❖ Able to manage each source separately
CHANGE MASTER TO [...] FOR CHANNEL = 'name'
Replication - Multi-Source Replication
❖ Integrated with GTIDs
❖ Integrated with Multi-threaded Slave❖ each channel has its own multi-threaded applier set of threads
❖ Integrated with the new P_S tables❖ replication_execute_status_by_coordinator shows multiple entries, one per
channel/source applier
❖ replication_connection_status shows multiple entries, one per connection to different sources
❖ Integrated with crash-safe tables❖ Progress state is stored in these tables for recoverability purposes
Transactions logged in sequence
Transactions applied in parallel
Replication - Transaction Based Parallel Replication
❖ Before MySQL5.6:Single-Threaded Slave
SESSION DUMP I/O SQLNETWORK
Master Database
Salve Database
Binary Log
Relay Log
Master Log
Binary Log
Transactions applied in sequence
Bottleneck
Replication - Transaction Based Parallel Replication
❖ MySQL5.6:Multi-Threaded Slave by database❖ Different databases go to different threads
❖ Great for some applications, BUT:
❖ No improvement if there is only one database
❖ May break cross-database consistency
Transactions logged in sequence
Transactions applied in parallel
SESSION DUMP I/O SQLNETWORK
Master Database
Salve Database
Binary Log
Relay Log
Master Log
Binary Log
Transactions applied in parallel
DB3DB2
DB1
Replication - Transaction Based Parallel Replication
❖ MySQL 5.7.2: Multi-threaded slave by master concurrency❖ Transactions that were prepared at the same time on master
cannot conflict
❖ Master stores a logical timestamp in the binary log
❖ Slave applies transactions with same timestamp in parallel
❖ Works always
❖ Even for one-database applications
❖ Consistent
❖ To enable:slave> SET GLOBAL slave_parallel_type = logical_clock(DEFAULT)(database for MySQL5.6);
Replication - Transaction Based Parallel Replication
❖ Multi-threaded slave by master concurrency
Replication - Online Replication Changes
❖ This procedure is suited to use in production
❖ Pre-conditions❖ All servers in your topology must use MySQL 5.7.6 or later
❖ All servers have gtid_mode set to the default value OFF
Replication - Semi-Sync Replication Enhancements
❖ By default, replication is asynchronous❖ In parallel: Master acks to app and sends transaction to
slave
❖ Fast
❖ Changes lost if master dies
❖ MySQL 5.5: semi-synchronous replication possible❖ In sequence: slave receives transaction, then master
acks to app
❖ Slower: Master waits for slave
❖ Less risk for lost updates
Replication - Semi-Sync Replication Enhancements
SESSION DUMP I/O SQLNETWORK
Master Database
Salve Database
Binary Log
Relay Log
Master Log
Binary Log
1
2
46
3
5
SESSION DUMP I/O SQLNETWORK
Master Database
Salve Database
Binary Log
Relay Log
Master Log
Binary Log
1
2
34
4
Replication - Semi-Sync Replication Enhancements
❖ MySQL 5.5: semi-synchronous replication
❖ Master commit
❖ Slave receive
❖ Client ack
❖ If master crashes between 1 and 2, committed data is lost
❖ Concurrent clients may have seen the transaction
❖ MySQL 5.7.2:❖ loss-less semi-sync replication
❖ Slave receive
❖ Master commit
❖ Client ack
❖ If master crashes, all committed data is on slave
Replication - Semi-Sync Replication Enhancements
❖ rpl_semi_sync_master_wait_point❖ AFTER_SYNC(Default)
❖ AFTER_COMMIT
SESSIONDUMP
I/O SQLNETWORK
Master Database
Salve Database
Binary Log
Relay Log
Master Log
Binary Log
1
2
56
3
4ACK
Replication - Enhanced Monitoring
❖ Traditional replication monitoring: SHOW SLAVE STATUS❖ Simple
❖ Not SQL friendly – no WHERE, no joins, etc
❖ Multi-source has per-source status
❖ Multi-threaded slave has per-applier status
❖ Performance_Schema tables for replication slave in 5.7.2
Replication - Enhanced Monitoring
I/O SQLNETWORK
Salve Database
Relay Log
Master Log
Binary Log
execute status execute
configuration connection configuration
connection status
execute status by coordinator execute status
by worker
❖ 6 tables in performance_schema database:❖ replication_connection_configuration
❖ replication_connection_status
❖ replication_execute_configuration
❖ replication_execute_status
❖ replication_execute_status_by_coordinator
❖ replication_execute_status_by_worker
❖ Consistent semantics across tables
❖ Consistent naming across tables
Replication - Enhanced Monitoring
❖ Example:Connection status
mysql> select * from performance_schema.replication_connection_status\G *************************** 1. row ***************************
CHANNEL_NAME: CHANNEL1 SOURCE_UUID: 7cff7406-23ca-11e3-ac3e-5c260a83b12b THREAD_ID: 13
SERVICE_STATE: ONRECEIVED_TRANSACTION_SET: 7cff7406-23ca-11e3-ac3e-5c260a83b12b:1-4
LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 *************************** 2. row ***************************
CHANNEL_NAME: CHANNEL2 . . .
Replication - Group Replication
❖ Introduction
Replication - Group Replication
❖ Monitoring and Inspecting the System Through Performance_Schema
❖ Self-healing, Elasticity, Redundancy
❖ Update Everywhere
❖ It's available on labs
Replication - Group Replication
❖ vs Percona XtraDB Cluster
High Availability Improvements
❖ A new data migration tool❖ mysqlpump
❖ Backup Examples
❖ Restore Example
Generated Columns
❖ The syntax
❖ As a materialized cache for often used expressions
❖ Function based indexes
❖ Limitations:❖ Only use native functions
❖ Can’t refer to itself or to other that are later defined
Generated Columns
InnoDB Improvements
❖ InnoDB Online Operations
❖ InnoDB General Tablespaces
❖ InnoDB Transparent Page Level
❖ InnoDB Native Partitioning
❖ InnoDB Native Full Text Search
❖ InnoDB Cache Retention
InnoDB - Online Operations
❖ Resizing the InnoDB Buffer Pool Online
❖ Truncating Undo Logs Online
❖ Renaming Index Online
❖ Enlarging VARCHAR column size Online
InnoDB - Resizing the InnoDB Buffer Pool Online
❖ Activing transactions MUST be completed and waited
mysql> SET GLOBAL innodb_buffer_pool_size=<chunk_size*N>;
❖ Configuring InnoDB buffer pool
❖ Modifing InnoDB buffer pool chunk size
❖ Configuring InnoDB buffer pool chunk size
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
❖ Command line:
❖ Configuration file:
./mysqld --innodb_buffer_pool_chunk_size=134217728
innodb_buffer_pool_chunk_size=134217728
InnoDB - Resizing the InnoDB Buffer Pool Online
❖ Monitoring online buffer pool resize progress
[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)[Note] InnoDB: disabled adaptive hash index.[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.[Note] InnoDB: buffer pool 0 : hash tables were resized.[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.[Note] InnoDB: re-enabled adaptive hash index.
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';+----------------------------------+----------------------------------+| Variable_name | Value |+----------------------------------+----------------------------------+| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |+----------------------------------+----------------------------------+
❖ Buffer Pool Resizing Internals(Increasing)❖ 1.Adds pages in chunks
❖ 2.Coverts hash tables, lists, and pointers to use new addresses in memory
❖ 3.Adds new pages to the free list
❖ 4.While these operations are in progress, other threads are blocked from accessing the buffer pool.
❖ Buffer Pool Resizing Internals(Decreasing)❖ 1.Defragments the buffer pool and withdraws (frees) pages
❖ 2.Removes pages in chunks
❖ 3.Converts hash tables, lists, and pointers to use new addresses in memory
❖ 4.Of these operations, only defragmenting the buffer pool and withdrawing pages allow other threads to access to the buffer pool concurrently.
InnoDB - Resizing the InnoDB Buffer Pool Online
InnoDB - Truncating Undo Logs Online
❖ Truncating Undo Logs That Reside in Undo Tablespaces
❖ innodb_undo_tablespaces=2
❖ innodb_undo_logs=35(or greater)
mysql> SET GLOBAL innodb_undo_log_truncate=ON;
❖ Enabling Truncation of Undo Tablespaces
❖ Expediting Truncation of Undo Tablespace Files
❖ Performance Impact of Truncating Undo Tablespace Files Online
mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;
InnoDB - Renaming Index Online
❖ Three processes❖ Renaming in InnoDB data dictionary (SYS_INDEXES)
❖ Renaming in InnoDB data dictionary cache (the dict_table_t/dict_index_t objects)
❖ Renaming in InnoDB persistent stats storage
❖ Online DDLmysql> ALTER TABLE <tbl_name> RENAME INDEX xxx to yyy;
InnoDB - Enlarging VARCHAR column size Online
❖ Enlarging VARCHAR column size Online
❖ InnoDB Tablespace Types:❖ InnoDB System Tablespace
❖ InnoDB File-Per-Table Tablespace(Single)
❖ InnoDB Undo Tablespace
❖ InnoDB General Tablespace
InnoDB - General Tablespaces
“A tablespace consists of a chain of files. The size of the files does not have to be divisible by the database block size, because we may just leave the last incomplete block unused. When a new file is appended to the tablespace, the maximum size of the file is also specified. At the moment, we think that it is best to extend the file to its maximum size already at the creation of the file, because then we can avoid dynamically extending the file when more space is needed for the tablespace.”
InnoDB - General Tablespaces
❖ Shared tablespaces that can store data for multiple tables
❖ General tablespaces support both Antelope and Barracuda file formats
❖ Data files may be placed in a directory relative to or independent of the MySQL data directory
❖ Have a potential memory advantage over file-per-table tablespaces
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/path/ts1.ibd' Engine=InnoDB;
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '../path/ts1.ibd' Engine=InnoDB;
InnoDB - General Tablespaces
❖ Adding tables to a general tablespace
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;
❖ Moving tables between tablespaces using ALTER TABLE
ALTER TABLE tbl_name TABLESPACE [=] tablespace_name;
mysql> ALTER TABLE t2 TABLESPACE ts1;
ALTER TABLE tbl_name ... TABLESPACE [=] innodb_system;
ALTER TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table;
InnoDB - General Tablespaces
❖ General Tablespace Limitations❖ A generated or existing tablespace cannot be
changed to a general tablespace
❖ Creation of temporary general tablespaces is not supported
❖ General tablespaces do not support temporary tables
❖ ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ...IMPORT TABLESPACE are not supported for tables that belong to a general tablespace
InnoDB - Transparent Page Level Compression
Database
Logical Physical
Tablespace Data file
Segment
Extent
Pages
Storage System • SAN • NAS • File System
• NFS • RAW
OS block
❖ Logical and Physical Database Structures in InnoDB
InnoDB - Transparent Page Level Compression
❖ InnoDB table & page level compression
❖ Zlib & LZ4 compression algorithmsEnable:CREATE TABLE t1 (c1 INT) COMPRESSION="zlib";
Disable:ALTER TABLE t1 COMPRESSION="None";
❖ 32K & 64K page sizes❖ The extent size is 2M for 32k page sizes
❖ The extent size is 4M for 64k page sizes
❖ Only support file_per_table tablespace
InnoDB - Native Partitioning
❖ Background❖ Tables
❖ Storage engines
❖ Handlers
❖ Saving up to 90% memory
❖ Parallel query processing
❖ Foreign key support
❖ Partition pruning improvement
❖ Global secondary indexes
❖ Full-text searches
InnoDB - Native Full Text Search
❖ Supporting external parsers
❖ N-gram parser plugin for CJK
❖ MeCab full-text parser plugin for Japanese
InnoDB - Cache Retention
❖ Displaying Buffer Pool Status
❖ Saving the Buffer Pool State Now
InnoDB - Cache Retention
❖ Displaying Buffer Pool Dump/Load Progress
❖ Restoring the Buffer Pool State Now
InnoDB - Cache Retention
InnoDB - Cache Retention
❖ Monitoring Buffer Pool Load Progress Using P_S Tables
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;+-------------------------------+----------------+----------------+| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |+-------------------------------+----------------+----------------+| stage/innodb/buffer pool load | 5353 | 7167 |+-------------------------------+----------------+----------------+
mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history;+-------------------------------+----------------+----------------+| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |+-------------------------------+----------------+----------------+| stage/innodb/buffer pool load | 7167 | 7167 |+-------------------------------+----------------+----------------+
Native JSON Support
❖ Native JSON data type was introduced in MySQL 5.7.7
❖ It provides some major benefits compared with TEXT or VARCHAR column❖ Document Validation
❖ Efficient Access
❖ Performance
❖ Convenience
❖ You can seamlessly blend RDBMS and NoSQL in a single tool
Native JSON Support - Document Validation
❖ Insert valid data
❖ Insert invalid data
Native JSON Support - Efficient Access
❖ Add a column within JSON, and update the value
❖ Retrieve data from JSON column
Security Improvements
❖ AES 256 Encryption
❖ Password Rotation Policies
❖ MySQL Secure Installation
❖ Comfortable User Account Management
Security - AES 256 Encryption
❖ Supporting multiple AES Encryption modes
❖ Deprecating ENCODE()/DECODE() and replacing by AES based functions❖ AES_ENCRYPT()
❖ AES_DECRYPT()
❖ Supporting for larger key sizes
Security - Password Rotation Policies
❖ MySQL5.6❖ validate_password plugin
❖ password expiration mechanism
❖ MySQL5.7.4❖ password lifetime policy
❖ default_password_lifetime=360
❖ New Columns❖ authentication_string
❖ password_last_changed
❖ password_lifetime
❖ password_expired
Security - MySQL Secure Installation
❖ MySQL deployments installed using mysqld --initialize are secure by default.❖ Random account passwords on install
❖ Deploy without tests and demos
❖ No anonymous accounts
❖ Limit the scope of the FILE privilege
❖ Stricter permissions on installation files
❖ Remove Server Package’s Perl Dependencies❖ mysql_install_db converted to a C program
❖ mysql_secure_install converted to a C program
❖ mysql_upgrade now doesn’t need to call external binaries
Security - Comfortable User Account Management
❖ Fully functional ALTER USER
❖ IF [NOT] EXISTS clause to user account statements
❖ Temporarily disable user accounts
❖ Authentication plugin that disables logins
❖ Time-based password expiration policy
❖ “Offline” server mode
❖ User account names now 32 characters long
GIS Improvements
❖ New & Improved Spatial Extensions
❖ InnoDB Spatial Indexes
❖ GeoJSON❖ ST_GeomFromGeoJson()
❖ ST_AsGeoJson()
❖ GeoHash
❖ Spatial Aware Client Tools
GIS Improvements
Thank you for using MySQL!
Q & A