Re: Test lab

On Sat, 3 Nov 2007, Stefan Kaltenbrunner wrote:

> there is the various dbt workloads,sysbench, jans tpc-w implementation,
> hell even pgbench

The DBT workloads are good for simulating disk-bound operations, but I
don't think they're sufficient by themselves for detecting performance
regressions because of that. TPC-W might serve to better simulate when
things are CPU-bound, that particular implementation felt a bit out of
date when I tried using it and I think it could use a round of polishing.

I never got the database tests in SysBench to produce useful results, the
minute I cranked the number of simultaneous clients up there were deadlock
issues that suggested the PostgreSQL porting effort still needed work.
Lots of general crashes when I was testing that as well.

pgbench can work well for testing low-level operations. I use it
frequently to see how fast a system can execute individual statements, and
in that context I've found it useful for finding performance regressions.
If you run it enough to average out the noise the results can be stable
(I've been working on some pgbench tools to do just that:
http://www.westnet.com/~gsmith/content/postgresql/pgbench-tools.htm ) The
main problem I've run into is that the pgbench binary itself becomes
increasingly a bottleneck once the client load increases. The simple,
single select()/parse/execute loop it runs breaks down around 50 clients
on the systems I've tested, and you really need to run pgbench on another
server to reach even 100 usefully.

The big problem with all these benchmarks is that none of them stress
query planning in any useful way. One thing I've been looking for is a
public data set and tests that depend on the planner working correctly in
order to work efficiently. For example, it would be great to be able to
show someone how to test whether they had correctly analyzed the tables
and set shared_buffers + effective_cache_size usefully on a test system.
I envision loading a bunch of data, then running a difficult plan that
will only execute effectively if the underlying components are tuned
properly. Sadly I don't actually know enough about that area to write
such a test myself.

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