At 09:40 PM 8/17/2005, Alan Stange wrote:
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.
I'm assuming there is some significant write activity going on at
some point as a result of the query, since MVCC should not care about
concurrent read activity?
Is that "a few seconds each query" or "a few seconds total if we run
50 queries sequentially but 70+ seconds per query if we try to run 50
queries concurrently"?
A) If the former, "a few seconds" * 50 can easily be 70+ seconds, and
things are what you should expect. Getting higher performance in
that situation means reducing per query times, which may or may not
be easy. Looking at the stored procedure code with an eye towards
optimization would be a good place to start.
B) If the later, then table access contention is driving performance
into the ground, and there are a few things you can try:
1= lock the table(s) under these circumstances so only one query of
the 50 can be acting on it at a time. If the table(s) is/are small
enough to be made RAM resident, this may be a particularly low-cost,
low-effort, reasonable solution.
2= put a queue into place and only let some small number n of queries
run against the table(s) concurrently. Adjust n until you get best
performance. There are a few ways this could be done.
3= Buy a SSD and put the table(s) in question on it. IIRC, 3.5"
format SSDs that can "drop in" replace HDs are available in up to
147GB capacities.
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.
This will only help in case "B" above. If you go the "hard" route of
using systems programming, you will have a lot of details that must
be paid attention to correctly or Bad Things (tm) will
happen. Putting the semaphore in place is the tip of the iceberg.
Hope this helps,
Ron Peacetree
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
