From: Gavin Sherry <swm@alcove.com.au>
To: Alan Stange <stange@rentec.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: limit number of concurrent callers to a stored proc?
Date: Thu, 18 Aug 2005 12:30:24 +1000 (EST)
Message-ID: <Pine.LNX.4.58.0508181217440.22179@linuxworld.com.au> (raw)
In-Reply-To: <4303E704.4030200@rentec.com>
References: <4303E704.4030200@rentec.com>
Hi Alan,
On Wed, 17 Aug 2005, Alan Stange wrote:
> Hello all,
>
> is there a simple way to limit the number of concurrent callers to a
> stored proc?
>
> The problem we have is about 50 clients come and perform the same
> operation at nearly the same time. Typically, this query takes a few
> seconds to run, but in the case of this thundering herd the query time
> drops to 70 seconds or much more. The query can return up to 15MB of data.
>
> The machine is a dual opteron, 8 GB memory, lots of fiber channel disk,
> Linux 2.6, etc.
>
> So, I'm thinking that a semaphore than will block more than N clients
> from being in the core of the function at one time would be a good thing.
There is no PostgreSQL feature which will do this for you. It should be
possible to implement this yourself, without too much pain. If you're
using PL/PgSQL, write another function in C or one of the other more
sophisticated PLs to implement the logic for you. At the beginning of the
function, execute the function to increment the count; at the end, execute
a function to decrement it.
If you're writing the function in C or one of those more sophisticated
PLs, it's even easier.
As an aside, using semaphores might be a little painful. I'd just grab
some shared memory and keep a counter in it. If the counter is greater
than your desired number of concurrent executions, you sleep and try again
soon.
That being said, did you want to give us a look at your function and data
and see if we can improve the performance at all?
Thanks,
Gavin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: swm@alcove.com.au, stange@rentec.com
Subject: Re: limit number of concurrent callers to a stored proc?
In-Reply-To: <Pine.LNX.4.58.0508181217440.22179@linuxworld.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox