Re: Copy performance issues

s anwar wrote:
> 3. 8T RAID5 partition for database on a Dell PERC 5/E controller
> (I understand that I'll never get fast inserts/updates on it based on
> http://wiki.postgresql.org/wiki/SlowQueryQuestions but cannot change
> to a RAID0+1 for now).
> Database's filesystem is ext4 on LVM on RAID5.

And LVM slows things down too, so you're stuck with basically the worst
possible layout here. Have you checked that your PERC is setup with a
write-back cache? If not, that will kill performance. You need to have
a battery for the controller for that to work right. This is the first
thing I'd check on your system. With ext4, on this controller you might
need to use the "nobarrier" write option when mounting the filesystem to
get good performance too. Should be safe if you have a battery on the
controller.

> shared_buffers = 10GB
> checkpoint_segments = 40

I've gotten some reports that the fall-off where shared_buffers stops
helping is lower than this on Linux. I'd suggest at most 8GB, and you
might even try something like 4GB just to see if that turns out to be
better.

With what you're doing, you could likely increase checkpoint_segments
quite a bit from here too. I'd try something >100 and see if that helps.

> enable_seqscan = off

That's going to cause you serious trouble one day if you leave it like
this in production, on a table where index scans are much more expensive
than sequential ones for what you're doing.

One thing that can help large COPYies a lot is to increase Linux
read-ahead. Something like:

/sbin/blockdev --setra 4096 /dev/sda

Done for each physical drive the system sees will help large sequential
reads of files the way COPY does significantly. The default is probably
256 on your system given the LVM setup, possibly even smaller.

Beyond that, you may just need to investigate clever ways to reduce the
indexing requirements.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us