>>> On Mon, Aug 27, 2007 at 11:13 PM, in message <200708272213.14277.kevin@xxxxxxxxxxxxxxxxxxx>, Kevin Kempter <kevin@xxxxxxxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
