solving large-scale-dba-admin-with-tungsten-2012-09-30

©Continuent 2012.

Solving Large-Scale Database Administration with Tungsten

Neil Armitage, Sr. Software EngineerRobert Hodges, CEO

Sunday, 5 May 13

©Continuent 2012

Introducing Continuent

2

• The leading provider of clustering and replication for open source DBMS

• Our Product: Continuent Tungsten

• Clustering - Commercial-grade HA, performance scaling and data management for MySQL

• Replication - Flexible, high-performance data movement

Sunday, 5 May 13

©Continuent 2012

Quick Continuent Facts

• Largest Tungsten clustering installation processes about 500M transactions each day

• Tungsten Replicator was application of the year at the 2011 MySQL User Conference

• Continuent is well known in the MySQL community for its clustering and replication

• Over 90 customers currently deployed

3

Sunday, 5 May 13

©Continuent 2012

Select Continuent Customers

4

Sunday, 5 May 13

©Continuent 2012 5

Why Tungsten?

Sunday, 5 May 13

©Continuent 2012

MySQL is a Great OLTP Database

6

But how do I...

• Handle DBMS failures?

• Perform maintenance and upgrades without taking applications o!ine?

• Load balance SQL across replicas?

• Create a disaster recovery site?

• Set up multi-master replication?

• Integrate with another DBMS?

Sunday, 5 May 13

©Continuent 2012 7

Tungsten Master/Slave Clustering

Sunday, 5 May 13

©Continuent 2012

Introducing Tungsten Clustering

8

Tungsten clusters combine o"-the-shelf open source DBMS servers into data services with:

• 24x7 data access • Scaling of load on replicas• Simple management commands

...without app changes or data migration

AmazonUS West

apache/php

GonzoPortal.com

Connector Connector

Sunday, 5 May 13

©Continuent 2012

Doesn’t Replication Do This Already?

9

Application

Libmysqlclient.a

Application

Libmysqlclient.a

Replicator

Db2

Replicator

Db3Db1

Replicator

Slave SlaveMaster

Sunday, 5 May 13

©Continuent 2012

Replication Limitations

Replication alone cannot enable constant access to data...

• What happens if the master fails?

• What happens if a slave is lagging too far behind to be usable?

• How do I maintain the DBMS without stopping apps?

10

Sunday, 5 May 13

©Continuent 2012

From Replicating to Clustering

11

Application

Tungsten Connector

Application

Tungsten Connector

Replicator

Db2

Replicator

Db3Db1

Replicator

Slave SlaveMaster

Manager Manager Manager

Monitoring and Control

Monitoring and Control

Data Service: sjc

Sunday, 5 May 13

©Continuent 2012

Basic Cluster Operations

12

• Switch -- Promote a slave node to master

• Shun -- Kick node out of cluster

• Welcome -- Return node to cluster

• Backup -- Run a backup

• Restore -- [Re-]provision a node

• Recover -- Return a broken node to slave

• Heartbeat -- Check cluster liveness

Sunday, 5 May 13

©Continuent 2012

Automatic Failover

13

Application

Tungsten Connector

Sunday, 5 May 13

©Continuent 2012

Automatic Failover

14

Application

Tungsten Connector

1. Detect non-responsivemaster

2. Halt master connections

Sunday, 5 May 13

©Continuent 2012

Automatic Failover

15

Application

Tungsten Connector

3. Select most advanced slave and wait for pending transactions to apply

Sunday, 5 May 13

©Continuent 2012

Automatic Failover

16

Application

Tungsten Connector

4. Promote to master, reconfigure slave(s), and re-enable connections

Sunday, 5 May 13

©Continuent 2012

Automatic Failover

17

Application

Tungsten Connector

5. Recover failed master

Sunday, 5 May 13

©Continuent 2012

Rolling SQL Schema Upgrade

18

Final UpgradeSwitchSlave Upgrade Slave Upgrade

• Shun slave 1, run upgrade, and return to cluster. Discard and recover on failure

• Upgrade old master. Cluster schema is now fully upgraded

• Repeat for additional slave(s)

• Switch master to promote an upgraded slave

Sunday, 5 May 13

©Continuent 2012

Backup, Recovery, and Restore

19

• Backups

/cluster > datasource DB1 backup

