From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Bruce Momjian <bruce(at)momjian(dot)us>, Craig James <cjames(at)emolecules(dot)com>, Hugo <hugo(dot)tech(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: pg_dump and thousands of schemas |
Date: | 2012-05-25 21:28:02 |
Message-ID: | CAMkU=1xVWjX90EA2nBaobjOutJsu57SbN9T_R6_H9fgP9Vd=OA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
On Fri, May 25, 2012 at 1:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
>> For dumping entire databases, It looks like the biggest problem is
>> going to be LockReassignCurrentOwner in the server. And that doesn't
>> seem to be easy to fix, as any change to it to improve pg_dump will
>> risk degrading normal use cases.
>
>> If we want to be able to efficiently dump entire databases in a
>> scalable way, it seems like there should be some way to obtain a
>> data-base-wide AccessShare lock, which blocks AccessExclusive locks on
>> any object in the database, and turns ordinary object-level
>> AccessShare lock requests into no-ops.
>
> I thought a little bit about that, but it seems fairly unworkable.
> In the first place, pg_dump doesn't necessarily want lock on every table
> in the database.
The database-wide method could be invoked only when there are no
options given to pg_dump that limit to a subset. Or does that not
resolve the objection?
> In the second, such a lock mechanism would have
> logical difficulties, notably whether it would be considered to apply to
> tables created after the lock request occurs. If it does, then it would
> effectively block all such creations (since creation takes exclusive
> locks that ought to conflict).
That seems acceptable to me. With unrestricted dump, almost all other
DDL is locked out already, I don't know that locking out one more
thing is that big a deal. Especially if there is some way to
circumvent the use of that feature.
> If it doesn't, how would you implement
> that? In any case, we'd be adding significant cost and complexity to
> lock acquisition operations, for something that only whole-database
> pg_dump operations could conceivably make use of.
Before Robert's fast-path locks were developed, I wanted a way to put
the server into 'stable schema' mode where AccessExclusive locks were
forbidden and AccessShared were no-ops, just for performance reasons.
Now with fast-path, that might no longer be a meaningful feature.
If databases scale out a lot, won't max_locks_per_transaction, and the
amount of shared memory it would require to keep increasing it, become
a substantial problem?
> As far as the specific problem at hand goes, I think there might be a
> less invasive solution. I poked into the behavior with gdb (and you're
> right, LockReassignCurrentOwner does get called during portal drop)
> and noted that although pg_dump is indeed holding thousands of locks,
> any given statement that it issues touches only a few of them. So the
> loop in LockReassignCurrentOwner iterates over the whole lock table but
> does something useful at only a few entries.
>
> We could fix things for this usage pattern with what seems to me to
> be a pretty low-overhead method: add a fixed-size array to
> ResourceOwners in which we can remember up to N LOCALLOCKs, for N around
> 10 or so.
I had thought along these terms too. I think 10 would capture most of
the gain. with pg_dump, so far I see a huge number of resource owners
with maximum number of locks being 0, 2 or 4, and only a handful with
more than 4. Of course I haven't looked at all use cases.
The reason we want to limit at all is not memory, but rather so that
explicitly removing locks doesn't have to dig through a large list to
find the specific one to remove, therefore become quadratic in the
case that many locks are explicitly removed, right? Does anyone ever
add a bunch of locks, and then afterward go through and explicitly
remove them all in FIFO order? I think most users would either remove
them LIFO, or drop them in bulk. But better safe than sorry.
> Add a LOCALLOCK to that array when we add the ResourceOwner to
> that LOCALLOCK, so long as the array hasn't overflowed. (If the array
> does overflow, we mark it as overflowed and stop adding entries.) Then,
> in LockReassignCurrentOwner, we only iterate over the whole hash table
> if the ResourceOwner's array has overflowed. If it hasn't, use the
> array to visit just the LOCALLOCKs that need work.
>
> Comments?
I have some basic parts of this already coded up. I can try to finish
coding this up for CF next or next+1. I'm not yet sure how to avoid
weakening the boundary between resowner.c and lock.c, my original code
was pretty ugly there, as it was just a proof of concept.
What would be a situation that might be adversely affected by the
overhead of such a change? I think pgbench -S except implemented in a
plpgsql loop would probably do it.
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Kohei KaiGai | 2012-05-25 21:32:25 | Re: [RFC] Interface of Row Level Security |
Previous Message | Kohei KaiGai | 2012-05-25 21:08:57 | Re: [RFC] Interface of Row Level Security |
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Voras | 2012-05-25 22:04:58 | Parallel (concurrent) inserts? |
Previous Message | Tom Lane | 2012-05-25 20:02:50 | Re: pg_dump and thousands of schemas |