Re: understanding postgres issues/bottlenecks

On Wed, Jan 7, 2009 at 7:11 PM, Scott Carey <scott(at)richrelevance(dot)com> wrote:
> If you're stuck with a Dell, the Adaptec 5 series works, I'm using 5085's in
> a pair and get 1200 MB/sec streaming reads best case with 20 SATA drives in
> RAID 10 (2 sets of 10, software raid 0 on top). Of course, Dell doesn't
> like you putting in somebody else's RAID card, but they support the rest of
> the system when you add in a third party PCIe card.
> Sure, they are a bit pricey, but they are also very good performers with
> drivers for a lot of stuff, including OpenSolaris. I tested a PERC 6 versus
> this with the same drives, but only 10 total. The Adaptec was 70% faster
> out of the box, and still 35% faster after tuning the linux OS read-ahead
> and other parameters.

Sequential read performance means precisely squat for most database
loads. The dell stuff is ok....decent RAID 5 performance and mediocre
raid 10. Unfortunately switching the disks to jbod and going software
raid doesn't seem to help much. The biggest problem with dell
hardware that I see is that overflowing the raid cache causes the
whole system to spectacularly grind to a halt, causing random delays.

To the OP, it looks like you are getting about 300 or so tps out of
sdc (80% read), which is where I'm assuming the data is. I'm guessing
most of that is random traffic. Here's the bad news: while this is on
the low side for a 6 disk raid 10 7200 rpm, it's probably about what
your particular hardware can do. I have some general suggestions for
you:
*) upgrade hardware: more/faster disks, etc
*) disable fsync (dangerous!) can risk data loss, but maybe you have
redundancy built in a different place. This will let linux reorganize
i/o on top of what the hardware is doing.
*) upgrade to postgres 8.3. Numerous efficiency advantages, and has
the synchronous_commit setting, which is 'fsync lite'...most of the
advantages and a lot less risk.
*) tune the app

merlin