Re: [SPAM?] Re: PG8 Tuning - Mailing list pgsql-performance

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