Skip site navigation (1) Skip section navigation (2)

Re: Improve BULK insertion

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-performance(at)postgresql(dot)org, grupos(at)carvalhaes(dot)net
Subject: Re: Improve BULK insertion
Date: 2004-12-05 23:19:45
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

> 3. My transaction log configuration are : checkpoint_segments = 3  and
> checkpoint_timeout = 300 and my transaction logs are on the same disk .

Well, you need to move your transaction logs to another disk, and increase 
them to a large number ... like 128, which is about 1GB (you'll need that 
much disk space).   Also, increase the checkpoint_timeout to minimize 
checkpointing during the load; like, 1500.

> I know that I can increase the performance separating the transaction
> logs and making a RAID 5 array 

Actually, RAID5, unless you're using > 5 disks, would make things slower.   
Speeding writes up through RAID would require at least 6 drives, and probably 
RAID 1+0.

> BUT I am really curious about WHY this 
> performance is so poor and HOW can I try to improve on this actual
> machine because actualy this inserts are taking around 90 minutes!!!

Are you doing INSERTS and not COPY?   If so, are you batching them in 

Josh Berkus
Aglio Database Solutions
San Francisco

In response to

pgsql-performance by date

Next:From: Postgres LearnerDate: 2004-12-06 09:28:46
Subject: 8.0 vs. 7.4 benchmarks
Previous:From: Josh BerkusDate: 2004-12-05 23:06:40
Subject: Re: Partitioned table performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group