From owner-freebsd-isp@FreeBSD.ORG Sun Apr 6 12:40:46 2003 Return-Path: Delivered-To: freebsd-isp@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 21E9037B401 for ; Sun, 6 Apr 2003 12:40:46 -0700 (PDT) Received: from blue.centerone.com (blue.centerone.com [204.133.183.111]) by mx1.FreeBSD.org (Postfix) with ESMTP id 6DAA843F93 for ; Sun, 6 Apr 2003 12:40:45 -0700 (PDT) (envelope-from rf-list@centerone.com) Received: from DELIVERANCE-XP.centerone.com (ppp-168-253-13-120.den1.ip.ricochet.net [168.253.13.120]) by blue.centerone.com (8.9.3/8.9.3) with ESMTP id OAA04590 for ; Sun, 6 Apr 2003 14:03:35 -0600 Message-Id: <5.1.0.14.2.20030406125652.015cbb10@mail.centerone.com> X-Sender: rf-list@mail.centerone.com X-Mailer: QUALCOMM Windows Eudora Version 5.1 Date: Sun, 06 Apr 2003 13:35:54 -0600 To: freebsd-isp@freebsd.org From: Ralph Forsythe In-Reply-To: <3E906828.8060001@mac.com> References: <20030406120112.I11095-100000@netmint.com> <20030406120112.I11095-100000@netmint.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: DB server config (was Re: load testing and tuning a 4GB RAM server) X-BeenThere: freebsd-isp@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Internet Services Providers List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 06 Apr 2003 19:40:46 -0000 At 01:47 PM 4/6/2003 -0400, Chuck Swiger wrote: >Also, your configuration is suited for where the users will be doing >mostly reads and not a lot of writes. If the users might be doing a lot >of writes, you should use RAID-1,0 instead of RAID-5. ..... >Specificly, disk I/O contention is likely to happen if the DB is expected >to see any significant usage while the drives are busy serving up user >files. That's aside from the fact that you really don't want to keep >database files on RAID-5 storage in the first place, either-- keep the DB >on RAID-1 or RAID-1,0 if you can: use some of that 15K swapspace if you >need to. Well, this brings up some interesting points WRT a project I'm working on right now. As-is, the system will bring in about 2-4 gigs a day in raw data, which will get parsed out and stored in a database with various other information as to how it's sorted, etc. From there, a PHP site will access the database, figure out the sorting information, and present it to the user on request. Pretty simple so far... However in about a year, I expect the daily data input to exceed 35 gigs, with that extra data representing a lot of different things, but being presented to the user with basically the same interface. So... Writing to the database is probably best suited for Raid 0/1, but reading from it is best done on Raid 5, correct? The database will be MySQL, DB server will be FreeBSD. The system which takes in the data will be Linux (not my choice, it's a software issue) which will then push to the database over a dedicated 100mbit interface. Then there is the web server ... this will be FreeBSD as well, running Apache with PHP. Let's assume the web server and DB server are not having issues with RAM, have been tuned, etc. All web pages will be dynamic with PHP (all content is determined from the database), except for the style sheets and a few static graphics like logos and such. Initially the load will be low, but in peak times down the road I expect connections in the thousands potentially. There will be SSL on some parts, but it will be a tiny fraction of the normal traffic (i.e. commerce and registration stuff only). So what would be a better drive configuration for a system like this? My instinct is telling me a Raid0/1, since the majority of the traffic will be writes with the incoming data. A lot of that data might not be used for a given time period, but the idea is that it's all available 24/7, and archived for a period of time (yes I know, this also means a ridiculous amount of disk space, at least half a terabyte just for 2 weeks). The data pruning activities to remove old information could involve a lot of reads, but if I index it properly I think that will be minimal and will be spread out. I just had a thought about a master R/W DB server with Raid0/1 and a read-only slave on Raid5 that replicates for web access, but I'm not sure I'm gaining anything. The slave will still have to push just as much data to the drives, though replication might have benefits over normal database writes I'm not aware of... (MySQL tuning perhaps?) Anyone have any suggestions or expertise to share here? How would YOU build something like this? Thanks! -Ralph