• Recover

/cluster > datasource DB2 recover

• Restore

/cluster > datasource DB2 restore

• Out-of-box support for mysqldump/XtraBackup; simple integration for others

Sunday, 5 May 13

©Continuent 2012 20

Understanding Tungsten Connectivity

Sunday, 5 May 13

©Continuent 2012

Tungsten Connector Basics

• Any MySQL/PostgreSQL client can connect

• Initiates connections using JDBC

21

App NativeprotocolCOM_QUERY

COM_INIT_DBCOM_DROP_DB

Connector

Sunday, 5 May 13

©Continuent 2012

Tungsten Connector Basics

• Pass-through operation after connection

• Full transparency & low overhead for clients

22

App

Protocol packetCOM_QUERY

select * from t

ConnectorProtocol packet

OKResultSet Rows:1

Sunday, 5 May 13

©Continuent 2012

Simple Read/Write Splitting

• Very fast, easy-to-understand scaling model

• Transactions go to master

• Auto-commit SELECTs go to slaves, if available, but...

• Reads potentially outdated data

• Ephemeral SQL objects won’t be available

• May miss: select update_function()

23

Sunday, 5 May 13

©Continuent 2012

SmartScale Session Load Balancing

24

App

Connect/Write

Connector

TransactionHistory Log

Slavereplay

position

Session xSession y

Slave DBMS

MasterDBMS

Slave DBMS

Sunday, 5 May 13

©Continuent 2012


25

AppRead

Connector


Slavereplay

position

Session xSession y

Slave DBMS

MasterDBMS

Slave DBMS

Sunday, 5 May 13

©Continuent 2012


26

AppRead

Connector


Slavereplay

position

Session x

Slave DBMS

MasterDBMS

Slave DBMS

Sunday, 5 May 13

©Continuent 2012

Tungsten DBMS Array Connectivity

27

AppRead/Write

Connector

cluster3 login

cluster1 login

cluster2 login

Sunday, 5 May 13

©Continuent 2012

Deployment on App Server Hosts

28

App Server+ Connector





Sunday, 5 May 13

©Continuent 2012

Deployment on Dedicated Hosts

29

App Server App ServerApp Server App ServerApp Server

Hardware Load Balancer

Sunday, 5 May 13

©Continuent 2012

Deployment on Dedicated Hosts

30

App Server App ServerApp Server App ServerApp Server

Software Load Balancer

Sunday, 5 May 13


Managing Data Across Multiple Sites

Sunday, 5 May 13

©Continuent 2012

Topics:

• Tungsten Replicator basics

• Disaster recovery sites

• Multi-master topology

• Fan-in

32

Sunday, 5 May 13

©Continuent 2012

Tungsten Replicator Overview

33

Master

(Transactions + Metadata)

Slave

THL

DBMSLogs

Replicator

(Transactions + Metadata)

THLReplicator

Download transactions via network

Apply using JDBC

Sunday, 5 May 13

©Continuent 2012

Tungsten Replication Service

34

Extract Filter Apply

StageExtract Filter Apply


Stage

Pipeline

MasterDBMS


In-MemoryQueue

SlaveDBMS

Sunday, 5 May 13

©Continuent 2012

Parallel Replication of Sharded Data

35

Extract Filter Apply


StageStage

Pipeline

MasterDBMS


In-MemoryQueue

SlaveDBMS

Extract Filter ApplyExtract Filter ApplyExtract Filter Apply

Sunday, 5 May 13

©Continuent 2012

Multiple Services Per Replicator

36

NYCReplicator

London

Service nyc

FrankfurtReplicator

Service fra

Replicator

Service nyc

Service fra

Sunday, 5 May 13

©Continuent 2012

Disaster Recovery Sites

37

• Updates go to primary site master

• DR site receives updates in real time

• Simple command promotion

• Tungsten connectors can route to primary and DR sites

• No application changes required

• Simplest way to protect against region/site failures

Sunday, 5 May 13

©Continuent 2012

Switching and failover

39

• Planned switch

/sj_nyc > switch to nyc

• Failover

/cluster > failover

• Failover between sites is not automated!

Sunday, 5 May 13

©Continuent 2012

Multi-Master Replication

40

• Updates on 2+ sites (active-active mode)

