[Bucardo-general] Swap replication latency

Wed May 11 13:41:42 UTC 2011

I've experimented and collected a little more information for some 
follow-up:

On 05/10/2011 07:41 AM, Alex Balashov wrote:

> On 05/09/2011 11:28 PM, Greg Sabino Mullane wrote:

>> You could also consider turning off 'ping' for the sync, and thus
>> removing the NOTIFY triggers, and have the sync use checktime
>> instead.
>
> That I have not tried, but is there any reason to think it would
> result in higher throughput, assuming that the change operations are
> fairly uniformly distributed throughout time (they more or less are)?
>    I would think this would be advantageous only if there are
> occasional batched operations.

This did not appear to have any effect.  If anything, the real-time 
NOTIFYs seem better.

>> However, it's more likely there is some other problem - having a look
>> at the log.bucardo file would be the best way for us to diagnose from
>> afar, but some general tips that may apply to your situation or not:
>
> I will attempt to provide that soon-ish.

What I'm getting out of the log file by following the activity of a 
particular KID, as you suggested, is that it's executing statements very 
slowly for no apparent reason.  Note the timestamps:

May 11 09:26:54 db Bucardo[20300]: KID [18/300] public.location UPDATE 
source to target pk 2242559
May 11 09:26:54 db Bucardo[20300]: KID [19/300] public.location UPDATE 
source to target pk 2242590
May 11 09:26:54 db Bucardo[20300]: KID [20/300] public.location UPDATE 
source to target pk 2242593
May 11 09:26:54 db Bucardo[20300]: KID [21/300] public.location UPDATE 
source to target pk 2242731
May 11 09:26:54 db Bucardo[20300]: KID [22/300] public.location UPDATE 
source to target pk 2242849
May 11 09:26:54 db Bucardo[20300]: KID [23/300] public.location UPDATE 
source to target pk 2242914
May 11 09:26:54 db Bucardo[20300]: KID [24/300] public.location UPDATE 
source to target pk 2242935
May 11 09:26:54 db Bucardo[20300]: KID [25/300] public.location UPDATE 
source to target pk 2243080
May 11 09:26:54 db Bucardo[20300]: KID [26/300] public.location UPDATE 
source to target pk 2243090
May 11 09:26:55 db Bucardo[20300]: KID [27/300] public.location UPDATE 
source to target pk 2243093
May 11 09:26:55 db Bucardo[20300]: KID [28/300] public.location UPDATE 
source to target pk 2243112
May 11 09:26:55 db Bucardo[20300]: KID [29/300] public.location UPDATE 
source to target pk 2243159
May 11 09:26:55 db Bucardo[20300]: KID [30/300] public.location UPDATE 
source to target pk 2243168
May 11 09:26:55 db Bucardo[20300]: KID [31/300] public.location UPDATE 
source to target pk 2243228
May 11 09:26:55 db Bucardo[20300]: KID [32/300] public.location UPDATE 
source to target pk 2243238
May 11 09:26:55 db Bucardo[20300]: KID [33/300] public.location UPDATE 
source to target pk 2243239
May 11 09:26:55 db Bucardo[20300]: KID [34/300] public.location UPDATE 
source to target pk 2243244
May 11 09:26:55 db Bucardo[20300]: KID [35/300] public.location UPDATE 
source to target pk 2243246
May 11 09:26:55 db Bucardo[20300]: KID [36/300] public.location UPDATE 
source to target pk 2243252
May 11 09:26:55 db Bucardo[20300]: KID [37/300] public.location UPDATE 
source to target pk 2243261
May 11 09:26:55 db Bucardo[20300]: KID [38/300] public.location UPDATE 
source to target pk 2243265
May 11 09:26:55 db Bucardo[20300]: KID [39/300] public.location UPDATE 
source to target pk 2243274
May 11 09:26:55 db Bucardo[20300]: KID [40/300] public.location UPDATE 
source to target pk 2243283
May 11 09:26:55 db Bucardo[20300]: KID [41/300] public.location UPDATE 
source to target pk 2243289
May 11 09:26:55 db Bucardo[20300]: KID [42/300] public.location UPDATE 
source to target pk 2243319
May 11 09:26:56 db Bucardo[20300]: KID [43/300] public.location UPDATE 
source to target pk 2243320
May 11 09:26:56 db Bucardo[20300]: KID [44/300] public.location UPDATE 
source to target pk 2243322
May 11 09:26:56 db Bucardo[20300]: KID [45/300] public.location UPDATE 
source to target pk 2243325
May 11 09:26:56 db Bucardo[20300]: KID [46/300] public.location UPDATE 
source to target pk 2243332
May 11 09:26:56 db Bucardo[20300]: KID [47/300] public.location UPDATE 
source to target pk 2243357
May 11 09:26:56 db Bucardo[20300]: KID [48/300] public.location UPDATE 
source to target pk 2243361
May 11 09:26:56 db Bucardo[20300]: KID [49/300] public.location UPDATE 
source to target pk 2243362
May 11 09:26:56 db Bucardo[20300]: KID [50/300] public.location UPDATE 
source to target pk 2243363
May 11 09:26:56 db Bucardo[20300]: KID [51/300] public.location UPDATE 
source to target pk 2243393
May 11 09:26:56 db Bucardo[20300]: KID [52/300] public.location UPDATE 
source to target pk 2243403

This isn't enough to keep with volume overall.

>> * Keep bucardo_delta and bucardo_track small by aggressive purging
>> via cron and the bucardo_purge_delta function. Same for the q table.
>> If they ever get big, make sure you VACUUM them as well - preferably
>> VACUUM FULL then REINDEX if needed. The bucardo_delta table should
>> only have thousands of rows, even on a busy server.

Yep, I run bucardo_delta_purge() and bucardo_purge_q_table() with '1 
minute'::interval every minute.

>> * Minimize the number of tables in a sync. It's better in Bucardo 4
>> to have many sync with few tables rather than few syncs with many
>> tables each.
>
> I'll definitely experiment with that approach.  However, in general,
> we have about 50 tables, about 45 of which are very static and hardly
> ever change.  The sync run time for those 45 is never above 5 seconds.

There is something to this idea, in that it allows me to separate the 
fast-moving parts of the database from the slow-moving ones, so that the 
slow-moving ones aren't bogged down.  However, even when I isolate the 
high-volume tables each in their own sync, 3-5 INSERT or UPDATE 
statements/sec is proving too much throughput for Bucardo to keep up.

I also tried lowering the sleep and ping times, to no apparent effect.

-- 
Alex Balashov - Principal
Evariste Systems LLC
260 Peachtree Street NW
Suite 2200
Atlanta, GA 30303
Tel: +1-678-954-0670
Fax: +1-404-961-1892
Web: http://www.evaristesys.com/