Re: [HACKERS] Load spikes on 8.1.11

On Fri, Jul 18, 2008 at 7:15 PM, David Fetter <david@fetter.org> wrote:

On Fri, Jul 18, 2008 at 10:15:42AM +0530, Gurjeet Singh wrote:
> On Fri, Jul 18, 2008 at 10:05 AM, Gurjeet Singh <singh.gurjeet@gmail.com>
> wrote:
>
> > On Fri, Jul 18, 2008 at 9:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> >> "Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
> >> >     During these spikes, in the 'top' sessions we see the 'idle' PG
> >> > processes consuming between 2 and 5 % CPU, and since the box has 8 CPUS
> >> (2
> >> > sockets and each CPU is a quad core Intel Xeon processors) and somewhere
> >> > around 200 Postgres processes, the load spikes to above 200; and it does
> >> > this very sharply.
> >>
> >> This looks like heavy contention for a spinlock.  You need to get a
> >> higher-level analysis of what's happening before anyone can say much
> >> more than that.
> >>
> >> Note that 8.1 is pretty much ancient history as far as scalability to
> >> 8-core hardware goes.  You should probably consider updating to 8.3
> >> before investing too much time in tracking down what's happening.
> >> If you can still show the problem on 8.3 then there would be some
> >> interest in fixing it ...
> >
> >
> > Upgrading is on the cards, but not as high priority as I would like it to
> > be! This is a production box, and we desperatly need some respite from these
> > spikes.
> >
> > Can you please elaborate on what high level diagnosis would you need?
> >
> > I just ran DROP SCHEMA _<slony schema> CASCADE; and it spiked again, on a
> > very low loaded box!!
> >
> > Thanks for all you help.
> >
>
> Would reducing the number of connections on the DB help in reducing the
> spike?

Just generally, reducing the number of connections to the DB will help
in reducing resource consumption.

Will try this option, at least in the next schema upgrade or when setting up Slony.

When you first get a chance, use or set up a test environment where
you can test the upgrade to 8.3.latest.

Based on the thread above, we seem to be moving towards greater consensus on upgrade. One of the major hurdles in our environment's upgrade is the loss of implicit casts in 8.3.

Following is the environment we have:

select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.11 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9)
(1 row)

I cannot see oprofile installed on this box, so will try to get that installed and get you guys some more details when this happens next.

Thanks,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device