commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Shawn Heisey <apa...@elyograg.org>
Subject Re: [DBCP] Connection just obtained from datasource is invalid
Date Tue, 21 Nov 2017 17:58:22 GMT
On 11/17/2017 3:27 PM, Phil Steitz wrote:
> OK, sorry I misread your initial post.  That means either the pool
> agrees that the connection is closed (which would point to a pool or
> DBCP bug) or the driver's isValid() on the underlying physical
> connection is returning true (without throwing).  If it's not too
> hard to do, it would be good to also log what comes back from
> isClosed() on the DelegatingConnection (what dsMaster returns).  If
> that returns true, then either this is a DBCP or pool bug or you
> have somehow closed the DelegatingConnection via another reference
> or something.  If it returns false, that means the pool thinks the
> connection is open so it is unlikely that the pool closed it.

I'm pretty sure (will have to check) that when my code uses the
connections it gets from the pool, it does do a close on it when
complete, but it's my understanding that this is how to return
connections to the pool, that it doesn't *truly* close the connection as
it would if I were using JDBC directly instead of via DBCP.

I have changed my program to check isClosed instead of making the
"SELECT 1" query on the invalid connection, and to log something if that
returns true.  So far the problem hasn't happened again, even though I
did set the eviction interval back to exactly one minute.

> What you have below is fine.  I just wanted to see the pool
> configuration settings.  Looks OK, but might end up creating a lot
> of connection churn having maxIdle set to 6 with maxTotal at 30.  I
> guess the idea is that when it runs, you want to have an idle
> connection available for each shard with the ability to add more up
> to a total of 30, but you don't want those idle connections to stay
> in the pool when the work isn't being done.  Do you know how many
> connections actually get opened during the work period?  If that is
> more than a few more than 6, you are kind of defeating the purpose
> of the pool by closing them all when they get returned (as the
> maxIdle setting will force).

Here's a screenshot showing the open connections on the DB server at a
particular moment.  The connections in this case are coming from the
server named "idxa1".  There are two copies of the program running --
one where numShards is 2, and one where numShards is 6.  As you can see
in the screenshot, there are twelve connections from idxa1.

https://www.dropbox.com/s/g9swe4hrq19jbcc/mysql-dbcp-connections-workbench.png?dl=0

As the program does its work, it uses one connection to the main server
to gather some information and then it can open up numShards+1
connections to the main server to actually handle updating the Solr
index from the database.  (numShards refers to the number of cold
shards, but there is one more shard in each index -- the hot shard).  On
each cycle, position information is written to the master server.

Because we had to do some work on a DB server a while back, the main
pool and the master pool are both set to the same host -- the master
server.  This is slightly unusual, but that configuration pre-dates the
recent problems with closed connections by quite a bit of time. 
Eventually I will be switching it back so the main server is one of the
slaves, but I would like to figure this problem out first.

> The fact that changing the eviction interval to not exactly coincide
> with when the work happens makes the problem go away is troubling. 
> I can see how that might improve performance, but it does make the
> pool bug scenario more likely.

Do you think I should be doing things differently than I currently do? 
If so, please feel free to make recommendations.  I'm not really sure
what your statement about performance is saying.  I'm not doing eviction
for performance reasons -- in fact, it might actually *reduce*
performance, because it's more likely that "getConnection" is going to
have to actually spin up a new TCP connection to the database.  The time
required for that doesn't worry me.

> One more question on the config.  Why are you running the evictor
> every minute and using eviction to close idle connections?  The
> maxIdle setting will keep the connection count down.  If what you
> are worried about is server-side idle timeouts, you could use set
> testWhileIdle to true and that would use the connections rather than
> closing them.

Because we keep having problems where we have too many connections open
on the database server, and when that happens, everything breaks.  I
want to be absolutely sure that the software I am responsible for is not
contributing to the problem.  At the moment, the massive numbers of
connections are being kept open by our webapps, code that I am not
responsible for.  The idle connection eviction in my programs ensures
that if my software has a connection open, that it's because it has
actually *needed* that connection in the last five minutes.  Our
application developers need to implement something similar in the code
running under Tomcat, so that there are not dozens of connections open
from each webserver with hours and hours of idle time.  I went with one
minute for the eviction interval because if I were to configure it with
a shorter interval, the program would be spending a lot of resources to
keep the connection count down.  Once a minute is (for a program)
relatively infrequent.  I can increase the interval.

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org


Mime
View raw message