From owner-freebsd-questions@FreeBSD.ORG Mon Jan 8 02:54:55 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 7287A16A407 for ; Mon, 8 Jan 2007 02:54:55 +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 2DD4313C455 for ; Mon, 8 Jan 2007 02:54:55 +0000 (UTC) (envelope-from kirk@strauser.com) Received: from localhost (localhost [127.0.0.1]) by kanga.honeypot.net (Postfix) with ESMTP id 7BCB920679F for ; Sun, 7 Jan 2007 20:54:54 -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 3JUaOGNSATaz for ; Sun, 7 Jan 2007 20:54:50 -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 637E2205C9C for ; Sun, 7 Jan 2007 20:54:50 -0600 (CST) From: Kirk Strauser To: freebsd-questions@freebsd.org Date: Sun, 7 Jan 2007 20:54:47 -0600 User-Agent: KMail/1.9.5 References: <200701071301.27423.kirk@strauser.com> <200701071618.11242.kirk@strauser.com> <20070108122245.53e3140b@localhost> In-Reply-To: <20070108122245.53e3140b@localhost> 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: Mon, 08 Jan 2007 02:54:55 -0000 --nextPart3671080.6uiMXo8huO Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Sunday 07 January 2007 19:22, Norberto Meijome wrote: > I could be wrong and this only apply to generating indexes? That's what we're doing now. By dropping the table and recreating it, all = the=20 index maintenance gets deferred until one batch at the end (which is vastly= =20 faster in practice). We also wrap the whole thing in a transaction so that= =20 select queries still have access to the old data until the instant the comm= it=20 is finished, assuming that the import doesn't fail for some reason and get= =20 rolled back. > Something also to consider is , do you *have* to import all that data eve= ry > 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? I wish. First, we run the import hourly (more or less). I save the output= of=20 the last hour's Foxpro->PostgreSQL conversion, and run a hand-written=20 optimized diff against it. If nothing has changed, that table gets skipped= =2E =20 If a reasonably small percentage of rows have changed (which is almost alwa= ys=20 the case), then I re-write it as a serious of deletes followed by a bulk=20 import. Basically, I patch the table. It's nice to see a twenty million r= ow=20 table update reduced to a patch file 100 lines long. Oh, for the record, m= y=20 diff algorithm is written in Python and is still IO limited, even when=20 several copies are running in parallel. > 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 ? {Un,}fortunately, no. The CPUs are still idle enough to get a lot of other= =20 processing done without slowing the import process. > Let us know what solution you come up with, and good luck :) Will do, and thanks! =2D-=20 Kirk Strauser --nextPart3671080.6uiMXo8huO Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (FreeBSD) iD8DBQBFobJ45sRg+Y0CpvERAg+cAJ4gfRM7Cdk2zags461NGEvIf1F6cQCgiTCc XGyAPB3D22NgKYiNsfxHAKI= =0kVj -----END PGP SIGNATURE----- --nextPart3671080.6uiMXo8huO--