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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
