Downloaded 18 times
Uploaded bySteve Purkis
High Availability Perl DBI + MySQL
The document discusses high availability (HA) solutions for MySQL, focusing on minimizing downtime for critical applications through strategies like redundancy, data replication, and load balancing. It presents various architectures for setting up MySQL, including setups with multiple masters and slaves, and highlights the importance of analyzing application needs when designing HA systems. The talk also introduces a DBI framework for managing connections, failover, and load balancing, while suggesting upcoming technologies and frameworks for better HA implementation.
More Related Content
![Java script nirvana in netbeans [con5679]](https://cdn.slidesharecdn.com/ss_thumbnails/javascriptnirvanainnetbeanscon5679-160927041807-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
Similar to High Availability Perl DBI + MySQL
![UiPath Automation Suite Installation (Hands-On) [2/3]](https://cdn.slidesharecdn.com/ss_thumbnails/automationsuitecommunitysession2-251015095633-a6d862f1-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
High Availability Perl DBI + MySQL
- 1. High Availability? Setting UpMySQL Our Approach DBI Framework Questions High Availability DBI & MySQL YAPC::NA 2005 High Availability DBI & MySQL Steve Purkis
- 2. High Availability? Setting UpMySQL Our Approach DBI Framework Questions High Availability DBI & MySQL
- 3. Slide 3 High Availability? •What is High Availability? • Ever have an application fail because a mission critical server crashed? • High Availability is an attempt to provide zero application downtime.
- 4. Slide 4 High Availability? •How do I achieve HA?: • Analyze your apps • Most important components? • Remove single points of failure (SPOFs): • Add redundant servers • Replicate data (backups, realtime) • Load balancing • Failover when things go pear-shaped
- 5. Slide 5 High Availability? •This talk… • Introduces one approach to building HA Perl apps that rely on MySQL. • We’ve used similar techniques with Oracle • They work for us… • They may not be right for you!
- 6. High Availability? Setting UpMySQL Our Approach DBI Framework Questions High Availability DBI & MySQL
- 7. Slide 7 Setting UpMySQL • Some typical architectures…
- 8. Slide 8 Setting UpMySQL • Ye olde basic setup • Not HA. Application Server MySQL Server
- 9. Slide 9 Setting UpMySQL • One master, one read-only slave • Good for load balancing • Still not really HA! Application Server MySQL Master MySQL Slave Data Replication reads writes Load Balancer
- 10. Slide 10 Setting UpMySQL • Two masters • It’s HA, but… • Flaky, tricky to work with! Application Server MySQL MasterTwo-way Replication MySQL Master Load Balancer
- 11. Slide 11 MySQL Slave Setting UpMySQL • Two masters, multiple slaves • Only one master active at a time • HA Application Server MySQL Slaves Data Replication reads writes Primary Master 2nd ary Master Master Failover Load Balancer • HA (unless your hosting center goes down)
- 12. Slide 12 Setting UpMySQL • Your HA MySQL architecture will depend on: • SLAs • Application requirements • Load • Physical spread of servers • Budget • Caveats: • Are your apps read or write heavy? • Beware of replication lag time • Common in high-latency networks • Do your apps need the latest data? • Transactions?
- 13. Slide 13 Setting UpMySQL • Load balancing • Hardware? • Software? • Master Failover • On Slaves: CHANGE MASTER TO … • Manual? • Automate? • Cron? Application? SLB? • On App servers: • Connect to Secondary • Not required if using a hot spare w/same ip
- 14. Slide 14 Setting UpMySQL • Recommended reading: • High Performance MySQL - Jeremey Zawodny & Derek Balling • MySQL Replication docs • MySQL Cluster white paper • For Linux: • Linux Virtual Server Project • Linux-HA
- 15. Slide 15 Setting UpMySQL • If you have any trouble: • MySQL mailing list
- 16. High Availability? Setting UpMySQL Our Approach DBI Framework Questions High Availability DBI & MySQL
- 17. Slide 17 Our Approach •Our requirements: • Read-heavy apps • Over 750 clients, many with SLAs • Reliability: • Read availability most important • Must work if a server farm goes down • Speed is of essence: • Millions of requests a day • Don’t need transactions • Minimize costs
- 18.
- 19. Slide 19 Farm 2 Farm3 Farm 1 Our Approach Application Server Application Server MySQL Slave Application Server MySQL Master MySQL 2nd ary MasterFailover In-Farm Data Replication Application Server MySQL Slave MySQL Slaves MySQL Slave MySQL Slaves Query caching
- 20. Slide 20 Our Approach •DBI Framework: • Load balancing • Server selection • Read / Write query? • Failover Application DBI Framework ----------------------------------- snip: how to tell reads from writes? --------------------------------- my ($action, $dbh) = $query =~ /Aselect|A(select|Ashow|Adesc/i ? ("read", $dbh_read) : ("write", $dbh_write); ----------------------------------------------------------------------------------------------------------
- 21. Slide 21 Our Approach •Load balancing • On connect • Weighted server selection • Availability, number of processes, user weights, which farm • MySQL idle timeout • Failover • Connect to next slave • Automatic query retry Application DBI Framework MySQL Slave MySQL Slave reads
- 22. Slide 22 Our Approach •Failover • Persistent connection to all masters • Automatic query retry • Automatic fallback Application Server DBI Framework Primary Master 2nd ary Master MasterFailover writes
- 23. High Availability? Setting UpMySQL Our Approach DBI Framework Questions High Availability DBI & MySQL
- 24. Slide 24 DBI Framework •Two wrapper functions: • dbconnect( 'read' | 'write' ) • Read: Select slave to connect to • Write: Connect to all masters • $sth = sql( $query ) • Read / write dbh selection • Failover & fallback • Pseudo code?
- 25. Slide 25 DBI Framework •dbconnect( 'read' | 'write' ) • Read: For each slave weight = 0, next if can’t ping check number of processes with mysqladmin weight = no. processes / user weighting Connect to slave with lowest weight Sanity check: run a simple query Try next slave if that failed • Write: For each master Connect Sanity check: run a simple query Set $write_dbh to primary master
- 26. Slide 26 DBI Framework •$sth = sql( $query ) • Determine query type… • Read: Execute query. Failover to next slave on error, and retry query. • Write: Fallback? If not using primary, and we failed over X seconds ago, try reconnecting to master. Execute query. Failover to next master on error, and retry query.
- 27. Slide 27 DBI Framework •Major Drawbacks: • Using DBI-based CPAN modules is hard!
- 28. Slide 28 DBI Framework •Looking Ahead… • Push HA logic into the DBI layer • Write DBD::MysqlHA ? • DBIx::HA ? • DBD::Multiplex ? • DBIx::DBCluster ? • SQL Relay ? • MySQL Cluster?
- 29. High Availability? Setting UpMySQL Our Approach DBI Framework Questions High Availability DBI & MySQL
- 30. Slide 30 DBI Frameworkson CPAN • If there’s time…
- 31. Slide 31 DBI Frameworkson CPAN • The ones I know a bit about: • DBIx::HA • DBD::Multiplex • DBIx::DBCluster
- 32. Slide 32 DBI Frameworkson CPAN • DBIx::HA • Generic HA solution (written & tested for Sybase) • Configurable by db name (%DATABASE::conf) • Looks well thought out
- 33. Slide 33 DBIx::HA Pros • DoesFailover • On query failure & dbh disconnected • Does timeouts: • connect, query execute • Safe signals • Connect all dsns on init • Supports Apache::DBI Cons • No read / write distinction • No way to choose which dbh to use on failover • (want to use a mysql- specific algorithm) • Timeouts with SIGALRM • (but how else, really?) • No ping checks for non- Apache::DBI • (uses $dbh->ping anyways - we prefer ICMP ping)
- 34. Slide 34 DBIx::HA • Also:written & tested for Sybase! • Potential DBD::Mysql problems: • auto_reconnect we may reconnect to a db when we should be failing over
- 35. Slide 35 DBI Frameworkson CPAN • DBD::Multiplex • send requests to multiple dsn's • Configure servers to use in $dsn (pipe-separated list)
- 36. Slide 36 DBD::Multiplex Pros • Supportsmaster/slave setup: • differentiates between reads / writes • Connects to all dsns (good for fast master failover) • Does failover: • default behaviour reads: first_success writes: first_error Cons • Can only specify one master • (though if you specify none, writes can go to all with 'first_error' exit mode) • Connects to all dsns • (don't want to connect to all slaves) • No customizable slave failover algorithm • No reconnects • No fallback!
- 37. Slide 37 DBI Frameworkson CPAN • How could we re-use CPAN modules? • DBIx::HA • Sub-class to introduce MySQL specific functionality? • Introduce a callback for server selection on failover? • Use in conjunction with DBD::Multiplex for read/write dbh selection? • There could be problems though… • In general: • Backwards compat • sql() wrapper (for backwards compat) • Custom logging? • Our db wrappers do other things too…
- 38. Slide 38 DBI Frameworkson CPAN • Maybe the best way to reuse them is to nick their ideas? • DBD::MysqlHA ? • Combination of DBIx::HA and DBD::Multiplex • MySQL specific: • Customizeable server selection algorithm • Persistent connections
Editor's Notes
- #3 Start off by asking who knows what HA is, and get a feel for how much experience they’ve got with HA.
- #4 Did your clients notice? Did you have an SLA? Were you in bed at the time? Did you have a backup server? Did you have a backup of the data? How long did it take to get the backup server with the backup data back online?
- #10 HA for reads, but not for writes.
- #11 Due to the way MySQL replication works - conflicting writes (typically auto increment keys) Mention FS replication
- #14 Include further links
- #18 Confirm with andrew on # requests a day.
- #20 Speak about minimizing costs: replication across farms. Query caching run on all slaves out of memory to remove io bottleneck
- #21 S/w Loadbalancing
- #23 Mention Tear down / setup of connections.
- #38 HA dsn: dsn1: Multiplex to primary master + many slaves dsn2: Multiplex to secondary master + many slaves If all slaves go down, will all reads go to master?