Re: Performance

Dne 27.4.2011 23:55, Greg Smith napsal(a):

> The easiest place to start is by re-using the work already done by the
> TPC for benchmarking commercial databases. There are ports of the TPC
> workloads to PostgreSQL available in the DBT-2, DBT-3, and DBT-5 tests;
> see http://wiki.postgresql.org/wiki/Category:Benchmarking for initial
> information on those (the page on TPC-H is quite relevant too). I'd
> like to see all three of those DBT tests running regularly, as well as
> two tests it's possible to simulate with pgbench or sysbench: an
> in-cache read-only test, and a write as fast as possible test.

That's a natural first step, I guess.

> The main problem with re-using posts from this list for workload testing
> is getting an appropriately sized data set for them that stays
> relevant. The nature of this sort of benchmark always includes some
> notion of the size of the database, and you get different results based
> on how large things are relative to RAM and the database parameters.
> That said, some sort of systematic collection of "hard queries" would
> also be a very useful project for someone to take on.

Yes, I'm aware of that. The examples posted to the lists usually lack
the data, but I guess we could get it at least from some of the posters
(anonymized etc.). And some of the examples are rather simple so it's
possible to generate as much data as you want using a PL/pgSQL or so.

Anyway I hesitate to call those examples 'workloads' - it's usually just
one query, sometimes two. But it's still a useful test IMHO.

I was thinking about several VMs, each with a different configuration
(amount of RAM, CPU, ...). The benchmarks might be a bunch of very
simple scripts I guess, each one taking care of preparing the data,
running the test, uploading the results somewhere.

And I guess it'd be useful to make this awailable for download, so that
everyone can run the tests locally ...

A bit naive question - where to run this? I know there's a build farm
but I guess this it's mostly for building and not for such benchmarks.

> People show up regularly who want to play with the optimizer in some
> way. It's still possible to do that by targeting specific queries you
> want to accelerate, where it's obvious (or, more likely, hard but still
> straightforward) how to do better. But I don't think any of these
> proposed exercises adjusting the caching model or default optimizer
> parameters in the database is going anywhere without some sort of
> benchmarking framework for evaluating the results. And the TPC tests
> are a reasonable place to start. They're a good mixed set of queries,
> and improving results on those does turn into a real commercial benefit
> to PostgreSQL in the future too.

100% true.

regards
Tomas