From: Alex <alex@meerkatsoft.com>
To: John A Meinel <john@arbash-meinel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How can I make use of both CPUs in a dual processor
Date: Fri, 11 Feb 2005 00:18:26 +1100
Message-ID: <420B5F22.2070407@meerkatsoft.com> (raw)
In-Reply-To: <420A2933.6060100@arbash-meinel.com>
References: <420A1D9B.1040801@meerkatsoft.com>
<420A22E7.5010501@arbash-meinel.com>
<420A2580.3040404@meerkatsoft.com>
<420A2933.6060100@arbash-meinel.com>
Thanks for all the suggestions. It seems that creating indices, or even
import data using a copy is easy to implement. I also have some jobs
that create reports and want to try if I gain anything if i work reports
in parallel.
will give it a try in the next week and let you know the resuls.
Alex
John A Meinel wrote:
> 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 <host> -c "DROP INDEX ...; COPY FROM ..."
>
> psql -h <host> -c "CREATE INDEX ..." &
> psql -h <host> -c "CREATE INDEX ..." &
> psql -h <host> -c "CREATE INDEX ..." &
> psql -h <host> -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
> =:->
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alex@meerkatsoft.com, john@arbash-meinel.com
Subject: Re: How can I make use of both CPUs in a dual processor
In-Reply-To: <420B5F22.2070407@meerkatsoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox