Monday, August 15, 2011

Performance books I have or I wish to have

Overall Performance Engineering Methodology:
* Performance Analysis for Java Web Sites By Stacy Joines
* Software Performance and Scalability: A Quantitative Approach By Henry Liu
* Improve .Net Application Performance and Scalability By Microsoft
* Building Scalable Web Sites By Cal Henderson

Performance Testing Process & Practice:
* Performance Testing Guidance for Web Application By Microsoft
* The Art of Application Performance Testing By Ian Molyneaux

DB Performance tuning:
* Inside SQL Server 2005 Query Tuning and Optimization By Kalen Delaney
* High Performance MySQL By Baron Shwartz

Programming Language Performance Tuning:
* Effective Java By Joshua Bloch
* Java Concurrency in Practice By Brian Goetz
* The Art of Concurrency by Clay Breshears
* Java Performance Tuning by Jack Shirazi

Front-End Performance Optimization Practice:
* High Perforamnce WebSites By Steve Souders
* Even Faster WebSites By Steve Souders
* High Performance Javascript By Zakas

Web Operations and Capacity Planning:
* Web Operations By John Allspaw
* The Art of Capacity Planning By John Allspaw

Tuesday, August 09, 2011

C3p0 infinity wait for "availble" connections by default

If you have connection leaks on C3p0 which means the Connections are being checked-out that don't make it back into the pool. You will be in trouble...

By default, C3p0 will wait for the connection forever if there is no one available in the pool currently.

When testing against the application, i found some threads are wait for the connections for a long time, and after monitoring the JMX console in VisualVM, all the connections are taken, here is the thread dump i took:
 "1658227@qtp-25018827-56" - Thread t@321  
   java.lang.Thread.State: WAITING  
      at java.lang.Object.wait(Native Method)  
      - waiting on <103344b> (a com.mchange.v2.resourcepool.BasicResourcePool)  
      at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1315)  
      at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)  
      at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)  
      at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)  
      at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)  
      at org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSessionFromDataSource(DefaultSqlSessionFactory.java:72)  
      at org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSession(DefaultSqlSessionFactory.java:32)  

I am not discussing why the code makes connection leaks here in this post.(Actually, we did not invoke session.close from our code finally ), i just want to make sure if you meet this kind of similar pattern of problem, then you can temperately change some C3p0 configuration before you fix your bad code...

we are using C3p0 0.9.1.2 version now, here is the source code which have the infinity loop to wait for the connection, FYI
 1297        while ((avail = unused.size()) == 0)   
  1298        {  
  1299          // the if case below can only occur when 1) a user attempts a  
  1300          // checkout which would provoke an acquire; 2) this  
  1301          // increments the pending acquires, so we go to the  
  1302          // wait below without provoking postAcquireMore(); 3)  
  1303          // the resources are acquired; 4) external management  
  1304          // of the pool (via for instance unpoolResource()   
  1305          // depletes the newly acquired resources before we  
  1306          // regain this' monitor; 5) we fall into wait() with  
  1307          // no acquires being scheduled, and perhaps a managed.size()  
  1308          // of zero, leading to deadlock. This could only occur in  
  1309          // fairly pathological situations where the pool is being  
  1310          // externally forced to a very low (even zero) size, but   
  1311          // since I've seen it, I've fixed it.  
  1312          if (pending_acquires == 0 && managed.size() < max)  
  1313            _recheckResizePool();  
  1314    
  1315          this.wait(timeout);  
  1316          if (timeout > 0 && System.currentTimeMillis() - start > timeout)  
  1317            throw new TimeoutException("A client timed out while waiting to acquire a resource from " + this + " -- timeout at awaitAvailable()");  
  1318          if (force_kill_acquires)  
  1319            throw new CannotAcquireResourceException("A ResourcePool could not acquire a resource from its primary factory or source.");  
  1320          ensureNotBroken();  
  1321        }  

timeoutvalue is set to 0 by default in C3p0, which means wait indefinitely
checkoutTimeout
Default: 0
The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted. Zero means wait indefinitely. Setting any positive value will cause the getConnection() call to time-out and break with an SQLException after the specified number of milliseconds.

And also if you have connection leaks with your code, and you hardly find it in short time, then you should pay attention to this parameter:
unreturnedConnectionTimeout
Default: 0
Seconds. If set, if an application checks out but then fails to check-in [i.e. close()] a Connection within the specified period of time, the pool will unceremoniously destroy() the Connection. This permits applications with occasional Connection leaks to survive, rather than eventually exhausting the Connection pool. And that's a shame. Zero means no timeout, applications are expected to close() their own Connections. Obviously, if a non-zero value is set, it should be to a value longer than any Connection should reasonably be checked-out. Otherwise, the pool will occasionally kill Connections in active use, which is bad. This is basically a bad idea, but it's a commonly requested feature. Fix your $%!@% applications so they don't leak Connections! Use this temporarily in combination with debugUnreturnedConnectionStackTraces to figure out where Connections are being checked-out that don't make it back into the pool!

So you can try to set these two parameters to the same time as your goal keeper, say 30 seconds. So that you can prevent some extremely leak cases, but dev need to find the root cause for sure :)
checkoutTimeout= 30000
unreturnedConnectionTimeout=30

also adjust your maxPoolSize to 30 or more instead of 15 by default under a heavy load situation.