From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Kevin Kempter <kevin@kevinkempterllc.com>
To: pgsql-performance@postgresql.org
Subject: Re: server performance issues - suggestions for tuning
Date: Tue, 28 Aug 2007 08:12:06 -0500
Message-ID: <46D3D8D6.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <200708272213.14277.kevin@kevinkempterllc.com>
References: <200708272213.14277.kevin@kevinkempterllc.com>
>>> 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
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: Kevin.Grittner@wicourts.gov, kevin@kevinkempterllc.com
Subject: Re: server performance issues - suggestions for tuning
In-Reply-To: <46D3D8D6.EE98.0025.0@wicourts.gov>
* 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