[Bucardo-general] Performance option...

[Michelle Sullivan]

Michelle Sullivan wrote:
>
> 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?)
>   

Ok running a test using the 'dangerous' patch shows it works - mostly.

The next 10000 transactions are sync'd and the speed improvement is
significant - the memory usage is reduced from G's to around 140M (on my
setup).

However 'mostly' because there are a couple of problems:

* First I can't count - I have 66m rows in the bucardo_delta table not
6.6m ;-)
* The first 10000 are synced but no more are until a
bucardo.bucardo_purge_delta is performed.
* Because of the bucardo.bucardo_purge_delta issue it's only replicating
at 40k/hour (using the default crontab of every 15 mins)


Solution is going to be use a temp table to collect the transaction
times and select 10k at a time and remove the transactions until the
table is empty.  When it is empty commit the sync.... I'm investigating
the code deeper to see how easy this is, but Greg, you'd be better as
your coding style vs mine is very different.  Want me to produce a patch
or would you like to look at it...?

Michelle

PS: finally found the IRC channel on freenode if you want to chat about
it.  I'm 'Shells'