[Bucardo-general] Performance option...

[Michelle Sullivan]

Michelle Sullivan wrote:
> Looking into the code over dinner (so not a proper look) I see the
> following at line 4100:
>
>             ## Mark all unclaimed visible delta rows as done in the
> track table
>             ## This must be called within the same transaction as the
> delta select
>             ## no critic (ProhibitInterpolationOfLiterals)
>             $SQL{track} = qq{
>                 INSERT INTO bucardo.bucardo_track
> (txntime,targetdb,tablename)
>                 SELECT DISTINCT txntime, '\$1'::text, \$2::oid
>                 FROM bucardo.bucardo_delta d
>                 WHERE d.tablename = \$2::oid
>                 AND NOT EXISTS (
>                     SELECT 1
>                     FROM   bucardo.bucardo_track t
>                     WHERE  d.txntime = t.txntime
>                     AND    t.targetdb = '\$1'::text
>                     AND    t.tablename = \$2::oid
>                 );
>             };
>
> So I suspect this would have to be modified to accept the time of the
> latest transaction sync'd which should in theory be a simple change.
>
> Comments?
>   


The obvious - but dangerous - patch is as follows:

diff --git a/Bucardo.pm b/Bucardo.pm
index bc960b0..874f4f3 100644
--- a/Bucardo.pm
+++ b/Bucardo.pm
@@ -3993,7 +3993,12 @@ sub start_kid {
                 $SQL{delta} = qq{
                 SELECT    DISTINCT $drow,
                           BUCARDO_PK $aliaslist
-                FROM      bucardo.bucardo_delta d
+                FROM      (
+                               SELECT *
+                               FROM
+                                        bucardo.bucardo_delta dq
+                               WHERE dq.txntime IN ( SELECT DISTINCT
txntime FROM bucardo.bucardo_delta ORDER BY txntime ASC LIMIT 10000)
+                         ) AS d
                 LEFT JOIN $S.$T t ON BUCARDO_JOIN
                 WHERE     d.tablename = \$1::oid
                 AND       NOT EXISTS (
@@ -4034,7 +4039,12 @@ sub start_kid {
                 ## This is the main query: grab all changed rows since
the last sync
                 $SQL{delta} = qq{
                 SELECT  DISTINCT $rowids
-                FROM    bucardo.bucardo_delta d
+                FROM      (
+                               SELECT *
+                               FROM
+                                        bucardo.bucardo_delta dq
+                               WHERE dq.txntime IN ( SELECT DISTINCT
txntime FROM bucardo.bucardo_delta ORDER BY txntime ASC LIMIT 10000)
+                         ) AS d
                 WHERE   d.tablename = \$1::oid
                 AND     NOT EXISTS (
                            SELECT 1
@@ -4049,7 +4059,12 @@ sub start_kid {
                     ## no critic (ProhibitInterpolationOfLiterals)
                     $SQL{deltarate} = qq{
                     SELECT  DISTINCT txntime
-                    FROM    bucardo.bucardo_delta d
+                    FROM (
+                               SELECT *
+                               FROM
+                                        bucardo.bucardo_delta dq
+                               WHERE dq.txntime IN ( SELECT DISTINCT
txntime FROM bucardo.bucardo_delta ORDER BY txntime ASC LIMIT 10000)
+                         ) AS d
                     WHERE   d.tablename = \$1::oid
                     AND     NOT EXISTS (
                                SELECT 1
@@ -4098,7 +4113,12 @@ sub start_kid {
             $SQL{track} = qq{
                 INSERT INTO bucardo.bucardo_track
(txntime,targetdb,tablename)
                 SELECT DISTINCT txntime, '\$1'::text, \$2::oid
-                FROM bucardo.bucardo_delta d
+                FROM      (
+                               SELECT *
+                               FROM
+                                        bucardo.bucardo_delta dq
+                               WHERE dq.txntime IN ( SELECT DISTINCT
txntime FROM bucardo.bucardo_delta ORDER BY txntime ASC LIMIT 10000)
+                         ) AS d
                 WHERE d.tablename = \$2::oid
                 AND NOT EXISTS (
                     SELECT 1

dangerous because it assumes that SELECT DISTINCT txntime FROM
bucardo.bucardo_delta ORDER BY txntime ASC LIMIT 10000 always returns
the same rows.  I'm thinking about writing a patch to select into a
temporary table (${syncname}_txns) the 'LIMIT 10000' and then use that
in all 4 SQL statements... thoughts? (I'm after guidance because you
know the code ;-) have I missed something?)

Shells