From owner-freebsd-questions@FreeBSD.ORG Sun Jan 7 21:15:57 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 ED08716A407 for ; Sun, 7 Jan 2007 21:15:57 +0000 (UTC) (envelope-from freebsd-questions@m.gmane.org) Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by mx1.freebsd.org (Postfix) with ESMTP id 67F1813C457 for ; Sun, 7 Jan 2007 21:15:56 +0000 (UTC) (envelope-from freebsd-questions@m.gmane.org) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1H3fMl-0001F1-Ry for freebsd-questions@freebsd.org; Sun, 07 Jan 2007 22:15:43 +0100 Received: from 89-172-46-216.adsl.net.t-com.hr ([89.172.46.216]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sun, 07 Jan 2007 22:15:43 +0100 Received: from ivoras by 89-172-46-216.adsl.net.t-com.hr with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sun, 07 Jan 2007 22:15:43 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: freebsd-questions@freebsd.org From: Ivan Voras Date: Sun, 07 Jan 2007 22:15:05 +0100 Lines: 56 Message-ID: References: <200701071301.27423.kirk@strauser.com> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigB3C3CDB3E1E0BB55F6F7B7E5" X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: 89-172-46-216.adsl.net.t-com.hr User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) In-Reply-To: <200701071301.27423.kirk@strauser.com> X-Enigmail-Version: 0.94.1.2 Sender: news 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 21:15:58 -0000 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigB3C3CDB3E1E0BB55F6F7B7E5 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Kirk Strauser wrote: > I have an hourly job that converts our legacy Foxpro database into > PostgreSQL tables so that our web applications, etc. can run reports of= f > the data in a reasonable amount of time. Believe it or not, this has b= een > running perfectly in production for over a year. The only problem I'd > still like to solve is that loading the data pegs the filesystem at 100= % > for many minutes at a time. 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: - have you tuned pgsql away from the (very conservative) defaults? increased shared_buffers, effective_cache_size, temp_buffers and work_mem= ? - 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. - 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. If you want to be somewhat adventurous (but still within data safety limits), you can try fiddling with increasing wal_buffers, commit_delay and checkpoint_timeout. 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). --------------enigB3C3CDB3E1E0BB55F6F7B7E5 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.4 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFoWLjldnAQVacBcgRAnSgAJ9/TvTR4yP4vY8+LZrgfiprLHj6GACgztWr G8ZBbUw0HnsHZot3lz6v6dg= =96dd -----END PGP SIGNATURE----- --------------enigB3C3CDB3E1E0BB55F6F7B7E5--