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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org