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"| | | <[EMAIL PROTECTED]| | | otmail.com> | | | | | | 29/06/2003 17:47 | | | | |---------+-----------------------------> >------------------------------------------------------------------------------------------------------------------------------| | | | To: <[EMAIL PROTECTED]> | | 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
