Hannu Krosing wrote: > ?hel kenal p?eval, R, 2006-06-23 kell 13:08, kirjutas Tom Lane: > > Csaba Nagy <[EMAIL PROTECTED]> writes: > > >> Surprisingly its mostly WAL traffic, the heap/index pages themselves are > > >> often not yet synced to disk by time of vacuum, so no additional traffic > > >> there. If you had made 5 updates per page and then vacuum it, then you > > >> make effectively 1 extra WAL write meaning 20% increase in WAL traffic. > > > > > Is this also holding about read traffic ? I thought vacuum will make a > > > full table scan... for big tables a full table scan is always badly > > > influencing the performance of the box. If the full table scan would be > > > avoided, then I wouldn't mind running vacuum in a loop... > > > > 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. > > One thing that would help updates quite a lot in some scenarios is > keeping the pages only partially-filled, so that most updates could keep > the new version in the same page. I think that has also been discussed > as an option to vacuum and maybe as part of initial inserts. Maybe some > of it even ended up as a todo item.
We have a patch in the queue for index fillfactor which will be in 8.2. I am also hoping the frequently updated rows will migrate out to the empty pages. > > 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, > > My patch enabling effective continuous vacuum of fast-update tables, > while still being able to vacuum huge slowly changing ones is still not > applied. Without that patch there is no reason to vacuum the small and > fast changingg tables while vacuum on bigger tables is running, as it > won't clean out dead tuples anyway. I think it will be applied, but I am looking for someone else to eyeball it since Tom has come concerns. > > 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? > > Maybe we could start from reusing the index tuples which point to > invisible tuples ? The index is not MVCC anyway, so maybe it is easier > to do in-place replacement there ? > > This probably has the same obstacles which have prevented us from > removing those in the first place (removing instead of marking as > invisible). Does it cause some locking issues ? Or does it go against > some other constraints of our index lookups ? > > I think that just setting the invisible bit in an index leaf node causes > nearly as much disk io as removing the node. > > If we could delete/reuse old index tuples, it would solve a sizable > chunk of index-growth problem, especially for cases where referenced key > value does not change. I think heap _and_ index reuse is the only useful direction. Index or heap reuse alone seems too marginal for the added complexity. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster