Re: too much WAL volume

On Apr 27, 2007, at 4:58 AM, Greg Smith wrote:
> On Thu, 26 Apr 2007, Zeugswetter Andreas ADI SD wrote:
>> I am not sure that shrinking per WAL record size (other than the full
>> page images), e.g. by only logging changed bytes and not whole
>> tuples,
>> would have a huge impact on OLTP tx/sec, since the limiting factor is
>> IO's per second and not Mb per second.
>
> With the kind of caching controller that's necessary for any
> serious OLTP work with Postgres, number of I/Os per second isn't
> really an important number. Total volume of writes to the WAL
> volume can be though. It's difficult but not impossible to
> encounter a workload that becomes bottlenecked by WAL volume on a
> good OLTP server, particularly because that's often going to a
> single or RAID-1 disk. Whether those workloads also have the
> appropriate properties such that their WAL could be shrunk usefully
> in real-time is a good question.

Yes, but how many data drives would you need to have to bottleneck on
WAL? Even if the entire database is memory resident you'd still have
to write all the pages out at some point, and it seems to me that
you'd need a fair amount of disk capacity the data directory before
you got pegged by WAL.

When I did some DBT2 testing a bit over a year ago I had a 20 drive
RAID10 for data and a mirror for WAL and was nowhere close to pegged
on WAL (this was on a Sun V40 connected to one of their storage arrays).
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)