Обсуждение: Connections performance is reduced

Hi all,

I'm having some troubles with the performance of the JDBC driver (or
maybe is a problem of postgres).

The scenario is as follows:

- I have two connections opened.
- One of them performs a query and it is suspended for a long long long
time (it remains opened). This connection is set to READ_COMMITED
transaction isolation level, and also autocommit is set to true (but the
problem appears too if autocommit is false).
- The other connection performs queries, updates, and a lot of work, it
is closed and renewed with a new connection, etc. The transaction
isolation level is sometimes READ_COMMITED and sometimes SERIALIZABLE.
And autocommit is set always to false.

The problem is that when I have performed a large number of "commits"
with the second connection, performance is reduced a lot. This
connection goes slower and slower... I thought the problem was the
"VACUUM", but it is not, because I call "VACUUM" very often. So I tried
to run the same tests but in this case closing the first connection that
is doing nothing. The fact is that in this case performance is always
the same, there isn't a performance degradation...

How is it possible? Why if I have a connection opened that does nothing
the performance is worse?

I need to have this connection opened because there are cases in which
it is better to have it opened than open a new one.

Any idea of what is happening?



--
----------------------------------------------
Antonio Calero Monteagudo
Instituto Tecnológico de Informática

acalero@iti.upv.es
http://www.iti.upv.es/~acalero
----------------------------------------------

Antonio,

This problem isn't jdbc related, and the general or performance lists
would probably be a better place to post this.  But I will try to give
an answer.

Vacuum will have a significant impact on performance.  I suspect what is
happening is that vacuum can only remove records that it knows can't be
used again.  I suspect it does this by only cleaning up records that are
  considered dead and are older than the oldest open connection.  Thus
your vacuums really aren't doing much until after your long running
connection is closed.  One way to verify this theory of mine, would be
to run 'vacuum verbose' and look at how many records are actually
vacuumed relative to when other connections are opened/closed.  I don't
know the internals of the vacuum process very well, so I could be
completely wrong here as well.

thanks,
--Barry

Antonio Calero wrote:
> Hi all,
>
> I'm having some troubles with the performance of the JDBC driver (or
> maybe is a problem of postgres).
>
> The scenario is as follows:
>
> - I have two connections opened.
> - One of them performs a query and it is suspended for a long long long
> time (it remains opened). This connection is set to READ_COMMITED
> transaction isolation level, and also autocommit is set to true (but the
> problem appears too if autocommit is false).
> - The other connection performs queries, updates, and a lot of work, it
> is closed and renewed with a new connection, etc. The transaction
> isolation level is sometimes READ_COMMITED and sometimes SERIALIZABLE.
> And autocommit is set always to false.
>
> The problem is that when I have performed a large number of "commits"
> with the second connection, performance is reduced a lot. This
> connection goes slower and slower... I thought the problem was the
> "VACUUM", but it is not, because I call "VACUUM" very often. So I tried
> to run the same tests but in this case closing the first connection that
> is doing nothing. The fact is that in this case performance is always
> the same, there isn't a performance degradation...
>
> How is it possible? Why if I have a connection opened that does nothing
> the performance is worse?
>
> I need to have this connection opened because there are cases in which
> it is better to have it opened than open a new one.
>
> Any idea of what is happening?
>
>
>

On Thu, May 15, 2003 at 08:56:03AM -0700, Barry Lind wrote:
> to run 'vacuum verbose' and look at how many records are actually
> vacuumed relative to when other connections are opened/closed.  I don't
> know the internals of the vacuum process very well, so I could be
> completely wrong here as well.

You're not wrong; you're right on.

This is a very common problem with long-running transactions.  A
search of the archives on something like "long running transaction"
will turn up a bunch of info about this.

The short answer to Antonio's question, though, is that long-running
transactions in Postgres are a real "gotcha".  You need to design to
avoid them.  This is especially true with JDBC, ODBC, and the rest
who use the same strategy for auto commit.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110