Re: [Testperf-general] pg_autovacuum w/ dbt2

Guys,

> So, here's my suggested test series for autovacuum.  After we have these
> results, we can introduce vacuum delay to see if it has an effect:
>
> 1) Control Test.  No autovacuum.
> shared_buffers = 60000
> maintainence_work_mem = 65536
> default_statistics_target = 100
> effective_cache_size = 100000
> all stats_* on
> rest of params as run 207

On second thought, this isn't going to do much good outside of the control
test.   Other than Warehouse and District, even on the most aggresive
settings the other tables won't get vacuumed inside of even 2 hours.  For
example, these very busy tables have the following vacuum skew at the end of
the run:

[2005-01-06 15:36:59 PST] INFO:      table name: dbt2."public"."orders"
[2005-01-06 15:36:59 PST] INFO:         relid: 17263;   relisshared: 0
[2005-01-06 15:36:59 PST] INFO:         reltuples: 18011200.000000;  relpages:
162775
[2005-01-06 15:36:59 PST] INFO:         curr_analyze_count: 1553269;
curr_vacuum_count: 730780
[2005-01-06 15:36:59 PST] INFO:         last_analyze_count: 0;
last_vacuum_count: 0
[2005-01-06 15:36:59 PST] INFO:         analyze_threshold: 18011700;
vacuum_threshold: 36023400

[2005-01-06 15:36:59 PST] INFO:      table name: dbt2."public"."stock"
[2005-01-06 15:36:59 PST] INFO:         relid: 17275;   relisshared: 0
[2005-01-06 15:36:59 PST] INFO:         reltuples: 59989800.000000;  relpages:
2936743
[2005-01-06 15:36:59 PST] INFO:         curr_analyze_count: 8629017;
curr_vacuum_count: 8629017
[2005-01-06 15:36:59 PST] INFO:         last_analyze_count: 0;
last_vacuum_count: 0
[2005-01-06 15:36:59 PST] INFO:         analyze_threshold: 59990300;
vacuum_threshold: 119980600

As you can see, they don't really even *need* to be vacuumed.    Analyzed,
maybe.   So I'm really wondering if we'd get any results worth testing in
less than a 6-hour run.

Ideas, anyone?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco