Choosing a MySQL High Availability Solution

1 .Choosing a MySQL High Availability Solution Marcos Albe, Percona Inc. Live Webinar June 2017 

2 . Agenda ● What is availability ● Components to build an HA solution ● HA options in the MySQL ecosystem ● Failover/Routing tools ● Percona’s picks www.percona.com 

3 . What is availability ● Uninterrupted delivery of a service (a.k.a. Uptime) ● With reasonable response times (SLAs) ● Guaranteeing consistency (the C in ACID) www.percona.com 

4 . Uptime Percentil Max downtime 99% 3.65 days 99.5% 1.83 days 99.9% 8.76 hours 99.99% 52.56 minutes 99.999% 5.25 minutes 99.9999% 31.5 seconds www.percona.com 

5 . Estimated levels of availability Method Level of Availability Simple replication 98-99.9% Master-Master/MMM 99% SAN 99.5-99.9% DRBD, MHA, Tungsten Replicator 99.9% NDBCluster, Galera Cluster 99.999% www.percona.com 

6 . Agenda ● What is availability ● Components to build an HA solution ● HA options in the MySQL ecosystem ● Failover/Routing tools ● Percona’s picks www.percona.com 

7 . Components for HA ● Redundancy (no SPoF) ● Durability (recovery/identity) ● Clustering (monitoring/failover) ● Performance (latency) www.percona.com 

8 . HA is Redundancy ● RAID: disk crashes? Another works ● Clustering: server crashes? Another works ● Power: fuse blows? Redundant power supplies ● Network: Switch/NIC crashes? 2nd network route ● Geographical: Datacenter offline/destroyed? Computation to another DC www.percona.com 

9 . Durability ● Data stored on disks ○ Is it really written to the disk? ○ being durable means calling fsync() on each commit ● Is it written in a transactional way to guarantee atomicity, crash safety, integrity? www.percona.com 

10 . Clustering ● Load balancers and Proxies ● Monitor health of replication components ● Direct traffic to the appropriate node based on status or other rules www.percona.com 

11 . Performance ● HA always implies some performance overhead ● To cope with overhead we need to have a resonable base performance ○ Good queries/schema design ○ Good configuration ○ Good hardware ○ Good connectivity www.percona.com 

12 . HA for databases ● HA is harder for databases ● Hardware resources and data need to be redundant ● Constantly changing data ● Operations can continue uninterrupted ○ Not by restoring a new/backup server ● Uninterrupted: measured in percentiles www.percona.com 

13 . Agenda ● What is availability ● Components to build an HA solution ● HA options in the MySQL ecosystem ● Failover/Routing tools ● Percona’s picks www.percona.com 

14 . Redundancy through XA ● Client writes to 2 independent but identical databases ○ HA-JDBC (http://ha-jdbc.github.io/) ○ Coordinated two-phase commit ● No replication anywhere ● Many pitfalls and known bugs www.percona.com 

15 .Redundancy through Shared Storage ● Requires specialized hardware (SAN) ● Complex to operate (specially for DBAs) ● One set of data is your single point of failure ● Cold standby ● Failover 1-30 minutes ● Not scale-out ● Active/Active solutions: Oracle RAC, ScaleDB www.percona.com 

16 .Redundancy through disk replication ● DRBD ○ Linux administration vs. DBA skills ● Synchronous ● Failover: 0.5 - 30 minutes ● Second set of data inaccessible for use ● Not scale-out ● Performance hit: worst case is ~60% www.percona.com 

17 . Redundancy through MySQL replication ● MySQL replication ● Galera Cluster / InnoDB Cluster ● MySQL Cluster (NDBCLUSTER) ● Tungsten Replicator ● Computing/storage requirements are multiplied ● Huge potential for scaling out www.percona.com 

18 . MySQL replication ● Statement based ● Row based became available in 5.1, and the default in 5.7 ● Asynchronous ● GTID/UUID in 5.6 ● MTS per schema in 5.6 ● MTS intra schema in 5.7 www.percona.com 

19 . Semi-sync replication ● Slave acknowledges transaction event only after written to relay log ● Timeout occurs? master reverts to async replication; resumes when slaves catch up ● It scales, Facebook runs semi-sync: http:// yoshinorimatsunobu.blogspot.com/2014/04/semi- synchronous- replication-at-facebook.html ● Affected by latency www.percona.com 

20 . Galera Cluster ● Inside MySQL, a replication plugin (wsrep) ● Replaces MySQL replication (can work alongside it too) ● Virtually Synchronous ● True multi-master, active-active solution ● No slave lag or integrity issues ● Automatic node provisioning ● WAN performance: 100-300ms/commit, works in parallel www.percona.com 

21 . Galera Cluster (2) ● Minimum 3 nodes are recommended ● It’s elastic ○ Automatic node provisioning ○ Self healing / Quorum ● Slowest node drives performance ● Scales reads, NOT writes ● Has some limitations (InnoDB only, transaction size, transportable tablespaces) www.percona.com 

22 . Group Replication ● Very much the same than Galera ● Built-in to MySQL; All Platforms ● A bit too-early for production (https://goo.gl/oKHm27) ● https://goo.gl/AbTRco for Vadim’s (Percona’s CTO) comparison of Galera and Group Replication ● https://goo.gl/emL9zX for Frederic Descamps (Oracle) comparing them www.percona.com 

23 . Tungsten ● MySQL writes binlog,Tungsten reads it and uses its own replication protocol ● Replaces MySQL Replication layer ● Per-schema multi-threaded slave ● Heterogeneous replication: MySQL <-> MongoDB <-> Postgres <-> Oracle ● Multi-master replication ● Multiple masters to single slave (multi-source replication) ● Other complex topologies www.percona.com 

24 . Agenda ● What is availability ● Components to build an HA solution ● HA options in the MySQL ecosystem ● Failover/Routing tools ● Percona’s picks www.percona.com 

25 .All in... sometimes it can get out of sync ● Changed information on slave directly ● Statement based replication ● Master in MyISAM, slave in InnoDB (deadlocks) ● --replication-ignore-db with fully qualified queries ● Binlog corruption on master ● Lack of primary keys ● read_buffer_size larger than max_allowed_packet ● PURGE BINARY LOGS issued and not enough files to update slave ● Bugs www.percona.com 

26 . Handling failure ● How to detect failure? Polling, monitoring, alerts, error returned to client side ● What to do? Direct requests to the spare nodes (or DCs) ● How to preserve integrity? ○ Async: Must ensure there is only one master at all times. ○ DRBD/SAN cold-standby: Must unmount disks and stop mysqld; then the opposite on promoted node. ● In all cases must ensure that 2 disconnected replicas or clusters cannot both commit independently. (split brain) www.percona.com 

27 . Tooling to handle failure ● Orchestrator ● MySQL MHA ● Tungsten Replicator ● 5.6: mysqlfailover, mysqlrpladmin ● Percona Replication Manager ● Severalnines ClusterControl ● MariaDB Replication Manager ● MySQL MMM www.percona.com 

28 . Orchestrator ● Topology introspection, keeps state, continuous polling ● Smart picking of node to be promoted ● No manual promotions ● Flapping protection ● No checking for transactions on master ● Modify your topology — move slaves around ● Nice GUI, JSON API, CLI ● https://goo.gl/ELWM7S and https://goo.gl/Uy9I3c for more in-depth reviews www.percona.com 

29 . MHA ● Similar to Orchestrator ● Automated and manual failover options ● Choose new master by comparing slave binlog positions. ○ Fetch missing from master if possible. ● Can be used in conjunction with other solutions (example: https://goo.gl/Wds1es) ● No longer developed; Still maintained www.percona.com