Message-ID: <42FB9235.4090505@jobflash.com>
Date: Thu, 11 Aug 2005 11:00:21 -0700
From: Tom Arthurs <tarthurs@jobflash.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Steve Poe <spoe@sfnet.cc>
Cc: paul@oxton.com, pgsql-performance@postgresql.org
Subject: Re: [SPAM?] Re: PG8 Tuning
References: <1536.217.45.209.171.1123763001.squirrel@www.gradwell.com>
	<1123836428.19976.26.camel@amd64-laptop-spoe>
In-Reply-To: <1123836428.19976.26.camel@amd64-laptop-spoe>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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