From: Tom Arthurs <tarthurs@jobflash.com>
To: Steve Poe <spoe@sfnet.cc>
Cc: paul@oxton.com
Cc: pgsql-performance@postgresql.org
Subject: Re: [SPAM?] Re: PG8 Tuning
Date: Thu, 11 Aug 2005 11:00:21 -0700
Message-ID: <42FB9235.4090505@jobflash.com> (raw)
In-Reply-To: <1123836428.19976.26.camel@amd64-laptop-spoe>
References: <1536.217.45.209.171.1123763001.squirrel@www.gradwell.com>
<1123836428.19976.26.camel@amd64-laptop-spoe>
I think the T-3 RAID at least breaks some of these rules -- I've got 2
T-3's, 1 configured as RAID-10 and the other as RAID5, and they both
seem to perform about the same. I use RAID5 with a hot spare, so it's
using 8 spindles.
I got a lot of performance improvement out of mount the fs noatime and
turning journaling off. Of course it takes a *long* time to recover
from a crash.
Steve Poe wrote:
> Paul,
>
> Before I say anything else, one online document which may be of
> assistance to you is:
> http://www.powerpostgresql.com/PerfList/
>
> Some thoughts I have:
>
> 3) You're shared RAM setting seems overkill to me. Part of the challenge
> is you're going from 1000 to 262K with no assessment in between. Each
> situation can be different, but try in the range of 10 - 50K.
>
> 4) pg_xlog: If you're pg_xlog is on a spindle is *only* for pg_xlog
> you're better off. If it is sharing with any other OS/DB resource, the
> performance will be impacted.
>
>>From what I have learned from others on this list, RAID5 is not the best
> choice for the database. RAID10 would be a better solution (using 8 of
> your disks) then take the remaining disk and do mirror with your pg_xlog
> if possible.
>
> Best of luck,
>
> Steve Poe
>
> On Thu, 2005-08-11 at 13:23 +0100, Paul Johnson wrote:
>
>>Hi all, we're running PG8 on a Sun V250 with 8GB RAM and 2*1.3GHz SPARC
>>CPUs running Solaris 10. The DB cluster is on an external fibre-attached
>>Sun T3 array that has 9*36GB drives configured as a single RAID5 LUN.
>>
>>The system is for the sole use of a couple of data warehouse developers,
>>hence we are keen to use 'aggressive' tuning options to maximise
>>performance.
>>
>>So far we have made the following changes and measured the impact on our
>>test suite:
>>
>>1) Increase checkpoint_segments from 3 to 64. This made a 10x improvement
>>in some cases.
>>
>>2) Increase work_mem from 1,024 to 524,288.
>>
>>3) Increase shared_buffers from 1,000 to 262,143 (2 GB). This required
>>setting SHMMAX=4294967295 (4 GB) in /etc/system and re-booting the box.
>>
>>Question - can Postgres only use 2GB RAM, given that shared_buffers can
>>only be set as high as 262,143 (8K pages)?
>>
>>So far so good...
>>
>>4) Move /pg_xlog to an internal disk within the V250. This has had a
>>severe *negative* impact on performance. Copy job has gone from 2 mins to
>>12 mins, simple SQL job gone from 1 min to 7 mins. Not even run long SQL
>>jobs.
>>
>>I'm guessing that this is because pg_xlog has gone from a 9 spindle LUN to
>>a single spindle disk?
>>
>>In cases such as this, where an external storage array with a hardware
>>RAID controller is used, the normal advice to separate the data from the
>>pg_xlog seems to come unstuck, or are we missing something?
>>
>>Cheers,
>>
>>Paul Johnson.
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
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: tarthurs@jobflash.com, spoe@sfnet.cc, paul@oxton.com
Subject: Re: [SPAM?] Re: PG8 Tuning
In-Reply-To: <42FB9235.4090505@jobflash.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