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