MySQL Options in OpenStack

Matt Lord

MySQL Product Manager

August 23, 2016

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |

Safe Harbor Statement

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

MySQL in OpenStack Default database component

How Is It Used?

• MySQL is the world’s most popular FOSS database

• The worlds biggest web properties rely on it • Facebook, Twitter, LinkedIn, Booking.com, Taobao, …

• As well as many of the worlds largest banks and governments

• OpenStack components use MySQL as a persistent data store • Standard MySQL installation, using InnoDB and UTF8 • Standard operations – install, configure, backup, etc. • For HA or Active/Active setups, MySQL+Galera is the de facto standard

• At the heart of the RedDwarf project, which became Trove • MySQL is still the most widely used/supported database in Trove today

4

Galera 3rd party active/active update anywhere clusters

What Is It?

• Write Set Replication library

• Officially called “Galera Cluster for MySQL”

• Implementation of Replicated Database State Machine theory • More specifically it uses a Totem based GCS

• Provides virtually synchronous replication for MySQL/InnoDB 5.1+

• Bundled in MariaDB 10 and Percona XtraDB Cluster 5.5+

• Fully supported on Linux only

• Created by Codership Oy • Founded in 2007

• 1.0 GA release in Oct 2011

• 2.0 released Feb 2012

• 3.0 released Sept 2013 (latest major release)

6

MySQL Group Replication Native active/active update anywhere clusters

What Is It?

“Multi-master update anywhere replication plugin for MySQL with built-in conflict detection and resolution, automatic distributed recovery, and group membership.”

• Group Replication library • Implementation of Replicated Database State Machine theory

• MySQL GCS is based on Paxos (variant of Mencius)

• Provides virtually synchronous replication for MySQL/InnoDB 5.7+

• Supported on all MySQL platforms • Linux, Windows, Solaris, OSX, FreeBSD

• Latest release is 0.8, with “1.0” approaching fast

8

What Does It Provide?

• A highly available distributed MySQL database service • Removes the need for manually handling server fail-over

• Provides distributed fault tolerance

• Enables Active/Active update anywhere setups

• Automates reconfiguration (adding/removing nodes, crashes, failures)

• Automatically detects and handles conflicts

9

Use Cases

• Elastic Replication • Environments that require a very fluid replication infrastructure, where

the number of servers has to grow or shrink dynamically and with as little pain as possible.

• Highly Available Shards • Sharding is a popular approach to achieve write scale-out. Users can

use MySQL Group Replication to implement highly available shards in a federated system. Each shard can map into a Replication Group.

• Alternative to Master-Slave replication • It may be that a single master server makes it a single point of

contention. Writing to an entire group may prove more scalable under certain circumstances.

10

What Does It Look Like?

11

Node Types

R: Traffic routers/proxies: mysqlrouter, haproxy, sqlproxy, ...

M: mysqld nodes participating in Group Replication

The Internal Stack

12

Major Building Blocks: MySQL Server

• Server calls into the plugin through a generic interface

• Most server internals are hidden from the plugin

• Plugin interacts with the server through a generic interface

• Replication plugin determines the fate of the commit operation through a well defined server interface

• The plugin makes use of the relay log infrastructure to inject changes in the receiving server

13

GCS API

Replication

Plugin

Plugin API

MySQL

Server

Group Comm.

System (Corosync) Group Com. Engine

Major Building Blocks: Replication Plugin • The plugin is responsible for

• Maintaining distributed execution context

• Detecting and handling conflicts

• Handling distributed recovery • Detect membership changes

• Donate state if needed

• Collect state if needed

• Proposing transactions to other members

• Receiving and handling transactions from other members

• Deciding the ultimate fate of transactions • commit or rollback

14

GCS API

Replication

Plugin

Plugin API

MySQL

Server

Group Comm.

System (Corosync) Group Com. Engine

Major Building Blocks: MySQL GCS • The communication API (and binding) is

responsible for: • Abstracting the underlying group communication

system implementation from the plugin itself

• Mapping the interface to a specific group communication system implementation

• The Group Communication System engine: • Variant of Paxos developed at MySQL

• Building block to provide distributed agreement between servers

15

GCS API

Replication

Plugin

Plugin API

MySQL

Server

Group Comm.

System (Corosync) Group Com. Engine

Why Use It Instead of Galera?

• A single provider of software and support • Important if you require an Enterprise support contract

• Better performance with > 3 nodes • MySQL GCS is peer to peer, while Galera uses a ring protocol

• Easier monitoring • Performance Schema tables for group and node status/stats

