From: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net> |
---|---|
To: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>, "Jim C(dot) Nasby" <jim(at)nasby(dot)net>, Hackers <pgsql-hackers(at)postgresql(dot)org>, Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com> |
Subject: | Re: autovacuum next steps, take 2 |
Date: | 2007-02-26 23:23:22 |
Message-ID: | 45E36BEA.4060902@zeut.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:
>> How can you determine what tables can be vacuumed within
>> autovacuum_naptime?
>
> My assumption is that
> pg_class.relpages * vacuum_cost_page_miss * vacuum_cost_delay = time to vacuum
>
> This is of course not the reality, because the delay is not how long
> it takes to fetch the pages. But it lets us have a value with which we
> can do something. With the default values, vacuum_cost_delay=10,
> vacuum_cost_page_miss=10, autovacuum_naptime=60s, we'll consider tables
> of under 600 pages, 4800 kB (should we include indexes here in the
> relpages count? My guess is no).
I'm not sure how pg_class.relpages is maintained but what happens to a
bloated table? For example, a 100 row table that is constantly updated
and hasn't been vacuumed in a while (say the admin disabled autovacuum
for a while), now that small 100 row table has 1000 pages in it most of
which are just bloat, will we miss this table? Perhaps basing this on
reltuples would be better?
> A table over 600 pages does not sound like a good candidate for hot, so
> this seems more or less reasonable to me. On the other hand, maybe we
> shouldn't tie this to the vacuum cost delay stuff.
I'm not sure it's a good idea to tie this to the vacuum cost delay
settings either, so let me as you this, how is this better than just
allowing the admin to set a new GUC variable like
autovacuum_hot_table_size_threshold (or something shorter) which we can
assign a decent default of say 8MB.
Thoughts?
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2007-02-26 23:25:31 | Re: COMMIT NOWAIT Performance Option |
Previous Message | Tom Lane | 2007-02-26 23:14:24 | Re: COMMIT NOWAIT Performance Option (patch) |