From owner-freebsd-questions@FreeBSD.ORG Sun Jan 7 22:18:17 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 072BC16A403 for ; Sun, 7 Jan 2007 22:18:17 +0000 (UTC) (envelope-from kirk@strauser.com) Received: from kanga.honeypot.net (kanga.honeypot.net [208.162.254.122]) by mx1.freebsd.org (Postfix) with ESMTP id B887713C458 for ; Sun, 7 Jan 2007 22:18:16 +0000 (UTC) (envelope-from kirk@strauser.com) Received: from localhost (localhost [127.0.0.1]) by kanga.honeypot.net (Postfix) with ESMTP id 0780D2099E2 for ; Sun, 7 Jan 2007 16:18:16 -0600 (CST) X-Virus-Scanned: amavisd-new at honeypot.net Received: from kanga.honeypot.net ([127.0.0.1]) by localhost (kanga.honeypot.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 5eBTAz-45-ub for ; Sun, 7 Jan 2007 16:18:12 -0600 (CST) Received: from kanga.honeypot.net (kanga.honeypot.net [IPv6:2001:470:1f01:224:1::2]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by kanga.honeypot.net (Postfix) with ESMTP id C7B40208200 for ; Sun, 7 Jan 2007 16:18:12 -0600 (CST) From: Kirk Strauser To: freebsd-questions@freebsd.org Date: Sun, 7 Jan 2007 16:18:06 -0600 User-Agent: KMail/1.9.5 References: <200701071301.27423.kirk@strauser.com> In-Reply-To: X-Face: &'; cS03F?rr_w2Qce.d2f7xmwXfcJWDs>}CkpDw.c]ZJJ_)i0Nx 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: Sun, 07 Jan 2007 22:18:17 -0000 --nextPart1795280.xDvRYJSnzX Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Sunday 07 January 2007 15:15, Ivan Voras 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=20 seemed reasonable. I was sort of hoping for a FreeBSD-specific=20 magic "go-fast switch". > - What might help you is to keep the WAL (write-ahead-log, i.e. journal) > files on a completely separate (and fast) drive from the rest of the > database, to allow parallelism and speed. For best results, format it > with 32k blocks/8k fragments. Thanks for the idea. Assuming I actually get my wish of a matched set of 4= =20 high speed drives, would I be better off setting one aside for the journal,= =20 or striping them all together so everything benefits? > - If you don't specifically need the atomicity of transactions, you > might divide your import into many small transactions, for example one > for every 100,000 rows instead of doing 8 million at once. Would that actually make a difference in total elapsed time spent importing? > Also, what version of PostgreSQL are you using? As a general rule, the > newer the version, the faster it is. This is especially true if you're > using 7.x - go to 8.1.5 immediately (but don't use 8.2 until 8.2.1 gets > out). We're already running 8.2 because it fixed some problems we were having wit= h=20 8.1.5. Other than the excessively long import times, it's absolutely=20 screaming and we couldn't be more pleased. =2D-=20 Kirk Strauser --nextPart1795280.xDvRYJSnzX Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (FreeBSD) iD8DBQBFoXGj5sRg+Y0CpvERAgv6AKCP4zG4AOqen3WC9Vd0SdguQkur8QCdGRVL UxAkM66Iy+44TOLMBUZaJtE= =TZNE -----END PGP SIGNATURE----- --nextPart1795280.xDvRYJSnzX--