Re: vacuum, performance, and MVCC

Tom Lane wrote:
> If you're doing heavy updates of a big table then it's likely to end up
> visiting most of the table anyway, no? There is talk of keeping a map
> of dirty pages, but I think it'd be a win for infrequently-updated
> tables, not ones that need constant vacuuming.
>
> I think a lot of our problems in this area could be solved with fairly
> straightforward tuning efforts on the existing autovacuum
> infrastructure. In particular, someone should be looking into
> recommendable default vacuum-cost-delay settings so that a background
> vacuum doesn't affect performance too much. Another problem with the
> current autovac infrastructure is that it doesn't respond very well to
> the case where there are individual tables that need constant attention
> as well as many that don't. If you have N databases then you can visit
> a particular table at most once every N*autovacuum_naptime seconds, and
> *every* table in the entire cluster gets reconsidered at that same rate.
> I'm not sure if we need the ability to have multiple autovac daemons
> running at the same time, but we definitely could use something with a
> more flexible table-visiting pattern. Perhaps it would be enough to
> look through the per-table stats for each database before selecting the
> database to autovacuum in each cycle, instead of going by "least
> recently autovacuumed".
>
> Bottom line: there's still lots of low-hanging fruit. Why are people
> feeling that we need to abandon or massively complicate our basic
> architecture to make progress?

I think at some point we have to admit that _polling_ the tables, which
is what autovacuum does, just isn't going to work well, no matter how
much it is tweeked, and another approach should be considered for
certain workload cases.

At some point, the autovacuum approach starts to look like a car with
fifty bumper stickers. The first few were fine, but at some point, the
tweeks (bumper stickers) start to overwhelm the car, and it is time to
look for a new car.

I think particularly for the UPDATE with no index key changes, a new
approach must be considred.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +