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