From owner-freebsd-questions@FreeBSD.ORG Wed Feb 2 02:21:53 2005 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id F3D2016A4CE; Wed, 2 Feb 2005 02:21:52 +0000 (GMT) Received: from server1.ultratrends.com (S01060004e20310fa.rd.shawcable.net [70.65.87.221]) by mx1.FreeBSD.org (Postfix) with ESMTP id D348543D2F; Wed, 2 Feb 2005 02:21:51 +0000 (GMT) (envelope-from trodat@ultratrends.com) Received: from server1.ultratrends.com (localhost [127.0.0.1]) j122Lotw061547; Tue, 1 Feb 2005 19:21:50 -0700 (MST) (envelope-from trodat@ultratrends.com) Received: from localhost (trodat@localhost)j122LoWs061544; Tue, 1 Feb 2005 19:21:50 -0700 (MST) (envelope-from trodat@ultratrends.com) X-Authentication-Warning: server1.ultratrends.com: trodat owned process doing -bs Date: Tue, 1 Feb 2005 19:21:50 -0700 (MST) From: Technical Director To: Drumslayer In-Reply-To: <20050202003824.12705.qmail@web42107.mail.yahoo.com> Message-ID: <20050201190017.D61397@server1.ultratrends.com> References: <20050202003824.12705.qmail@web42107.mail.yahoo.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Milter: Spamilter (Reciever: server1.ultratrends.com; Sender-ip: 127.0.0.1; Sender-helo: server1.ultratrends.com;) cc: FreeBSD Database cc: questions Subject: Re: MySql Load balancing Solutions? X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 02 Feb 2005 02:21:53 -0000 Drumslayer, I am part of a team running MySQL 4.1.X on 5 machines in a replication setup. Our first way to help manage load is the use of useful rules in our connection classes to direct "W"rites to our big server with fast I/O and memory and directing "R"reads to our slower I/O less RAM slaves only. This one step in itself has done a LOT for keeping uptimes high and queries fast. A positive advantage is that the 5 machines allows us the opportunity to change the configuration if say one fails we can promote another slave to take that position or in the case of the "W"rite server we can promote a slave to a "W"rite server until the original "W"rite server can be recovered. As well whether you use C/C++, Java, PHP or some other scripting language to access your database it shouldn't be too hard to write some sort of algorithm in your connection to spread the connections across your host base. When it comes to management I won't lie, 4.0.XX's handling of Replication was tough. Since though we've made the move to 4.1.X our problems have become less and less. A final advantage to having seperate machines in a replication setup is the ability to upgrade a segment or machine to a newer MySQL version to see how it will operate on your hardware/OS and with your programs. We did this with our move from 4.0.XX to 4.1.X by taking 2 slaves out of the main loop, promoting one to the new 4.1.X master and the other slave to a new 4.1.X slave. After testing in pre-production we proceeded with the deployment on our other 3 boxes. INFO: Our 5 machine replication setup consists of: 1) 1 - 4 x P4 Xeon Compaq Server ("W"rite DB Server) 2) 4 - 1 x P3 Compaq Servers ("R"ead DB Server) NOTE: On a smaller scale on my home network I do the same on three machines all sub-server class. I still have great reliability and "robust" performance from such a simple design. I hope this information is helpful, I know it works well for us. Rob. On Tue, 1 Feb 2005, Drumslayer wrote: > Hi > I have been running a fairly heavy duty server for > MySQL on FreeBSD but its starting to peak. I would > like to know what others have done as far as using a > load balancing solution for MySQL or their success > with replication. > Also has anyone done a 64 bit build of MySQL on > FreeBSD successfully? > > Thanks! > > M. > > > > > > __________________________________ > Do you Yahoo!? > The all-new My Yahoo! - What will yours do? > http://my.yahoo.com > _______________________________________________ > freebsd-questions@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-questions > To unsubscribe, send any mail to "freebsd-questions-unsubscribe@freebsd.org" >