Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

DBI load balancing / high availability: DBIx::HA vs DBD::Multi vs DBD::Multiplex

by andye (Curate)
on Aug 22, 2012 at 17:34 UTC ( #989110=perlquestion: print w/ replies, xml ) Need Help??
andye has asked for the wisdom of the Perl Monks concerning the following question:

Hi fellow monks,

I'm converting an existing web application to a cloud environment.

I need to do failover / load balancing for the MySQL database connection.

One option I'm looking at is doing that from within Perl. (I'm aware that there are other options such as HAProxy etc - they have their pros and cons).

So, Perl module options for load balancing with failover for non-responsive connections seem to be:

Q1: Does anyone know of any other options?

I'm aware that each of these modules is designed for a slightly different scenario, but in fact any of them would fit the bill for my scenario, which is:

  • One connection for writing to the database (failover on this connection is managed by Amazon RDS, so we don't need to worry about that).
  • A set of connections for reading from replicas of the database. These connections need to be load-balanced, and if one of them fails then we need to take it out of the pool.
The existing code gets the database handle from a single library, so it's easy to modify the part where the connection is set up (i.e. my $dbh = whatever()). The part of the app which deals with both reads and writes is separate from the part of the app which just deals in reads (but in a higher volume), so it would be pretty easy to serve them up different database handles, if needed.

I have no experience using any of the above modules; I've been using DBI in general for about 10 years.

The app is running on Apache2, mod_perl2, Apache::DBI.

So, Q2: Does anyone have any experience with any of the above modules which they'd be willing to share?

Thoughts or recommendations?

I see that Tim Bunce is involved with DBD::Multiplex, behind the scenes, and that that module is now on v2. Those things make me think happy thoughts about it. I don't know anything much at all about the maintenance of the other two modules (not to say that it's not great, it may be fantastic, just that I'm ill-informed on this).

Thank you, and Happy Wednesday.

Comment on DBI load balancing / high availability: DBIx::HA vs DBD::Multi vs DBD::Multiplex
Re: DBI load balancing / high availability: DBIx::HA vs DBD::Multi vs DBD::Multiplex
by Anonymous Monk on Aug 22, 2012 at 19:51 UTC
    Your question assumes a great deal about AWS RDS that is not true. RDS handles failure of the database to a remote copy. I doubt if it handles "connection failover." And RDS does not support "replicas of the database." You cannot setup a replication slave with RDS. RDS is intended for simple usage, not complex architectures.
      Actually, Read Replica(s) are now available. You would have to see what the lag is.
      Was referring to these features:
      Multi-AZ Deployments – A deployment option for your production DB Instances that enhances database availability while protecting your latest database updates against unplanned outages. When you create or modify your DB Instance to run as a Multi-AZ deployment, Amazon RDS will automatically provision and manage a “standby” replica in a different Availability Zone (independent infrastructure in a physically separate location). Database updates are made concurrently on the primary and standby resources to prevent replication lag. In the event of planned database maintenance, DB Instance failure, or an Availability Zone failure, Amazon RDS will automatically failover to the up-to-date standby so that database operations can resume quickly without administrative intervention. Prior to failover you cannot directly access the standby, and it cannot be used to serve read traffic.

      Read Replicas – This replication feature makes it easy to elastically scale out beyond the capacity constraints of a single DB Instance for read-heavy database workloads. You can create one or more replicas of a given source DB Instance and serve high-volume application read traffic from multiple copies of your data, thereby increasing aggregate read throughput. Amazon RDS uses MySQL’s native replication to propagate changes made to a source DB Instance to any associated Read Replicas. Since Read Replicas leverage standard MySQL replication, they may fall behind their sources, and they are therefore not intended to be used for enhancing fault tolerance in the event of source DB Instance failure or Availability Zone failure.

      http://aws.amazon.com/rds/mysql/#Multi-AZ

      Not meaning to imply that AWS is the be-all and end-all of this kind of thing - clearly it isn't.

      To get back on track: Does anyone have views / experiences / reviews of the above modules?

Re: DBI load balancing / high availability: DBIx::HA vs DBD::Multi vs DBD::Multiplex
by sundialsvc4 (Monsignor) on Aug 22, 2012 at 20:17 UTC

    Okay, guys ... (and gals...) ... let’s all be sure to rip-off our “Anonymous Monk” cloaks here, and especially, provide details.   References, footnotes, all-of-that.   We durn well know right now that this is going to be a hot topic that a lot of future visitors are gonna look at.   So, let’s make it a point now to do them all a favor.   Let's make its content good from the very start.   Complete and thorough and useful, even three-plus years from now.   (“Why, wasn’t it just yesterday ...?”)

      Lead by example
Re: DBI load balancing / high availability: DBIx::HA vs DBD::Multi vs DBD::Multiplex
by andye (Curate) on Aug 29, 2012 at 11:00 UTC
    use bump;

    nobody?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://989110]
Approved by Perlbotics
Front-paged by davido
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (6)
As of 2014-04-17 02:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (437 votes), past polls