Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
What’s New in MySQL 5.7
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Safe Harbor Statement The following is intended to outline our general product direcOon. It is intended for informaOon purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or funcOonality, and should not be relied upon in making purchasing decisions. The development, release, and Oming of any features or funcOonality described for Oracle’s products remains at the sole discreOon of Oracle.
2
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
0
100,000
200,000
300,000
400,000
500,000
600,000
700,000
8 16 32 64 128 256 512 1,024
Que
ries p
er Secon
d
Connec9ons
MySQL 5.7: Sysbench Read Only (Point Select)
MySQL 5.7
MySQL 5.6
MySQL 5.5
MySQL 5.7: Sysbench: Read Only
Intel(R) Xeon(R) CPU E7-‐4860 x86_64 4 sockets x 10 cores-‐HT (80 CPU threads) 2.3 GHz, 512 GB RAM Oracle Linux 6.5
2x Faster than MySQL 5.6 3x Faster than MySQL 5.5
645,000 QPS
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: InnoDB, NoSQL With Memcached 6x Faster than MySQL 5.6 Thank you, Facebook
0
200,000
400,000
600,000
800,000
1,000,000
1,200,000
8 16 32 64 128 256 512 1,024
Que
ries p
er Secon
d
Connec9ons
MySQL 5.7 vs 5.6 -‐ InnoDB & Memcached
MySQL 5.7
MySQL 5.6
1 Million QPS
Intel(R) Xeon(R) CPU E7-‐4860 x86_64 4 sockets x 10 cores-‐HT (80 CPU threads) 2.3 GHz, 512 GB RAM Oracle Linux 6.5
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: ConnecOons per Second 1.7x Faster than MySQL 5.6 2.5x Faster than MySQL 5.5
67,000 Connec9ons/Sec
0
10,000
20,000
30,000
40,000
50,000
60,000
70,000
80,000
MySQL 5.5 MySQL 5.6 MySQL 5.7
Conn
ec9o
ns/Secon
d
Connec9ons Per Second
MySQL 5.5
MySQL 5.6
MySQL 5.7
Intel(R) Xeon(R) CPU E7-‐4860 x86_64 4 sockets x 10 cores-‐HT (80 CPU threads) 2.3 GHz, 512 GB RAM Oracle Linux 6.5
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
“The actual list of changes and improvements is simply *huge*, and includes many items that I personally find rather exci<ng! Perhaps I'm mistaken but I think this may be one of the largest number of changes packed into a MySQL point release that I've witnessed in a long <me. “ Roland Bouman on MySQL 5.7.5 DMR
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: Improvements in latest DMR 6 • InnoDB for beder transacOonal throughput, availability, IO • Replica9on for beder scalability and availability • MySQL Fabric for high availability and sharding • Performance Schema for new and improved performance metrics • Op9mizer for beder EXPLAINing, parsing, query performance • GIS with naOve InnoDB spaOal indexes and Boost.Geometry integraOon • Security with easier, safer instance iniOalizaOon, setup and management
Available Now! dev.mysql.com/downloads/mysql/
7
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7 Parser & OpOmizer Refactoring
OpOmizer
Logical transformaOons
Cost-‐based opOmizer: Join order and access methods
Plan refinement
Query execuOon plan
Query execuOon
Parser
Resolver: SemanOc check,name resoluOon
SQL DML query
Query result
Storage Engine InnoDB MyISAM
Improves readability, maintainability and stability
– Cleanly separate the parsing, opOmizing, and execuOon stages
– Allows for easier feature addiOons, with lessened risk
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: Parser Refactoring • Challenge:
– Overly complex, hard to add new syntax
• SoluOon: – Create an internal parse tree bodom-‐up – Create an AST (Abstract Syntax Tree) from the parse tree and the user's context.
– Have syntax rules that are more precisely defined and are closer to the SQL standard.
– More precise error messages – Beder support for larger syntax rules in the future
Resolver
Optimizer
SE
Lexical Scanner (lexer)
GNU Bison-generated Parser (bottom-up parsing style)
Contextualization
Parser (new)
Executor
AST
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7 OpOmizer
SELECT a, b FROM t1, t2, t3 WHERE t1.a = t2.b AND t2.b = t3.c AND t2.d > 20 AND t2.d < 30;
MySQL Server
Cost based opOmizaOons
HeurisOcs
Cost Model OpOmizer
Table/index info (data dicOonary)
StaOsOcs (storage engines)
t2 t3
t1
Table scan
Range scan
Ref access
JOIN
JOIN
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: OpOmizer Improvements • Parser and opOmizer refactoring • Improved cost model
– CondiOon filtering – Configurable cost constants – API for storage engines to indicate locaOon of data (disk or memory buffer) – Costs used to determine opOmal execuOon (including LIMIT)
• Query Rewrite Plugin • Explain on a running query • Generated columns
11
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7 OpOmizer: New Cost Model -‐ Why? • More accurate cost esOmates
– Beder decisions by the opOmizer should improve query performance
• Adapt to new hardware architectures – SSDs, larger memory sizes, improved caches
• More maintainable cost model implementaOon – Avoid hard coded “cost constants” – Refactoring of exisOng cost model code
• Configurable and tunable
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: Query Rewrite Plugin • New pre and post parse query rewrite APIs
– Users can write their own plug-‐ins • Provides a post-‐parse query plugin
– Rewrite problemaOc queries without the need to make applicaOon changes – Add hints – Modify join order – Many more …
• Improve problemaOc queries from ORMs, third party apps, etc • ~Zero performance overhead for queries not to be rewriden
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: OpOmizer -‐ Cost Info in JSON EXPLAIN • Expanded JSON EXPLAIN
– Now includes all available cost info – Used for Visual Explain In MySQL Workbench
14
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "200.40" }, "table": { "table_name": "nicer_but_slower_film_list", "access_type": "ALL", "rows_examined_per_scan": 992, "rows_produced_per_join": 992, "filtered": 100, "cost_info": { "read_cost": "2.00", "eval_cost": "198.40", "prefix_cost": "200.40", "data_read_per_join": "852K" }, "used_columns": [ "FID", "title", "description", "category", "price", "length", "rating", "actors" ], ...
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Early Access Feature (EAF): Data DicOonary Replacing the FRMs
• A single repository for database object metadata – InnoDB tables replace .frm, .trg, .trn, .par files
• Atomic & crash-‐safe operaOons today – TransacOonal in the future
• Makes adding new features much easier • Eliminates complexity, resolves bugs • Improves performance • Leverages InnoDB strengths
15
labs.mysql.com
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
InnoDB
New Data DicOonary: Architecture
Query ExecuOoner
Parser OpOmizer
Data DicOonary Tablespace
Data DicOonary Internal API
Internal SE
Data DicOonary External API
Plugin
Storage Engine
Plugin
Storage Engine
Plugin Plugin Plugin
User Table Tablespace
labs.mysql.com
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
Memory Instrumenta9on • Aggregates staOsOcs by
– Type of memory used (caches, internal buffers, …)
– Thread/account/user/host indirectly performing the memory operaOon
• Adributes include – Memory used (bytes) – OperaOon counts – High/Low Water Marks
Statement Instrumenta9on • Stored Procedures • Stored FuncOons • Prepared Statements
• TransacOons
Addi9onal Informa9on • ReplicaOon slave status • MDL lock instrumentaOon
• User variables per thread • Server stage tracking • Track long running SQL • Improved configuraOon
• All while reducing total footprint and overhead
MySQL 5.7: Performance Schema
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: SYS Schema Helper objects for DBAs, Developers and Opera9ons staff • Helps simplify DBA / Ops tasks -‐ Monitor server health, user, host staOsOcs -‐ Spot, diagnose, and tune performance issues
• Easy to understand views with insights into -‐ IO hot spots, Locking, Costly SQL statements -‐ Schema, table and index staOsOcs
• SYS is similar to -‐ Oracle V$ catalog views -‐ Microso{ SQL DMVs (Dynamic Mgmnt Views)
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
• Replaced custom code – For spaOal calculaOons – For spaOal analysis
• Provides OGC compliance – With improved performance
• Boost.Geometry contains – Field and domain experts – Bustling and robust community
• We’re also Boost.Geometry contributors!
MySQL 5.7: GIS -‐ IntegraOng Boost.Geometry
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
• R-‐tree based – Full transacOonal support – Predicate locking to prevent phantoms – Records contain minimum bounding box
• Small and compact
– Currently only supports 2D data • We would like to add 3D support in the future
– Supports historical spaOal index DDL syntax
MySQL 5.7: GIS -‐ InnoDB SpaOal Indexes
20
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
• GeoHash – B-‐tree indexes on the generated hash values – Quick lookups for exact locaOon matches
• GeoJSON • Many addiOonal type combinaOons • Geometry CollecOon support • New helper funcOons
– ST_Distance_Sphere(), ST_IsValid(), ST_MakeEnvelope(), ST_Simplify(), ST_Validate()
• Limited SRID support
{ "type": "Feature", "geometry": { "type": "Point", "coordinates": [125.6, 10.1] }, "properties": { "name": "Dinagat Islands" } }
GeoJSON Example
MySQL 5.7: GIS -‐ AddiOonal Features
21
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: InnoDB Improvements • Scalability! RO, RW • Parallel “dirty page” flushing
– Improved adapOve flushing algorithm and mechanisms
• AutomaOc truncaOon of UNDO logs – Stored in separate tablespace
• Online, resize buffer pool • Fast Create Index, Bulk load • Improved Temporary Table performance • Compression, ParOOoning, Tablespaces, GIS
22
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: InnoDB -‐ Online • Resize the InnoDB Buffer Pool online
– Allows DBAs to tune the buffer size without any downOme – Adapt in real-‐Ome to changes in database usage paderns
• Online ALTER TABLE – Enlarge VARCHAR, Rename Index
• Dynamic configuraOon – As a design principle for new features & se}ngs – But also make exisOng variables dynamically sedable
23
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: InnoDB -‐ Bulk Load for Create Index • Much faster INDEX creaOon and bulk loads • Sorted index builds, done from the bodom-‐up
– Improves speed by increasing locality and decreasing node spli}ng
• Pages are compressed only when full • New innodb_fill_factor opOon controls free space le{ in each page • Performance results show
– 2-‐3x performance improvement for ADD/CREATE INDEX operaOons – 2-‐5% improvement for standard INSERT operaOons
24
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: InnoDB Temporary Tables • New separate tablespace for temporary tables
– Improved CREATE/DROP performance – DDL changes are transient, which eliminates some disk IO
• OpOmize DML operaOons – No REDO logging, no change buffering, less locking
• New intrinsic temporary tables – Specialized temporary tables with tailored ACID/MVCC semanOcs – Light weight and ultra-‐fast, great for intermediate query execuOon operaOons
• InnoDB as default storage engine for disk based temp tables – OpOmizer switched from MyISAM to InnoDB (faster) for internal temp tables
25
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: InnoDB Full-‐Text CJK Support • Two new Full-‐Text Parser plugins • N-‐gram parser supports Chinese, Japanese, & Korean
– Supports all ideographic languages that do not use word delimiters
• MeCab parser supports Japanese – NaOve Japanese focused language support
• Easily customized – Token sizes, stop words, …
• Supports advanced searches – BOOLEAN MODE, NATURAL LANGUAGE MODE, with Ranking
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: InnoDB • InnoDB support for MySQL Group ReplicaOon
– High priority transacOons • InnoDB NaOve ParOOoning
– Will allow us to eliminate previous limitaOons on parOOoned tables – Removes memory usage problems when many parOOons are used
• Support for 32K and 64K pages • General TABLESPACE support
– Store mulOple tables in user defined shared tablespaces
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
EAF: InnoDB Compression Thank you, SanDisk Fusion-‐io
• Transparent Page Level Compression – Happens transparently in background threads – Managed enOrely within the IO layer – Uses sparse file and "hole punching" support in OS kernels and File Systems
• Reduces IO – Improves MySQL performance – Improves storage efficiency – Reduces write cycles, thus increasing SSD lifespan
• Applies to all InnoDB data, including the system tablespace and UNDO logs
28
labs.mysql.com
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: Syslog Support for Linux/Unix pla�orms
• NaOve support for syslog • Simple opOon to (re)direct log output to naOve syslog facility • Start-‐up server configuraOon opOon • Dynamically in the running server
– System variable log_syslog (ON/OFF, defaults to OFF).
Thank you, Simon Mudd at booking.com
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: Security -‐ EncrypOon, Passwords, InstallaOon • AES 256 EncrypOon
– Default in MySQL 5.7
• Password rotaOon policies – Can be set globally, and at the user level
• Deployment: enable secure unadended install by default – Random password set on install – Remove anonymous accounts – Deployment without test account, schema, demo files
• Easier instance iniOalizaOon and setup: mysqld -‐-‐iniOalize
30
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: Security -‐ SSL • Enabled by default • Auto-‐detecOon of exisOng keys and certs • Auto generaOon of keys and certs when needed • New helper uOlity: mysql_ssl_rsa_setup
31
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: Locking
MulOple User Level Locks per ConnecOon • User-‐level locks can be used to organize mutual exclusion
– When accessing some resource – When table or row-‐level locks are not appropriate
• Request mulOple locks by issuing a series of GET_LOCK statements • Replaces custom user-‐level lock implementaOon
– With one based on the MDL lock manager – Deadlocks between different connecOons acquiring user-‐level locks, metadata locks, and those waiOng for table flushes are properly detected and reported as errors.
Thank you, Konstan9n Osipov!
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: Improved MDL locking • Fast-‐path for DML locks • Lock-‐free DML lock acquisiOon • Lock-‐free hash
– Now uses MurmurHash library
• Removes bodlenecks around DML access to a single table – 10% increased throughput in OLTP_RO/POINT_SELECT sysbench – OpOmized for typical DML heavy workloads
33
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: Server-‐side Statement Timeouts Thank you Davi Arnaut!
• Server side statement Omeouts – Global for server, per session, or for individual SELECT statements
• Expanded to Windows and Solaris, restricted by removing USER opOon
SELECT MAX_STATEMENT_TIME = 109 * FROM my_table;
34
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL HA & Scaling SoluOons MySQL
Replica9on MySQL Fabric
Oracle VM Template
Oracle Clusterware
Solaris Cluster
Windows Cluster DRBD MySQL
Cluster
App Auto-‐Failover ✖ ✔ ✔ ✔ ✔ ✔ ✔ ✔
Data Layer Auto-‐Failover ✖ ✔ ✔ ✔ ✔ ✔ ✔ ✔
Zero Data Loss MySQL 5.7 MySQL 5.7 ✔ ✔ ✔ ✔ ✔ ✔
Pla�orm Support All All Linux Linux Solaris Windows Linux All
Clustering Mode Master + Slaves
Master + Slaves
Ac9ve/Passive
Ac9ve/Passive
Ac9ve/Passive
Ac9ve/Passive
Ac9ve/Passive
Mul9-‐Master
Failover Time N/A Secs Secs + Secs + Secs + Secs + Secs + < 1 Sec
Scale-‐out Reads ✔ ✖ ✖ ✖ ✖ ✖ ✔
Cross-‐shard operaOons N/A ✖ N/A N/A N/A N/A N/A ✔
Transparent rouOng ✖ For HA ✔ ✔ ✔ ✔ ✔ ✔
Shared Nothing ✔ ✔ ✖ ✖ ✖ ✖ ✔ ✔
Storage Engine InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ NDB
Single Vendor Support ✔ ✔ ✔ ✔ ✔ ✖ ✔ ✔
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7: ReplicaOon Improvements • On-‐line, phased deployment of GTIDs • Use GTIDs without binary logging on slave • Improved Master throughput
– Beder synchronizaOon between replicaOon-‐user sessions; Ack receiver thread, group commit tuning…
• Lossless ReplicaOon through enhanced Semi-‐sync
• Configurable number of Semi-‐sync slave Acks
36
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
• MulO-‐Source ReplicaOon – Consolidate updates from mulOple Masters into one Slave • Consolidated view of all shards • More flexible topologies • Centralized point for backups
– CompaOble with Semi-‐Sync ReplicaOon & enhanced MTS
• Performance Schema tables for monitoring slave
• Online OperaOons: Dynamic ReplicaOon Filters, switch master
MySQL 5.7: ReplicaOon Improvements
Binlog
Master 1
Binlog
Master 2
…
…
Binlog
Master N
IO 1
Relay 1
Coordinator
W1 W2 … WX
IO 2
Relay 2
Coordinator
W1 W2 … WX
…
…
Coordinator
W1 W2 … WX
IO N
Relay N
Coordinator
W1 W2 … WX
Slave
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
• MulO-‐Threaded Slaves: – Performance -‐ Slave applies transacOons in parallel even within same database; group commit tuning
– OpOon to preserve Commit order – AutomaOc slave transacOon retries
• Up to 5X performance vs single threaded slave – No changes to the applicaOon
• GTIDs & Crash-‐Safe Slave enabled
MySQL 5.7: ReplicaOon Improvements
0
500
1000
1500
2000
2500
3000
3500
4000
4500
5000
0 4 16 25 50 100 200 Worker Threads
Slave Transac9ons per Second
Baseline
50 clients
100 clients
150 clients
200 clients
• Sysbench OLTP test – 10M rows – SSD / 48 core HT / 512 GB RAM
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
• Shared-‐nothing virtually synchronous database system
• MulO-‐master update anywhere – Conflict detecOon and resoluOon (transacOon rollback) – OpOmisOc State Machine ReplicaOon
• AutomaOc group membership management and failure detecOon – No need for server fail-‐over – ElasOc scale out/in – No single point of failure – AutomaOc reconfiguraOon
• Well integrated – InnoDB – GTID-‐based replicaOon – PERFORMANCE_SCHEMA
EAF: Group ReplicaOon
ApplicaOon
MySQL Masters ReplicaOon Plugin
API
MySQL Server
Group Comms (Corosync)
labs.mysql.com
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
EAF: HTTP Plugin for MySQL • Server Plugin adds HTTP(S) endpoints to MySQL • Results are serialized to JSON format encoded as UTF8 • Provides 3 choices of User Endpoint Types
– SQL – CRUD -‐ Key-‐Value – JSON -‐ Document
labs.mysql.com
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL Fabric 1.5
• High Availability – Server monitoring with auto-‐promoOon and transparent applicaOon failover
• OpOonally scale-‐out through sharding – ApplicaOon provides shard key – Range or Hash – Tools for resharding – Global updates & tables
• Fabric-‐aware connectors rather than proxy: Python, Java, PHP, .NET, C (labs) – Lower latency, bodleneck-‐free
• Server provisioning using OpenStack etc.
High Availability + Sharding-‐Based Scale-‐out
MySQL Fabric
Connector
ApplicaOon
Read-‐slaves
mappings
SQL
HA group
Read-‐slaves
HA group
Connector
ApplicaOon
GA
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL Workbench 6.2 • Fabric
– Add fabric node, browse, view, connect • Performance Dashboard
– Performance Schema Reports & Graphs
• Visual Explain • GIS Viewer • MigraOon
– New Microso{ Access – Microso{ SQL Server, Sybase, PostgreSQL
GA
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL on Windows • MySQL Installer for Windows • MySQL Workbench
• MySQL MigraOon Wizard – Microso{ SQL Server – Microso{ Access
• MySQL for Visual Studio
• MySQL for Excel
• MySQL NoOfier
• MySQL Connector/.Net
• MySQL Connector/ODBC
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
• Simple and convenient way to install & update MySQL products
• Supports the following distribuOons – Oracle, Red Hat, CentOS – Fedora – Ubuntu, Debian – SUSE
• Coming Soon – Preconfigured Containers – Improved support for popular DevOps deployment tools
• The latest releases of – MySQL Database – MySQL Workbench – MySQL Connector/ODBC – MySQL Connector/Python – MySQL Connector/NET – MySQL UOliOes
MySQL Community Repositories: Yum, APT, NuGET
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
MySQL Source Code Available on GitHub • Git for MySQL Engineering
– Fast, flexible and great for a distributed team – Great tooling – Large and vibrant community
• GitHub for MySQL Community – Easy and fast code availability to the community and to downstream projects
hdps://github.com/mysql