Solving Large-Scale Database
Administration with Tungsten
Neil Armitage, Sr. Software Engineer Robert Hodges, CEO
Introducing Continuent
•
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 movementQuick Continuent Facts
•
Largest Tungsten clustering installationprocesses about 500M transactions each day
•
Tungsten Replicator was application of the year at the 2011 MySQL User Conference•
Continuent is well known in the MySQLcommunity for its clustering and replication
MySQL is a Great OLTP Database
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?Introducing Tungsten Clustering
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 migrationapache /php
GonzoPortal.com
Doesn’t Replication Do This Already?
Application Libmysqlclient.a Application Libmysqlclient.a Replicator Db2 Replicator Db3 Db1 ReplicatorReplication 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?From Replicating to Clustering
Application Tungsten Connector Application Tungsten Connector Replicator Db2 Replicator Db3 Db1 ReplicatorSlave
Master
Slave
Manager Manager Manager
Monitor
ing and Contr
ol
Monitor
ing and Contr
ol
Basic Cluster Operations
•
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 slaveAutomatic Failover
Application Tungsten Connector
Automatic Failover
Application Tungsten Connector 1. Detect non-responsive masterAutomatic Failover
Application Tungsten Connector
3. Select most advanced slave and wait for pending transactions to apply
Automatic Failover
Application Tungsten Connector
4. Promote to master, reconfigure slave(s), and re-enable connections
Automatic Failover
Application Tungsten Connector
Rolling SQL Schema Upgrade
Final Upgrade Switch
Slave 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
Backup, Recovery, and Restore
•
Backups/cluster > datasource DB1 backup
•
Recover/cluster > datasource DB2 recover
•
Restore/cluster > datasource DB2 restore
•
Out-of-box support for mysqldump/Tungsten Connector Basics
•
Any MySQL/PostgreSQL client can connect•
Initiates connections using JDBCApp
Native protocol COM_QUERY COM_INIT_DB COM_DROP_DB ConnectorTungsten Connector Basics
•
Pass-through operation after connection•
Full transparency & low overhead for clientsApp
Protocol packet COM_QUERY select * from t Connector Protocol packetSimple 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 availableSmartScale Session Load Balancing
App
Connect/Write Connector Transaction History Log Slave replay position Session x Session y Master DBMSSmartScale Session Load Balancing
App
Read Connector Transaction History Log Slave replay position Session x Session y Slave DBMS Master DBMS Slave DBMSSmartScale Session Load Balancing
App
Read Connector Transaction History Log Slave replay position Session x Master DBMSTungsten DBMS Array Connectivity
App
Read/Write Connector cluster3 login cluster1 login cluster2 loginDeployment on App Server Hosts
App Server
+ Connector + ConnectorApp Server App Server
+ Connector + ConnectorApp Server App Server
Deployment on Dedicated Hosts
App Server App Server
App Server App Server App Server
Deployment on Dedicated Hosts
App Server App Server
App Server App Server App Server
Disaster Recovery Sites
•
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 failuresTungsten Composite Data Services
App Server
+ Connector + ConnectorApp Server
Master Slave
San Jose New York
Switching and failover
•
Planned switch/sj_nyc > switch to nyc
•
Failover/cluster > failover
Multi-Master Replication
•
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 requiredSimple Multi-Master Con$guration
NYC Replicator Frankfurt
fra (master) nyc (slave) Replicator
fra (slave) nyc (master)
Complex Multi-Master Topologies
Star
Replication
All
Masters
Fan-In Replication
•
Multiple masters feed into a single slave•
Use case: Aggregate data for reporting•
Consolidate data from multiple servers into one server•
Moving shardsProblem: Real-Time Order Ful$llment
Real-Time Replication
MySQL
Web-Facing Sales Purchase Ordering
Oracle
PHP/ Perl Java/ JBoss 3M Order Items 1-2M transactions dailyHow Does Tungsten Do That?
MySQL Tungsten Master
Replicator
Oracle
Service oracle
MySQLExtractor Special 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 columns
MySQL Binlog
Batch Loading to Data Warehouses
Csv File CsvFile Csv File. . .
London Replicator Service batch Vertica Data Warehouse Transaction History Log MySQL Master THLPossibilities for Analytics...
OLTP DataMySQL Master
Complex, near real-time reporting Light-weight, real-time operational status Web-facing mini-data marts forHow Do I Obtain Tungsten?
•
Register at Continuent to get downloads for Oracle replication:http://www.continuent.com/downloads/software
