From: Greg Smith <greg@2ndquadrant.com>
To: s anwar <sanwar@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Copy performance issues
Date: Wed, 18 Aug 2010 18:49:50 -0400
Message-ID: <4C6C638E.9090204@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTi=ipBC7-12=YHUcLEn7MODGh9Vjeu7mj3W3RcDJ@mail.gmail.com>
References: <AANLkTi=y+JPmuqU5gAX=gtMTJRcNDGDToa61QQVtAbF0@mail.gmail.com>
<AANLkTi=ipBC7-12=YHUcLEn7MODGh9Vjeu7mj3W3RcDJ@mail.gmail.com>
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@2ndQuadrant.com www.2ndQuadrant.us
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: greg@2ndquadrant.com, sanwar@gmail.com
Subject: Re: Copy performance issues
In-Reply-To: <4C6C638E.9090204@2ndquadrant.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