Re: HOT patch - version 15

On Sat, 2007-09-08 at 23:13 -0400, Bruce Momjian wrote:

> Defragementation is the HOT feature we really want. Single-chain
> pruning is kind of nice because it speeds things up, but isn't
> necessary. The fact that the entire chain is on the same page makes me
> think that we could just leave single-chain pruning for 8.4 if
> necessary.

Agreed

> I think allowing the chain to be more often on the same page via
> defragmentation and having a single index entry for the chain is going
> to be a win, and the fact we don't have marked-dead index entries for
> some of the chain isn't going to be a problem. My guess is that the
> marked-dead index entries were a win only when the chain was on several
> pages, which isn't the case for HOT chains.

Agreed

> In summary, I feel we have the HOT mechanics down well, but the open
> issue is _when_ to activate each operation.

That has been the only open issue for months. The interplay of behaviour
is where HOT succeeds and fails. Some behaviours sound like they will be
good, but aren't.

> However, there are two complexities to this. One, we can't be sure we
> can defragment when we need it because we might not get the lock, and

That is a probability call. Heikki measured that, and its a good bet.

If we do fail to fragment, then one of the rows will migrate to another
block. There will likely be fewer backends contending for that block and
then fragmentation will succeed. So this behaviour is self-balancing.

> second, we are only going to try to put a row on a page if we are
> updating a row on that page. If the page is 90% dead but no rows are
> being updated on that page no one will try to add a row to the page
> because FSM thinks it is full. That might be OK, it might not.

We must consider data row contention as well as space efficiency.

In the current system UPDATE contention is not an issue because we treat
them as INSERTs and there is a good scheme in place to deal with INSERT
contention. It would be easy to neglect UPDATE contention issues with
HOT because of this.

If we actively send more backends towards a full block, we may increase
contention which is a bad thing, and we may cause defragmentation to
fail, which would be even worse. The net result will be that some
backends go to a different block again, so it seems simpler to not send
backends towards other blocks just because they are full.

> Another issue. My guess is that it will take 2-3 weeks to get HOT
> applied, meaning we aren't going to go to beta before October 1.

Seems reasonable.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com