On Mon, Nov 9, 2009 at 7:52 AM, Jan Wieck <JanWieck at yahoo.com> wrote:
> On 11/7/2009 11:23 PM, Scott Marlowe wrote:
>>
>> On Sat, Nov 7, 2009 at 9:02 PM, Vick Khera <vivek at khera.org> wrote:
>>>
>>> On Fri, Nov 6, 2009 at 4:07 PM, Scott Marlowe <scott.marlowe at gmail.com>
>>> wrote:
>>>>
>>>> I have about 1500 objects in my main db, and running create set takes
>>>> about 5 seconds per table or sequence.  Is this typical?  Any changes
>>>> I can make in terms of cost of any slony functions or creating indexes
>>>> to make it faster?
>>>>
>>>
>>> The most complicated one I have has about 250 tables and about 50
>>> sequences.  I don't recall it taking that long to create a set.
>>> Perhaps the time is proportional to the number of objects?  Which
>>> versions of everything are you using?  I run Pg 8.3 and slony1 1.2.x.
>>
>> Yep, pg 8.3.7 and slony 1.2.14 at the moment.  If I do this on a
>> machine with JUST the objects I'm replicating, I can get 10 seconds
>> per table and 5 or so per sequence.
>>
>> On the DB with 29000 other objects not replicated, it gets up to 30
>> seconds per table and 20 seconds per sequence.  Which means a 5 hour
>> time for create set.  And if autovac kicks in it blocks create set.
>> so I had to turn that off.
>
> This sounds like some of the system catalog operations done by Slony are
> using seq scans. I've never tested Slony with a database having that many
> objects.

Could be.  But whatever it is it must be doing it over and over (i.e.
a seq scan feeding a nested loop ARG!!!)  because I can seq scan
things like pg_class in way under a second.  But if I had to do it 37k
times or so it would then be as slow as I'm seeing.  Got some good
troubleshooting tips I'm gonna look at.  Thanks to all who posted.
I'll keep you informed.