[Bucardo-general] Performance option...

[Michelle Sullivan]

Michelle Sullivan wrote:
> Greg Sabino Mullane wrote:
>   
>> On Sat, Aug 07, 2010 at 02:34:04PM +0200, Michelle Sullivan wrote:
>>   
>>     
>>> Ok 2 days after resolving my swap issues it's still trying to process
>>> the sync... still over 1 day of swapping (3G RAM on the machine, 32G
>>> swap, and the process is just over 20G)..
>>>
>>> May I make a suggestion - have an option to 'Tie::Hash' to a file and
>>> avoid the huge amount of swapping... maybe a manual config option that
>>> will warn of the delays it can cause, or perhaps a quick count of the
>>> delta table and if there is over 100k entries (configurable)
>>> automatically use it...?
>>>     
>>>       
>> Sounds okay to me, although I'd have to poke around in the code to 
>> see how much we really can offload like that, I seem to recall there 
>> might be some issues with the stuff passed to the customcodes.
>>
>>   
>>     
>
>
> Poked! ;-)
>
>
>   
<snip>
> Would a patch like this work?
>
> Thanks,
>
> Shells
>   

I should add this which will show the issue:

$ psql -X -q -d sorbs -U pgsql -c " SELECT    DISTINCT d.rowid AS
"BUCARDO_ID",
>              t.rawid
,msgid,msgdeliverer,msgsender,msgchksum,msgreceived,msgbody
>    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 public.rawevidence t ON (t.rawid::int8 = d.rowid::int8)
>    WHERE     d.tablename = 9830363::oid
>    AND       NOT EXISTS ( 
>                    SELECT 1               
>                    FROM   bucardo.bucardo_track bt
>                    WHERE  d.txntime = bt.txntime 
>                    AND    bt.targetdb = 'usmaster'::text
>                    AND    bt.tablename = 9830363::oid
>              )                      
> " > /tmp/10000rows.txt
$ wc /tmp/10000rows.txt
    9984 2415440 44895993 /tmp/10000rows.txt
$ ls -lh /tmp/10000rows.txt
-rw-r--r--  1 root  wheel    43M Aug 23 04:57 /tmp/10000rows.txt
$

Umm 43M with 10k rows and was it 6M it's trying to sync? ;-)

Michelle