From owner-freebsd-questions@FreeBSD.ORG Mon Jan 8 01:22:50 2007 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 2DBB316A501 for ; Mon, 8 Jan 2007 01:22:50 +0000 (UTC) (envelope-from freebsd@meijome.net) Received: from sigma.octantis.com.au (ns2.octantis.com.au [207.44.189.124]) by mx1.freebsd.org (Postfix) with ESMTP id EDFC813C458 for ; Mon, 8 Jan 2007 01:22:49 +0000 (UTC) (envelope-from freebsd@meijome.net) Received: (qmail 23840 invoked from network); 8 Jan 2007 12:22:48 +1100 Received: from 203-217-81-135.dyn.iinet.net.au (HELO localhost) (203.217.81.135) by sigma.octantis.com.au with (DHE-RSA-AES256-SHA encrypted) SMTP; 8 Jan 2007 12:22:48 +1100 Date: Mon, 8 Jan 2007 12:22:45 +1100 From: Norberto Meijome To: Kirk Strauser Message-ID: <20070108122245.53e3140b@localhost> In-Reply-To: <200701071618.11242.kirk@strauser.com> References: <200701071301.27423.kirk@strauser.com> <200701071618.11242.kirk@strauser.com> X-Mailer: Claws Mail 2.6.1 (GTK+ 2.10.7; i386-portbld-freebsd6.2) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Cc: freebsd-questions@freebsd.org Subject: Re: Tuning PostgreSQL for bulk imports 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, 08 Jan 2007 01:22:50 -0000 On Sun, 7 Jan 2007 16:18:06 -0600 Kirk Strauser wrote: > > There's a dedicated mailing list for PostgreSQL performance: > > pgsql-performance/at/postgresql.org, which can give you really good > > advice, but here's some tips: > > I've read, read, and re-read the general tuning tips, and done as much as > seemed reasonable. I was sort of hoping for a FreeBSD-specific > magic "go-fast switch". Hi Kirk, I haven't touched PGSQL for a little while...but I remember some comments in the pgsql lists about disabling ACID features / WAL while big imports are happening - I could be wrong and this only apply to generating indexes ? Of course this is a no goer if you still need to you use your DB server for other tasks during the import. Something also to consider is , do you *have* to import all that data every time? ie., can you create data partitions, assign a new table to each day ,create the appropriate rules, and then only dump from FoxPro the last day's info? It may not be possible, but it helped me loads when I had to import 60 mill new rows/day. Indexing becomes somewhat easier too (as you should only have to regenerate index for the new partition) if cpu is hitting to hard, you can always run nice pgsql while importing this - it will still take 100% cpu, but yield as soon as something else needs to do some work . Of course, this wont help if you are IO bound (i think? ). *are* you CPU bound ? Let us know what solution you come up with, and good luck :) _________________________ {Beto|Norberto|Numard} Meijome "The music business is a cruel and shallow money trench, a long plastic hallway where thieves and pimps run free, and good men die like dogs. There's also a negative side." Hunter S. Thompson I speak for myself, not my employer. Contents may be hot. Slippery when wet. Reading disclaimers makes you go blind. Writing them is worse. You have been Warned.