[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: NFS or replication?
From:       Alec.Cawley () Quantel ! Com
Date:       2003-06-30 9:35:08
[Download RAW message or body]


I am mildly surprised that nobody has answered this yet - bit you did post
on the weekend - so I will put in my $0.02.

On http://www.mysql.com/doc/en/Multiple_servers.html your NFS idea is
described as "a bad idea". I concur. I believe that MySQL makes a lot of
use of cacheing - locking updates would be a horrendous problem.

Having looked at the same situation myself, I think that there is an
inherent limitation in the MySQL architecture that performance is
"redlined" by the need to funnel all writes through asdingle master update
box. However, given the number of CPUs you can cram into a box nowadays,
and the speed of disks/raids, I think that is a pretty high redline. Have
you estimated (a) how close you are to this rexdline today, and (b) how
close your current machine is to the best you can buy today? However, when
you hit that redline, the write master, read slaves option still gives you
a way out.

I was about to agree that it would be nice to have a tool to measure the
read/write ratio of a running system, then realised that the information it
yielded would probably not be very valuable. The cost of both inserts and
selects can vary so widely (indexs slowing inserts, speeding selects) that
the raw figures would be useless.,The only way to find out the relative
lod, as opposed to number, of reads vs writes would be to do the split into
read and write servers (work you will hae to do anyway) and then measure
the effective load with one of each.

With regard to your synchronization problem, the simplest way is
undoubtedly to do what you say - rout ethe read-after-write to the write
server. An alternative would be to ask the master its binlog position
immediately after the update, then poll the slave until it has caught up,
using SHOW MASTER/SLAVE STATUS. In my (limited) experience, replication is
pretty fast, so the percieved delay should be small.. (If it isn't, I would
see that as a sign that something was oing wrong.)

      Alec




> ---------+----------------------------->
> > "Steven Balthazor"|
> > <stevenbalthazor@h|
> > otmail.com>       |
> > > 
> > 29/06/2003 17:47  |
> > > 
> ---------+----------------------------->
  >------------------------------------------------------------------------------------------------------------------------------|
  |                                                                                   \
|  |       To:       <mysql@lists.mysql.com>                                          \
|  |       cc:                                                                        \
|  |       Subject:  NFS or replication?                                              \
|  >------------------------------------------------------------------------------------------------------------------------------|





I am interested in any thoughts that people may have for creating a
scalable mysql infrastructure.
I have a web application which runs on several front end web servers which
hit one backend mysql
server.  Presently I can continue to grow by adding front end webservers --
the mysql server is not
close to maxed out.  Looking toward the future I will have to make a
decision about how to grow the
mysql serving capability and have several ideas on how to do it.

Now some questions for the group:
1.  I can guess that my select to insert/update ratio is probably on the
order of 4:1 but is there a
simple tool to use to determine the actual ratio.
2.  When I want to scale up the mysql server what are the pros/cons of each
of the following:
             a.  Create an NFS server on the backend and load balance
several mysql servers all accessing
the same database files via NFS (is this even possible/desirable?)
             b.  Make one big server the primary insert/update server and
replicate the data out to many
read-only slaves (at what ratio of read to writes in conjunction with
number of slaves does this
start to limit scalability).
             c.  Buy one big monster server every year and hope to stay
ahead of my needs (and have the
previous years machine as a backup)
3.  With a replication strategy how does one make sure that the current
information is displayed to
a user?   For example, a frequent action in a web application is to update
information in a user's
profile.  Typically the way this is done is for the user to enter the
information into a web form
submit the form and then the user gets a page with the current data
displayed in a read-only format
(so the user knows the update was successful).  How do most people handle
this to make sure that the
current data is displayed?  Do you just perform the select from the write
server for this one case?
Or is replication fast enough that performing the select from one of the
slaves is ok?
4.  Replication (choice b) seems to be the preferred way to go, based on
what I have seen on the
list; is there a reason why NFS is not an option?  Also is the choice
determined by the type of
database (InnoDB vs. MyIsam).

I am interested in any comments/experience people may have on this issue.
I have many thoughts of
my own regarding ease of maintenance, backup, reliability, ease of
expansion, cost, performance,
etc.  However I have not had time or hardware to test the different
possibilities and would greatly
appreciate hearing what others have to say.

Thank you for your comments,
Steven Balthazor


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=alec.cawley@quantel.com







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com


[prev in list] [next in list] [prev in thread] [next in thread]