Re: PG8 Tuning - Mailing list pgsql-performance

(Musing, trying to think of a general-purpose performance-tuning rule
that applies here):

Actually, it seems to me that with the addition of the WAL in PostgreSQL
and the subsequent decreased need to fsync the data files themselves
(only during checkpoints?), that the only time a battery-backed write
cache would make a really large performance difference would be on the
drive(s) hosting the WAL.

So although it is in general good to have a dedicated spindle for the
WAL, for many workloads it is in fact significantly better to have the
WAL written to a battery-backed write cache.  The exception would be for
applications with fewer, larger transactions, in which case you could
actually use the dedicated spindle.

Hmmm, on second thought, now I think I understand the rationale behind
having a non-zero commit delay setting-- the problem with putting
pg_xlog on a single disk without a write cache is that frequent fsync()
calls might cause it to spend most of its time seeking instead of
writing (as seems to be happening to Paul here).  Then again, the OS IO
scheduler should take care of this for you, making this a non-issue.
Perhaps Solaris 10 just has really poor IO scheduling performance with
this particular hardware and workload?

Ah well.  Thought myself in circles and have no real conclusions to show
for it.  Posting anyway, maybe this will give somebody some ideas to
work with.

-- Mark Lewis

On Fri, 2005-08-12 at 08:47 +0000, 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