Skip site navigation (1) Skip section navigation (2)

Re: LISTEN / NOTIFY performance in 8.3

From: Joel Stevenson <joelstevenson(at)mac(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: LISTEN / NOTIFY performance in 8.3
Date: 2008-02-26 16:26:30
Message-ID: p06240812c3e9ea5ea7f8@[10.0.1.253] (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-performance
At 11:58 PM -0500 2/23/08, Tom Lane wrote:
>  > Attached is a perl script that sort of simulates what's going on.
>
>Thanks for the script.  It's not showing any particular problems here,
>though.  With log_min_duration_statement = 10, the only statements that
>(slightly) exceed 10ms are the select count(*) from generate_series(1,
>15000) ones.

I tried the test script on another machine (similar but not identical 
to the original machine) running 8.3 and although the notify 
performance was *much* better than the original I still see 
notifications taking longer than the select count(*) from 
generate_series(1, 15000) queries, and also longer than some simple 
updates to other tables that are also happening on the server.

duration: 10.030 ms  statement: select count(*) from generate_series(1, 15000)
duration: 224.833 ms  statement: NOTIFY to_producer

Perhaps this shouldn't be made much of as I'm sure there are many way 
that this could quite naturally happen.

I've been thinking of LISTEN / NOTIFY as one of the least expensive 
and therefore speedy ways to get the word out to participating 
processes that something has changed (versus using a manually setup 
signals table that interested parties updated and selected from).

Now that I see a little bit more of what goes on under the hood of 
this function I see that it's still basically table-driven and I'll 
adjust my expectations accordingly, but I'm still puzzled by the 
hugely slow notifications showing up on the original server running 
the producer / consumer setup.

With ps I can see some postgres backends with a 'notify interrupt 
waiting' command line during the tests - could it be an issue with 
signal handling on the original machine - something entirely outside 
of PG's control?

Thx,
-Joel

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-02-26 16:31:42
Subject: Re: PG planning randomly ?
Previous:From: Laurent RaufasteDate: 2008-02-26 15:35:03
Subject: Re: PG planning randomly ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group