Re: [Testperf-general] Re: ExclusiveLock

On Thu, 2004-11-18 at 22:51, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > The main problem on INSERTs is that it is usually the same few pages:
> > the lead data block and the lead index block. There are ways of
> > spreading the load out across an index, but I'm not sure what happens on
> > the leading edge of the data relation, but I think it hits the same
> > block each time.
>
> FSM does what it can to spread the insertion load across multiple pages,
> but of course this is not going to help much unless your table has lots
> of embedded free space. I think it would work pretty well on a table
> with lots of update turnover, but not on an INSERT-only workload.

OK, thats what I thought.

So with a table with an INSERT-only workload, the FSM is always empty,
so there only ever is one block that gets locked. That means we can't
ever go faster than 1 CPU can go - any other CPUs will just wait for the
block lock. [In Josh's case, 32 INSERT streams won't go significantly
faster than about 4 streams, allowing for some overlap of other
operations]

Would it be possible to: when a new block is allocated from the relation
file (rather than reused), we check the FSM - if it is empty, then we
allocate 8 new blocks and add them all to the FSM. The next few
INSERTers will then use the FSM blocks normally.

Doing that will definitely speed up DBT-2 and many other workloads. Many
tables have SERIAL defined, or use a monotonically increasing unique
key.

--
Best Regards, Simon Riggs