commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Phil Steitz <phil.ste...@gmail.com>
Subject Re: [DBCP] Connection just obtained from datasource is invalid
Date Tue, 21 Nov 2017 20:12:39 GMT
On 11/21/17 10:58 AM, Shawn Heisey wrote:
> 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.

That is correct.
>
> 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.

That will tell if something funny is going on.  Having isClosed
return true on a connection that you have just gotten from the pool
(and no other thread has messed with) would be DBCP/Pool badness.
>
>> 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.

The performance comment was just to point out that if you run the
evictor exactly at the time when the work happens, there is some
contention between the evictor and the borrowing threads.  But this
has really been optimized in pool2, so you probably can't measure
it.  So basically forget that comment.  The fact that the problem
goes away when you eliminate the contention remains troubling from
DBCP/Pool standpoint.
>
>> 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.

I get what you are trying to do now.  Should not be a problem to run
with this config if connections are dear.  It just means you end up
closing and reopening connections a lot in exchange for having fewer
of them idle at any moment in time.

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


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


Mime
View raw message