Best practices for MySQL High Availability Feb 2017

1 .Best practices for MySQL High Availability Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://www.bytebot.net/blog/ | @bytebot on Twitter Percona Webminar 7 February 2017 

2 . whoami • Chief Evangelist (in the CTO office), Percona Inc • Founding team of MariaDB Server (2009-2016), previously at Monty Program Ab, merged with SkySQL Ab, now MariaDB Corporation • Formerly MySQL AB (exit: Sun Microsystems) • Past lives include Fedora Project (FESCO), OpenOffice.org • MySQL Community Contributor of the Year Award winner 2014 2 

3 .3 

4 .4 

5 .5 

6 . Uptime Percentile target Max downtime per year 90% 36 days 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 6 

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

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 8 

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? 9 

10 . High Availability for databases • HA is harder for databases • Hardware resources and data need to be redundant • Remember, this isn’t just data - constantly changing data • HA means the operation can continue uninterrupted, not by restoring a new/backup server • uninterrupted: measured in percentiles 10 

11 . Redundancy through client-side XA transactions • Client writes to 2 independent but identical databases • HA-JDBC (http://ha-jdbc.github.io/) • No replication anywhere 11 

12 . InnoDB “recovery” time •innodb_log_file_size • larger = longer recovery times • Percona Server 5.5 (XtraDB) - innodb_recovery_stats 12 

13 . Redundancy through shared storage • Requires specialist hardware, like a SAN • Complex to operate • One set of data is your single point of failure • Cold standby • failover 1-30 minutes • this isn’t scale-out • Active/Active solutions: Oracle RAC, ScaleDB 13 

14 . Redundancy through disk replication • DRBD • Linux administration vs. DBA skills • Synchronous • Second set of data inaccessible for use • Passive server acting as hot standby • Failover: 1-30 minutes • Performance hit: DRBD worst case is ~60% single node performance, with higher average latencies 14 

15 . Redundancy through MySQL replication • MySQL replication • Tungsten Replicator • Galera Cluster • MySQL Cluster (NDBCLUSTER) • Storage requirements are multiplied • Huge potential for scaling out 15 

16 . MySQL Replication • Statement based generally • Row based became available in 5.1, and the default in 5.7 • mixed-mode, resulting in STATEMENT except if calling • UUID function, UDF, CURRENT_USER/USER function, LOAD_FILE function • 2 or more AUTO_INCREMENT columns updated with same statement • server variable used in statement • storage engine doesn’t allow statement based replication, like NDBCLUSTER 16 

17 . MySQL Replication II • Asynchronous by default • Semi-synchronous plugin in 5.5+ • However the holy grail of fully synchronous replication is not part of standard MySQL replication (yet?) • MariaDB Galera Cluster is built-in to MariaDB Server 10.1 17 

18 . The logs • Binary log (binlog) - events that describe database changes • Relay log - events read from binlog on master, written by slave i/o thread • master_info_log - status/config info for slave’s connection to master • relay_log_info_log - status info about execution point in slave’s relay log 18 

19 . Semi-synchronous replication • semi-sync capable slave acknowledges transaction event only after written to relay log & flushed to disk • timeout occurs? master reverts to async replication; resumes when slaves catch up • at scale, Facebook runs semi-sync: http:// yoshinorimatsunobu.blogspot.com/2014/04/semi-synchronous- replication-at-facebook.html 19 

20 . MySQL Replication in 5.6 • Global Transaction ID (GTID) • Server UUID • Binary log (binlog) checksums • Ignore (master) server IDs • Crash safe binlog and relay (filtering) logs • Per-schema multi-threaded • Time delayed replication slave • Parallel replication (per • Group commit in the binary database) log 20 

21 . Replication: START TRANSACTION WITH CONSISTENT SNAPSHOT • Works with the binlog, possible to obtain the binlog position corresponding to a transactional snapshot of the database without blocking any other queries. • by-product of group commit in the binlog to view commit ordering • Used by the command mysqldump--single-transaction -- master-data to do a fully non-blocking backup • Works consistently between transactions involving more than one storage engine • https://kb.askmonty.org/en/enhancements-for-start-transaction-with-consistent/ • Percona Server made it better, by session ID, and also introducing backup locks 21 

22 . Multi-source replication • Multi-source replication - (real-time) analytics, shard provisioning, backups, etc. • @@default_master_connection contains current connection name (used if connection name is not given) • All master/slave commands take a connection name now (like CHANGE MASTER “connection_name”, SHOW SLAVE “connection_name” STATUS, etc.) 22 

23 . Global Transaction ID (GTID) • Supports multi-source replication • GTID can be enabled or disabled independently and online for masters or slaves • Slaves using GTID do not have to have binary logging enabled. • (MariaDB) Supports multiple replication domains (independent binlog streams) • Queries in different domains can be run in parallel on the slave. 23 

24 .Why MariaDB GTID is different compared to 5.6? • MySQL 5.6 GTID does not support multi-source replication • Supports —log-slave-updates=0 for efficiency • Enabled by default • Turn it on without having to restart the topology 24 

25 . Parallel replication • Multi-source replication from different masters executed in parallel • Queries from different domains are executed in parallel • Queries that are run in parallel on the master are run in parallel on the slave (based on group commit). • Transactions modifying the same table can be updated in parallel on the slave! • Supports both statement based and row based replication. 25 

26 . All in… sometimes it can get out of sync • Changed information on slave directly • --replication-ignore-db with fully qualified queries • Statement based replication • Binlog corruption on master • non-deterministic SQL (UPDATE/ DELETE with LIMIT and without • PURGE BINARY LOGS issued and ORDER BY) not enough files to update slave • triggers & stored procedures • read_buffer_size larger than max_allowed_packet • Master in MyISAM, slave in InnoDB (deadlocks) • Bugs? 26 

27 . Replication Monitoring • Percona Toolkit is important • pt-slave-find: find slave information from master • pt-table-checksum: online replication consistency check • executes checksum queries on master • pt-table-sync: synchronise table data efficiently • changes data, so backups important 27 

28 . Replication Monitoring with PMM •http://pmmdemo.percona.com/ 28 

29 . mysqlbinlog versions • ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 56, event_type: 30 • 5.6 ships with a “streaming binlog backup server” - v.3.4; MariaDB 10 doesn’t - v.3.3 (fixed in 10.2 - MDEV-8713) • GTID variances! 29