Re: Strange performance degradation

You may use connection pooling for "idle connections" like pgbouncer or pgpool. Following link will give you details about pgbouncer & pgpool.

https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer
http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html

Hope it may help you!!!

--
Thanks
Sam Jas

--- On Mon, 23/11/09, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: [GENERAL] Strange performance degradation
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
Date: Monday, 23 November, 2009, 9:26 PM

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

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/