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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
