From owner-freebsd-questions@FreeBSD.ORG Mon Jul 7 13:36:31 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 7B8FD106567D for ; Mon, 7 Jul 2008 13:36:31 +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 E979C8FC13 for ; Mon, 7 Jul 2008 13:36:30 +0000 (UTC) (envelope-from m.seaman@infracaninophile.co.uk) Received: from lack-of-gravitas.thebunker.net (gateway.ash.thebunker.net [213.129.64.4]) (authenticated bits=0) by smtp.infracaninophile.co.uk (8.14.2/8.14.2) with ESMTP id m67DZnuA077006 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 7 Jul 2008 14:35:50 +0100 (BST) (envelope-from m.seaman@infracaninophile.co.uk) X-DKIM: Sendmail DKIM Filter v2.6.0 smtp.infracaninophile.co.uk m67DZnuA077006 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=infracaninophile.co.uk; s=200708; t=1215437750; bh=KCyOdwqHrRZwcf WSXq+wZmcmduD2GwQGOQmesZh7uOU=; h=Message-ID:Date:From:MIME-Version: To:CC:Subject:References:In-Reply-To:Content-Type: Content-Transfer-Encoding:Cc:Content-Type:Date:From:In-Reply-To: Message-ID:Mime-Version:References:To; z=Message-ID:=20<48721BB5.1 050203@infracaninophile.co.uk>|Date:=20Mon,=2007=20Jul=202008=2014: 35:49=20+0100|From:=20Matthew=20Seaman=20|Organization:=20Infracaninophile|User-Agent:=20Thunderbird= 202.0.0.14=20(X11/20080609)|MIME-Version:=201.0|To:=20Bob=20McConne ll=20|CC:=20freebsd-questions@freebsd.org|Subject:=2 0Re:=20Why=20would=20it=20make=20such=20a=20difference=20to=20move= 20mysqld=20to=09anothermachine?|References:=20<4870894E.7090708@infracaninophile.co. uk>=09<200807061759.12129.fbsd.questions@rachie.is-a-geek.net>=20|In-Reply-T o:=20|X-E nigmail-Version:=200.95.6|Content-Type:=20text/plain=3B=20charset=3 DUTF-8=3B=20format=3Dflowed|Content-Transfer-Encoding:=207bit; b=Ba c+jJPBLiM8afwCrz0jd9BdQIj7/0Va5wS42VXL+8W/MsI8FccJCTTDgNG+gnd1YZhKs xq/qjVLR7hiM97MeGznTGOjYGd3h9H7ak5ujoNdV6djFNFaM5ba5J44B7Lu6kCV+wtj /vByT1CVOr5XPQedgJ6XwCj+jmUphp/H9e8= Message-ID: <48721BB5.1050203@infracaninophile.co.uk> Date: Mon, 07 Jul 2008 14:35:49 +0100 From: Matthew Seaman Organization: Infracaninophile User-Agent: Thunderbird 2.0.0.14 (X11/20080609) MIME-Version: 1.0 To: Bob McConnell References: <4870894E.7090708@infracaninophile.co.uk> <200807061759.12129.fbsd.questions@rachie.is-a-geek.net> In-Reply-To: X-Enigmail-Version: 0.95.6 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (smtp.infracaninophile.co.uk [81.187.76.162]); Mon, 07 Jul 2008 14:35:50 +0100 (BST) X-Virus-Scanned: ClamAV 0.93.1/7651/Mon Jul 7 11:24:48 2008 on happy-idiot-talk.infracaninophile.co.uk X-Virus-Status: Clean X-Spam-Status: No, score=-2.3 required=5.0 tests=AWL,BAYES_00,DKIM_SIGNED, DKIM_VERIFIED,SPF_FAIL autolearn=no 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 anothermachine? 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: Mon, 07 Jul 2008 13:36:31 -0000 -----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Bob McConnell wrote: | On Behalf Of Mel | On Sunday 06 July 2008 10:58:54 Matthew Seaman wrote: | |>> 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. | | I have not been following this thread closely, and I don't know much | about MySQL, but I do have a question here. Is it possible that the | process of moving the database to the second machine also resulted in | cleaning up (defragmenting?) the files and reordering some tables to | more closely match their indexes? Would this reduce the response time on | the new server, at least until a significant amount of additional data | was added that reverses these effects? Yes, that could be the case if the database was transferred by doing mysqldump on the first machine and then loading the dump on the second. It wouldn't be the case if the data were transferred just by tarring up the DB data directory and copying it over. You can achieve the same effect by running 'OPTIMIZE TABLE foo;' from within MySQL or using 'mysqlcheck --optimize ...'. However, the scale of improvement described is rather more than can generally be achieved by table optimization. Cheers, Matthew - -- Dr Matthew J Seaman MA, D.Phil. Flat 3 ~ 7 Priory Courtyard PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate ~ Kent, CT11 9PW, UK -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEAREDAAYFAkhyG7UACgkQ3jDkPpsZ+VYDJwCgtaV/BOE+YqGICUEVht3y40Ga 7oAAn2nQOZjZ7oJsSXAi4KsoNXi8R0Vh =efqJ -----END PGP SIGNATURE-----