pgbench appears to already support arbitrary SQL queries with the -f switch, so why couldn't we just make it a little smarter and have people enable SQL query logging for a day or two, then pass the log off to pgbench:
pgbench -f <log file> Seems to me like that wouldn't be too difficult to do, and would give much closer "real-world" results than pgbench's built-in benchmark. On top of that the community could start offering up "template" benchmarks like: "busy website", "data warehouse", "forums", "financial" and distribute them with pgbench: pgbench -f templates/data_warehouse.pgbench pgbench -f templates/forums.pgbench ... From that point a brute force auto-tune utility would be pretty straight forward to write. pgautotune -f templates/data_warehouse.bench,myapp.sqllog Or if one server runs multiple custom apps that you want to tune for: pgautotune -f myapp1.sqllog,myapp2.sqllog,myapp3.sqllog Even if it took 48hrs to run, it would be a good burn-in test for a brand new server. ;) On Fri, 2006-06-02 at 19:38 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > >> One objection to this is that after moving "off the gold standard" of > >> 1.0 = one page fetch, there is no longer any clear meaning to the > >> cost estimate units; you're faced with the fact that they're just an > >> arbitrary scale. I'm not sure that's such a bad thing, though. For > >> instance, some people might want to try to tune their settings so that > >> the estimates are actually comparable to milliseconds of real time. > > > Any chance that the correspondence to time could be made a part of the > > design on purpose and generally advise people to follow that rule? > > We might eventually get to that point, but I'm hesitant to try to do it > immediately. For one thing, I really *don't* want to get bug reports > from newbies complaining that the cost estimates are always off by a > factor of X. (Not but what we haven't gotten some of those anyway :-() > In the short term I see us sticking to the convention that seq_page_cost > is 1.0 in a "typical" database, while anyone who's really hot to try to > make the other happen is free to experiment. > > > If we could tell people to run *benchmark* and use those numbers > > directly as a first approximation tuning, it could help quite a bit > > for people new to PostgreSQL experiencing poor performance. > > We don't have such a benchmark ... if we did, we could have told > people how to use it to set the variables already. I'm very very > suspicious of any suggestion that it's easy to derive appropriate > numbers for these settings from one magic benchmark. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Mike Benoit <[EMAIL PROTECTED]>
signature.asc
Description: This is a digitally signed message part