>>> On Mon, Aug 27, 2007 at 11:13 PM, in message
<200708272213.14277.kevin@kevinkempterllc.com>, Kevin Kempter
<kevin@kevinkempterllc.com> wrote:
> Each night during the nightly batch processing several of the servers (2 in
> particular) slow to a crawl - they are dedicated postgres database servers.
> There is a lot of database activity going on sometimes upwards of 200
> concurrent queries
> Any thoughts on where to start?
Is there any way to queue up these queries and limit how many are running at
a time? I don't know what the experience of others is, but I've found that
when I have more than two to four queries running per CPU, throughput starts
to drop, and response time drops even faster.
For purposes of illustration, for a moment let's forget that a query may
block waiting for I/O and another query might be able to use the CPU in the
meantime. Then, think of it this way -- if you have one CPU and 100 queries
to run, each of which will take one second, if you start them all and they
time slice, nobody gets anything for 100 seconds, so that is your average
response time. If you run the one at a time, only one query takes that
long, the rest are faster, and you've cut your average response time in
half. On top of that, there is overhead to switching between processes,
and there can be contention for resources such as locks, which both have a
tendency to further slow things down.
In the real world, there are multiple resources which can hold up a
query, so you get benefit from running more than one query at a time,
because they will often be using different resources.
But unless that machine has 50 CPUs, you will probably get better throughput
and response time by queuing the requests.
-Kevin