Load Balanced Mysql Cluster

[Jared Saul]

 I have a database heavy site that utilizes a master mysql server which 
processes all updates and replicates to 4 slave servers that handle the 
queries.   Right now I have a simple perl randomized selection that 
determines which of the four database slaves will receive any given 
query.  This works but is very limited and not fault tolerant at all.  In 
particular I have periodic (and unexplained) server load spikes and 
continued requests to a spiking server results in a rapid site wide 
downward spiral of hung connections.... another topic for another 
post.  But even a simple hardware failure would not be tolerated in the 
current scheme.

Anyway,  my hope was to utilize one of our Alteon Ace  Director 3 load 
balancers (AD3) to perform intelligent database load balancing.  I have 
found that this doesn't work in an obvious manner with the following 
complications:

1) There are no suitable health checks and tcp health checks result in many 
half-open connections (seem to hang on authentication) that ultimately max 
out the connections on the DB servers. ICMP work better but also causes 
sporadic hung connections and other bizzarre behaviors.  I have considered 
scripted http health checks against a web page that ran a script that 
monitored the database but that seems convoluted.

2) The use of persistent database connections (via. mod_perl) becomes a 
nightmare with each server receiving (total_apache_processess)x(number 
apache servers) persistant connections.  Such that, with 3 apache servers 
running 100-200 processes each all of the 4 mysql servers maintains 300-600 
persistant connections.  This quickly gets out of control.  Note, this 
issue exists regardless of load balancing methodology.

Well, I've contemplated this situation for months and it's really driving 
me rather nuts.  Any help is much appreciated.

Thanks-



Jared



---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php