Making PagerDuty More Reliable Using XtraDB Cluster

Percona Live Session: https://www.percona.com/live/mysql-conference-2015/sessions/making-pagerduty-more-reliable-using-xtradb-cluster

Databases are tough to deploy as an HA solution. Most companies have multiple database servers, but when disaster strikes, they find their systems don't handle the failure well. Failing over to a cold slave could still keep production unavailable. The failover process could be error prone, leading to split brain setups. There has to be a better way.

In this talk, we will discuss how PagerDuty switched our production MySQL system (running in EC2) from a DRBD based solution to Percona XtraDB Cluster based on Codership's synchronous replication library, Galera. With that change, we now have a MySQL setup where setting up a new MySQL server is completely automated, including grabbing a copy of the production dataset. We can withstand losing one of our servers with no downtime. Most importantly, each MySQL server is effectively equivalent, making our lives that much easier.

This talk will cover why we chose XtraDB Cluster, and what benchmarks we ran to test its suitability for handling our production traffic (including a couple important configuration changes). We will explore Galera's synchronous replication scheme, and the application tradeoffs you need to understand before switching to this system. We will discuss the roll out process, how we monitor the produciton cluster, and how we test its ability to handle failure in production.

Transcript

1. 4/15/15 @dougbarth MAKING PAGERDUTY MORE RELIABLE USING PXC PERCONA LIVE 2015
2. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC
3. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC
4. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC
5. 4/15/15 PagerDuty stack MAKING PAGERDUTY MORE RELIABLE USING PXC Then Now Monothilic Rails App Rails & Scala Cloud hosted Cloud hosted MySQL Community (later Percona Server) Percona XtraDB Cluster Cassandra Zookeeper
6. 4/15/15 MySQL at PagerDuty MAKING PAGERDUTY MORE RELIABLE USING PXC Data size ~ 600 GB Queries / s 6,000 - 7,500 Txns / s 200 - 300
7. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Ye Olden Setup ca. Jan 2013
8. 4/15/15 Ye Olden Setup MAKING PAGERDUTY MORE RELIABLE USING PXC EBS EBS Primary Secondary DRBD us-west-2a us-west-2b
9. 4/15/15 Ye Olden Setup MAKING PAGERDUTY MORE RELIABLE USING PXC Primary Backup Delayed DR
10. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC DRBD Failover Problem #1
11. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC 1. FLUSH TABLES WITH READ LOCK 2. Stop MySQL on primary 3. Unmount DRBD volume 4. Set primary to secondary role 5. Flip EIP over to secondary 6. Confirm secondary is now primary 7. Mount DRBD volume on new primary 8. Start MySQL on new primary 9. Wait for clients to reconnect 10. Wait for buffer pool to warm up
12. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC ~ 3 minutes of downtime
13. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Broken replicas after flip Problem #2
14. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Impact of binlogs on DRBD volume (sysbench) txn/s 0 150 300 450 600 # of concurrent clients 1 2 4 8 16 32 64 128 DRBD (data) DRBD (data+binlogs) DRBD (data+binlogs sync)
15. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Percona XtraDB Cluster
16. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Percona Server + Galera = PXC
17. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Multi-master Synchronous replication Parallel replication
18. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Automatic provisioning pxc1 pxc2 (DONOR) pxc3 (JOINER) SST
19. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Failure handling pxc1 pxc2 pxc3 Partition Primary Component
20. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Failure handling pxc1 pxc2 pxc3 IST or SST
21. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC The New Hotness
22. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC pxc01 pxc02 pxc03 app01 HAProxy app02 HAProxy app03 HAProxy us-west-2a us-west-2b us-west-2c http://www.mysqlperformanceblog.com/2012/06/20/percona-xtradb-cluster-reference-architecture-with-haproxy/
23. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC wsrep_node_name = pxc01 wsrep_sst_donor = pxc03,pxc02 wsrep_sst_method = xtrabackup-v2 SST configuration
24. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC wsrep_causal_reads = ON Causal reads enabled
25. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC innodb_blocking_buffer_pool_restore = ON innodb_buffer_pool_restore_at_startup = 300 Buffer pool restoration enabled
26. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC pxc01 pxc02 pxc03 app01 HAProxy app02 HAProxy app03 HAProxy us-west-2a us-west-2b us-west-1c
27. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC The Long Road to Production
28. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC InnoDB only Primary keys on every table Step 1 — Schema compatibility
29. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 2 — Gain Experience Primary pxc01 pxc02 pxc03
30. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 3 — Monitoring
31. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 3 — Monitoring wsrep_flow_control_paused wsrep_flow_control_sent wsrep_flow_control_received
32. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 3 — Monitoring wsrep_flow_control_paused wsrep_flow_control_sent wsrep_flow_control_received Stateful counters in 5.5
33. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Measure usage then disable query cache Step 4 — Query cache not supported
34. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 5 — Row base replication Primary Replica SBR PXC RBR
35. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 6 — Locking Locking per node Switch to Zookeeper
36. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 7 — Eliminate large transactions Break up transactions into several smaller ones
37. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 8 — Benchmarks sysbench
38. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 8 — Benchmarks sysbench (writes only) txns/s 0 750 1500 2250 3000 # of concurrent clients 1 2 4 8 16 32 64 128 256 512 Percona Server PXC
39. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 8 — Benchmarks innodb_flush_log_at_trx_commit = 0 innodb_log_file_size = 1G
40. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 8 — Benchmarks sysbench (writes only, tuned IO settings) txns/s 0 750 1500 2250 3000 # of concurrent clients 2 4 8 16 32 64 128 Percona Server (small table) Percona Server (large table) PXC (small) PXC (large)
41. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Step 8 — Benchmarks sysbench (75% reads, 25% writes) txns/s 0 150 300 450 600 # of concurrent clients 2 4 8 16 32 64 128 Percona Server (small table) Percona Server (large table) PXC (small) PXC (large)
42. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Production rollout Oct 2013
43. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Rollout DRBD pair DRBD pair PXC DR Backup Delayed DRBD pair DRBD pair
44. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Rollout DRBD pair DRBD pair PXC DR Backup Delayed DRBD pair DRBD pair
45. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Rollout PXC DR Backup Delayed DRBD pair DRBD pair
46. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Life in Production
47. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Rolling changes
48. 4/15/15 Benefit: Rolling changes MAKING PAGERDUTY MORE RELIABLE USING PXC pxc01 pxc02 pxc03 app01 HAProxy app02 HAProxy app03 HAProxy us-west-2a us-west-2b us-west-2c
49. 4/15/15 Benefit: Rolling changes MAKING PAGERDUTY MORE RELIABLE USING PXC pxc01 pxc02 pxc03 app01 HAProxy app02 HAProxy app03 HAProxy us-west-2a us-west-2b us-west-2c
50. 4/15/15 Benefit: Rolling changes MAKING PAGERDUTY MORE RELIABLE USING PXC pxc01 pxc02 pxc03 app01 HAProxy app02 HAProxy app03 HAProxy us-west-2a us-west-2b us-west-2c
51. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Moving replicas
52. 4/15/15 Benefit: Moving replicas MAKING PAGERDUTY MORE RELIABLE USING PXC http://www.percona.com/blog/2013/06/21/changing-an-async-slave-of-a-pxc-cluster-to-a-new-master/ pxc01 Xid = 2341 mysql-bin.001234 83452 pxc02 Xid = 2341 mysql-bin.003004 98234 backup01 Xid = 2341 mysql-relay.002311 5002
53. 4/15/15 Benefit: Moving replicas MAKING PAGERDUTY MORE RELIABLE USING PXC http://www.percona.com/blog/2013/06/21/changing-an-async-slave-of-a-pxc-cluster-to-a-new-master/ pxc01 Xid = 2341 mysql-bin.001234 83452 pxc02 Xid = 2341 mysql-bin.003004 98234 backup01 Xid = 2341 mysql-relay.002311 5002
54. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC Schema changes pt-online-schema-change
55. 4/15/15 github.com/steverice/pt-osc MAKING PAGERDUTY MORE RELIABLE USING PXC
56. 4/15/15 github.com/steverice/pt-osc MAKING PAGERDUTY MORE RELIABLE USING PXC
57. 4/15/15 github.com/PagerDuty/whazzup MAKING PAGERDUTY MORE RELIABLE USING PXC
58. 4/15/15 github.com/PagerDuty/whazzup MAKING PAGERDUTY MORE RELIABLE USING PXC
59. 4/15/15 github.com/PagerDuty/whazzup MAKING PAGERDUTY MORE RELIABLE USING PXC
60. 4/15/15 github.com/PagerDuty/whazzup MAKING PAGERDUTY MORE RELIABLE USING PXC https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1443755
61. 4/15/15 Deadlock errors MAKING PAGERDUTY MORE RELIABLE USING PXC LB App PXC App Deadlock 503 Retry
62. 4/15/15 Deadlock errors MAKING PAGERDUTY MORE RELIABLE USING PXC
63. 4/15/15 MAKING PAGERDUTY MORE RELIABLE USING PXC
64. 4/15/15 doug@pagerduty.com PAGERDUTY.COM/JOBS MAKING PAGERDUTY MORE RELIABLE USING PXC
65. 4/15/15 pagerduty.com Questions?