Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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

pgsql-performance by date

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

pgsql-general by date

Next:From: John R PierceDate: 2009-11-23 21:51:16
Subject: Re: C++ and Postgis
Previous:From: Pedro Doria MeunierDate: 2009-11-23 21:19:44
Subject: C++ and Postgis

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group