From owner-freebsd-stable@FreeBSD.ORG Tue Oct 24 11:52:21 2006 Return-Path: X-Original-To: stable@freebsd.org Delivered-To: freebsd-stable@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 7A0AD16A40F for ; Tue, 24 Oct 2006 11:52:21 +0000 (UTC) (envelope-from gavin.atkinson@ury.york.ac.uk) Received: from mail-gw4.york.ac.uk (mail-gw4.york.ac.uk [144.32.128.249]) by mx1.FreeBSD.org (Postfix) with ESMTP id E516343D55 for ; Tue, 24 Oct 2006 11:52:09 +0000 (GMT) (envelope-from gavin.atkinson@ury.york.ac.uk) Received: from buffy.york.ac.uk (buffy-128.york.ac.uk [144.32.128.160]) by mail-gw4.york.ac.uk (8.13.6/8.13.6) with ESMTP id k9OBq435014367; Tue, 24 Oct 2006 12:52:04 +0100 (BST) Received: from buffy.york.ac.uk (localhost [127.0.0.1]) by buffy.york.ac.uk (8.13.6/8.13.6) with ESMTP id k9OBq4rq017943; Tue, 24 Oct 2006 12:52:04 +0100 (BST) (envelope-from gavin.atkinson@ury.york.ac.uk) Received: (from ga9@localhost) by buffy.york.ac.uk (8.13.6/8.13.6/Submit) id k9OBq4AD017942; Tue, 24 Oct 2006 12:52:04 +0100 (BST) (envelope-from gavin.atkinson@ury.york.ac.uk) X-Authentication-Warning: buffy.york.ac.uk: ga9 set sender to gavin.atkinson@ury.york.ac.uk using -f From: Gavin Atkinson To: Mike Jakubik In-Reply-To: <453D49D2.1010705@rogers.com> References: <453D49D2.1010705@rogers.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Tue, 24 Oct 2006 12:52:03 +0100 Message-Id: <1161690723.17592.22.camel@buffy.york.ac.uk> Mime-Version: 1.0 X-Mailer: Evolution 2.6.1 FreeBSD GNOME Team Port X-York-MailScanner: Found to be clean X-York-MailScanner-From: gavin.atkinson@ury.york.ac.uk Cc: stable@freebsd.org Subject: Re: Running large DB's on FreeBSD X-BeenThere: freebsd-stable@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Production branch of FreeBSD source code List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 24 Oct 2006 11:52:21 -0000 [ Replying offlist as pretty much all my points have already been said by others, so I'm just trying to help reinforce what others have said ] On Mon, 2006-10-23 at 19:01 -0400, Mike Jakubik wrote: > I am in the process of implementing a fairly large mysql server for > an even larger company, and naturally i want to use FreeBSD. The > hardware will be an HP DL385, 2 x dual-core Opterons, 16GB RAM, 7 x 15k > rpm disks in a RAID5 setup. I'm not exactly informed as to the specific > workload yet, however i know the database will have several million rows > and be larger than 10GB. Go for RAID10 rather than RAID5 - in my testing with an LSI LSI MegaRAID SCSI 320-2 RAID card, this gave roughly 4-5x speed benefit over RAID5, on the FreeBSD-based database mentioned below. You'd also want to use a 64 bit operating system on that server, otherwise it will perform far worse than expected. > So, first of all, am i crazy for choosing fbsd+mysql for this rather > than something like Solaris + Oracle? :) I would suggest either FreeBSD/PostgreSQL or Solaris/Oracle. I have several databases in excess of 30 gig on each and both perform well once tuned. The largest PostgreSQL database I set up tops 100G and grows relatively steadily. I've been very impressed with PostgreSQL, and may well be replacing some of my Oracle licenses with it in the future. You don't say what data you will be storing, but this may also affect the decision. Do you need to easily do full-text searches? Are you storing spacial/geographic (GIS) data? Think about backing up the data too - can the database be taken down for cold backups or must it be 100% available? And can you see a need for multiple servers in the future for redundancy? The answers to these questions may influence your choice more than anything else. It is probably worth your time testing the three options - I believe Oracle and Solaris can both be downloaded freely for evaluation purposes. Write a few small scripts to basically hammer the database with selects, inserts and deletes, and see which performs best. Pull the power on the server a few times and see which recovers best. I suspect you may go off MySQL. After having being burned several times with MySQL (data loss, unexplained slowdowns and general lack of scalability with growing database sizes), I must be honest and say I haven't tried it for a while, though I also have no desire to as PostgreSQL has never let me down yet. Summary: FreeBSD is a great platform for this, but I would seriously reconsider RAID5 and MySQL. No matter which option you choose, be prepared to put considerable effort into tuning the OS and database. Hope that helps, Gavin