$30 off During Our Annual Pro Sale. View Details »

[SunshinePHP 2014] MySQL HA, Recovery and Load Balancing

Davey Shafik
February 07, 2014

[SunshinePHP 2014] MySQL HA, Recovery and Load Balancing

Using Oracle MySQL Server and Percona XtraDB Cluster we will take a look at several possible replication configurations for MySQL high-availability, disaster recovery and load-balancing. After comparing them for administration ease, scalability of reads and writes, robustness of the system for errors and time for data recovery as well as looking at how they impact your code-layer we will discuss the ins-and-outs of each setup, looking at the pros and cons, and what trade-offs you will need to make depending on your needs.

Davey Shafik

February 07, 2014
Tweet

More Decks by Davey Shafik

Other Decks in Programming

Transcript

  1. MySQL HA, DR and
    Load Balancing

    View Slide

  2. Proprietary and Confidential
    •Community Engineer at
    Engine Yard
    •Author of Zend PHP 5
    Certification Study Guide,
    Sitepoints PHP Anthology:
    101 Essential Tips, Tricks &
    Hacks & PHP Master: Write
    Cutting Edge Code
    •A contributor to Zend
    Framework 1 & 2, phpdoc,
    and PHP internals
    •@dshafik
    Davey Shafik

    View Slide

  3. About These Slides

    View Slide

  4. Proprietary and Confidential
    • Two slides per “slide”!
    – Title Slide (for when I’m talking)!
    – Details slide (for later)!
    • Nobody likes it when you can read the slide just as well as the speaker
    can!
    • I like slides that are useful
    About These Slides

    View Slide

  5. High Availability

    View Slide

  6. Proprietary and Confidential
    • Not: servers that don’t die!
    • Ensures service availability, not server availability
    High Availability (HA)

    View Slide

  7. Disaster Recovery

    View Slide

  8. Proprietary and Confidential
    • Process for recovering from different failure scenarios!
    –Automated or not (or in-between)!
    • Planning!
    –Something to recover from!
    –Something to recover to
    Disaster Recovery (DR)

    View Slide

  9. Load Balancing

    View Slide

  10. Proprietary and Confidential
    • Balancing the load between multiple machines!
    –Smart (based on system resources)!
    –Dumb (round-robin)!
    • ELB is easy, but slow!
    • HAProxy is super faster (and still pretty easy)
    Load Balancing

    View Slide

  11. Why?

    View Slide

  12. Proprietary and Confidential
    • Curiosity — MySQL admin is fun!
    • Goal: Make system predictable — then it can be automated (some)!
    • Impacts of design decisions!
    • Percona Cluster: oooh shiny!
    Why?

    View Slide

  13. What

    View Slide

  14. Proprietary and Confidential
    • HA + Performance!
    • Minimal code impact (drop-in system)!
    • Simple (less moving parts)
    What

    View Slide

  15. MySQL 5.5

    View Slide

  16. Proprietary and Confidential
    • Improved Data Integrity!
    • Master blocks until 1 slave has written to relay log, flushed to disk &
    acknowledged!
    • Slower... a little.!
    • Failover to asynchronous replication
    Semi-Sync Replication

    View Slide

  17. Proprietary and Confidential

    View Slide

  18. Proprietary and Confidential
    •INSTALL PLUGIN rpl_semi_sync_master SONAME
    'semisync_master.so';
    •rpl_semi_sync_master_enabled = {0|1};
    •rpl_semi_sync_master_timeout = N;
    Semi-Sync Master Setup

    View Slide

  19. Proprietary and Confidential
    •INSTALL PLUGIN rpl_semi_sync_slave SONAME
    'semisync_slave.so';
    •rpl_semi_sync_slave_enabled = {0|1};
    Semi-Sync Slave Setup

    View Slide

  20. Disaster Recovery

    View Slide

  21. Proprietary and Confidential
    • Primary slave is pre-determined failover master!
    –Will have the most up-to-date content (not necessarily ALL the content)!
    • Slaves must re-connect to new master!
    –CHANGE MASTER to correct (new) position!
    • Switch app to new master
    Disaster Recovery

    View Slide

  22. Performance

    View Slide

  23. Proprietary and Confidential
    • Benchmarker: 1 XL AWS Instance!
    • Master: 1 AWS Medium Instance!
    • Slaves: 3 AWS Large Instances (one semi-sync)!
    • HAProxy: 1 AWS Large Instance
    Performance

    View Slide

  24. Proprietary and Confidential
    • Sysbench OLTP Benchmark!
    –1.5 Million Rows!
    –64 Connection!
    –InnoDB Storage Engine!
    –50,000 Queries!
    –Optionally: Read-only
    Benchmarks

    View Slide

  25. Proprietary and Confidential
    • Writing 1.5 Million Records, with indexes:!
    –1m 13s seconds (replicated)!
    • Sysbench OLTP Benchmark:!
    –Read/Write (with semi-sync): 2850/qps!
    –Read/Write (without semi-sync): 2900/qps!
    –Read only: 10850/qps
    Performance

    View Slide

  26. MySQL 5.6

    View Slide

  27. Proprietary and Confidential
    • Unique identifier across all server (binlogs)!
    –combines server UUID:Transaction ID!
    • Transaction based!
    • No need for log files or positions with CHANGE MASTER!
    • Consistent group-wide!
    • Easier failover
    Global Transaction IDs (GTIDs)

    View Slide

  28. Proprietary and Confidential

    View Slide

  29. Proprietary and Confidential
    •gtid-mode = ON!
    •log-bin!
    •enforce-gtid-consistency = ON!
    •log-slave-updates = ON
    GTID Master & Slave Setup

    View Slide

  30. Proprietary and Confidential
    • STOP SLAVE; !
    • CHANGE MASTER TO MASTER_HOST=' ',
    MASTER_USER='', MASTER_PASSWORD='',
    MASTER_AUTO_POSITION=1;!
    • START SLAVE;
    GTID Slave Setup

    View Slide

  31. Disaster Recovery

    View Slide

  32. Proprietary and Confidential
    • Performs replication health monitoring!
    –handles automatic failover!
    –automatic !== immediate (interval in seconds >= 5s)!
    • Hook scripts for failed check, before/after failover, after complete recovery,
    Disaster Recovery: mysqlfailover

    View Slide

  33. Proprietary and Confidential
    • Failed check:!
    –Alert somebody!
    –Set app to maintenance!
    • Before failover:!
    –Alert somebody!
    –Log Something!
    –Take backups
    Hooks

    View Slide

  34. Proprietary and Confidential
    • After Failover:!
    –Alert Somebody!
    • After complete recovery: !
    –Modify Application Config!
    –Switchover elastic IPs (amazon)!
    –Verify Application!
    –Alert Somebody!
    Hooks

    View Slide

  35. Performance

    View Slide

  36. Proprietary and Confidential
    • Benchmarker: 1 XL AWS Instance!
    • Master: 1 AWS Medium Instance!
    • Slaves: 3 AWS Large Instances !
    • HAProxy: 1 AWS Large Instance
    Performance

    View Slide

  37. Proprietary and Confidential
    • Writing 1.5 Million Records, with indexes:!
    –60 seconds (replicated)!
    • Sysbench OLTP Benchmark:!
    –Read/Write: 2950/qps!
    –Read only: 9500/qps
    Performance

    View Slide

  38. Percona XtraDB Cluster

    View Slide

  39. Proprietary and Confidential
    • Everything replicates to everything!
    • Uses XtraDB table engine (innodb compatible) — not NDB!
    • Automatically transfer state to newly introduced nodes!
    • Still has a “primary” server!
    • Based on Galera for multi-master writes
    Multi-Master Replication

    View Slide

  40. Proprietary and Confidential

    View Slide

  41. Proprietary and Confidential
    •[mysqld]

    wsrep_cluster_address=gcomm://

    datadir=/var/lib/mysql

    user=mysql

    binlog_format=ROW

    wsrep_provider=/usr/lib/libgalera_smm.so

    wsrep_slave_threads=2

    wsrep_cluster_name=benchmarking

    wsrep_sst_method=xtrabackup

    wsrep_node_name=

    innodb_locks_unsafe_for_binlog=1

    innodb_autoinc_lock_mode=2
    Primary Server Setup

    View Slide

  42. Proprietary and Confidential
    •[mysqld]

    wsrep_cluster_address=gcomm://

    datadir=/var/lib/mysql

    user=mysql

    binlog_format=ROW

    wsrep_provider=/usr/lib/libgalera_smm.so

    wsrep_slave_threads=2

    wsrep_cluster_name=benchmarking

    wsrep_sst_method=xtrabackup

    wsrep_node_name=

    innodb_locks_unsafe_for_binlog=1

    innodb_autoinc_lock_mode=2
    Other Server Setup

    View Slide

  43. Disaster Recovery

    View Slide

  44. Proprietary and Confidential
    • Choose a new Primary server!
    • Update config!
    • Restart all servers
    Disaster Recovery

    View Slide

  45. Performance

    View Slide

  46. Proprietary and Confidential
    • Unable to test!!
    • More than 4 concurrent “users” resulted in auto increment primary key
    collisions!
    • Unable to complete the benchmark at all with >= 16 threads!
    • ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate entry

    '500519' for key 'PRIMARY'

    FATAL: database error, exiting...
    Performance

    View Slide

  47. Other Issues

    View Slide

  48. Proprietary and Confidential
    • Replication was very brittle, restarting the daemon would sometimes
    break it!
    • Restarting the server almost always break it!
    • Adding new nodes very often failed!
    • Completely unable to get the system up and running with the latest
    version for this talk.
    Other Issues

    View Slide

  49. Agenda
    mysqlnd_ms
    Read/Write Splitting

    View Slide

  50. Proprietary and Confidential
    $ pecl install mysqlnd_ms



    mysqlnd_ms.enable=1

    mysqlnd_ms.config_file=

    /path/to/mysqlnd_ms.json
    Read/Write Splitting

    View Slide

  51. Proprietary and Confidential
    {
    "myapp": {
    "master": {
    "master_0": {
    "host": "localhost",
    "socket": "\/tmp\/mysql.sock"
    }
    },
    "slave": {
    "slave_0": {
    "host": "192.168.2.27",
    "port": "3306"
    }
    }
    }
    Read/Write Splitting

    View Slide

  52. Proprietary and Confidential
    •SELECT * FROM foo; -- Use Slave!
    •/* MYSQLND_MS_SLAVE_SWITCH */SELECT * FROM foo; -- Use Slave!
    •SELECT * FROM foo; INSERT INTO bar VALUES ('baz'); -- Use Slave!!
    •/* MYSQLND_MS_MASTER_SWITCH */SELECT * FROM foo; -- Use
    Master!
    •/* MYSQLND_MS_LAST_USED_SWITCH */SELECT * FROM foo; -- Use
    whatever !
    • -- was last used
    Read/Write Splitting

    View Slide

  53. Proprietary and Confidential
    • Semi-Sync Replication: !
    –http://ey.io/18PllQP!
    • Replication with GTIDs: !
    –http://ey.io/15nTo20!
    • MySQL Utilities (include mysqlfailover): !
    –http://ey.io/GGP2Kh!
    • Percona XtraDB Cluster: !
    –http://ey.io/1gqyyUn
    Other Resources
    http://ey.io/ssp14-mysql

    View Slide

  54. Proprietary and Confidential
    Feedback & Questions:
    Feedback: https://joind.in/9093

    Twitter: @dshafik
    Email: [email protected]
    Slides: http://daveyshafik.com/slides
    http://ey.io/ssp14-mysql

    View Slide