Heikki, Thanks for your comments, we do need some review on the expected behaviour.
On Wed, 2007-10-10 at 11:17 +0100, Heikki Linnakangas wrote: > Simon Riggs wrote: > > My thoughts are that it doesn't need to. Typically we create objects and > > then fill them. It isn't that frequent that we would load data, then > > delete or update more than 20% of it, then attempt other DDL. > > One scenario that comes to mind is a table that's used in OLTP fashion > during day, but it's taken offline for data loading during night. To > speed up the data loading, indexes are dropped before the load and > recreated afterwards. Yes, delaying the index re-creation could cause an effective outage, even if it is more efficient to let the VACUUM happen then re-add the indexes. > Even if there's no dead rows in a table, autovacuum will still kick in > to freeze it at some point. Yeh, but that can wait a while. > > If a COPY fails it will create dead rows, which should be cleared up by > > an autoVACUUM. If a COPY fails, the user knows to run a VACUUM or a > > re-TRUNCATE before re-attempting a modified COPY. So there is potential > > for more than one VACUUM to be attempted in that case. > > I wish the user didn't have to know to do that. Yeh, but thats an 8.4 feature now. > > So there could be an argument for TRUNCATE causing a cancellation of a > > VACUUM, but I don't see the use case for other DDL. Maybe it would be > > easier to make all conflicting lock requestors cancel VACUUM. > > Any VACUUM, or just autovacuum? After some thought, you and Michael have persuaded me that there is cause to do this for VACUUM as well, but just autovacuum, I think. That also makes the patch simpler, since we don't need to delve inside the av worker to see what it is doing. Alvaro: That means we can just skip your patch altogether, or at least we can discuss them separately now. > The only danger I can see is that the autovacuum is always killed and > never gets to finish, leading to degrading performance at first and > shutdown to prevent xid wraparound at the extreme. Doesn't seem likely > under normal circumstances, though. Yeh agreed. Table locks aren't that common, so I think we are safe for 100s of millions of transactions. The user has log messages to warn of that, so I think we're good. > A scenario that comes to mind is > having very lazy autovacuum settings, so that vacuum of the table takes > longer than 24h, and a daily cron job to run REINDEX. A table that big would have a REINDEX run for a very long time too, so I hope the user would notice before too long. > The "priority inheritance" scheme I proposed earlier would work well > with that: instead of killing the autovacuum, set cost delay to zero to > let it finish out of the way ASAP. It has it's own set of problems, > though. An innocent-looking DROP INDEX would cause the autovacuum to go > full steam ahead, hurting performance for others. Not very nice performance behaviour. > > I think it would be helpful if user-initiated VACUUMs waited behind > > another VACUUM that was already in progress on the table and then > > returned immediately as successful when the first VACUUM finishes. That > > would seem better than queuing up behind the first VACUUM and then > > repeating the process. > > I don't think that's a good idea. The second VACUUM wouldn't be a no-op, > it would clean up any dead rows accumulated during the first VACUUM. That was my first reaction to that thought too! In practice, whoever submitted the first VACUUM can re-run it. So that might be a custom program emitting a stream of VACUUMs or autovacuum doing the same thing. If we need to VACUUM almost continuously then autovacuum will realise this and re-submit. Sitting in the locking queue won't make anything more efficient; like waiting for Rolling Stones tickets at 4am doesn't make them play any better at the gig. I'm not proposing to do this latter idea for now though. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
