MySQL Scaling for SCaLE 12x conference

100 %
0 %
Information about MySQL Scaling for SCaLE 12x conference
Technology

Published on February 20, 2014

Author: davidmstokes

Source: slideshare.net

Description

How to scale from 1 to N servers overview for Southern California Linux Expo 21 Feb 2014

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

MySQL Scaling: Drinking from the fire hose, never being down & serving billions Dave Stokes MySQL Community Manager David.Stokes@Oracle.com @Stoker Slideshare.net/davestokes/presentations 2Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Insert Picture Here

You want to get a bigger, badder MySQL -BUT what are the costs???  Budget  Complexity  Application  Opportunity  Sanity 3 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Simple Stuff Insert PicturePicture Here Insert Here 4 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

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 5 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

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'. 6 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

What database service Levels do you Need to Provide? 7 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Insert PicturePicture Here Insert Here

UpTime 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 8 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Simple Level 1 Read/Write Splitting 9 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Insert PicturePicture Here Insert Here

Use MySQL Replication to Split Read/Writes 10 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Read/Write Splitting Most Common Scaling Technique ● More complex environment ● Application Changes ● Easier with PHP & Java ● ● 11 Low opportunity and sanity costs Less risk of one server stopping operations Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Read/Write Splitting—Easy Way Use MySQL Utilities to copy master, set up failover ● mysqlserverclone ● mysqlfailover ● ● 12 When master dies, most up to date slave (GTID) promoted automatically Mysqlrplcheck & mysqlrplshow Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Simple Level 2 Moderately Easy, Better Availability 13 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Insert PicturePicture Here Insert Here

Distributed Replicated Block Device (DRBD) High Availability ● More complex environment ● Proven, supported ● ● 14 Please practice fail over and back Easy to manage but not HA panacea Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

CLOUD High Availability, Low Cost (maybe) ● Slightly more complex environment ● Proven, supported ● ● 15 Best for ephemeral data, may not make upper management comfortable (or you) Easy to manage but not HA panacea Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Complex Level N^N Never, Ever Down 16 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Insert PicturePicture Here Insert Here

MySQL Cluster For 99.999% uptime, upper bound on response time ● ● ● 17 Extremely Complex No single point of failure = lots of $$ hardware Not general purpose MySQL Copyright © 2013, Oracle and/or its affiliates. All rights reserved. ● Auto sharding ● Add/Subtract nodes at will ● ● Replicate between geographically separated locations Need a smart staff

MySQL Cluster Shards Data Automatically ● ● 18 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Single or multiple data node failures do not bring down a cluster Easy to add/remove data nodes

MySQL Cluster Topology ● Nodes ● ● ● ● 19 Data ● ● MGMT – startup/add node Mysqld Can be engineered for no single point of failure Can replicate across data centers Expensive on all fronts Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Big Data Insert PicturePicture Here Insert Here 20 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Ask ten DBAs to define 'Big Data' and You Will Get At Least Eleven Answers .BIG as massive amounts ● Define MASSIVE BIG as lots of eyes ● 21 How many simultaneous users Copyright © 2013, Oracle and/or its affiliates. All rights reserved. BIG as in fast ● Define fast BIG as all of the other three!!!

The Easiest Way to Get Speed ● NoSQL/Memcached to InnoDB/NBD ● ● 22 9x faster by skipping parser, optimizer Key/Value pair access and SQL access of same data Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Easiest Way to Gain Size ● Columnar Storage Engines for MySQL ● Not InnoDB ● Unique features, quirks ● ● 23 Need > 26,000,000,000 rows to start seeing 'sweet spot' Use new server, don't scrimp Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Calpont InfiniDB ● ● ● ● ● 24 Column-oriented MySQL front end Multi threaded High speed data loading Automatic partitioning Enterprise Features ● Partition Drop ● UDF’s ● Physical compression ● MPP, multi-node scale-out ● Distributed, Shared Nothing data cache ● Auto Increment ● Centralized database and operating system monitoring ● Notifications and Alerts ● INSERT INTO...SELECT FROM... ● Views on InfiniDB Tables Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Infobright ● 25 Similar Features to Calpont but different implementation Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

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 ● ● 26 Kinda 'SQL-ish' Squoop and MySQL Hadoop Applier to move data Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Hadoop Applier ● 27 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Infobright

