On Tue, Jun 19, 2012 at 8:06 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Jun 19, 2012 at 10:56 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> But in the 9.2 branch, the slow phenotype was re-introduced in
>> 1575fbcb795fc331f4, although perhaps the details of who is locking
>> what differs. I haven't yet sorted that out.
>
> It very much does. That commit prevents people from creating a
> relation in - or renaming a relation into - a schema that is being
> concurrently dropped, which in previous releases would have resulted
> in inconsistent catalog contents. I admit that it harms your test
> case, but how likely is it that someone is going to put every single
> table into its own schema?
Yep, I see that even having 10 tables per scheme (which I think was
the case for the person who started this line of inquiry) rather than
just 1 greatly reduces this regression. It basically goes from about
2x slower to about 1.1x slower. So I think that pretty much settles
the issue for 9.2.
> And have shared_buffers low enough for
> this to be the dominant cost?
That one is not so unlikely. After all, lowering shared_buffers is a
defensive move to get around the FlushRelationBuffers problem, so
hopefully people faced with the task of restoring such a dump would
take advantage of it, if they have a maintenance window in which to do
so. Also, the FlushRelationBuffers issue is linear while the locks
are quadratic, so eventually the locking would regain dominance even
if shared_buffers is large.
> I think in real-world scenarios this
> isn't going to be a problem - although, of course, making the lock
> manager faster would be nifty if we can do it, and this might be a
> good test case.
The resource owner reassign lock patch does effectively solve the lock
manager problem of restoring dumps of such databases, as well as the
similar problem in creating such dumps.
Cheers,
Jeff