• Native MySQL HA being built around it • Native end-to-end easy to use sharded InnoDB clusters

• Stay tuned!

16

17

The Bigger Picture…

M

App

M M

Orchestrate & Manage

M App

M M

Simple Shard Mapping, State and Extra Metadata

Control, Coordinate, Provision

...

Monitoring (MEM)

MySQL Router Group Replication – Shard 1

Group Replication – Shard N C, Java, JavaScript, Python, ...

MySQL Router

MySQL NDB Cluster For real-time in-memory OLTP and linear R/W scaling

What Is It?

“Distributed in-memory database with automatic transparent sharding – for painless read-write scaling – with SQL and NoSQL APIs, and with support for Active/Active multi-datacenter geographic clusters.”

• A strongly consistent partitioned/sharded database cluster • 2PC protocol used for writes • High performance cross-shard operations • Transparent shard rebalancing • Redundancy at every level with millisecond failover • SQL access via MySQL servers • NoSQL access with C++, Node.JS, Java, … • Latest release is 7.5 (now in Release Candidate stage)

• Supported on Linux, Windows, Solaris, OSX

19

What Does It Provide?

• A highly available distributed MySQL database service • Transparent sharding

• Support for all cross-shard operations

• Online shard rebalancing

• Automatic and custom sharding schemes

• Strong consistency across all shards

• Transparent built-in load balancing

• Multi-datacenter Active/Active geographic replication

• SQL access via standard MySQL Servers • Using the NDBCLUSTER storage engine

• Online operations (ALTER TABLE, etc.)

• Direct NoSQL access to the underlying Key/Value storage • NDB C++ API -- with wrappers for JavaScript, Java, memcached, HTTP/REST

20

SQL

• Industry standard

• Joins & complex queries

• Relational model

Memcached

• Simple to use API

• Key/value based

• Drivers for many languages

Mod-ndb

• RESTful

• JSON over HTTP

• Plugin for Apache

ClusterJ

• Simple to use Java API

• Web & Telco

• Object Relational Mapping

• Native & fast access to data

ClusterJPA

• OpenJPA plugin

• Standards defined ORM

• Cross table Joins

JavaScript/Node.js

• Native JavaScript: client to DB

• Blazing fast asynchronous throughput

SQL and NoSQL APIs

21

22

What Does It Look Like? Node Types

M: MySQL Server (SQL API)

A: NDB API Node

S: NDB Management Server

D: NDB Data Node

23

• Partition customers across multiple clusters, distributed by region to optimize low latency access

• Each sub-cluster is replicated for High Availability and DR

• Active/Passive or Active/Active

Geographic Replication for Low-Latency Local Access

Cluster 2C

Cluster 2B

Cluster 1B

Cluster 1C

Cluster 2

Cluster 1

Cluster 4

Cluster 3

Cluster 3B

Cluster 3C

Cluster 4C

Cluster 4B

24

How Do The Two Compare?

MySQL Group Replication MySQL NDB Cluster

Storage Engine InnoDB NDBCLUSTER

Distributed Architecture Shared nothing Shared nothing

Consistency Model Weak Consistency Strong Consistency

Sharding No Yes

Arbitration No Yes

Load Balancing No Yes

NoSQL APIs MySQL Document Store Native NDB API

Operational Complexity Medium High

Administration Standard (MySQL) Custom (MySQL + NDB)

25

Case Study: Oracle OpenStack

• MySQL NDB Cluster Active/Active

MySQL Cluster RabbitMQ Keepalived Nova Neutron Memcached

Cinder Swift Keystone Glance Heat Horizon

Docker Containers

Controller Node(s)

MySQL Cluster RabbitMQ Keepalived Nova Neutron Memcached

Cinder Swift Keystone Glance Heat Horizon

Docker Containers

Container life cycle management (Ansible)

Management Controller Nodes

API

MySQL Cluster Data Nodes

Management

Data Layer

HA

P

roxy

Galera MySQL

Cluster

Scaling Read Linear

Read/Write

Performance Standard Real-time

Online DDL No Yes

Auto Sharding No Yes

NoSQL APIs No Yes

Load Balancing No Yes

More Information

• Quick Start Guide: http://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/

• MySQL HA Blog: http://mysqlhighavailability.com

• MySQL Documentation: http://dev.mysql.com/doc/

• Forums • MySQL Group Replication: http://forums.mysql.com/list.php?26 • MySQL NDB Cluster: http://forums.mysql.com/list.php?25

• Existing customers: http://support.oracle.com

• Sales questions: https://www.mysql.com/buy-mysql/

• Reach out to me directly: @mattalord

26

27

THANK YOU!