List:General Discussion« Previous MessageNext Message »
From:shawn green Date:March 30 2012 10:56pm
Subject:Re: HA & Scalability w MySQL + SAN + VMWare: Architecture Suggestion
Wanted
View as plain text  
Hello Wes,

On 3/29/2012 9:23 PM, Wes Modes wrote:
> First, thank you in advance for good solid suggestions you can offer. I
> suppose someone has already asked this, but perhaps you will view it as
> a fun challenge to meet my many criteria with your suggested MySQL
> architecture.
>
> I am working at a University on a high-profile database driven project
> that we expect to be slammed within the first few months. Since this is
> a new project and one that we expect to be popular, we don't know what
> kind of usage to expect, but we want to be prepared. Therefore, we are
> building in extra capacity.
>
> Our top goals are scalability and high availability, provided we hope
> through multiple MySQL nodes and VMWare functionality. I've been
> surprised that there are not more MySQL architects trying to meet these
> high-level goals using virtualization and shared storage (or at least
> they do not seem to be writing about it).
>
> I've looked at replication, multi-mastering, DRBD, clustering,
> partitioning, and sharding.
>
> Here's what we got, and some of our constraints:
>
> * We are concerned that One Big Database instance won't be enough to
> handle all of the queries, plus it is a single point of failure.
> Therefore, multiple nodes are desirable.
>
> * With the primary application that will be using the database, writes
> and reads cannot be split off from each other. This limitation alone,
> rules out replication, MMM, and a few other solutions.
>
> * We do not expect to be especially write-heavy.
>
> * We have shared storage in the form of an iSCSI SAN. We'd like to
> leverage the shared storage, if possible.
>
> * We have VMWare HA which already monitors hosts and brings them up
> within minutes elsewhere if we lose a host. So some of the suggested HA
> solutions are redundant.
>
> * We expect to have another instance of our system running in the Amazon
> cloud for the first few months while the traffic is high, so we may take
> advantage of RDS, though an exact duplicate of our local system will
> save us development work.
>
> Thanks for any advice you can give.
>
> Wes Modes
>

As the others have already pointed out, your specifications are rather 
vague and appear to be only hardware-focused. I can state with some 
certainty, through my six years of direct MySQL support experience, that 
the majority of the problems related to performance are NOT hardware 
related.  The majority of the problems I need to resolve are related to 
bad queries, bad table design, and bad application design.

For example, you state:
 > * With the primary application that will be using the database, writes
 > and reads cannot be split off from each other.

This is already a red flag that your application is not designed for scale.

Also, you say:
 > * We are concerned that One Big Database instance won't be enough to
 > handle all of the queries, plus it is a single point of failure.
then you say:
 > * We have shared storage in the form of an iSCSI SAN. We'd like to
 > leverage the shared storage, if possible.

This is another red flag in that your 'shared storage' once again 
becomes a single point of failure.  Also, MySQL instances do not share 
files. Period. Each MySQL instance needs its own, completely 
independent, set of files and folders. Please take the hint that 
Replication should be in your very near future.

Here are some solid suggestions:

a) modularize your code so that WRITES can go to a MASTER server and 
READS can be distributed over a large number of SLAVE servers.

b) modularize your data so that you can easily subdivide subsets of it 
to different master-slave trees. For example, students whose last name 
starts with A-D are assigned to machines in group1, E-K in group2, ... etc.

c) Optimize your queries to avoid subqueries and to take full advantage 
of indexes.

d) Build indexes wisely -  A well-crafted multi-column index can take up 
less space and will be more functional that multiple single-column indexes.

e) Test, test, and retest - Until you can push your system to the 
choking point in the lab, you will have no idea of how much traffic it 
will be able to handle in the field.

Good luck!
-- 
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