From owner-freebsd-questions@FreeBSD.ORG Sun Jul 6 15:56:20 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 2FAA01065678 for ; Sun, 6 Jul 2008 15:56:20 +0000 (UTC) (envelope-from jalmberg@identry.com) Received: from mx1.identry.com (on.identry.com [66.111.0.194]) by mx1.freebsd.org (Postfix) with ESMTP id A46C18FC18 for ; Sun, 6 Jul 2008 15:56:19 +0000 (UTC) (envelope-from jalmberg@identry.com) Received: (qmail 45654 invoked by uid 89); 6 Jul 2008 15:56:18 -0000 Received: from unknown (HELO ?192.168.1.110?) (jalmberg@75.127.142.66) by mx1.identry.com with ESMTPA; 6 Jul 2008 15:56:18 -0000 Mime-Version: 1.0 (Apple Message framework v752.3) To: freebsd-questions@freebsd.org Message-Id: <7B56BE54-2EE8-40F3-AE1B-8C299A9FA81B@identry.com> References: <67A42EB4-4794-4F21-AF9F-123C1DEE8A39@identry.com> From: John Almberg Date: Sun, 6 Jul 2008 11:56:17 -0400 X-Mailer: Apple Mail (2.752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Content-Transfer-Encoding: 7bit X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Subject: Fwd: Why would it make such a difference to move mysqld to another machine? X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 06 Jul 2008 15:56:20 -0000 On Jul 6, 2008, at 4:58 AM, Matthew Seaman wrote: > John Almberg wrote: >> Luckily, I have a pretty powerful machine sitting right next to my >> main webserver that I mainly use for backup. The two servers are >> directly connected to each other with a twisted ethernet cable, >> using extra NIC cards in the machines, so they have a fast, >> dedicated 'LAN' to share. >> Desperate, I moved mysqld to this other machine, so basically this >> second machine became a dedicated database server. >> The improvement this change made seems out of proportion. Both >> machines are now cruising with extremely low load averages and the >> WCPU for the mysqld instance on the new machine is practically zero. >> I'm not complaining. Problem solved. But I am scratching my head >> over how mysql could be getting crushed on the first, 8 core/8G >> machine, but running cool as a cucumber on the second, 2-core/2G >> machine??? > > Database performance is critically dependant on the underlying Disk > IO sub- > systems. > > There's two things to consider here: > > * What's the difference in disk hardware between the two machines? > > * How well does the database compete with apache for disk IO when > they are sharing the same drives? > > In the first case, you'll see very big improvements in MySQL > performance > if you use disks with plenty of IO cache memory on them, plus disks > that > work with controllers that support tagged queuing (SAS and higher > end SATA > generally.) DB disk IO consists primarily of a large number of small > transactions randomly distributed across the drive. Reducing the > latency > of each of those transactions gets you the best improvement in > performance. > RAID levels are important: single disks are not bad if dedicated to > the DB. > RAID1 mirrors are fine. RAID10 striped mirrors are excellent. > RAID5 not > so good. If you're willing to spend money, hardware RAID > controllers help. > Hardware RAID controllers with Battery Backup Units (so you can > safely turn > on write caching) help a very great deal. The first box has 2 independent raid arrays, with an Intel hardware raid controller with battery backup. But apache and mysql were probably on the same array. I have to confirm this. The second box has 2 drives with raid 1, from an integrated controller (i.e, software raid). So the second box is much less powerful, but running mysqld is all it's doing. I guess that is the primary difference. My main worry about the second box is that it only has 1G of RAM (not 2 as I mis-remembered before checking specs.) Easily expanded, but it's something to watch. Bottom line, I might do better by moving mysqld back to the first machine, but putting the data on the second, raid 10 array (which is more or less empty at the moment.) I appreciate your insight. This is starting to make sense to me. Brgds: JOhn ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Websites for On-line Collectible Dealers ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Identry, LLC John Almberg (631) 546-5079 jalmberg@identry.com www.identry.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~