advertisement

Database2011 MySQL Sharding

60 %
40 %
advertisement
Information about Database2011 MySQL Sharding
Technology

Published on January 18, 2011

Author: RockeTier

Source: slideshare.net

Description

How MySQL become an Internet standard? based on a presentation I gave at the Database2011 conference: Who uses MySQL? (everyone inc. Facebook, Twitter and Google), What are MySQL limitations? (multi threading, INSERTS, SELECTS), How the big boys solved these issues? (Sharding, Partitioning), What are the pros and cons for each Sharding strategy? (Vertical, Horizonal: directory based, static hashing, signature based), How to implement Sharding from an applicative view? How to take care of reporting? What are the emerging solutions in this field? (Gizzard, ScaleBase, Xeround and Akibian)
advertisement

Moshe Kaplan Database2011, Jan 13 [email_address] http://blogs.microsoft.co.il/blogs/vprnd http://top-performance.blogspot.com

Presentation Objectives Who is using MySQL? MySQL Limitations How to get over this? Sharding Case Studies Products

Who is using MySQL?

MySQL Limitations

How to get over this?

Sharding Case Studies

Products

Who is Using MySQL?

MySQL Limitations 50-100M Records/Table 50 SELECT Statements/Second

50-100M Records/Table

50 SELECT Statements/Second

700 Inserts/Sec In Memory Engine 3000 Inserts/Sec InnoDB Engine 700 Inserts/Sec Amazon AWS Standard Large Instance

Why Do I Care? From 0 to 100 (US mass adaptation) Phone: 100 yrs Radio: 40 yrs TV: 30 yrs Mobile: 20 yrs Internet: 10 yrs Facebook: 2 yrs

From 0 to 100 (US mass adaptation)

Phone: 100 yrs

Radio: 40 yrs

TV: 30 yrs

Mobile: 20 yrs

Internet: 10 yrs

Facebook: 2 yrs

100K New Users/Week

The Network Effect

What Should I Do? Oracle SQL Server $$$

Oracle

SQL Server

$$$

Sharding

Vertical Sharding

Horizontal Sharding Static Hashing Complex growth Simple Mod 10 = 0 Mod 10 = 1 Mod 10 = 2 Mod 10 = 3 Mod 10 = 4 Mod 10 = 5 Mod 10 = 6 Mod 10 = 7 Mod 10 = 8 Mod 10 = 9

Static Hashing

Complex growth

Simple

Key locations are defined in a directory Simple growth Directory is SPOF Horizontal Sharding

Key locations are defined in a directory

Simple growth

Directory is SPOF

Horizontal Sharding Static Hashing with Directory Mapping Simple Growth Small Directory still SPOF Mod 1000 = 4

Static Hashing with Directory Mapping

Simple Growth

Small Directory still SPOF

Horizontal Sharding Each key signed by DB# generated on creation Simple growth New key generation is SPOF

Each key signed by DB# generated on creation

Simple growth

New key generation is SPOF

Limitations: Reporting

Best Practices $connection = new_db_connection("customer://1234"); $statement = $connection->prepare( $sql_statement, $params ); $result = $statement->execute();

$connection = new_db_connection("customer://1234");

$statement = $connection->prepare( $sql_statement, $params );

$result = $statement->execute();

Lessons Vertical Sharding: User Actions, Users, Comments, Items Horizontal Sharding Denormalization MySQL Replication

Vertical Sharding:

User Actions, Users, Comments, Items

Horizontal Sharding

Denormalization

MySQL Replication

‘ s Path to Sharding Single Server Single Master with Multiple Read Slaves Partitioned Sharding

Single Server

Single Master with Multiple Read Slaves

Partitioned

Sharding

‘ s Path Master-Master replication Each Shard is 50% loaded 40K queries/second

Master-Master replication

Each Shard is 50% loaded

40K queries/second

Ad Network Reference Architecture

Sharding Management http://dev.mysql.com/tech-resources/articles/mysql_5.0_psea1.html Storage Engine MySQL Proxy

Grizzard http://readwriteweb.com.br/search/scala-framework/

ScaleBase

The Bottom Line: Grow ∞

Startup your Engines Thank you Moshe Kaplan: mokplan@gmail.com http://top-performance.blogspot.com

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

MySQL :: MySQL Fabric: Blogs, Presentations

MySQL Fabric: Sharding http://vnwrites.blogspot.com/2013/09/mysqlfabric-sharding ... http://www.slideshare.net/RockeTier/database2011-my-sql-sharding
Read more

MySQL Sharding - Scale Hacking: Cloud Computing, Software ...

MySQL Sharding A few weeks ago we had a presentation in the Israeli MySQL User Group, ... Database2011 (2) datawarehouse (1)
Read more

SQL Server Partitioning: The bad, the good and the evil

SQL Server Partitioning: The bad, the good and the ... Horizontal Sharding is used to separate rows between several tables based ... MySQL Sharding;
Read more

Oracle NoSQL Database - Oracle | Integrated Cloud ...

Oracle NoSQL Database .3 Technical Overview Oracle NoSQL Database leverages the Oracle Berkeley DB Java Edition High Availability storage engine to provide ...
Read more

PostgreSQL at the centre of your dataverse

PostgreSQL at the centre of your dataverse! ... Data sharding ! ... database © 2011 EnterpriseDB.
Read more

Speaker Slides & Videos: O'Reilly MySQL Conference & Expo ...

Speaker Slides & Video for O'Reilly MySQL Conference and Expo happening April 11-14 2011, Santa Clara, CA
Read more