Using NOTIFY... Slow Client Querys

[Joe Lester]

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

[Tom Lane]

Joe Lester <jo********@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

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

http://archives.postgresql.org

[Mikhail Terekhov]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Joe Lester]

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:

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.

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

[Joe Lester]

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:

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.

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