mysql: from single instance to big data

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

From Single Instance to Big DataPopular LAMP Architectures

Morgan Tocker MySQL Community Manager August, 2014


Safe Harbor Statement

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 decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Oracle Confidential – Internal/Restricted/Highly Restricted3

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Welcome.. and thank you for coming!

• Today talk is: • Experience-‐Level: Beginner

• We will be discussing topologies and common MySQL setups to respond to various needs

• There are no stupid questions • Please raise your hand and interrupt!

4

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted5

Program Agenda

Common Architectures

Sharding and Functional Partitioning

Caching

The Cloud

Hadoop

1

2

3

4

5


In the beginning…• Single server for all usage • MySQL and Web Server run side-‐by-‐side

6

www + mysql


Web and Database Tiers• Web Server Tier speaking to Database Server via local network

• Network latency not normally the issue

• Retrofits well

7

www

MySQL

www www


Network LatencyHow important is it to merge queries together?

• Simple queries to MySQL: • 0.1ms

• Typical network latency: • 0.1ms to 1ms

• It can matter in some poorly designed applications that repeat similar queries within a loop (often called N+1).

8

http://www.tocker.ca/2013/11/18/how-‐important-‐is-‐it-‐to-‐merge-‐queries-‐together.html

http://www.tocker.ca/2013/11/18/how-important-is-it-to-merge-queries-together.html


Web and Database (Alternative)• Common practice for shared hosting environments

• Provides increased “noisy neighbour” protection.

• Less common otherwise.

9

www + mysql

www + mysql

www + mysql


High Availability Options

Built-‐in MySQL Replication

Asynchronous and Semi-‐Synchronous

Good support for remote replicas.

SAN/Shared Storage

Requires use of ACID Storage Engine.

Use with cluster tools to failover.

DRBD

Linux kernel module to provide network-‐level RAID.

Usage is similar to SAN/Shared Storage

MySQL Cluster

Storage Engine with built in Replication.

Suitability situationally dependent.

10

MySQLMySQLMySQL MySQLMySQLSAN

MySQLMySQLDRBD MySQLMySQLNDB


How HA Failover WorksIn combination with a management framework

• A Virtual IP Address pointing to active master • A proxy address (routing to active master) • A modified MySQL connector

11

^typically


MySQL

Oracle Confidential – Internal/Restricted/Highly Restricted

Most Common HA Starting Point• Primary MySQL Server • Passive slaves using MySQL Replication

• Replication is logical • Very useful to support version upgrades (and some other changes)

12

www (multiple)

MySQL


Circular ring

13

• Not a common topology • No conflict resolution or shared locking infrastructure. • Bad things can happen.

MySQL

MySQL

MySQL

MySQL


Master-‐master

14

• Works best with writes being directed to only one master.

• Similar to master/slave but failover is pre-‐configured.

MySQLMySQL


Making Use of the Slave• Reporting queries are an immediate candidate for slaves

• Ensures good QoS for primary MySQL Server

15

www (multiple)

MySQLMySQLMySQL


Read-‐Write Split• Application-‐awareness to send writes to master and reads to slaves

• Historically very common with MySQL

16

www (multiple)

MySQLMySQLMySQL


Read-‐Write SplitLimitations

• Read-‐after-‐write can return stale data • All slaves must accept all writes • Relies on read volume being much higher than write volume (true for many applications)

17


Multi-‐Source Fan-‐In

18

• MySQL 5.7 announced feature

• Currently a slave can only have one master

MySQL MySQL

MySQL

MySQL


1

Oracle Confidential – Internal/Restricted/Highly Restricted19

Program Agenda



Caching

The Cloud

Hadoop

2

3

4

5


Write Scaling

• Replication Provides Read-‐Scaling • By default all nodes accept all writes • Filtering by table or database possible, but reduces failover ability

20


Functional Partitioning

• Find a set of unrelated tables and move them to their own MySQL Server

• The application must manage which MySQL server to connect to

21


Sharding

• Split an Individual table across multiple Servers • Sharding is performed based on a key

22


Shard Key

• i.e. user_id, country, network_id • Ideally the key follows the access pattern of the data. • Bad examples:

• First letter of username • Good Examples:

• Network, Country etc.

23


