List:General Discussion« Previous MessageNext Message »
From:Prabhat Kumar Date:August 4 2010 9:19pm
Subject:Re: Moving from one MySQL server to three MySQL servers?
View as plain text  
>
> 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?
>

Replication is not an answer to all performance problems. Although updates
on the slave are more optimized than if you ran the updates normally, if you
use MyISAM tables, table-locking will still occur, and databases under
high-load could still struggle.

Replication is not a guarantee that the slave will be in sync with the
master at any one point in time. Even assuming the connection is always up,
a busy slave may not yet have caught up with the master, so you can't simply
interchange SELECT queries across master and slave servers.

On Thu, Aug 5, 2010 at 2:11 AM, Wm Mussatto <mussatto@stripped> wrote:

> 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
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Best Regards,

Prabhat Kumar
MySQL DBA

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

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