List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:August 4 2010 8:41pm
Subject:Re: Moving from one MySQL server to three MySQL servers?
View as plain text  
On Wed, August 4, 2010 11:40, Nunzio Daveri wrote:
> Hello Gurus :-)� I was running a simple load generator against our 16GB
> Dual
> Quad core server and it pretty much came down to it's knees within two
> hours of
> running tests.� The customer DOES NOT WANT to change any code, they just
> want to
> throw hardware at it since it took them a year to create all of the code.�
> It is
> a 140GB database with 21GB of indexs all using InnoDB - currently doing
> 70%
> reads and 30% writes.
>
> My question is what is the best way of distributing the load without
> changing
> any of the php / perl code that their web server uses?� This is what I am
> thinking but need someone to tell me it is a good idea or bad please?
>
> 1. Setup a single master and 2 slaves.� The question is how to tell the
> web
> servers to get all the read data from the slaves and to only write to the
> master?
>
> 2. Install a MySQL proxy box and let mysql proxy handle the load, problem
> is now
> it is the SPOF!
>
> 3. Use DNS round robin, BUT how to tell round robin to ONLY go to master
> for
> writes and ONLY use one of the 2 slaves for reads?
>
As was mentioned, what the test was would help.  Are you using single file
or separate file per table.  If this is a web application, separate the
database onto another server, move to separate files per table and put the
files on separate spindles.  Of course, the real next step is to find
where the actual bottle neck is.  Do you have slow query log enables etc.?
What are the results.  How critical is the consistency between read and
writes.  There will be a lag between the master and slave which may or may
not be critical.

------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154

Thread
Moving from one MySQL server to three MySQL servers?Nunzio Daveri4 Aug
  • Re: Moving from one MySQL server to three MySQL servers?Warren Young4 Aug
  • Re: Moving from one MySQL server to three MySQL servers?Wm Mussatto4 Aug
    • Re: Moving from one MySQL server to three MySQL servers?Prabhat Kumar4 Aug
      • Re: Moving from one MySQL server to three MySQL servers?Andrés Tello5 Aug
  • Re: Moving from one MySQL server to three MySQL servers?Joerg Bruehe5 Aug
  • RE: Moving from one MySQL server to three MySQL servers?Steven Staples5 Aug
    • Re: Moving from one MySQL server to three MySQL servers?Nunzio Daveri5 Aug
      • Re: Moving from one MySQL server to three MySQL servers?Andrés Tello6 Aug
      • Re: Moving from one MySQL server to three MySQL servers?Warren Young9 Aug
      • Re: Moving from one MySQL server to three MySQL servers?Todd Lyons18 Aug