[Bucardo-general] Fwd: Syncing smaller batches

[Ioana Danes]

Hello Greg,


On Mon, Nov 2, 2015 at 2:55 PM, Greg Sabino Mullane <greg at endpoint.com>
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 greg at endpoint.com
> End Point Corporation
> PGP Key: 0x14964AC8
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20151103/9c8af59b/attachment.html>