Jeff:

Thanks for your help.

I have some big/complicated indexes on one particular table and since
I have lots of memory to spare I've just now increased my
shared_buffers from 20000 to 40000 (and SHMMAX at the OS level
appropriately) and reload'ed postgresql but things are still
progressing slowly.

I notice in my /var/log/messages on the origin (and not on the subscriber) this:

May 16 14:55:26 coliseum postgres[21817]: [1-1] ERROR:  duplicate key
violates unique constraint "sl_nodelock-pkey"

sl=slon, right?

Doing this: select * from "_usedstuff".sl_nodelock; I get:

 nl_nodeid | nl_conncnt | nl_backendpid
-----------+------------+---------------
         1 |          0 |         18083
         2 |         10 |         18089
         2 |         11 |         18090

Only three records. And I notice that the index on this table is:
"sl_nodelock-pkey" PRIMARY KEY, btree (nl_nodeid, nl_conncnt)

This looks like a problem to me but I cannot fathom where to start to
fix it. Any clues? Thanks in advance!

- Don Barthel


On 5/16/07, Jeff Davis <pgsql at j-davis.com> wrote:
> On Wed, 2007-05-16 at 11:58 -0500, Don Barthel wrote:
> > I have one subscriber, one master.
> >
> > Running 'select * from sl_status;' on the master I see:
> >
> > <<
> >  st_origin | st_received | st_last_event |      st_last_event_ts
> > | st_last_received |    st_last_received_ts    |
> > st_last_received_event_ts  | st_lag_num_events |   st_lag_time
> > -----------+-------------+---------------+----------------------------+------------------+---------------------------+-------
> > ---------------------+-------------------+-----------------
> >          1 |           2 |       1713287 | 2007-05-16 11:46:39.741258
> > |          1708951 | 2007-05-16 11:45:30.50751 | 2007-05-16
> > 06:37:55.004498 |              4336 | 05:08:45.916761
> > >>
> >
> > I.e.
> > st_last_received_event_ts == '2007-05-16 06:37:55.004498' and
> > st_lag_num_events == 4336 and
> > st_lag_time == '05:08:45.916761' (5 hours?)
> >
> > Slony is controlled from the subscriber. I have restarted slony from
> > the subscriber (clumsily with 'kill' then 'slon.sh restart' because
> > 'slon.sh stop' didn't seem to stop it).
> >
> > Events are getting processed but very slowly such that st_lag_time is
> > increasing.
> >
> > Every night I update a field in 100,000 records and slony can't seem
> > to quite catch up.
> >
>
> Slony doesn't handle transactions that change a lot of data very well.
> Slony uses the same mechanism to replicate a single-tuple INSERT as an
> unqualified UPDATE on 100M records.
>
> If you can break up transactions into smaller chunks that will improve
> the lag time a lot.
>
> However, 100K tuples isn't that much really, so maybe your problem comes
> from somewhere else? Do your indexes fit in the shared buffers on the
> subscriber?
>
> Regards,
>         Jeff Davis
>
>