Re: Relation extension scalability

On 3/30/15 10:48 PM, Amit Kapila wrote:
> > If we're able to extend based on page-level locks rather than the global
> > relation locking that we're doing now, then I'm not sure we really need
> > to adjust how big the extents are any more. The reason for making
> > bigger extents is because of the locking problem we have now when lots
> > of backends want to extend a relation, but, if I'm following correctly,
> > that'd go away with Andres' approach.
> >
>
> The benefit of extending in bigger chunks in background is that backend
> would need to perform such an operation at relatively lesser frequency
> which in itself could be a win.

The other potential advantage (and I have to think this could be a BIG
advantage) is extending by a large amount makes it more likely you'll
get contiguous blocks on the storage. That's going to make a big
difference for SeqScan speed. It'd be interesting if someone with access
to some real systems could test that. In particular, seqscan of a
possibly fragmented table vs one of the same size but created at once.
For extra credit, compare to dd bs=8192 of a file of the same size as
the overall table.

What I've seen in the real world is very, very poor SeqScan performance
of tables that were relatively large. So bad that I had to SeqScan 8-16
tables in parallel to max out the IO system the same way I could with a
single dd bs=8k of a large file (in this case, something like 480MB/s).
A single SeqScan would only do something like 30MB/s.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com