Thread: Performance

Performance

From
"Andy Pearce"
Date:
Hi,
 
I have a postgresql 7.2.1 db running on a Pentium Pro server with 160mb ram and scsi drive. I tried to perform a copy to load about 7000 records, using a c program I'd written and it takes about 20 minutes to complete. each record has 5 field and the total size of each record is about 56 characters.
 
Does 20 minutes sound about right for such an action?
 
Thanks for your help
 
Andy

Re: Performance

From
Andrew McMillan
Date:
On Fri, 2002-11-08 at 22:47, Andy Pearce wrote:
> Hi,
>
> I have a postgresql 7.2.1 db running on a Pentium Pro server with
> 160mb ram and scsi drive. I tried to perform a copy to load about 7000
> records, using a c program I'd written and it takes about 20 minutes
> to complete. each record has 5 field and the total size of each record
> is about 56 characters.
>
> Does 20 minutes sound about right for such an action?

No, that seems quite low.

A couple of fairly simple things to consider:

- what is the -B parameter the postmaster is running with?  Consider
increasing it.

- consider running a single transaction for the insert.  If you commit
after inserting each row then you are asking the database to do a lot
more work.

- what indexing is there on the table?  What triggers? Constraints?
Sometimes these can impact substantially in data load situations where
all the data is known correct prior to load.  Consider
disabling/removing these things during the load and
reenabling/recreating them afterwards.

Hmmm...  That's 3 - guess I can't count :-)

Cheers,
                    Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------