[Bucardo-general] LEFT JOINs are killing performance...

Michelle Sullivan michelle at sorbs.net
Tue Jul 27 08:06:44 UTC 2010


Greg Sabino Mullane wrote:
> On Mon, Jul 26, 2010 at 11:35:59AM +0200, Michelle Sullivan wrote:
>   
>> I saw some messages previously about working on getting rid of the left
>> joins to speed up replication...
>>     
>
> Hmm..I don't think they are going away any time soon, so we should 
> probably figure out what's going on to make them slow otherwise...
>
> ...
>   
>> Bitmap Heap Scan on bucardo_delta d (cost=42877.71..668218.03 rows=2646208
>>     
> ...
>   
>> Bitmap Index Scan on bucardo_delta_public_nets2evid_txn
>>   (cost=0.00..42216.16 rows=2646208
>>     
> ...
>   
>> Bitmap Heap Scan on bucardo_track bt
>>   (cost=80259.57..347551.98 rows=1686442
>>     
>
> That's an awful lot of rows. Is bucardo_delta and bucardo_track being 
> purged regularly? How many unreplicated rows are there for the 
> table in question? bucardo_purge_delta() should empty out all the 
> no longer used rows (consider it a vacuum full for bucardo_delta), 
> and bucardo_compress_delta can slim down and remove duplicate rows 
> (consider it a regular vacuum for bucardo_delta). Do you have 
> bucardo_purge_delta() running as a cronjob?
>
>   

Well I don't recall reading about it but together with the post from
Mathieu Arnold I now have a cronjob... and the first run produced:

[michelle at ricochet ~]$ psql -X -q -d sorbs -U pgsql -c "SELECT
bucardo.bucardo_purge_delta('10 minutes'::interval)"
                              
bucardo_purge_delta                               
----------------------------------------------------------------------------------
 Rows deleted from bucardo_delta: 22282590 Rows deleted from
bucardo_track: 32190
(1 row)

No idea on the aumaster - it's still going some 2 hours later...!

This HowTo I'm going to do will save an aweful lot of heartache for the
next person... ;-)


Michelle



More information about the Bucardo-general mailing list