X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 55A158B9B26 for ; Wed, 9 Feb 2005 15:16:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04177-07 for ; Wed, 9 Feb 2005 15:16:06 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id E1E8B8B9B45 for ; Wed, 9 Feb 2005 15:16:09 +0000 (GMT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id j19FG9308450; Wed, 9 Feb 2005 09:16:09 -0600 Message-ID: <420A2933.6060100@arbash-meinel.com> Date: Wed, 09 Feb 2005 09:16:03 -0600 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Alex Cc: pgsql-performance@postgresql.org Subject: Re: How can I make use of both CPUs in a dual processor References: <420A1D9B.1040801@meerkatsoft.com> <420A22E7.5010501@arbash-meinel.com> <420A2580.3040404@meerkatsoft.com> In-Reply-To: <420A2580.3040404@meerkatsoft.com> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig87F4BAB39965BEEA6FB68C7A" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200502/140 X-Sequence-Number: 10469 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig87F4BAB39965BEEA6FB68C7A Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Alex wrote: > Thanks John. > > Well as I mentioned. I have a Dual AMD Opteron 64 2.4ghz, 15k rpm > SCSI Disks, 4GB of memory. > Disks are pretty fast and memory should be more than enough. Currently > we dont have many concurrent connections. > Well, you didn't mention Opteron before (it makes a difference against Xeons). How many disks and in what configuration? Do you have pg_xlog on a separate set of disks? Are your drives in RAID 10 (0+1) or RAID 5? If you have enough disks the recommended configuration is at least a RAID1 for the OS, RAID 10 for pg_xlog (4drives), and RAID 10 (the rest of the drives) for the actual data. If your dataset is read heavy, or you have more than 6 disks, you can get away with RAID 5 for the actual data. But since you are talking about loading 8million rows at once, it certainly sounds like you are write heavy. If you only have a few disks, it's still probably better to put pg_xlog on it's own RAID1 (2-drive) mirror. pg_xlog is pretty much append only, so if you dedicate a disk set to it, you eliminate a lot of seek times. > I run PG 8.0.1 on Fedora Core 3 > > When I now run the batch job, one CPU runs in the 80-90% the other in > 5-10% max. Anyway, it doesn't completely sound like you are CPU limited, but you might be able to get a little bit more if you spawn another process. Have you tried dropping the index, doing the copy, and then recreating the 4-indexes in separate processes? The simple test for this is to open 3-4 psql connections, have one of them drop the indexes and do the copy, in the other connections you can already have typed "CREATE INDEX ..." so when the copy is done and committed to the database, you just go to the other terminals and hit enter. Unfortunately you'll have to use wall clock time to see if this is faster. Though I think you could do the same thing with a bash script. The authentication should be in "trust" mode so that you don't take the time to type your password. #!/bin/bash psql -h -c "DROP INDEX ...; COPY FROM ..." psql -h -c "CREATE INDEX ..." & psql -h -c "CREATE INDEX ..." & psql -h -c "CREATE INDEX ..." & psql -h -c "CREATE INDEX ..." Now, I don't really know how to wait for all child processes in a bash script (I could give you the python for it, but you're a perl guy). But by not spawning the last INDEX, I'm hoping it takes longer than the rest. Try to put the most difficult index there. Then you could just run time loadscript.sh I'm sure you could do the equivalent in perl. Just open multiple connections to the DB, and have them ready. I'm guessing since you are on a dual processor machine, you won't get much better performance above 2 connections. You can also try doing 2 COPYs at the same time, but it seems like you would have issues. Do you have any serial columns that you expect to be in a certain order, or is all the information in the copy? If the latter, try it, let us know what you get. I can't tell you the perl for this, since I'm not a perl guy. John =:-> --------------enig87F4BAB39965BEEA6FB68C7A 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.2.6 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCCikzJdeBCYSNAAMRAhf1AKC7JW14BGhCPvgqQVeLyN1uBSxv4gCdHKvb 1iuEAd6MTnPKuIVeZR1nCWg= =kJpx -----END PGP SIGNATURE----- --------------enig87F4BAB39965BEEA6FB68C7A--