Subject: Re: Extremely irregular query performance
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Jean-Philippe =?ISO-8859-1?Q?C=F4t=E9?=
	<jean-philippe.cote@crt.umontreal.ca>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20060111222900.EA357384020@crt0.crt.umontreal.ca>
References: <20060111222900.EA357384020@crt0.crt.umontreal.ca>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Message-Id: <1137022154.3959.1.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 11 Jan 2006 17:29:14 -0600

On Wed, 2006-01-11 at 16:37, Jean-Philippe C=C3=B4t=C3=A9 wrote:
> Hi,
>=20
> I'm running version 8.1 on a dedicated Sun v20 server (2 AMD x64's)
> with 4Gb of RAM. I have recently noticed that the performance of
> some more complex queries is extremely variable and irregular.
> For example, I currently have a query that returns a small number=20
> of rows (5) by joining a dozen of tables. Below are the running times
> obtained by repeatedly lauching this query in psql (nothing else
> was running on the server at that time):
>=20
> Time: 424.848 ms
> Time: 1615.143 ms
> Time: 15036.475 ms
> Time: 83471.683 ms
> Time: 163.224 ms
> Time: 2454.939 ms
> Time: 188.093 ms
> Time: 158.071 ms
> Time: 192.431 ms
> Time: 195.076 ms
> Time: 635.739 ms
> Time: 164549.902 ms
>=20
> As you can see, the performance is most of the time pretty good (less
> than 1 second), but every fourth of fifth time I launch the query
> the server seems to go into orbit. For the longer running times,
> I can see from 'top' that the server process uses almost 100% of
> a CPU.

As mentioned earlier, it could be you're exceeding the GEQO threshold.

It could also be that you are doing just enough else at the time, and
have your shared buffers or sort mem high enough that you're initiating
a swap storm.

Mind posting all the parts of your postgresql.conf file you've changed
from the default?