Pro Yearly is on sale from $80 to $50! »

[SunshinePHP 2014] MySQL HA, Recovery and Load Balancing

Fee39f0c0ffb29d9ac21607ed188be6b?s=47 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.

Fee39f0c0ffb29d9ac21607ed188be6b?s=128

Davey Shafik

February 07, 2014
Tweet

Transcript

  1. MySQL HA, DR and Load Balancing

  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
  3. About These Slides

  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
  5. High Availability

  6. Proprietary and Confidential • Not: servers that don’t die! •

    Ensures service availability, not server availability High Availability (HA)
  7. Disaster Recovery

  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)
  9. Load Balancing

  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
  11. Why?

  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?
  13. What

  14. Proprietary and Confidential • HA + Performance! • Minimal code

    impact (drop-in system)! • Simple (less moving parts) What
  15. MySQL 5.5

  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
  17. Proprietary and Confidential

  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
  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
  20. Disaster Recovery

  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
  22. Performance

  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
  24. Proprietary and Confidential • Sysbench OLTP Benchmark! –1.5 Million Rows!

    –64 Connection! –InnoDB Storage Engine! –50,000 Queries! –Optionally: Read-only Benchmarks
  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
  26. MySQL 5.6

  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)
  28. Proprietary and Confidential

  29. Proprietary and Confidential •gtid-mode = ON! •log-bin! •enforce-gtid-consistency = ON!

    •log-slave-updates = ON GTID Master & Slave Setup
  30. Proprietary and Confidential • STOP SLAVE; ! • CHANGE MASTER

    TO MASTER_HOST='<host> ', MASTER_USER='<user>', MASTER_PASSWORD='<password>', MASTER_AUTO_POSITION=1;! • START SLAVE; GTID Slave Setup
  31. Disaster Recovery

  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
  33. Proprietary and Confidential • Failed check:! –Alert somebody! –Set app

    to maintenance! • Before failover:! –Alert somebody! –Log Something! –Take backups Hooks
  34. Proprietary and Confidential • After Failover:! –Alert Somebody! • After

    complete recovery: ! –Modify Application Config! –Switchover elastic IPs (amazon)! –Verify Application! –Alert Somebody! Hooks
  35. Performance

  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
  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
  38. Percona XtraDB Cluster

  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
  40. Proprietary and Confidential

  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=<name>
 innodb_locks_unsafe_for_binlog=1
 innodb_autoinc_lock_mode=2 Primary Server Setup
  42. Proprietary and Confidential •[mysqld]
 wsrep_cluster_address=gcomm://<ip>
 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=<name>
 innodb_locks_unsafe_for_binlog=1
 innodb_autoinc_lock_mode=2 Other Server Setup
  43. Disaster Recovery

  44. Proprietary and Confidential • Choose a new Primary server! •

    Update config! • Restart all servers Disaster Recovery
  45. Performance

  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
  47. Other Issues

  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
  49. Agenda mysqlnd_ms Read/Write Splitting

  50. Proprietary and Confidential $ pecl install mysqlnd_ms
 
 
 mysqlnd_ms.enable=1


    mysqlnd_ms.config_file=
 /path/to/mysqlnd_ms.json Read/Write Splitting
  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
  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
  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
  54. Proprietary and Confidential Feedback & Questions: Feedback: https://joind.in/9093
 Twitter: @dshafik

    Email: davey@engineyard.com Slides: http://daveyshafik.com/slides http://ey.io/ssp14-mysql