From owner-freebsd-questions@FreeBSD.ORG Mon Dec 13 15:10:09 2004 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 76FFE16A4CE for ; Mon, 13 Dec 2004 15:10:09 +0000 (GMT) Received: from yggdrasil.interstroom.nl (yggdrasil.interstroom.nl [80.85.129.11]) by mx1.FreeBSD.org (Postfix) with ESMTP id D310443D4C for ; Mon, 13 Dec 2004 15:10:08 +0000 (GMT) (envelope-from o.greve@axis.nl) Received: from ip102-211.introweb.nl ([80.65.102.211] helo=sjees) by yggdrasil with esmtp (Exim 3.35 #1 (Debian)) id 1Cdrpq-0003sG-00; Mon, 13 Dec 2004 16:10:02 +0100 From: "Olaf Greve" To: Date: Mon, 13 Dec 2004 16:10:17 +0100 Message-ID: <00bd01c4e125$dae496f0$1e01a8c0@sjees> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 Importance: Normal In-Reply-To: <20041205125807.I84999@pukruppa.net> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2742.200 X-MailScanner-Information: Interstroom virusscan, please e-mail helpdesk@interstroom.nl for more information X-MailScanner-SpamCheck: Subject: How to distribute MySQL over various machines (or otherwise up its performance)? 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: Mon, 13 Dec 2004 15:10:09 -0000 Hi, I have been asked to assist in a most interesting challenge: getting rid of a Win2K server (running MySQL) on which MySQL takes up around 100% of the CPU. :) I have near total freedom in suggesting a replacement architecture (within some reasonable finacial limits, of course), and I am considering suggesting a solution that involves one or more FreeBSD MySQL DB servers, in order to speed up the database performance. Now, there are various strategies that spring to mind, and I was hoping someone could perhaps tell me some more about this from personal experience or hearsay. Regardless of what the eventual suggestion will be, first I'll tune the current DB by assigning a proper DB scheme and by properly using indexes. I've got a gut feeling that these guys set up the DB without paying proper attention to that (in this case probably due to a lack of experience with this), so hopefully a lot can already be gained by doing so. Nonetheless: for setting up a more robust and fast DB server (or server cluster?) I'd like to take matters a step further, by using a fast hardware set-up as well (note: in any and all proposed architecture, I'll propose to use plenty of memory). Now, here comes the bit with which I do not have any experience, so I'm hoping perhaps someone can help me getting started on the proper path. The following is what I'm considering as potential steps: -The guys for whom this will be done mentioned having acquired 64-bit motherboards (I do not yet know the exact type), they do not mind installing multiple processors on it. Question: which FreeBSD version can best be used in order to optimally make use of a 64-bit and/or multi processor architecture? -RAID: for performance and security matters, I _think_ a RAID 10 architecture would be a very good choice. By using a proper U320 SCSI hardware set-up, running in RAID 10 mode, I think much can be gained. Cost is somewhat of an issue, but not all too much. I'm considering the Adaptec 2200S RAID controller, with 15K U320 drives (like the Maxtor 15K 36 GB drives or so). Questions: does it really make sense to use U320 (and 15K instead of 10K) instead of U160? I'm not certain what the speed is of the PCI slots that are present on the motherboards that are to be used, but am I right that if it's the normal speed (being 133MHz), that virtually nothing is gained by using U320 over U160 (as U160 would then already be faster than the bus speed anyway)? Also: does anyone have an insight in actual DB performance gain by using striping? RAID 0 is not an option, it'll have to be fault tolerant. I'm somewhat suspicious of RAID 50 and the likes, hence the idea of using RAID 10... -Using multiple machines. Questions: is there something like a 'MySQL load balancer'? Is this a good idea at all, or will a fast machine (e.g. dual processor) + enough memory (1GB? 2GB?) + a fast hardware RAID 10 set-up already be more than sufficient to do the job? I realise this is a long mail, so sorry for that. :) I hope someone can give me some good pointers and/or other general information for how to best handle this... Thanks in advance, and cheerz! Olafo