From owner-freebsd-questions@FreeBSD.ORG Sun Jan 7 19:30:45 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 9538116A509 for ; Sun, 7 Jan 2007 19:30:45 +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 525BA13C467 for ; Sun, 7 Jan 2007 19:30:40 +0000 (UTC) (envelope-from kirk@strauser.com) Received: from localhost (localhost [127.0.0.1]) by kanga.honeypot.net (Postfix) with ESMTP id 0A7C220979A for ; Sun, 7 Jan 2007 13:01:33 -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 slflMNmZyfcr for ; Sun, 7 Jan 2007 13:01:29 -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 32D2A207F35 for ; Sun, 7 Jan 2007 13:01:29 -0600 (CST) From: Kirk Strauser To: freebsd-questions@freebsd.org Date: Sun, 7 Jan 2007 13:01:22 -0600 User-Agent: KMail/1.9.5 X-Face: &'; cS03F?rr_w2Qce.d2f7xmwXfcJWDs>}CkpDw.c]ZJJ_)i0Nx Subject: 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 19:30:45 -0000 --nextPart1259854.V8Dn41d0Ya Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I have an hourly job that converts our legacy Foxpro database into PostgreSQL tables so that our web applications, etc. can run reports off the data in a reasonable amount of time. Believe it or not, this has been 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. The biggest table we have holds about 800MB of data in about 8 million rows. My program loads the Foxpro table files and generates another file that looks like the following, which then gets piped into the psql command: =2D---------------------- begin; drop table ln; create table ln (invid integer, ln integer, charge text, servcode varchar(1= 0), odn varchar(1), itemcode varchar(6), qty1 varchar(10), uom1 varchar(2),= qty2 varchar(10), uom2 varchar(2), aw integer, bw integer, rate text, perc= integer, miles integer, loc1qual varchar(3), loc1id integer, loc2qual varc= har(3), loc2id integer); \copy ln from stdin 1078987 1 518.73 LHS N LHS 0 = 0 785 785 66.0800 100 0 = PW -1049 DA 16736 =2E.. [snip 8 million lines] =2E.. \. create index ln_invid on ln(invid); create index ln_uom1 on ln(uom1); commit; =2D---------------------- The /usr/local/pgsql/data filesystem is on two SCSI 320 drives via a geom_stripe with a 128KB stripe size. The drives are of unequal size. I've had a new set of four matched drives on order from our purchasing department for about size months now - yes, Mike, I'm looking at you - but I'm doing the best I can with what I have. The filesystem itself has soft updates enabled but was otherwise newfs'ed with the defaults. So, given that I'd like to throw more hardware at the problem but can't yet, is there anything I could do to make these imports go faster, short of running it async (which is far more dangerous than we're willing to risk)? =2D-=20 Kirk Strauser --nextPart1259854.V8Dn41d0Ya Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (FreeBSD) iD8DBQBFoUOH5sRg+Y0CpvERAlKFAJ4j2qlyfJFpXaczEE2H10nb9llKDwCfU+or uhpzm6uUJoEzq4pKXyIGlxQ= =+Pnp -----END PGP SIGNATURE----- --nextPart1259854.V8Dn41d0Ya--