Why not use a load balancer such as HA/Proxy as a way of maintaining high availability on your mysql nodes?
http://blogs.reliablepenguin.com/2011/03/31/mysql-load-balancing-with-haproxy http://linuxadminzone.com/how-to-install-setup-and-config-haproxy-loadbalancer-for-content-switching/ http://agiletesting.blogspot.com/2010/10/mysql-load-balancing-with-haproxy.html I've done this a few times in the past over the years and (depending on your setup) the combination of HA/Proxy can be dead simple to setup and rock solid in terms of reliability. Regards, tim ----- Original Message ----- From: "Wes Modes" <wmo...@ucsc.edu> To: mysql@lists.mysql.com Sent: Monday, April 2, 2012 7:47:18 PM Subject: Re: HA & Scalability w MySQL + SAN + VMWare: Architecture Suggestion Wanted Thanks again for sharing your knowledge. I do believe the answers I've receiving, but since I have requirements that I cannot easily alter, I'm also gently pushing my expert advisers here to look beyond their own preferences and direct experience. RE: Shared storage. I can easily let go of the preference to take advantage of shared storage. I understand duplicated databases are the essence of database redundancy. You make good points. In terms of the acceptability of a small fraction of users being temporarily unable to access services: rather than sharding, which again requires more control over the application than we have, I was more envisioning that would be the fraction of users who hit the one peer MySQL server that is temporarily unavailable due to h/w or s/w failure or DB corruption while its fail over is powered up. Does MySQL cluster seem like it will address my requirements to allow us to horizontally scale a number of MySQL nodes as peers without separating reads and writes, or slaves and masters. Wes On 4/2/2012 2:25 PM, shawn green wrote: > Hello Wes, > > On 4/2/2012 4:05 PM, Wes Modes wrote: >> Thanks Shawn and Karen, for the suggestions, even given my vague >> requirements. >> >> To clarify some of my requirements. >> >> *Application: *We are using an open-source application called Omeka, >> which is a "free, flexible, and open source web-publishing platform for >> the display of library, museum, archives, and scholarly collections and >> exhibitions." Without getting into how free (or scalable) free software >> really is, we can view it as one aspect we cannot change, having been >> written into the grant requirements we received for the project. >> Experienced Omeka developers and our own developer have suggested >> that/it is not feasible to separate database writes from reads in the >> application/ (given time and resources). >> > > That's a shame. Sounds like you are back to one big server or several > smaller servers with in-program sharding. > >> *SAN: *The SAN is a Dell EqualLogic 6100 which has redundant everything, >> including multiple NICs, controllers, and power. So we are less >> concerned about the SAN being a SPoF. On the other hand, if we have a >> single big MySQL server that fails, we could bring up another copy of it >> via VMWare, but until the server came up, the application would be dead >> in the water. If the database is corrupted, service will be interrupted >> for a considerable time. >> > > Again, each MySQL instance needs it's own copy of the data. Having > only one big powerful disk system means that each instance you fire up > must both share spindles and networking to access its data. Just like > a freeway at rush hour, you may find the traffic into and out of this > one device crawling to a halt exactly when you don't want it to. > >> *High Availability:* It sounds like there is some debate over how to >> provide HA best, but do people really disagree on the desired results? >> Without getting into the many meanings of this buzz word, here's what we >> mean: /We desire to maintain high availability of service, allowing a >> small fraction of users to experience outage for only seconds at a >> time. We desire to provide this through horizontal scaling, redundancy, >> failover planning, and external monitoring. / >> > > "Small fraction of users" - this implies data sharding. Multiple MySQL > instances each with enough data to operate independently for one slice > of your most important data and an application smart enough to know > which shard to go to for each slice of data. > > "For a few seconds at a time" - you do not want a shared disk. Should > the active MySQL die, it's data will be in an inconsistent state. Once > you fire up the passive daemon it will need to perform a recovery > restart. This down time is more than likely not going to take only a > few seconds. The more data you have, the longer the checks will take. > An independent copy maintained by a slave instance, provides a > logically consistent copy of the master's data as it will only > replicate complete transactions. > > "horizontal scaling" - one master, multiple slaves. This requires the > separation of writes and reads. > > >> *Scalability: *Again, seems like there are lots of applications and >> implementation, but people agree on the general concept. Here's what we >> mean for this project: /We desire to scale our services so that a >> usage surge does not cause unavailability of the services for some >> users. We prefer to horizontally increase scalability using >> load-balancing strategies to treat clusters of servers as single logical >> units./ >> > > "Horizontal scalability" - see above > > "clusters of servers" - unless they share a common data set (which > cannot happen via file sharing) they will not be useful as a logical > unit. Multiple slaves all replicating from the same master does match > this requirement. > >> The application may have not been designed with great scalability in >> mind, but if multiple application instances are accessing multiple >> database servers treated as one logical unit, that may not be too >> relevant. >> > > "logical unit" - see above > "multiple database servers" - remember! each instance of MySQL > requires its own set of data. No sharing is allowed. > >> I am responsible for creating an architecture upon which this project >> will run. I am not responsible for redesigning the application. So >> far, no one has suggested anything that approached meeting our >> requirements, even our vague ones. Perhaps I am asking the wrong list? >> > > No, you are asking the correct list. You are just not believing our > answers. The application design is your stumbling block. It is written > to function only with one large database (SPOF). Until you can fix > that (as painful as it may be), you cannot achieve your availability > goals. > > >> Does anyone have any experience with MySQL Multi-Master Replication? >> Perhaps that should be a separate post. >> > > Replication rings are possible but you must design your application to > take special care to NOT update the same row in multiple nodes of the > ring at the same time. This is even harder to design and code for than > splitting writes/reads to master/slaves. > > Also the loss of one node of a replication ring is not as easy to > recover from as simply promoting one slave to become the new master of > a replication tree (demoting the recovered former-master to become yet > another slave) as there may be pending events in the relay logs of the > lost node that have not yet been relayed to the downstream node. > > I may not have every answer, but I have seen nearly every kind of > failure. Everyone else is encouraged to add their views to the > discussion. > -- Wes Modes Systems Designer, Developer, and Administrator University Library ITS University of California, Santa Cruz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
