Wed May 16 13:10:28 PDT 2007
- Previous message: [Slony1-general] Log warnings of incorrect escape characters?
- Next message: [Slony1-general] Slony Replication Slow
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
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 > >
- Previous message: [Slony1-general] Log warnings of incorrect escape characters?
- Next message: [Slony1-general] Slony Replication Slow
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
More information about the Slony1-general mailing list