[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