> > 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 <mussa...@csz.com> 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=aim.prab...@gmail.com > > -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
