From: Merlin Moncure
Subject: Re: PG8 Tuning
Date: ,
Msg-id: 6EE64EF3AB31D5448D0007DD34EEB3417DD095@Herge.rcsinc.local
(view: Whole thread, Raw)
In response to: PG8 Tuning  ("Paul Johnson")
List: pgsql-performance

Tree view

PG8 Tuning  ("Paul Johnson", )
 Re: PG8 Tuning  (Richard Huxton, )
 [SPAM?] Re: PG8 Tuning  (Steve Poe, )
  Re: [SPAM?] Re: PG8 Tuning  ("Jeffrey W. Baker", )
   Re: [SPAM?] Re: PG8 Tuning  (Jeff Trout, )
   Re: [SPAM?] Re: PG8 Tuning  (Josh Berkus, )
    Re: [SPAM?] Re: PG8 Tuning  (Alvaro Herrera <-ip.org>, )
     Re: [SPAM?] Re: PG8 Tuning  ("Joshua D. Drake", )
     Re: PG8 Tuning  (John A Meinel, )
      Re: PG8 Tuning  (Tom Lane, )
       Re: PG8 Tuning  (John A Meinel, )
        Re: PG8 Tuning  (Tom Lane, )
    Re: [SPAM?] Re: PG8 Tuning  (Michael Stone, )
     Re: PG8 Tuning  (Josh Berkus, )
  Re: [SPAM?] Re: PG8 Tuning  (Tom Arthurs, )
  Re: PG8 Tuning  (Mark Lewis, )
   Re: PG8 Tuning  (Michael Stone, )
 Re: PG8 Tuning  (Michael Stone, )
 Re: PG8 Tuning  ("Merlin Moncure", )

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

It still helps.  In my experience a good BBU Raid controller is only
slightly slower than fsync=false.  Fear the checkpoint storm if you
don't have some write caching.  Beyond that I don't really care about
write delay.

Another thing to watch out for is that some sync modes (varying by
platform) can do >1 seeks per sync.  This will absolutely kill your
commit performance on the WAL without write caching.

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

Exactly.


> Hmmm, on second thought, now I think I understand the rationale behind
> having a non-zero commit delay setting-- the problem with putting

I don't trust commit_delay.  Get a good raid controller and make sure pg
is properly using it.  Now, if you can't (or won't) do some type of
write caching bbu or no, your system has to be very carefully designed
to get any performance at all, especially with high transaction volumes.


Merlin


pgsql-performance by date:

From: Michael Stone
Date:
Subject: Re: Mostly read performance
From: Greg Stark
Date:
Subject: Re: Mostly read performance