• Enables geographic distribution of data

• No failover necessary if network fails or site becomes unavailable

• Not all applications can handle multi-master

• Applications must avoid con!icts

• Careful testing required

• Restoration of broken systems may not be easy

Sunday, 5 May 13

©Continuent 2012

Simple Multi-Master Con!guration

41

NYC FrankfurtReplicator

fra (master)

nyc (slave)

Replicator

fra (slave)

nyc (master)

Sunday, 5 May 13

©Continuent 2012

Fan-In Replication

43

• Multiple masters feed into a single slave

• Use case: Aggregate data for reporting

• Consolidate data from multiple servers into one server

• Moving shards

• Applications must avoid con#icts

Sunday, 5 May 13


Heterogeneous Replication

Sunday, 5 May 13

©Continuent 2012

Problem: Real-Time Order Ful!llment

46

Real-Time Replication

MySQL

Web-Facing Sales Purchase Ordering

Oracle

PHP/Perl

Java/JBoss

3M Order Items

1-2M transactions daily

Sunday, 5 May 13

©Continuent 2012

How Does Tungsten Do That?

47

MySQL Tungsten Master Replicator

Oracle

Service oracle

MySQLExtractorSpecial Filters* Transform enum to string

binlog_format=row

Tungsten Slave Replicator

Service oracle

Special Filters* Ignore extra tables* Map names to upper case* Optimize updates to remove unchanged columnsMySQL

Binlog

Sunday, 5 May 13

©Continuent 2012

Batch Loading to Data Warehouses

48

CsvFile

CsvFile

CsvFile. . .

London

Replicator

Service batch

VerticaData

Warehouse


MySQLMaster

THL

Sunday, 5 May 13

©Continuent 2012

Possibilities for Analytics...

49

OLTP Data

MySQL MasterComplex, near real-time reporting

Light-weight, real-time operational status

Web-facing mini-data marts for SaaS users

Sunday, 5 May 13

©Continuent 2012

How Much Does Tungsten Cost?

51

• Continuent Tungsten Annual Subscription

• $7,000 per DBMS, $3500 for hot stand-by (DR)

• 24x7, worldwide support

• Product upgrades and fast patches

• No per-core pricing

• No extra Oracle licenses required

Volume, site, and multi-year discounts apply

Sunday, 5 May 13

©Continuent 2012

Consulting and Implementation Services

52

• Delivered by clustering / replication experts

• Wide range of consulting services

• Software deployment/upgrade

• Special projects

• Feature development from small to very large

• Standard daily rate: $2K

Discounts for large(r) engagements

Sunday, 5 May 13

©Continuent 2012

How Do I Obtain Tungsten?

53

• Register at Continuent to get downloads for Oracle replication: http://www.continuent.com/downloads/software

• Documentation is available on Continuent website

• MySQL replication is available from Tungsten Replicator project on code.google.comhttp://code.google.com/p/tungsten-replicator/

Sunday, 5 May 13

http://www.continuent.com/downloads/software

http://www.continuent.com/downloads/software

http://code.google.com/p/tungsten-replicator/

http://code.google.com/p/tungsten-replicator/

©Continuent 2012.

Continuent Web Page:http://www.continuent.com

Tungsten Replicator 2.0:http://code.google.com/p/tungsten-replicator

Our Blogs:http://scale-out-blog.blogspot.comhttp://datacharmer.org/bloghttp://www.continuent.com/news/blogs

560 S. Winchester Blvd., Suite 500 San Jose, CA 95128 Tel +1 (866) 998-3642 Fax +1 (408) 668-1009e-mail: [email protected]

Sunday, 5 May 13

http://www.continuent.com

http://www.continuent.com

http://code.google.com/p/tungsten-replicator

http://code.google.com/p/tungsten-replicator

http://scale-out-blog.blogspot.com

http://scale-out-blog.blogspot.com

http://datacharmer.blogspot.com

http://datacharmer.blogspot.com

http://continuent-tungsten.blogspot.com

http://continuent-tungsten.blogspot.com

mailto:[email protected]

mailto:[email protected]

solving large-scale-dba-admin-with-tungsten-2012-09-30

Documents

continuent customers4sunday

slave node

data management

multimaster replication

mysql replication flexible

data services

doesnt replication

node recover