Hello Greg,
On Mon, Nov 2, 2015 at 2:55 PM, Greg Sabino Mullane <[email protected]> wrote: > > By disabling the autosync the performance on the primary master is good > up > > to 1200TPS, so I created a script that kicks the sync in an endless loop > > but it has hard time keeping up with the master. I created actually 3 > > syncs. One for the OTHER tables, one for table 1 and one for table 2, > just > > for testing. Even this way the standby master is way behind. The sync I > > noticed it is very slow but not sure what part, probably the one that > > builds the delete statements and the copy command. The servers are on the > > same msa and they have identical configurations. > > Yes, you definitely want autokick off in this case. If the tables > are logically linked (or even if they always get updated together) > I would make a single sync for them (one for OTHER, one for table1&2). > > You may want to try HEAD, I've made some improvements to the > delete/copy code that may help in your circumstance. Either way, > it would be good to see where the pain points are: maybe you could > email me some log output? > > I installed the latest version 5.4.1 and it does look a little faster but still not fast enough for my volume test. I watched postgres and noticed that the bottleneck is the function bucardo.bucardo_delta_check(text, text). I changed it a little to check if the latest record in delta table exists in the track table which is very fast and now the performance looks a lot better.: CREATE OR REPLACE FUNCTION bucardo.bucardo_delta_check(text, text) RETURNS SETOF text AS $BODY$ DECLARE myst TEXT; myrec RECORD; mycount INT; BEGIN FOR myrec IN SELECT * FROM bucardo.bucardo_delta_names WHERE sync = $1 ORDER BY tablename LOOP RAISE DEBUG 'GOT % and %', myrec.deltaname, myrec.tablename; myst = $$WITH d AS (SELECT d.txntime FROM bucardo.$$ || myrec.deltaname || $$ d ORDER BY d.txntime DESC LIMIT 1) SELECT 1 FROM d WHERE NOT EXISTS (SELECT 1 FROM bucardo.$$ || myrec.trackname || $$ t WHERE t.txntime = d.txntime AND (t.target = '$$ || $2 || $$'::text OR t.target ~ '^T:'))$$; EXECUTE myst; GET DIAGNOSTICS mycount = ROW_COUNT; IF mycount>=1 THEN RETURN NEXT '1,' || myrec.tablename; ELSE RETURN NEXT '0,' || myrec.tablename; END IF; END LOOP; RETURN; END; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER; ALTER FUNCTION bucardo.bucardo_delta_check(text, text) OWNER TO bucardo; I will have to run more tests to see how it performs in time and I will let you know. Thanks, Ioana -- > Greg Sabino Mullane [email protected] > End Point Corporation > PGP Key: 0x14964AC8 >
_______________________________________________ Bucardo-general mailing list [email protected] https://mail.endcrypt.com/mailman/listinfo/bucardo-general
