cakefest 2014 -- mysql scaling without losing your sanity

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

Insert Picture Here

MySQL Scaling§ Dave Stokes

MySQL Community Manager

[email protected]

@stoker

slideshare.net/davestokes

23rd August 2014

mailto:[email protected]


Safe Harbor Agreement

.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 decision.

The development, release, and timing of any features or functionality

described for Oracle’s products remains at the sole discretion of Oracle.


Insert Picture Here

MySQL Releases since 2010

§ Enterprise Monitor 2.2, 2.3

§ Cluster 7.1, 7.2, 7.3

§ Cluster Manager 1, 1.1, 1.3

§ Workbench 5.2, 6, 6.1

§ Database 5.5, 5.6, DMR 5.7

§ Backup 3.5, 3.7, 3.1

§ Oracle VM for MySQL

§ Oracle Product Certifications

§ Windows Installer

§ Utilities 1.0.6

§ Audit

§ Fabric

● Under promise/Over Deliver

● More Developers

● More QA

● More Support

● Making $$

● Classes popular

– Instructor lead

– Self-paced

● We're hiring!


You want to get a bigger, badder MySQL --BUT what are the costs???

Budget

Complexity

Application

Opportunity

Sanity.


Simple Stuff


Option 0

Run the Latest, Greatest MySQL

MySQL 5.5 was 20% faster than 5.1! 5.6 is another 10-15%

--Make sure your config file matches your hardware

--ANALYSE TABLE

--Check for SLOW QUERIES, QUERIES w/o INDEXES

--TUNE QUERIES

This is the least complex, less upsetting, and usually lowest budget cost option with NO changes to your application!


Option 0.1

Add in a Memcached Layer

● Cache for quick access

● Session Data

● Sports Scores

● Opens up NoSQL access to InnoDB later

This is the second least complex, less upsetting, and usually lowest budget cost

option but does make you change your application to become 'cache aware'.


Up Time versus Response Time versus Access

Things to be engineered/negotiated upfront

Up Time

– How much down time can you afford?

Response Time

– How long is too long to wait?

Access

– How many do you need to serve simultaneously.


Insert Picture Here

Simple Level 1 -Splitting Reads


Use MySQL Replication to Split Read/Writes


READ Splitting

Most Common Scaling Technique

● More complex environment

● Application Changes

● Easier with PHP & Java

● Low opportunity and sanity costs

● Less risk of one server stopping operations


Read Splitting—Easy Way

Use MySQL Utilities to copy master, set up failover

● mysqlserverclone

● mysqlfailover

– When master dies, most up to date slave (GTID) promoted automatically

● Mysqlrplcheck & mysqlrplshow


Insert Picture Here

Simple Level 2Moderately Easy, BetterAvailability


Distributed ReplicatedBlock Device (DRBD)

● High Availability

More complex environment

● Proven, supported

● Please practice fail over

and back

● Easy to manage but

not HA panacea


Cloud

High Availability, low cost (sometimes)

Slightly more complex environment

● Proven, supported

● Best for ephemeral data, may not make upper management

comfortable (or you)

● Easy to manage but not a HA panacea


Insert Picture Here

Complex Level N^NNever, Ever Down


MySQL ClusterFor 99.999% up time, upper bound on response time

Extremely Complex

● No single point of

failure = lots of $$

hardware

● Not general

purpose MySQL

Auto sharding

● Add/Subtract nodes at will

● Replicate between

geographically separated

locations

● Need a smart staff


MySQL Cluster Shards Data Automatically

● Single or multiple data node

failures do not bring down a

cluster

● Easy to add/remove data

nodes


MySQL Cluster Topology

● Nodes

– MGMT – startup/add node

– Data

– Mysqld

● Can be engineered for no

single point of failure

● Can replicate across data centers


Insert Picture Here

BigData


Ask ten DBAs to define 'Big Data' and You WillGet At Least Eleven Answers

● BIG as massive amounts

– Define MASSIVE

● BIG as in fast

● Define fast

● BIG as lots of eyes

● BIG as all of the the above!!!

● How many simultaneous users


The Easiest Way to Get Speed

NoSQL/Memcached to InnoDB/NBD

● 9x faster by skipping parser, optimizer

● Key/Value pair access

and SQL access of same data


Easiest Way to Gain Size

Columnar Storage Engines for MySQL

● Not InnoDB

● Unique features, quirks

● Need > 26,000,000,000 rows to start seeing 'sweet spot'

● Use new server, don't scrimp

See Calpont and Inforbright


Hadoop-- getting data from MySQL to Hadoop

80% of Hadoop Installations feed from MySQL

● Hadoop is made up of several components

● Twisty little maze, all alike

● Use Apache Bigtop

– Tested for interoperability

● Cloudera & Horton Works have great tutorials

● Kinda 'SQL-ish'

● Squoop and MySQL Hadoop Applier to move data


Partitioning and Sharding /Dividing and Conquering

Partitions built-in to MySQL

● Nice management tool

– Rolling quarters/months

– Fast drops

● No change to application but

higher complexity, low

opportunity cost

Sharding

● Much more complex

application and operations

● Not off shelf

● Hard to re-engineer

● Works


MySQL Fabric

● Lets you shard and reshard

● Very new

● Looking for feedback

● Released in May


Insert Picture Here

Wrap up


Goals – What are you trying toaccomplishDefine problem completely

● Who produces, who consumes

data

● Negotiate performance rates

● Plan for failure, catastrophe

● Design for long life

What does end solution look/feel like?

● Platforms

● Speed

● Access areas

● Security

● Serenity


MySQL Marinate

● Free virtual class to learn MySQL run by the Boston MySQL Users Group

– 1 chapter a week

– Homework graded helpfully


Q&A

[email protected] @stoker

slideshare.net/davestokes

mailto:[email protected]

cakefest 2014 -- mysql scaling without losing your sanity

Technology

oracle andor

oracle vm

greatest mysql mysql

sole discretion of oracle

mysql cluster shards

mysql releases

badder mysql

insert picture