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?
>
>
>
