> On Tue, March 7, 2006 21:04, Christopher Browne said:
>> Jim C. Nasby wrote:
>>>On Tue, Mar 07, 2006 at 03:36:57PM +0800, Christopher Kings-Lynne wrote:
>>>Wait, are we talking about exclusive locks here? Or do we just aquire
>>>shared locks to prevent anyone from running DDL against the objects?
>>>
>> This is exclusive locks, but let me make it clear, the locking under
>> discussion is taking place on the SUBSCRIBER, not on the ORIGIN.
>>
>> On the subscriber, the rough set of things that are done used to be:
>>
>> - Check that the 22 tables being replicated are there, and have
>> PK/candidate PK
>> Then, for each table...
>> - Delete all existing entries from it; if we have subtransactions,
>> then try to truncate it (TRUNCATE will take out a pretty exclusive lock)
>> - Copy the data from the source
>> endloop
>> Alter all 22 tables to add the "denyaccess()" trigger
>>
>> In 1.2, it changes a bit...
>>
>> - Check that the 22 tables being replicated are there, and have
>> PK/candidate PK. Lock the table as we go...
>> Then, for each table...
>> - Delete all existing entries from it; if we have subtransactions,
>> then try to truncate it (TRUNCATE will take out a pretty exclusive lock)
>> - Copy the data from the source
>> endloop
>> Alter all 22 tables to add the "denyaccess()" trigger.
>>
>> It sounds to me as though there might be value in having a slon option
>> that controls the two bits of "particularly lock-happy" logic so that,
>> for people who really know what they are doing, the locking regimen can
>> be less, erm, "harsh."
>>
>> Proposal for alternative: RFC
>>
>> There is a case to be made that people might want to have access to the
>> old data until such time as it gets replaced by new data. (And if they
>> drop a "deadlock bomb" on Slony-I, that's the risk they are taking
>> on...)
>>
>> Thus, if the option, oh, call it "subscriber_lock", which defaults to 1,
>> is set to 0, then some if clauses won't be invoked:
>>
>> if (subscriber_lock) {
>> lock_table(n);
>> }
>>
>> if (subscriber_lock) {
>> try_to_truncate_table(n);
>> } else {
>> just_delete_from(n);
>> }
>>
>> Does that seem like a good idea?
>
> I quite like it ;-) One things that comes to my mind is that
> slony should do a vacuum between delete_from(n) and copy_into(n).
> Otherwise, the table suddenly grows to twice it's size, and even
> more if the long-running subscribe-event fails a few times (We've
> had that problem due to network outages).
>
> greetings, Florian Pflug
You're asking for a contradiction, then.
- You wanted for the old data to be visible until COPY_SET completes;
- Now, you want to vacuum that old data away.
Only one of those is possible. In order for the old data to be visible,
we *can't* have vacuumed it away.
The better option, if we *do* want the wasted space to be gone, is to use
TRUNCATE to clear out the tables, as that is a lot cheaper than the
vacuums. (And, by the way, that needs a pretty full lock on the table,
pretty early...)
- LOCK ASAP+Truncate is the approach that will lead to the most efficient
behaviour;
- Delete+Avoid Locking is what permissively allows the subscriber to
appear reusable when it is being rebuilt.
Originally, we used Delete+Avoid Locking; after all the troubles
experienced with it, I changed over to LOCK ASAP in preparation for 1.2.
I daresay I wish we had 'LOCK ASAP' in 1.1.5 or even 1.1.2; we had a node
that had to be rebuilt _last night_, and the "permissive" approach in the
elder versions allowed it to run into something of a deadlock that
required restarting the COPY_SET after it had been nearly complete. I
didn't lose any sleep, but we didn't get the node back as early as hoped
for.
If we're rebuilding a node, the "best practice," as far as I'm concerned,
is to lock everything out (via pg_hba.conf) other than the slony user so
that nothing can get in edgewise and mess up the data copy. If we demand
exclusive locks up front on all the tables, that makes the pg_hba.conf
changes less needful.
Any time we have been "permissive" about letting connections in while a
node was rebuilding, it has been easy for it to lead to grief. I'm
willing to listen and consider making this sort of thing optional, but in
my experience, it is not only very much not a "best practice;" I find it
turns out badly. It did, last night, Yet Again, supporting my preference
for "Lock them early, Lock them up tight." Events are doing a good job of
convincing me that my position on the matter is right...
After having watched it turn out badly, your arguments for the "Delete +
Avoid Locking Permissive Approach" need to be very persuasive to overcome
that. When you suggest impossibilities, that really doesn't help the
case...
_______________________________________________
Slony1-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/slony1-general
