From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Jean-Philippe Côté <jean-philippe.cote@crt.umontreal.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Extremely irregular query performance
Date: Wed, 11 Jan 2006 17:29:14 -0600
Message-ID: <1137022154.3959.1.camel@state.g2switchworks.com> (raw)
In-Reply-To: <20060111222900.EA357384020@crt0.crt.umontreal.ca>
References: <20060111222900.EA357384020@crt0.crt.umontreal.ca>
On Wed, 2006-01-11 at 16:37, Jean-Philippe Côté wrote:
> Hi,
>
> 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
> 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):
>
> 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
>
> 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?
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: smarlowe@g2switchworks.com, jean-philippe.cote@crt.umontreal.ca
Subject: Re: Extremely irregular query performance
In-Reply-To: <1137022154.3959.1.camel@state.g2switchworks.com>
* 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