From owner-freebsd-questions@FreeBSD.ORG Sun Jul 6 08:59:10 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 258D81065676 for ; Sun, 6 Jul 2008 08:59:10 +0000 (UTC) (envelope-from m.seaman@infracaninophile.co.uk) Received: from smtp.infracaninophile.co.uk (gate6.infracaninophile.co.uk [IPv6:2001:8b0:151:1::1]) by mx1.freebsd.org (Postfix) with ESMTP id 87FE88FC12 for ; Sun, 6 Jul 2008 08:59:09 +0000 (UTC) (envelope-from m.seaman@infracaninophile.co.uk) Received: from happy-idiot-talk.infracaninophile.co.uk (localhost [IPv6:::1]) (authenticated bits=0) by smtp.infracaninophile.co.uk (8.14.2/8.14.2) with ESMTP id m668x2xm005348; Sun, 6 Jul 2008 09:59:03 +0100 (BST) (envelope-from m.seaman@infracaninophile.co.uk) X-DKIM: Sendmail DKIM Filter v2.6.0 smtp.infracaninophile.co.uk m668x2xm005348 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=infracaninophile.co.uk; s=200708; t=1215334743; bh=U0kYCV7yjRJL+4 9bC/q9PsM2utRt5rbtb6mYIBOcrYc=; h=Message-ID:Date:From:MIME-Version: To:CC:Subject:References:In-Reply-To:Content-Type:Cc:Content-Type: Date:From:In-Reply-To:Message-ID:Mime-Version:References:To; z=Mes sage-ID:=20<4870894E.7090708@infracaninophile.co.uk>|Date:=20Sun,=2 006=20Jul=202008=2009:58:54=20+0100|From:=20Matthew=20Seaman=20|Organization:=20Infracaninophile|User -Agent:=20Thunderbird=202.0.0.14=20(X11/20080607)|MIME-Version:=201 .0|To:=20John=20Almberg=20|CC:=20freebsd-ques tions@freebsd.org|Subject:=20Re:=20Why=20would=20it=20make=20such=2 0a=20difference=20to=20move=20mysqld=20to=20another=0D=0A=20machine ?|References:=20| In-Reply-To:=20|X -Enigmail-Version:=200.95.6|Content-Type:=20multipart/signed=3B=20m icalg=3Dpgp-sha256=3B=0D=0A=20protocol=3D"application/pgp-signature "=3B=0D=0A=20boundary=3D"------------enigE32958F99AD96DA651039451"; b=ALuOGqHKHeIKp0DR8MFBvB0zYaYlEcO4xuQ5CHet3R9oivFtJAVZMu3s0qFOFvGRB O0qiYIt90AoC1v+YlAv3rpG8xE8lTX13U/9KqQf4LXzqTGPwWklPJFci8rxoCBNKW5b 1D8taZdAgjpu82/+9unzUavAH0L/jvPzwsJpN00= Message-ID: <4870894E.7090708@infracaninophile.co.uk> Date: Sun, 06 Jul 2008 09:58:54 +0100 From: Matthew Seaman Organization: Infracaninophile User-Agent: Thunderbird 2.0.0.14 (X11/20080607) MIME-Version: 1.0 To: John Almberg References: In-Reply-To: X-Enigmail-Version: 0.95.6 Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------enigE32958F99AD96DA651039451" X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (smtp.infracaninophile.co.uk [IPv6:::1]); Sun, 06 Jul 2008 09:59:03 +0100 (BST) X-Virus-Scanned: ClamAV 0.93.1/7644/Sun Jul 6 06:14:00 2008 on happy-idiot-talk.infracaninophile.co.uk X-Virus-Status: Clean X-Spam-Status: No, score=-3.0 required=5.0 tests=AWL,BAYES_00,DKIM_SIGNED, DKIM_VERIFIED,NO_RELAYS autolearn=ham version=3.2.5 X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on happy-idiot-talk.infracaninophile.co.uk Cc: freebsd-questions@freebsd.org Subject: Re: 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 08:59:10 -0000 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigE32958F99AD96DA651039451 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable John Almberg wrote: =20 > Luckily, I have a pretty powerful machine sitting right next to my main= =20 > webserver that I mainly use for backup. The two servers are directly=20 > 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. >=20 > Desperate, I moved mysqld to this other machine, so basically this=20 > second machine became a dedicated database server. >=20 > The improvement this change made seems out of proportion. Both machines= =20 > are now cruising with extremely low load averages and the WCPU for the = > mysqld instance on the new machine is practically zero. >=20 > I'm not complaining. Problem solved. But I am scratching my head over=20 > how mysql could be getting crushed on the first, 8 core/8G machine, but= =20 > running cool as a cucumber on the second, 2-core/2G machine??? Database performance is critically dependant on the underlying Disk IO su= b- 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 SAT= A 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 performanc= e.=20 RAID levels are important: single disks are not bad if dedicated to the D= B. RAID1 mirrors are fine. RAID10 striped mirrors are excellent. RAID5 not= so good. If you're willing to spend money, hardware RAID controllers hel= p. Hardware RAID controllers with Battery Backup Units (so you can safely tu= rn on write caching) help a very great deal. In the second case, it's really just about competition for resources. I suspect that you could have achieved a pretty good speed-up simply by adding another hard drive to your server and moving all of the database onto it, separate from the web root and any other areas which apache would be doing a lot of read/write operations on. Cheers, Matthew --=20 Dr Matthew J Seaman MA, D.Phil. 7 Priory Courtyard Flat 3 PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate Kent, CT11 9PW --------------enigE32958F99AD96DA651039451 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEAREIAAYFAkhwiVYACgkQ8Mjk52CukIwt7ACeN/gyGhW3aO8br5xRauFrnIGu A+EAn2H07z7wZ9shE+XaMoJ7iDQODp/X =RUat -----END PGP SIGNATURE----- --------------enigE32958F99AD96DA651039451--