Re: Concurrent HOT Update interference

On 10 May 2013 14:13, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> So what we have is that multiple UPDATEs repeatedly accessing the same
>> block will prevent each other from successful cleanup, since while one
>> session is performing the update, the second session is pinning the
>> block with an indexscan.
>
>> This effect has been noted for some time during pgbench runs, where
>> running with more sessions than scale factors causes contention. We've
>> never done anything about it because that's been seen as a poorly
>> executed test, whereas it does actually match the real situation we
>> experience at "hot spots" in the table.
>
> Uh, no. pgbench's problem at high scale factors is that multiple
> sessions want to update *the same row*, not just different rows on the
> same page. That contention is unavoidable.
>
> You may in fact have a good point, but you can't prove it by reference
> to pgbench.

I wasn't dissing pgbench, just saying that we've all witnessed the
case I'm discussing many times and looked passed it because we were
looking at general scalability, which we have now done a good job on
(well done, team).

There are two related use cases that demonstrate poor behaviour:

a) Updating two separate rows that happen to be on the same block will
eventually cause one or both of the rows to migrate to separate blocks
because of 1) the inability to clean the existing block and 2) the way
our fsm algorithm gives you a clean new block away from other people.
That leads to a one-block-per-row situation, or in other words quite
bad bloating, which seems to be avoidable, hence this thread.

b) Updating the same row is also relevant, since concurrent updates
cannot escape each other, so we get contention and bloating because
HOT page cleanup is ineffective. The best we can achieve in this case
is to make HOT page cleanup work, though we cannot ever escape the
contention.

Updating the same row is easier to reproduce with standard pgbench;
the effect is shown very well by using -s 1 -c 4
though even more purely by running the attached test

pgbench -i
pgbench -c 4 -t 10000 -f update_same_row.pgb

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services