elastic scalability in mysql fabric using openstack

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

Elastic Scalability inMySQL Fabric usingOpenStack

Mats KindahlSenior Principal Software Developer

Narayanan VenkateswaranPrincipal Software Developer

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

Copyright © 2014, 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.

2014-10-02

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |2014-10-02

Session Outline

Elasticity in Computing1

2

3

4

5

OpenStack

Introduction to MySQL Fabric

Elasticity in MySQL Fabric using OpenStack

Closing Remarks & Thoughts for the Future


Elasticity in Computing


“[Elasticity] is defined as the degree to which a system is able to adapt to workload changes by provisioning and deprovisioning resources in an autonomic manner”

What is Elasticity?


Elasticity from our Perspective● Managing read load

– Read load increases– Problem: Read servers

overloaded– Solution: Add more read

servers● Managing shards

– A shard is hot or full– Problem: slower queries– Solution: Split the shard

• Managing server failures– A server in a group fails– Slave is promoted– Problem: Availability is

affected– Solution: Add replacement

node


OpenStack


“OpenStack is a free and open-source software cloud computing software platform”

What is OpenStack?

What is that?


Cloud Computing According to NIST● Resouce pooling

– Resources are assigned and reassigned based on customer demand

● Broad network access– Capabilities are accessed over the network through standard mechanisms

● Measured service● On-demand self-service

– Customer can unilaterally provision computing capabilities

● Rapid elasticity– Capabilities can be elastically provisioned and released


OpenStack Deployment Example

● Controller Node– Manages Identities– Manages Images– Manages Block Storage

● Compute Node– (Virtual) machine host– Contain hypervisor– Spawn and destroy virtual

machines

Controller

Compute(VM Host)


Introduction toMySQL Fabric


What is MySQL Fabric?

An extensible and easy-to-use framework for managing a farm of MySQL servers supporting high-availability and sharding


What does all that mean?

● Management System– Manages a MySQL Farm– Distributed Framework

● Framework– Procedure execution– State store– Transaction Routing

● Extensible– Extensions are first-class– High-availability groups– “Semi-automatic” sharding

● Written in Python● Latest Release 1.5.2 GA● Open Source

– You can participate– Suggest features– Report bugs– Contribute patches

● MySQL 5.6 is focus


MySQL Fabric: Goals & Features

● Decision logic in connector– Eliminates network hop– Reduce network load– Eliminate single point of failure– Scale naturally: not a bottleneck

● Connector API extensions– Support transactions– Support full SQL

● Load balancing– Read-write split– Weighted round-robin

● Cloud integration– Support elasticity– Servers on-demand

● Multi-table sharding● Sharding functions

– Range– (Consistent) hash

● Shard operations– Shard move– Shard split

● Global updates– Global tables– Schema updates


A Brief History of MySQL Fabric

● MySQL Fabric 1.4– First public release Sept 2013– MySQL Fabric 1.4.3 GA May 2014– High-availability groups– Failure detection– Load balancing– Range and hash sharding– Shard move and split– Connector/J support– Connector/PHP support– Connector/Python support– Doctrine– Hibernate

● MySQL Fabric 1.5– MySQL Fabric 1.5.1 RC in Aug 2014– MySQL Fabric 1.5.2 GA in Sept 2014– Cloud Integration (WL#6432)– MySQL-RPC support (WL#7600)– Connector/NET support– Labs: C API


Birds-eye View

High Availability Groups

Application

XML-RPCMySQL-RPC

SQL

SQL

Connector

Connector

Connector

Operator

MySQLFabricNode

DatabaseServers


High-Level Components

● Fabric-aware Connectors– Python, PHP, Java, .NET, C– Enhanced Connector API

● MySQL Fabric Node– Manage information about farm– Provide status information– Execute procedures

● MySQL Servers– Organized in high-availability

groups– Handling application data

High AvailabilityGroup

ApplicationConnector

Connector

Connector

MySQLFabricNode


MySQL Fabric Node Architecture

MySQL

MySQL FabricFramework

Executor State Store(Persister)

Sh

?HA

MySQL-RPCAMQP XML-RPC

?Connector

Connector

Connector

Protocols

Extensions

BackingStore


High-Availability Concepts

● Redundancy– Duplicate components

● Monitoring– Detecting failing components– Monitor load

● Procedures– Activate replacements– Distribute load


MySQL Fabric and the CloudUsing OpenStack for Elasticity


Server Providers● Server Creation

– Create new server instances● Internal Memory● Disk Size● Number of CPUs

– Configured for the purpose● GTID enabled● Binary log enabled● Correct engines enabled

● Server Filling– Fill instance with data

● Clone from an existing server● Restore a backup

– Set up replication● Point secondary to primary

– Custom filling operation

Already handled

in MySQL Fabric

MySQL Fabric + Cloud


Server Providers● A “server factory”

– Abstract concept

– Extension interface added to MySQL Fabric

● Possible to support multiple providers– OpenStack Nova (in MySQL Fabric 1.5)

– OpenStack Trove

– Amazon AWS

– Amazon RDS

– Custom hardware registry


MySQL Fabric and OpenStack

MySQL Fabric NodeOpenStack Instance

Application

High-Availability Groups / Shards


Adding a new provider to MySQL Fabric● Register a provider with MySQL Fabric● Provide API address and user credentials● Provider type OPENSTACK spawns new virtual machines

mysqlfabric provider register my_stack mats xyzzy http://example.net:5000/v2.0/ my_project --provider_type=OPENSTACK


Creating a new server from MySQL Fabric● Create a new machine using the registered provider● Provide an image to use for the machine

– You have to set up the image beforehand

● Provide a cloud-init script to set up MySQL server

mysqlfabric machine create my_stack --image name=”Oracle Linux 7 amd64” --flavor name=m1.small --userdata=mysql-oracle-linux-init


Creating a snapshot from MySQL Fabric● Create a snapshot of an existing machine● Return an provider-internal name of the snapshot● Can be used to create your “golden image”

mysqlfabric snapshot create my_stack 903476b7-db4d-4d63-84e6-f79c7d0cec31


Elasticity using providers● Read load increases

– Spawn new read servers

– Add them to the group

● Shard is overloaded– Spawn new servers for a shard

– Create a new shard

– Split existing shard

● Server is lost– High-availability is reduced

– Spawn new server

– Add it to the group

Can we automate this?

Yes, we can!


MySQL Fabric executor● Event driven

– Events will trigger execution of procedures

– Procedures can trigger events themselves

– Each step of a procedure is called a job

● Procedures– Written in Python

– Interacts with servers

– Write state changes into backing store

– Lock manager for conflict resolution● Conservative two-phase locking strategy● Avoid deadlocks

Queue

BackingStore

Events


Automatically adding a server to a group

● Register procedure for event– @on_event decorator

– Accept event to register for

● Fetch the group the server belonged to

● Fetch a new server from the provider

● Add the server to the group

@on_event(SERVER_LOST)def _add_server(group_id, server_uuid): group = Group.fetch(group_id) machines = PROVIDER.create_machines( parameters ) server = MySQLServer( server_uuid, address ) MySQLServer.add(server) group.add(server) _configure_as_slave(server)


MySQL Fabric procedure execution● Before starting a job:

– Aquire the necessary locks

– Checkpoint execution state in backing store

– Start a transaction on the backing store

● When executing job:– Make updates to backing store inside transaction

– Interact with servers

● After executing a job:– Mark job completed in internal log

– Commit transaction on backing store

Queue

BackingStore

Events

What about crashes?


MySQL Fabric executor recovery● Two types of jobs:

– Idempotent: Restart the job

– Not idempotent: Execute compensation

● Recovery procedure– Start the executor

– Collect unfinished checkpoints

– Execute compensation activity for each job associated with the checkpoint

… if there is one

– Re-schedule each job

Queue

BackingStore

Events


Defining an compensation procedure

● Use the @undo decorator● Compensating action

– Search for the machine by UUID

– Give it back to provider

● Compensating actions need to be idempotent

@_add_server.undodef _undo_add_srv(group_id, server_uuid):

meta_filters = { 'mysqlfabricmachinegroupuuid': server_uuid }

machines = MANAGER.search_machines( {}, meta_filters )

for machine in machines: MANAGER.destroy_machine( machine.uuid )


Example using Rackspace Provider

mysqlfabric provider register my_rackspace mats xyzzy https://lon.identity.api.rackspacecloud.com/v2.0/ tenant accountnumber extra auth_system=rackspace provider_type OPENSTACK

mysqlfabric machine create my_rackspace image id=8c964b3f8c15496686ee7a55824df564 flavor name="512MB Standard Instance"

Note

Require support for authentication plugins

(coming)

https://lon.identity.api.rackspacecloud.com/v2.0/


Experiments with Amazon AWS

mysqlfabric provider register my_amazon AKIAIOSFODNN7EXAMPLE wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY euwest1 provider_type=AMAZON

mysqlfabric machine create my_amazon image imageid=ami892fe1fe flavor name=t2.micro

Note!Not in MySQL Fabric 1.5Will be on MySQL Labs


Experiments with OpenStack Trove

mysqlfabric provider register my_trove mats xyzzy https://lon.identity.api.rackspacecloud.com/v2.0/ tenant 123456 extra service_type=rax:database provider_type=OPENSTACK.DATABASE

mysqlfabric server create my_trove flavor name="512MB Instance" size 1 users mats:xyzzy databases employee datastore MySQL datastore_version 5.6 Note!

Not in MySQL Fabric 1.5Will be on MySQL Labs

https://lon.identity.api.rackspacecloud.com/v2.0/


Closing remarks


What do we have now?● High-availability

– Group Concept– Slave promotion

● Sharding– Range and hash sharding– Shard move and shard split

● Connector APIs– Transaction properties– “Virtual” connections

● Cloud integration– “Server providers”– OpenStack Nova

● Enhanced connectors– Connector/Python– Connector/PHP– Connector/J– Connector/NET

● Interfaces– Command-line– XML-RPC– MySQL-RPC

● Distributed failure detector– Connectors report failures– Custom failure detectors

● Credentials– RFC 2617– SSL support


Thoughts for the Future

● Connector multi-cast– UNION of result sets– More complex operations?

● Extension interfaces– Improve extension support– Improve procedures support

● Command-line interface– Focus on ease-of-use

● Cloud support– OpenStack Trove– Amazon AWS– Amazon RDS

● More protocols– MySQL-RPC Protocol?

● More frameworks?– Django?

● More connectors?– C/C++?– Fabric-unaware connectors?

● More HA group types– DRBD– MySQL Cluster– Amazon RDS?

Done!

In Labs!


Thoughts for the Future

● Automatic sharding– Single-query transactions?– Speculative execution?– Cross-shard queries?

● Multiple shard mappings– Independent tables

● Multi-way shard split– Efficient initial sharding– Better use of resources

● Highly-available executor– Node failure stop execution

● Execution restart on recovery– Replicated State Machine (RSM)

● Paxos?● Raft?

– Fail-over execution

● Session consistency– We have a distributed database– It should look like a single database– Read monotonicity– Write monotonicity


Reading for the Interested

● MySQL Forum: Fabric, Sharding, HA, Utilitieshttp://forums.mysql.com/list.php?144

● MySQL Fabric Documentationhttp://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html

● Migrating From an Unsharded to a Sharded Setuphttp://vnwrites.blogspot.com/2013/09/mysqlfabric-sharding-migration.html

● Configuring and running MySQL Fabrichttp://alfranio-distributed.blogspot.com/2014/03/mysqlfabric-installation.html

http://forums.mysql.com/list.php?144

http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html

http://vnwrites.blogspot.com/2013/09/mysqlfabric-sharding-migration.html

http://alfranio-distributed.blogspot.com/2014/03/mysqlfabric-installation.html


Want to contribute?

● Check it– … and send us use-case and feature suggestions

● Test it– … and send comments to the forum

● Break it– … and send in bugs to http://bugs.mysql.com


Mats KindahlTwitter: @mkindahlhttp://mysqlmusings.blogspot.com

Alfranio CorreiaTwitter: @alfraniohttp://alfranio-distributed.blogspot.com

Keeping in Touch

Narayanan VenkateswaranTwitter: @vn_tweetshttp://vnwrites.blogspot.com

Geert VanderkelenTwitter: @geertjanvdkhttp://geert.vanderkelen.org

http://mysqlmusings.blogspot.com/

http://alfranio-distributed.blogspot.com/

http://vnwrites.blogspot.com/

http://geert.vanderkelen.org/

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

Safe Harbor Statement

The preceding 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.

2014-10-02