mysql cluster 7.4
TRANSCRIPT
MySQL Cluster In-‐Memory Real-‐Time Performance, Web Scalability & 99.999% Availability
Mark Swarbrick Principle Presales Consultant
Safe Harbor Statement The following is intended to outline our general product direc?on. It is intended for informa?on purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or func?onality, and should not be relied upon in making purchasing decisions. The development, release, and ?ming of any features or func?onality described for Oracle’s products remains at the sole discre?on of Oracle.
Copyright 2015, oracle and/or its affiliates. All rights reserved 2
consumer
employee
business model
around-the-clock availability
mobile
social
personalized service
Information any time, anywhere
flawless engagement insight
real-time fulfillment
new markets
Focus on driving the business rather than on infrastructure Driving new Database Requirements
In-‐Memory Real-‐Time
Performance
Extreme Read & Write
Scalability
Rock Solid Availability
Rapid Service
Innova?on
Copyright 2015, oracle and/or its affiliates. All rights reserved 4
MySQL Cluster Overview • Memory op?mized tables with durability • Predictable Low-‐Latency, Bounded Access Time REAL-‐TIME
• Auto-‐Sharding, Mul?-‐Master • ACID Compliant, OLTP + Real-‐Time Analy?cs
HIGH SCALE, READS + WRITES
• Shared nothing, no Single Point of Failure • Self Healing + On-‐Line Opera?ons 99.999% AVAILABILITY
• Key/Value + Complex, Rela?onal Queries • SQL + Memcached + JavaScript + Java + HTTP/REST & C++ SQL + NoSQL
• Open Source + Commercial Edi?ons • Commodity hardware + Management, Monitoring Tools LOW TCO
Copyright 2015, oracle and/or its affiliates. All rights reserved 5
Who’s Using MySQL Cluster?
Copyright 2015, oracle and/or its affiliates. All rights reserved 6
Company Overview PayPal allows any business or individual with an email address to securely, conveniently and cost-‐effec?vely send and receive payments online. ApplicaSon PayPal built a cloud-‐based globally-‐distributed database with 100 TB of user-‐related data based on MySQL Cluster. “Must NOT lose data” system, delivering 99,999% availability, transac?onal, with data available WW anywhere in < 1 Sec. Why MySQL Cluster? “You can achieve high performance and availability without giving up rela8onal models and read consistency.” Daniel Aus?n, Chief Architect, PayPal
PayPal
Copyright 2015, oracle and/or its affiliates. All rights reserved 7
Company Overview World's largest producer of casual games. Big Fish has distributed more than 2.5 billion games to customers in 150 countries. ApplicaSon Serve real-‐?me recommenda?ons to Big Fish customers, requiring high velocity data ingest, low latency access, online scalability, 99.999% availability and Enterprise SLAs & roadmap. Why MySQL Cluster CGE? "The MMS pla@orm is a strategic project within Big Fish. We couldn’t afford to take any chances and MySQL Cluster provided us with a proven and trusted solu8on to meet the demands of both our business and our users.“ Sean Chighizola, Sr. Director of Database Administra?on, Big Fish
Big Fish
Copyright 2015, oracle and/or its affiliates. All rights reserved 8
Company Overview Global telecommunica?ons equipment company, focuses on fixed, mobile, and converged networking hardware, IP technologies, somware, and services. ApplicaSon MySQL Cluster CGE is at the heart of Alcatel-‐Lucent’s Subscriber Data Manager to deliver converged IMS services to mobile and fixed line users. Why MySQL Cluster CGE? • Delivers the performance, scalability and availability
required by this mission cri?cal real-‐?me applica?on • Real-‐?me read & write access for tens of millions of
subscribers in a single system • Always-‐on service: no offline maintenance window for
services users depend on (voice, SMS, email, Web, social media…)
• Rapid delivery & low TCO
Alcatel-‐Lucent
21/04/15 Copyright 2015, oracle and/or its affiliates. All rights reserved 9
Company Overview Playful Play created La?n America’s #1 Facebook game based on "El Chavo del 8“. ApplicaSon Playful Play selected MySQL Cluster CGE running on a public cloud to power their gaming plauorm, providing: • 45% improvement in performance • 99.999% up?me • 80% reduc?on in DBA overhead • Local language support, 24x7 Why MySQL Cluster CGE? “MySQL Cluster has allowed us to keep pace with the rapid success of the game. Being the number one Facebook game in La8n America is a big responsibility, and we rely on Oracle’s products and MySQL support for con8nued growth.” Ricardo Rocha, CEO, Playful Play
Playful Play
Copyright 2015, oracle and/or its affiliates. All rights reserved 10
MySQL ReplicaSon
MySQL Fabric
DRBD
Windows/Solaris/Clusterware Clustering or Oracle VM
MySQL Cluster
MySQL HA Solu?ons
19th February 2015
9 9 . 9 9 9 %
Copyright 2015, Oracle and/or its affiliates. All rights reserved 11
Oracle MySQL HA & Scaling Solu?ons MySQL
ReplicaSon 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 ✔ ✔ ✔ ✔ ✔ ✔
Plauorm Support All All Linux Linux Solaris Windows Linux All
Clustering Mode Master + Slaves
Master + Slaves
AcSve/Passive
AcSve/Passive
AcSve/Passive
AcSve/Passive
AcSve/Passive
MulS-‐Master
Failover Time N/A Secs Secs + Secs + Secs + Secs + Secs + < 1 Sec
Scale-‐out Reads ✔ ✖ ✖ ✖ ✖ ✖ ✔
Cross-‐shard opera?ons N/A ✖ N/A N/A N/A N/A N/A ✔
Transparent rou?ng ✖ For HA ✔ ✔ ✔ ✔ ✔ ✔
Shared Nothing ✔ ✔ ✖ ✖ ✖ ✖ ✔ ✔
Storage Engine InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ InnoDB+ NDB
Single Vendor Support ✔ ✔ ✔ ✔ ✔ ✖ ✔ ✔
21/04/15 Copyright 2015, oracle and/or its affiliates. All rights reserved 12
MySQL Cluster Architecture
MySQL Cluster Data Nodes
Clients
Applica?on Layer
Data Layer
Copyright 2015, oracle and/or its affiliates. All rights reserved 13
MySQL Cluster Scaling
MySQL Cluster Data Nodes
Clients
Applica?on Layer
Data Layer
Copyright 2015, oracle and/or its affiliates. All rights reserved 14
On-‐line Opera?ons • Scale the cluster (add & remove nodes on-‐line) • Repar??on tables • Upgrade / patch servers & OS • Upgrade / patch MySQL Cluster • Back-‐Up • Evolve the schema on-‐line, in real-‐?me
Copyright 2015, oracle and/or its affiliates. All rights reserved 15
MySQL Cluster Auto-‐Installer
• Fast configura?on • Auto-‐discovery • Workload op?mized • Repeatable best prac?ces
Specify Workload
Auto-‐Discover
Define Topology Deploy
Copyright 2015, oracle and/or its affiliates. All rights reserved 16
Copyright 2015, oracle and/or its affiliates. All rights reserved 17
Copyright 2015, oracle and/or its affiliates. All rights reserved 18
NoSQL Access to MySQL Cluster data
Apps Apps Apps Apps Apps Apps Apps Apps Apps Apps Apps Apps
JPA
Cluster JPA
PHP Perl Python Ruby JDBC Cluster J JS Apache Memcached
MySQL JNI Node.JS mod_ndb ndb_eng
NDB API (C++)
MySQL Cluster Data Nodes
Schema-‐Free apps
• Rapid applica?on evolu?on – New types of data constantly added
– No ?me to get schema extended – Missing skills to extend schema – Ini?ally roll out to just a few users – Constantly adding to live system
Copyright 2015, oracle and/or its affiliates. All rights reserved 20
Cluster & Memcached – Schema-‐Free
<town:maidenhead,SL6>
key value
<town:maidenhead,SL6>
key value
Key Value
town:maidenhead SL6
generic table
Applica?on view
SQL view
Copyright 2015, oracle and/or its affiliates. All rights reserved 21
Cluster & Memcached -‐ Configured Schema
<town:maidenhead,SL6>
prefix key value
<town:maidenhead,SL6>
key value
Prefix Table Key-‐col Val-‐col policy
town: map.zip town code cluster
Config tables
town ... code ...
maidenhead ... SL6 ...
map.zip
Applica?on view
SQL view
Copyright 2015, oracle and/or its affiliates. All rights reserved 22
Node.js NoSQL API • Na?ve JavaScript access to MySQL Cluster – End-‐to-‐End JavaScript: browser to the app & DB
– Storing and retrieving JavaScript objects directly in MySQL Cluster
– Eliminate SQL transforma?on
• Implemented as a module for node.js – Integrates Cluster API library within the web app
• Couple high performance, distributed apps, with high performance distributed database
• Op?onally routes through MySQL Server – Use with InnoDB
V8 JavaScript Engine
MySQL Cluster Node.js Module
MySQL Cluster Data Nodes
Clients
Copyright 2015, oracle and/or its affiliates. All rights reserved 23
NoSQL API for Node.js & FKs
FKs enforced on all APIs: { message: 'Error', sqlstate: '23000', ndb_error: null, cause: {message: 'Foreign key constraint violated: No parent row found [255]', sqlstate: '23000', ndb_error: { message: 'Foreign key constraint violated: No parent row found', code: 255, classification: 'ConstraintViolation', handler_error_code: 151, status: 'PermanentError' }, cause: null } }
Copyright 2015, oracle and/or its affiliates. All rights reserved 24
MySQL Cluster 7.4 GA
Copyright 2015, oracle and/or its affiliates. All rights reserved 25
MySQL Cluster 7.4 GA
• 200 Million NoSQL Reads/Sec
• 2.5M SQL Ops/Sec • 50% Faster Reads • 40% Faster Mixed
Performance
• Ac?ve-‐Ac?ve Geographic Redundancy
• Conflict Detec?on/Resolu?on
Ac?ve-‐Ac?ve
• 5X Faster Maintenance Ops
• Detailed Repor?ng
Management
Copyright 2015, oracle and/or its affiliates. All rights reserved 26
• Memory op?mized tables – Durable – Mix with disk-‐based tables
• Massively concurrent OLTP • Distributed Joins for analy?cs • Parallel table scans for non-‐indexed searches
• MySQL Cluster 7.4 FlexAsych – 200M NoSQL Reads/Second
27
MySQL Cluster 7.4 NoSQL Performance 200 Million NoSQL Reads/Second
Copyright 2015, oracle and/or its affiliates. All rights reserved
!"!!!!
!50,000,000!!
!100,000,000!!
!150,000,000!!
!200,000,000!!
!250,000,000!!
2! 4! 6! 8! 10! 12! 14! 16! 18! 20! 22! 24! 26! 28! 30! 32!
Read
s&per&se
cond
&
Data&Nodes&
FlexAsync&Reads&
• Memory op?mized tables – Durable – Mix with disk-‐based tables
• Massively concurrent OLTP • Distributed Joins for analy?cs • Parallel table scans for non-‐indexed searches
• MySQL Cluster 7.4 DBT2 BM – 2.5M SQL Statements/Second
21/04/15 28
MySQL Cluster 7.4 SQL Performance 2.5M SQL Statements/Second
Copyright 2015, oracle and/or its affiliates. All rights reserved
!"!!!!
!500,000!!
!1,000,000!!
!1,500,000!!
!2,000,000!!
!2,500,000!!
!3,000,000!!
2! 4! 6! 8! 10! 12! 14! 16!
SQL$Statem
ents/sec$
Data$Nodes$
DBT2$SQL$Statements$per$Second$
Performance Enhancements MySQL Cluster 7.4
50% Read-‐Only Increase
Copyright 2015, oracle and/or its affiliates. All rights reserved 29
0"
1000"
2000"
3000"
4000"
5000"
6000"
7000"
8000"
9000"
64" 128" 192" 256" 320" 384" 448" 512"
Tran
sac'on
s)per)se
cond
)
Threads)
Sysbench)R/W)
7.4"
7.3"
7.2"
0"
1000"
2000"
3000"
4000"
5000"
6000"
7000"
8000"
9000"
10000"
64" 128" 192" 256" 320" 384" 448" 512"
Tran
sac'on
)per)se
cond
)
Threads)
Sysbench)RO)
7.4"
7.3"
7.2"
40% Read/Write Increase
• Asynchronous replica?on between MySQL Clusters
• Ac?ve-‐Ac?ve – Update anywhere – Conflict detec?on
• Applica?on no?fied through excep?on tables • Can opt to have conflicts resolved automa?cally
– Auto-‐conflict-‐resolu?on • Conflic?ng transac?on and dependent ones are rolled-‐back
• No changes to applica?on schema
Ac?ve-‐Ac?ve Geo-‐Replica?on
Copyright 2015, oracle and/or its affiliates. All rights reserved 30
MySQL Cluster 7.4 Restart Improvements
• Dura?on of long-‐running maintenance ac?vi?es dominated by Data Node restart ?mes
• MySQL Cluster 7.4 = 5.5X faster restarts • Benefits both nodal and rolling restarts
– Upgrades, add-‐node,… • Benefits both SQL and NoSQL APIs • Benefits both ”manual” and MySQL Cluster Manager opera?ons • Achieve 5x as much during a single maintenance window
Make Data Node Restarts Fast!
Copyright 2015, oracle and/or its affiliates. All rights reserved 31
MySQL Cluster 7.4 – Enhanced memory repor?ng • ndbinfo.memory_per_fragment memory usage informa?on for each fragment replica, for each table and index
• Allocated memory and how much of that is actually in use. • Exposes
– Fragmenta?on of fixed and var-‐sized fragment pages – Accurate Data and Index Memory use – Comparison of Primary and Backup fragment usage – Par??oning effec?veness
Copyright 2015, oracle and/or its affiliates. All rights reserved 32
MySQL Cluster 7.4 – Enhanced ac?vity repor?ng • ndbinfo.operations_per_fragment ac?vity counters for each fragment replica, for each table and index
• PK & scan access – requests, bytes, rows… • Exposes
– How traffic maps to tables and indices – Query execu?on, use of indexes etc. – LDM and node imbalances – Hotspots and scan overloads
Copyright 2015, oracle and/or its affiliates. All rights reserved 33
When to Consider MySQL Cluster l Scalability demands
l Sharding for write performance? l Latency demands
l Cost of each millisecond? l Up?me requirements
l Cost per minute of down?me? l Failure versus maintenance?
l Applica?on agility l Developer languages and frameworks? l SQL or NoSQL?
Copyright 2015, oracle and/or its affiliates. All rights reserved 34
General Usage Considera?ons • MySQL Cluster is designed for
– Short transac?ons – Many parallel transac?ons
• U?lize simple access pa~erns for high running transac?ons – Use efficient scans and batching interfaces – AQL gives huge performance in JOIN opera?ons – MySQL Cluster 7.4 speeds up table scans
• Storage engine configurable for each table…InnoDB or NDB
MySQL Cluster Evalua?on Guide h~p://mysql.com/why-‐mysql/white-‐papers/mysql_cluster_eval_guide.php
Copyright 2015, oracle and/or its affiliates. All rights reserved 35
MySQL Cluster Carrier Grade Edi?on (CGE)
MySQL Cluster CGE
MySQL Cluster Manager
MySQL Enterprise Scalability
MySQL Enterprise Audit
MySQL Enterprise Security
Oracle Premier Life?me Support
Oracle Product
Cer?fica?ons
MySQL Enterprise Mornitor
MySQL Workbench
Copyright 2015, oracle and/or its affiliates. All rights reserved 36
Enhancing DevOps Agility, Reducing Down?me
Automated Management
• Start / Stop node or whole cluster
• On-‐Line Scaling • On-‐Line Reconfigura?on
• On-‐Line Upgrades • On-‐Line Backup & Restore
• Import Running Cluster
Self-‐Healing
• Node monitoring • Auto-‐recovery extended to SQL + mgmt nodes
HA Opera?ons
• Cluster-‐wide configura?on consistency
• Persistent configura?ons
• HA Agents
Copyright 2015, oracle and/or its affiliates. All rights reserved 37
Without MySQL Cluster Manager • 1 x preliminary check of cluster state • 8 x ssh commands per server • 8 x per-‐process stop commands • 4 x scp of config files (2 x mgmd & 2 x mysqld) • 8 x per-‐process start commands • 8 x checks for started and re-‐joined processes • 8 x process comple?on verifica?ons • 1 x verify comple?on of the whole cluster. • Excludes manual edi?ng of each configura?on file.
• Total: 46 commands – 2.5 hours of a~ended opera?on
With MySQL Cluster Manager
mcm> upgrade cluster --package=7.4 mycluster;
• Total: 1 Command -‐
– Una~ended Opera?on
Somware Upgrade – example of benefits of MCM?
Copyright 2015, oracle and/or its affiliates. All rights reserved 38
MCM: Upgrade Cluster
mcm> upgrade cluster --package=7.4 mycluster;
Copyright 2015, oracle and/or its affiliates. All rights reserved 39
MySQL Cluster Manager 1.3.3 GA
“Unmanaged” produc?on Cluster
mcm> create cluster --import
mcm> import config [--dryrun]
mcm> import cluster[--dryrun]
Cluster now managed by MCM
Import a running Cluster into MCM
Copyright 2015, oracle and/or its affiliates. All rights reserved 40
Oracle Premier Support for MySQL
• Straight from the Source • Largest Team of MySQL Experts • Backed by MySQL Developers • Forward Compatible Hot Fixes • MySQL Maintenance Releases • MySQL Support in 29 Languages • 24/7/365 • Unlimited Incidents • Knowledge Base • MySQL Consultative Support
Rely on The Experts -‐ Get Unique Benefits
"The MySQL support service has been essen?al in helping us with troubleshoo?ng and providing recommenda?ons for the produc?on cluster, Thanks." -‐-‐ Carlos Morales – Playfulplay.com
Only From Oracle
Copyright 2015, oracle and/or its affiliates. All rights reserved 41
Evaluate MySQL Cluster CGE
• Navigate to http://edelivery.oracle.com/ and step through (selecting “MySQL Database” as the Product Pack)
• Select MySQL Cluster Manager
30-‐Day Trial
Copyright 2015, oracle and/or its affiliates. All rights reserved 42
Next Steps Learn More • www.mysql.com/cluster • Authen?c MySQL Curriculum: h~p://oracle.com/educa?on/mysql
Try it Out • dev.mysql.com/downloads/cluster/
Let us know what you think • clusterdb.com • @clusterdb • forums.mysql.com/list.php?25
Copyright 2015, oracle and/or its affiliates. All rights reserved 43