Re: Test lab - Mailing list pgsql-hackers

Greg Smith wrote:
> 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.

sure it might need work but it is still a noteworthy thing we could use
(or at least seriously evaluate) and it seems a bit wrong to judge on
what might (or what might not) detect a regression in that regard.
Especially since we don't have any long term consistant tracking yet so
we don't really know what is good and what not.

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

hmm I have not seen that and the recent freebsd related scalability
benchmarks(http://people.freebsd.org/~kris/scaling/)  seem to indicate
that it seems to work quite well at least for some stuff.

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

well it might still give us a baseline to compare against - but point taken.

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

well one thing I have been been wondering about if it might make sense
as a start to just troll -hackers and -bugs from the past few years and
collect all the bug/regression reproduction samples posted (especially
the planner related ones) and do benchmarking/testing with a special
focus on plan changes or planning time/quality regressions.


Stefan