Обсуждение: 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!
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. 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.
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.