On 7/21/2005 9:56 AM, Hannu Krosing wrote:

> Hi
> 
> I have started to have locking issues on pg_listener (i.e listen/notify)
> and so I have again started to think about my old request of running
> slony without listen/notify.

The slon process only updates the local database ever. All remote slon's 
do select only. So whatever a slon wants to communicate to others who 
wait for events, he will insert something into sl_event, then NOTIFY. 
All the remote nodes are listening, see the notification and know that 
they now have to select from sl_event. When a node is done processing a 
remote event, it will record that in sl_confirm and notify on that. Now 
all other nodes that have a connection to here will pick up the new 
confirm status by selecting from sl_confirm.

The idea to reduce the amount of notifications actually done via NOTIFY 
is to keep track when the last ones were created and received. After 
creating a NOTIFY locally, subsequent ones will be suppressed for some 
amount of time (lets say 600 seconds). When a remote slon receives a 
notification, it will enter a polling state for that time plus some more 
  (lets say 660 seconds). During that polling time, it will just look 
every n milliseconds if there are new events - n being a new config 
parameter in sl_listen as well as the time to poll.

On a constantly busy database with only one subscriber and -s1000, we 
currently have one event and one confirm per second. That makes 7200+ 
(the subscriber does a few events as well) NOTIFY calls per hour. With 
the above we'd reduce this to about 12 NOTIFY calls per node.

What I just don't have is the time to actually implement it.


> Could somebody explain me what is the function of LISTEN/NOTIFY in
> slony ?
> 
> I can't imagine them being used for anything really important (as they
> are not all quaranteed to arrive to client at all), and thus I assume
> that they are used just as a pacekeeping device in order to not overload
> the database with slony requests.
> 
> Is my assumption right ?
> 
> In this case, I think I could just remove all "notify XXX" commands from
> slony queries and replace the function waiting for notifies with a dummy
> (or maybe a 50ms wait), as I run a database which is under heavy OLTP
> load and doesalways have something new for slony by the time it finishes
> its processing.
> 
> 
> Otoh, if the events got by LISTENing are used for something essential,
> could I just have a single separate thread that sends all possible
> notifies in a tight loop and keep the listener function intact ?

As a dirty hack you can switch to a constant polling scheme and just 
live with once every n msec SELECT on a completely idle database. Since 
you are using dedicated DB servers, it wouldn't hurt you at all.


Jan


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #