Scott Marlowe <[email protected]> writes: > On Mon, Nov 9, 2009 at 7:52 AM, Jan Wieck <[email protected]> wrote: >> On 11/7/2009 11:23 PM, Scott Marlowe wrote: >>> >>> On Sat, Nov 7, 2009 at 9:02 PM, Vick Khera <[email protected]> wrote: >>>> >>>> On Fri, Nov 6, 2009 at 4:07 PM, Scott Marlowe <[email protected]> >>>> 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.
A browse of the code in SetAddTable_int() doesn't show off anything that *ought* to be notably slow. There's a subquery that is a bit pointless: ... where attrelid = (select oid from pg_catalog.pg_class where oid = v_tab_reloid) That subquery could be replaced by v_tab_reloid. But it shouldn't worsen things because there's an index on pg_class(oid). The more major set of queries are in alterTableForReplication(). I'd be suspicious of it. You could exercise alterTableForReplication() by trying that over and over again in conjunction with alterTableRestore()... Pick a table, say the one with ID 5... begin; explain analyze select "_MyNameSpace".alterTableRestore(5); explain analyze select "_MyNameSpace".alterTableForReplication(5); commit; Throwing some notify requests into alterTableForReplication(), and logging to the millisecond, should help track down a query that is slower than it ought to be. -- let name="cbbrowne" and tld="ca.afilias.info" in name ^ "@" ^ tld;; Christopher Browne "Bother," said Pooh, "Eeyore, ready two photon torpedoes and lock phasers on the Heffalump, Piglet, meet me in transporter room three" _______________________________________________ Slony1-general mailing list [email protected] http://lists.slony.info/mailman/listinfo/slony1-general
