From owner-freebsd-stable@FreeBSD.ORG Tue Oct 24 07:49:25 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 9893E16A412 for ; Tue, 24 Oct 2006 07:49:25 +0000 (UTC) (envelope-from kometen@gmail.com) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by mx1.FreeBSD.org (Postfix) with ESMTP id E4E9643D46 for ; Tue, 24 Oct 2006 07:49:24 +0000 (GMT) (envelope-from kometen@gmail.com) Received: by nf-out-0910.google.com with SMTP id p77so120160nfc for ; Tue, 24 Oct 2006 00:49:23 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=qoyj9cgezMKR62NshYV9E2GnJ6h2iz19uMKcyYs4Y10wMArUE+aQEBqU5khn0XVK8myf/ltqoHUvZjQ1oCH4qCb7zSefHItFKPQcaaBVbxXtGkJ2iM4IAHckUERShjXUSvzdxd8MGHiLf/5/7sbZexj0N/BeChvSJ04Iitfa+vE= Received: by 10.78.150.7 with SMTP id x7mr8749629hud; Tue, 24 Oct 2006 00:49:22 -0700 (PDT) Received: by 10.78.97.15 with HTTP; Tue, 24 Oct 2006 00:49:17 -0700 (PDT) Message-ID: Date: Tue, 24 Oct 2006 09:49:17 +0200 From: "Claus Guttesen" To: "Mike Jakubik" In-Reply-To: <453D49D2.1010705@rogers.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <453D49D2.1010705@rogers.com> 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 07:49:25 -0000 > 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. > > So, first of all, am i crazy for choosing fbsd+mysql for this rather > than something like Solaris + Oracle? :) Secondly, i am just looking for > some suggestions, opinions, success/failure story's that may help me > out. Is anyone out there using FreeBSD for something of this size? I am > hoping that everything will work out well, and the client will be happy. > This would generate some good PR for FreeBSD, as it is a very large > international company and it would be the first FreeBSD server (that i > know of) of this type there. I'm managing a 28 GB postgresql (7.4.9) database running on FreeBSD 6.0 (release). The server is a quad-core opteron with 8 GB ram. The database has many smaller tables and one large with 47+ million entries and the activity is mainly inserts. The most important settings I tweaked was: shared_buffers = 32768 vacuum_mem = 262144 max_fsm_pages = 1250000 max_fsm_relations = 1000 effective_cache_size = 65536 random_page_cost = 2 These settings are for pg 7.4. If you go for postgresql you want 8.1. If you go for FreeBSD remembere to change these settings in the kernel: options SHMMAXPGS=393216 options SEMMNI=240 options SEMMNS=1440 options SEMUME=240 options SEMMNU=720 The command 'ipcs -ma' on FreeBSD will tell you SEGSZ (size in bytes) of the shared memory postgres is using. Our's is 299573248 bytes and you can adjust shared_buffers according to this. I found the information at http://www.varlena.com/GeneralBits/Tidbits/perf.html. regards Claus