[Bucardo-general] Latency on delete

[Alex Balashov]

Oh, and I should add, for the sake of completeness, that the q table and 
delta cleanup cron jobs are in place, running every few minutes.

On 01/17/2014 10:15 AM, Alex Balashov wrote:

> Hi,
>
> How long is a DELETE of ~4.5 million rows (in a relatively narrow table)
> supposed to take in a scenario with a simple pushdelta replication
> setup?  Mine took 1 hour and 45 minutes.  While I realise that Bucardo's
> row level-trigger based system incurs some additional latency, the
> latency is just inserting those deltas, by and large, right?  It
> shouldn't take quite so long as that, I would hypothesise.
>
> Both databases - including the 'bucardo' schema of the source - are
> tidily vacuumed nightly.
>
> The servers are both quad-core 2 GHz Xeons with 32 GB of RAM each with
> decent SATA drives on a Dell PERC.
>
> In addition, the following PostgreSQL tuning measures are in place:
>
>     shared_buffers = 512 MB
>     work_mem = 1024MB
>     wal_level = minimal
>     fsync = off
>     synchronous_commit = off
>
> The 'bucardo_ctl status' output does not show any delays, failures or
> other anomalies.
>
> The table on which I ran the DELETE statement had about 38 million rows,
> of which I was attempting to delete 4.5.  The table also has two indexes
> (primary key index and one additional indexed column) and a foreign key
> constraint.
>
> Is it reasonable and expected for this DELETE to have taken close to two
> hours?
>
> Thank much!
>
> -- Alex
>


-- 
Alex Balashov - Principal
Evariste Systems LLC
235 E Ponce de Leon Ave
Suite 106
Decatur, GA 30030
United States
Tel: +1-678-954-0670
Web: http://www.evaristesys.com/, http://www.alexbalashov.com/