Re: too much WAL volume

On Fri, 27 Apr 2007, Jim Nasby wrote:

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

Depends on the type of transactions. If you're doing something with lots
of INSERT and perhaps some UPDATE volume that doesn't need to read heavily
from the database to complete, because most of the important stuff is
already in memory, you might run into the WAL limit without too much on
the database disk side. I did say it was difficult...

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

No doubt, the main reason I haven't used DBT2 more is because the WAL
volume produced before you run into database limited bottlenecks isn't
large, and certainly not in the same ratio as some of the apps I'm
modeling. Mine lean more toward straightforward transaction logging in
parts.

I'm running on similar hardware (V40 is very close, I think the EMC array
I test against is a bit better than the most of the Sun models) and I've
seen some scenarios that produce 40MB/s average - 60MB/s peak of WAL
volume. Sure seems like I'm rate limited by the RAID-1 WAL disk. As you
say, eventually all the data has to make it to disk, but since it's not
too expensive nowadays to have gigabytes worth of memory and disk array
cache you can put off database writes for a surprisingly long period of
time with the right system design. It's harder to buffer those pesky
O_DIRECT WAL writes when they blow right though at least one level of
cache.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD