Re: Strange performance degradation

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bill Moran <wmoran(at)potentialtech(dot)com>
Cc: Lorenzo Allegrucci <lorenzo(dot)allegrucci(at)forinicom(dot)it>, pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: Strange performance degradation
Date: 2009-11-23 21:26:48
Message-ID: 24833.1259011608@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

Bill Moran <wmoran(at)potentialtech(dot)com> writes:
> In response to Lorenzo Allegrucci <lorenzo(dot)allegrucci(at)forinicom(dot)it>:
>> Tom Lane wrote:
>>> Are you killing off any long-running transactions when you restart?

>> Anyway, how can I get rid those "idle in transaction" processes?
>> Can I just kill -15 them or is there a less drastic way to do it?

> Connections idle in transaction do not cause performance problems simply
> by being there, at least not when there are so few.

The idle transaction doesn't eat resources in itself. What it does do
is prevent VACUUM from reclaiming dead rows that are recent enough that
they could still be seen by the idle transaction. The described
behavior sounds to me like other transactions are wasting lots of cycles
scanning through dead-but-not-yet-reclaimed rows. There are some other
things that also get slower as the window between oldest and newest
active XID gets wider.

(8.4 alleviates this problem in many cases, but the OP said he was
running 8.3.)

> If you -TERM them, any uncommitted data will be rolled back, which may
> not be what you want. Don't -KILL them, that will upset the postmaster.

-TERM isn't an amazingly safe thing either in 8.3. Don't you have a way
to kill the client-side sessions?

> My answer to your overarching question is that you need to dig deeper to
> find the real cause of your problem, you're just starting to isolate it.

Agreed, what you really want to do is find and fix the transaction leak
on the client side.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2009-11-23 21:51:16 Re: C++ and Postgis
Previous Message Pedro Doria Meunier 2009-11-23 21:19:44 C++ and Postgis

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2009-11-23 21:55:07 Re: Best possible way to insert and get returned ids
Previous Message Bill Moran 2009-11-23 21:05:17 Re: Strange performance degradation