Using NOTIFY... Slow Client Querys

Joe Lester <joe_lester@sweetwater.com> writes:

I'm using PostgreSQL 7.4.1. I have 140 clients connected on average
using libpq. When one client sends "NOTIFY timeclock;" to the server
all 140 clients are listening for it.

After receiving a notification from libpq (PQnotifies), each client
proceeds to execute a query for the last five records in the timeclock
table.

SELECT * FROM timeclock ORDER BY touched DESC LIMIT 5;

It varies, but it's often the case that clients wait up to 3 minutes
before the results come back. This seems like a really long time for a
query that I would think would go quickly. In fact, I can execute the
same query from a third party client and it runs fine, even while my
own client is still waiting for results.

Hmm. Are you certain that the clients have received the NOTIFY?
Perhaps the bottleneck is in delivering the NOTIFY messages, not in
executing the subsequent query.

regards, tom lane

Joe Lester wrote:

I'm using PostgreSQL 7.4.1. I have 140 clients connected on average
using libpq. When one client sends "NOTIFY timeclock;" to the server
all 140 clients are listening for it.

After receiving a notification from libpq (PQnotifies), each client
proceeds to execute a query for the last five records in the timeclock
table.

SELECT * FROM timeclock ORDER BY touched DESC LIMIT 5;

It varies, but it's often the case that clients wait up to 3 minutes
before the results come back. This seems like a really long time for a
query that I would think would go quickly. In fact, I can execute the
same query from a third party client and it runs fine, even while my
own client is still waiting for results.

Any ideas? It seems to be related to NOTIFY/LISTEN. Thanks!

I'd say it is related to the design of the application. Imagine what
happens:

1. You have 140 backends, most/all of them are sleeping.
2. One client sends a NOTIFY.
3. All 140 backends get awake all together and send a notify message to
their clients.
4. All 140 clients almost at the same time send a query to the same table.
5. Unless you have a _very_ powerful server it will be _very_ slow.

It is a classical multitask bottleneck problem.

Regards,
Mikhail Terekhov

Yes, my client receives the notification and then it immediately
executes a query that hangs for a while.

Thanks. I was kind of suspecting that. But it's nice to have it
confirmed.

I might try a random delay on the client side after receiving the
notification, before I query. That may help to break up the load on the
server.