Shard Balancing

• Harder than it sounds. • In many applications one user can consume far more resources than the average.

24


Cross shard queries?

• Cross Shard queries usually* pushed down to the application to perform

• Ideally the shard key follows the access pattern of the data so this is not required.

25

* Third party middleware does exist.


MySQL FabricMySQL Farm Management

• We now how an official tool to support sharding! • MySQL Fabric released May 2014

26


MySQL FabricMySQL Farm Management

27

http://dev.mysql.com/tech-‐resources/articles/mysql-‐fabric-‐ga.html

http://dev.mysql.com/tech-resources/articles/mysql-fabric-ga.html


Program Agenda



Caching

The Cloud

Hadoop

2

3

4

5

1

3


CachingWith memcached or a similar technology

• Common to include a look aside cache in combination with MySQL:

29

value = get_from_memcache(key); if (value === null) { value = get_value_from_database(key); set_memcache(key, value) }


Caching Benefits

• Increases capacity by eliminating high expense items • Sits on top of another application very easily

30


Caching Downsides

• Need to ensure that non-‐cached performance is still acceptable.

• Cache invalidation is hard :)

31

http://www.tocker.ca/2013/11/25/query-‐optimization-‐versus-‐caching.html

http://www.tocker.ca/2013/11/25/query-optimization-versus-caching.html


Not-‐CachingMySQL Memcached Interface

• MySQL 5.6 now speaks the memcached protocol! • An alternative high performance interface to MySQL

32


MySQL Memcached5.6 Performance Benchmark

33


MySQL Memcached5.7 Performance Benchmark

34

48 Core Machine / Read Only Memcached http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html http://mysqlserverteam.com/mysql-5-7-3-deep-dive-into-1mil-qps-with-innodb-memcached/

http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html

http://mysqlserverteam.com/mysql-5-7-3-deep-dive-into-1mil-qps-with-innodb-memcached/


Program Agenda



Caching

The Cloud

Hadoop

2

3

4

5

1

4


Cloud + MySQLNot much changes!

• Typically no VIPs for HA failover • Typical for smaller hardware to be used as building blocks

36


Is it better to scale vertical or horizontal?

• Ideally some level of both. • Too small: can not balance out sudden blips. • Too big: no natural isolation from very large bursts.

37

http://www.tocker.ca/2014/04/22/five-‐reasons-‐why-‐vertical-‐scalability-‐matters.html

http://www.tocker.ca/2014/04/22/five-reasons-why-vertical-scalability-matters.html


More RAM is often better

• Twice as much RAM != Twice as Fast • In some scenarios it will be >10x

38

http://www.tocker.ca/2013/05/10/twice-‐as-‐much-‐ram-‐does-‐not-‐equal-‐twice-‐as-‐fast.html

http://www.tocker.ca/2013/05/10/twice-as-much-ram-does-not-equal-twice-as-fast.html


Program Agenda



Caching

The Cloud

Hadoop

2

3

4

5

1

5


MySQL UsageBy Query Type

40

Easy :)

Great

GoodD

ata

Volu

me

Query Complexity*Hadoop Applier


The Edge CaseMySQL Hadoop Applier

• Common to use the tools together • Run queries in parallel with Hive

41


HiveSQL Queries for Hadoop

• Think of it like a large freight train • Queries may take minutes • Does not specifically require indexes

42


MySQL+Hive

• Incredibly common to see both in use • Real-‐time data stored in MySQL • Log-‐like historical data stored in Hive • Reporting queries sent to Hive

43


Hive (cont.)

44

CREATE EXTERNAL TABLE page_view_stg(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User', country STRING COMMENT 'country of origination') COMMENT 'This is the staging page view table' ROW FORMAT DELIMITED FIELDS TERMINATED BY '44' LINES TERMINATED BY '12' STORED AS TEXTFILE LOCATION '/user/data/staging/page_view'; hadoop dfs -put /tmp/pv_2008-06-08.txt /user/data/staging/page_view FROM page_view_stg pvs INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country='US') SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip WHERE pvs.country = 'US';

mysql: from single instance to big data

Software

www mysql www www

www multiple mysql mysqlmysql

writesplit limitations

ms typicalnetworklatency

usewithclustertoolsto