Partitioning and Sharding / Dividing and Conquering ● Partitions built-in to MySQL ● Nice management tool – ● 28 Fast drops Sharding ● Rolling quarters/months – ● No change to application but higher complexity, low opportunity cost Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Much more complex application and operations ● Not off shelf ● Hard to re-engineer ● Works

Hard Part – Thinking Insert PicturePicture Here Insert Here 29 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Goals – What are you trying to accomplish ● ● Define problem completely Who produces, who consumes data ● What does end solution look/feel like? ● ● 30 Negotiate performance rates Plan for failure, catastrophe Design for long life Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Platforms ● Speed ● Access areas ● ● ● Security ● Serenity

Where Else to Seek Info ● MySQL Connect Conference Sept. in San Francisco ● Hadoop ● Same time as OOW Weekdays! ● 31 Planet.mysql.com and forums.mysql.com Copyright © 2013, Oracle and/or its affiliates. All rights reserved. ● Horton Works and Cloudera both have wonderful tutorials Apache Bigtop project

MySQL Training ● MySQL Marinate ● Online Virtual Class run by Boston MySQL Users Group ● ● 32 1 chapter/week Free Copyright © 2013, Oracle and/or its affiliates. All rights reserved. ● Oracle and other vendors ● Dozens of book ● Online

David.Stokes@Oracle.com @Stoker slideshare.net/davestokes 33 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Insert PicturePicture Here Insert Here

Graphic Section Divider 34 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

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

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

Scaling Web Databases: Auto-Sharding with MySQL Cluster ...

... their databases can scale-out across regions. MySQL Cluster offers ... MySQL Cluster performance scaling-out on ... conference; conferences;
Read more

Mysql | SCALE 14x

SCALE 12x; SCALE 13x; CFP; Schedule; Register; Information . Venue; Hotel Information; Parking; Transportation; Presentations; Speakers; ... Scaling MySQL ...
Read more

MySQL :: Allan Packer on performance tuning: Scaling MySQL

Allan Packer on performance tuning: Scaling MySQL. ... Our goal is to get MySQL to scale to 64 ... Performance and Scalability at our upcoming MySQL ...
Read more

Scaling MySQL and MariaDB | SCALE 14x

Previous SCALEs . SCALE 11x; SCALE 12x; SCALE ... Scaling MySQL and ... There are a multitude of ways you can scale your database server beyond just one ...
Read more

MySQL :: MySQL Newsletter - Special Edition: Scaling with ...

MySQL Newsletter - Special Edition: Scaling ... better scale your MySQL ... 5.5 Release Candidate at MySQL Sunday, the first MySQL conference at ...
Read more

Scaling MySQL in AWS - Percona – The Database ...

Scaling MySQL in AWS Presented by: Laine Campbell April 3rd, 2014. ... Native replication allows for scale out of reads, just as in EC2 or your own datacenter
Read more

Scaling MySQL Databases for the Web | Percona Live: MySQL ...

Scaling MySQL Databases for the Web: ... and conventions that grew out of managing and scaling MySQL at ... most of the needs of large scale web ...
Read more

SCALE

SCALE, Pasadena, California ... The premier community open-source software conference! Facebook logo. Email or Phone: ... Scaling up for SCALE 10X November ...
Read more

MySQL :: MySQL Kunden: YouTube

YouTube scales MySQL with Go code; ... Scaling MySQL at YouTube; Videos. Seattle Conference on Scalability: YouTube Scalability
Read more

MySQL :: MySQL Customer: YouTube

YouTube scales MySQL with Go ... Scaling MySQL at YouTube; Video. Seattle Conference on Scalability: YouTube Scalability "Scaling MySQL at YouTube" with ...
Read more