Re: Benchmarking results, questions

Brian,

> * Why the closeness between RAID-1 with transaction log on the same
> array vs. on a separate array? Not running enough transactions to see
> the difference?

Because pgbench does small, simple transactions which don't involve as many
concurrent or sustained writes as a real OLTP application would. Since
writing to the DB files is so minimal, your main bottleneck is the Xlog.

The second area where RAID5 sucks is random seeks. However, it takes a
fairly large database (> 10x RAM) to demonstrate this.

> * Does it seem probable that in real-world use the RAID5 data + RAID1
> transaction log would not perform as well as in this test? Having a hot
> spare standby isn't such a bad thing if the trade-off is 3-5%.

I'd follow up with a more "realistic" test such as DBT2 or Jan-TPCW.

> * These are all with a default postgresql.conf. My plan was to test it
> out of the box to determine filesystem/array setup first and then tune
> the configuration after that. Does this approach make sense or am I
> potentially masking some underlying issues?

You're potentially masking some underlying issues.

> I read an (oldish from Bill Moran) filesystem comparison with 7.x and it
> seems like JFS was a speed demon (not done with pgbench):
> <http://www.potentialtech.com/wmoran/postgresql.php#results>.
> Unfortunately CentOS doesn't come with reiser, JFS or XFS by default.

Yeah, it sucks that way. Mind you, while we (GreenPlum, OSDL) have shown
good performance with XFS I'm reluctant to recommend it because I've also
seen a lot of instability using it on SuSE Linux (e.g. reboot and
"filesystem not found").

> Based on these results, I might try a 4-disk RAID-10 for the data and
> put the transaction log on the OS RAID-1 to get minimal striping for the
> data, although in real-world use, the OS disk would presumably be busier
> and it may reduce some of the performance.

Maybe ... depends on the server. In practice, I've found that 95% of OS
files get cache semi-permanently in RAM and there's very little disk
activity associated with the OS files. As a result, putting the XLog on
the disk with the OS files works fine.

> I know tuning is moot if you don't design the database well or properly
> index but with a fresh server, it seemed like a good opportunity to
> learn a thing or two. Any constructive input is appreciated.

Hey, there is no such thing as enough testing so your study is very
welcome.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco