scaling mysql -- swanseacon.co.uk

Scaling MySQLDave Stokes @Stoker david.stokes@oracle.com slideshare.net/DavidMStokes

13 September 2016 Swanseason.co.uk

Scaling MySQLDave Stokes @Stoker david.stokes@oracle.com slideshare.net/DavidMStokes

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

Safe Harbor3

Quick MySQL Catch Up

21 Years OldMySQL has been part of Oracle’s family of databases for six years.

MySQL 8MySQl 5.7 is the current release but the next version will be MySQL 8. Big feature is real time data dictionary

Group ReplicationActive master-master replication.

JSONA new native JSON datatype to store documents in a column of a table

Document StoreProgrammers not know SQL but need a database? X Devapi allows them to use RDMS from language of choice

EncryptionUse Oracle Key Vault to encrypt your data at rest.

4

Scaling!What is Scaling MySQL?

Why are there so many different ways to scale?

The database runs too fast!! How do I slow it down???5

How to grow your mysql instancesYour new amazing code started off on a Vagrant image and now you want it to scale. The problem is that relational databases do not easily scale. Do you need to scale for reads, or writes, or both?? This session will cover the easy, low cost ways to move from your development box where all is on one image to splitting services, splitting reads/writes, basic sharding, highly available & fault tolerant server farms, to clusters with 99.999% uptime. This talk will cover the costs (financial, time, opportunity cost, and sanity) as you scale with your data.

6

1.LAMP Stack

Linux Apache MySQL PHP

7

“LAMP stack was one box with all the services

running together at the same time.”

8

Databases are the nasty toddlers of software× Do not play well with others× Constantly need attention× Consume resources, time, and sanity

Moving the databaseaway from the other services is a wise First option.

9

MySQL1. Copy data from A to B

2. Start replication3. Copy changes in A to B4. A & B are exact copies

10

Server A Server B

The basic idea is to start with the master server and the slave server with the same information.

11

Server A Server B

Any changes on the master server are copies to the slave server.

12

Server A Server B

And so one and so forth.

13

Backups: 1. Stop SQL Thread on

Slave (still gets updates from Master, but does not apply them to Slave).

2. Run backup3. Restart SQL Thread,

slave catches up.

The way to do backups off a slave server

14

Replication uses

Save data to slave server and use it for backups while keeping main server up for users.

Give developers their own copy of data where they can not interfere with ‘real’ data.

Split reads/writes so that reads go to a pool of servers and writes go to one server. Great for read heavy circumstances. Does not scale writes.

15

Replicaition changes

MySQL up to 5.5

--Single threaded (airline example)

--Logs kept in files

MySQL 5.6

-- Multi threads but one per table

--Logs kept in InnoDB tables

MySQL 5.7

-- Multi threaded at table level

-- Checksums and other features to ensure data is correct

16

There is a PECL extension for use with PHP to load balance reads or do round robin. Or you could use some other load balancer.

17

18

Great presentation on mysqlnd_ms load balance -

PECL Packagehttps://speakerdeck.com/dshafik/

phptek-2015-extending-mysql-with-phps-native-driver

Multi Source replication (5.7) allows multiple servers to save data in parallel to a single slave server to make backups easier, coalesce data, manage shards

19

ShardingCut you data into smaller chunks, make application aware of which chunk has which range of records.

What ABout splitting reads?Sanity CheckNot all shards will grow equally, hard to re-engineer, need extra smarts to manage

20

MySQL Fabric

Python

MySQL Fabric is written in Python (along with the MySQL Utilities) and easy to extend.

High Availability or shards

Easy to set up groups of HA or sharding servers, can re-shard on the fly, sub second failover master->slave

Rough

MySQL Fabric is a little rough around edges currently, MySQL Router fills some gaps.

21

Galara Cluster for MySQL

Virtually synchronous replication for InnoDB

Bundled in MariaDB and Percona Cluster, Linux only

Codeship OY

22

MySQL Group Replication LibraryVirtually Synchronous Replication 5.7+Support on all MySQL platformsHighly Available distributed database serviceRemoved need to manually handling server fail-over

Provides distributed fault tolerance

Enables active/active update anywhere

Automatic detects and handles conflicts

Automates reconfiguration (crashes, adding/removing nodes)

23

Group Replication benefits● No third-party software required.● No network multicast support

required.● MySQL Group Replication can

now operate on cloud based installations on which multicast is disallowed.

● No message size limit.● No separate process.● MySQL Group Replication is

now self-contained on the same software stack.

● First committer wins!

24

•Built on top of proven technology!

–Shares much of MySQL Replication

infrastructure –

thence does not feel alien!

–Multi-Master approach to replication.

•Built on reusable components!–Layered implementation approach.

–Interface driven development.

–Decoupled from the server core.

–The plugin registers as listener to server events.–Reuses the capture procedure from regular Replication. Provides further decoupling from the communication infrastructure

25

USe Cases for Group Replication

Highly Available ShardsSharding is a popular approach to achieve

write scale-out. Users can use MySQL Group

Replication to implement highly

available shards. Each shard can map into a

Replication Group.

Elastic ReplicationEnvironments that require a very fluid

replication infrastructure, where

the number of servers has to grow or shrink dynamically and with

as little pain as possible.

Alternative to Master-Slave

replicationIt may be that a single master server makes it

a single point of contention. Writing to an entire group may prove more scalable

under certain circumstances

26

27

•Cloud Friendly

–Great techonology for deployments where elasticity is a requirement, such as cloud based infrastructures.

•Integrated

–With server core through a well defined API.

–With GTIDs, row based replication, performance schema tables.

•Autonomic and Operations Friendly

–It is self-healing: no admin overhead for handling server fail-overs.

–Provides fault-tolerance, enables multi-master update everywhere and a dependable MySQL service.

MySQL group replication

28

Proxy/router

29

There are lots of options for load balancing (hardware, mysqlnd_ms, MySQL router, MariaDB MaxScale, Proxysql, and many others)

All have their features -fail over, query rewrite, sharding support- that you may need or can do another way -- But they can smooth out your traffic at high levels.

Proxy/router continued

30

Be careful as some proxies do not support tracking schema, auto commit / transactions, or character encodings.

Try not to create more work for yourself.

What if you need replication between data centers?

31

Or need 99.99% uptime?

MySQl Cluster!

32

MySQL Cluster FeaturesIn-Memory Database Delivering 200 Million QPSUsing memory-optimized tables, MySQL Cluster provides real-time response time and throughput meet the needs of the most demanding web, telecommunications and enterprise applications - delivering 200 Million Queries Per Second.

Auto-sharding for Write-scalabilityMySQL Cluster automatically shards (partitions) tables across nodes, enabling databases to scale horizontally on low cost, commodity hardware while maintaining complete application transparency.

99.999% AvailabilityWith its distributed, shared-nothing architecture, MySQL Cluster has been designed to deliver 99.999% availability ensuring resilience to failures and the ability to perform scheduled maintenance without downtime.

SQL & NoSQL APIsMySQL Cluster enables users to blend the best of both relational and NoSQL technologies into solutions that reduce cost, risk and complexity.

Multi-site Clusters with Active Active Geographical ReplicationUpdate-anywhere geographic replication enables multiple clusters to be distributed geographically for disaster recovery and the scalability of global web services.

Online Scaling & Schema UpgradesTo support continuous operation, MySQL Cluster allows on-line addition of nodes and updates to live database schema to support rapidly evolving and highly dynamic workloads.

MySQL Cluster Auto-InstallerGet MySQL Cluster up and running in minutes! Graphically configure and provision a production-grade cluster, automatically tuned for your workload and environment.

33

Need redundant everythingThat’s a lot of money

Much more complexCan’t wing cluster!

Not generic MySQLRead_commited only, RBR, 2pb max storage

34

35

SoNow

what?!?!

Basics (kihon)

DataNormalize your data to 3NF (or better) or plan for it to get S - L - O - W sooner than later. Plan for schema changes.

MonitorHow fast ARE you growing? Constant rate, seasonal, exponential? When do you upgrade?

StaffDo you have the right people at the right time? The right training, tools, support? What about disasters?!?!

BackupsCan you restore entire database, table, or row? Legal retention requirements? What happens if media changes or ages? Backup backups?

Code ReviewWho is checking the queries run against the database? Do developers ‘know’ their data?

ManagementDoes ‘mahogany row’ know daa struggles. Can’t copy what Facebook does ‘cause you are not Facebook.

36

THANKS!Any questions?

You can find me at @stoker or david.stokes@oracle.com

Slides at slideshare.net/davidmstokes37