Re: Test lab - Mailing list pgsql-hackers

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@gregsmith.com http://www.gregsmith.com Baltimore, MD