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: 200412051519.45328.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Rodrigo,

> 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 
transactions?

-- 
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-2014 The PostgreSQL Global Development Group