Don't use NFS, bad idea.
You can do the master writer/multi reader, but it's always been a problem making sure every reader is up to date. You need to have a way to verify this manually.
If your really going to max out your platform there's other platforms to look at, but the costs go up FAST. Instead of looking at a 10k machine you start looking at a 100k to 1mil machine, but it all depends on your "needs." If you need to have the data correct that instant, you need it, but for a web profile it might be much more cost effictive to say "updates to your profile may take a few moments" or some such. Sun, HP and IBM make some very good hardware using their own designs (Sparc, PA-RISC and Power4)
-- Michael Conlen
Steven Balthazor wrote:
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/[EMAIL PROTECTED]
