Re: Extent Locks

Robert,

> But I still feel like that thought
> experiment indicates that there must be a solution here just by
> rejiggering the locking, and maybe with a bit of modest pre-extension.
> The mediocre results of my last couple tries must indicate that I
> wasn't entirely successful in getting the backends out of each others'
> way, but I tend to think that's just an indication that I don't
> understand exactly what's happening in the contention scenarios yet,
> rather than a fundamental difficulty with the approach.

Well, our practice of extending relations 8K-at-a-time is suboptimal on
quite a number of storage platforms. It leads to increased file
fragmentation, and increases write sizes on SSDs which have a default
128K block size. Also, on a large bulk load we spend way too much time
extending the relation.

My suggestion would be to have a storage parameter which defined the new
extent size for growing the table, and allocate that much free space in
the form of empty pages whenever we need new pages. The default would
be 1MB, but users could adjust it to anywhere between 8K and 1GB.

We'd still need an extent lock to add the 1MB (or whatever), but there's
a 128X difference between allocating 8K and 1MB.

The drawback to this is whatever size we choose is liable to be wrong
for some users. Users who currently have a lot of 16K tables would see
their databases grow alarmingly. But a default of 8K or 16K or 32K
wouldn't improve the current behavior except for the very advanced users
who know how to tinker with storage parameters.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com