Re: Why does slony use a cursor? Anyone know?

On 6 March 2013 14:35, Shaun Thomas <sthomas@optionshouse.com> wrote:
> On 03/06/2013 04:49 AM, Glyn Astill wrote:
>
>> What version of slony are you on?  The specifics of what you mention
>> don't sound quite right, but it sounds very much like bug 167 which
>> was fixed in 2.1.2 if I remember correctly.
>
>
> We're on 2.1.2. Presumably, anyway. I didn't encounter the problem in stage
> when I set up a testbed. But it also might not be related. The problem I can
> tell from the logs, is that it was closing the cursor pretty much right as
> soon as it got the results. 75 seconds to set up a cursor of that size and
> then an hour to sync all the data isn't a problem. 75 seconds for every 500
> rows *is*.
>
> The stage test I did didn't do that when I deleted 20M rows from a 50M row
> table, but I also only set it up with a single replication set. My next test
> will be to test with two or three replication sets that all get big deletes
> like that. My guess is that it can't adequately swap between them on SYNC
> events, so it has to rebuild the cursor every time.
>
> Either way, we're likely to be switching to an ETL system because we need to
> start scaling horizontally soon. Unless I want to set up a bunch of
> partition targets, we'll pretty much have to drop Slony then. I just want to
> keep it working until then. :)
>

A cursor can make use of indexes for sorting, so an index on
sl_log_1/2(log_actionseq) may help.

Regards,
Dean