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]