Re: Using NOTIFY... Slow Client Querys

From: Mikhail Terekhov <terekhov(at)emc(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Using NOTIFY... Slow Client Querys
Date: 2004-02-16 15:27:32
Message-ID: 4030E164.3020002@emc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-02-16 15:41:42 Re: tsearch2: restoring problem
Previous Message Tom Lane 2004-02-16 15:26:12 Re: making tsearch2 dictionaries