Using NOTIFY... Slow Client Querys

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!



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

re: Using NOTIFY... Slow Client Querys

Joe Lester <joe_lester@sweetwater.com> writes:[color=blue]
> 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.[/color]
[color=blue]
> After receiving a notification from libpq (PQnotifies), each client
> proceeds to execute a query for the last five records in the timeclock
> table.[/color]
[color=blue]
> SELECT * FROM timeclock ORDER BY touched DESC LIMIT 5;[/color]
[color=blue]
> 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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

re: Using NOTIFY... Slow Client Querys

Joe Lester wrote:
[color=blue]
> 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![/color]

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

re: Using NOTIFY... Slow Client Querys

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

On Feb 15, 2004, at 12:07 PM, Tom Lane wrote:[color=blue]
> 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.[/color]



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

re: Using NOTIFY... Slow Client Querys

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.

On Feb 16, 2004, at 10:27 AM, Mikhail Terekhov wrote:[color=blue]
> 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.[/color]



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster