List:General Discussion« Previous MessageNext Message »
From:shawn green Date:April 2 2012 9:25pm
Subject:Re: HA & Scalability w MySQL + SAN + VMWare: Architecture Suggestion
Wanted
View as plain text  
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.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedWes Modes30 Mar
  • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedRick James30 Mar
  • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedKaren Abgarian30 Mar
  • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedshawn green30 Mar
    • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedWes Modes2 Apr
      • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedRick James2 Apr
      • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedshawn green2 Apr
        • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedWes Modes2 Apr
          • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture Suggestion WantedTim Dunphy3 Apr
          • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedIan3 Apr
            • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture Suggestion WantedJohan De Meersman3 Apr
            • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedWes Modes3 Apr
            • Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedWes Modes4 Apr
        • Re: Replication rings/maatkit (was: HA & Scalability w MySQL + SAN+ VMWare)Wes Modes3 Apr
          • Re: Replication rings/maatkit (was: HA & Scalability w MySQL + SAN + VMWare)Eric Bergen3 Apr
Re: HA & Scalability w MySQL + SAN + VMWare: Architecture SuggestionWantedWes Modes4 Apr