Arrgh. Pooled MySQL connections

[kjkoster]

Dear All,

I must not have done my homework. I keep getting the problem that my MySQL connections die after a period of inactivity. I have appended a typical stack trace for reference.

I have 2 questions:

Where can I find a *complete* reference of Tomcat's Resource element in the context xml files? I know about this page on apache.org (http://tomcat.apache.org/tomcat-6.0-doc/config/context.html#Resource%20Definitions), but it lists only a few of the tunables on the resources.
How do I tune the MySQL resource to solve this problem?


What I have done so far is set maxidle to -1. This solves the issue, but effectively disables connection pooling. I am not happy with the resulting performance.

I have also set the pool's maxidle to 1, but that suffers from roughly the same problem, though traffic is so low in my test environment that I don't actually measure a difference in performance. :-)

Kees Jan


<Resource name="jdbc/ds" auth="Container"
type="javax.sql.DataSource" maxActive="100" maxIdle="30"
maxWait="10000" username="user" password="*****"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://example.com/database?autoReconnect=true" />


java.lang.RuntimeException: unable to check login status: Communications link failure

Last packet sent to the server was 0 ms ago.
at com.javamonitor.vbulletin.VBulletinAccountDao.find BySessionhash(VBulletinAccountDao.java:72)
at com.javamonitor.services.UiServiceImpl.findAccount (UiServiceImpl.java:63)
at com.javamonitor.services.UiServiceImpl.findAccount (UiServiceImpl.java:51)
at sun.reflect.GeneratedMethodAccessor341.invoke(Unkn own Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.javamonitor.services.ServiceProxy.invoke(Servi ceProxy.java:68)
at $Proxy49.findAccount(Unknown Source)
at com.javamonitor.filters.LoginFilter.doFilter(Login Filter.java:55)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:102)
at org.apache.catalina.valves.AccessLogValve.invoke(A ccessLogValve.java:568)
at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run( JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsExce ption: Communications link failure

Last packet sent to the server was 0 ms ago.
at sun.reflect.GeneratedConstructorAccessor55.newInst ance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Construc tor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:40 6)
at com.mysql.jdbc.SQLError.createCommunicationsExcept ion(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3134)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:18 18)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(P reparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.executeQuery(Prep aredStatement.java:1888)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStat ement.executeQuery(DelegatingPreparedStatement.jav a:93)
at com.javamonitor.vbulletin.VBulletinAccountDao.find BySessionhash(VBulletinAccountDao.java:56)
... 21 more
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutp utStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStre am.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedO utputStream.java:65)
at java.io.BufferedOutputStream.flush(BufferedOutputS tream.java:123)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3119)
... 28 more

[Kees de Kooter]

The parameters of the resource element depend on the type of resource you are configuring. As you undoubtedly know Tomcat uses the commons-dbcp package to implement database connection pools. So a good starting point would be the docs for that library: http://commons.apache.org/dbcp/.

I went through the motions a couple of years ago and this is what I ended up with (in this case I used a postgres database, but the pool params are the same):


<Resource
name="jdbc/aeos"
auth="Container"
type="javax.sql.DataSource"
username="xxxxx"
password=""
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://xxxxxx/xxxxxx"
maxActive="20"
maxIdle="1"
minIdle="0"
maxWait="1000"
validationQuery="select * from Company where companyId = 1"
testOnBorrow="true"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="1000"
poolPreparedStatements="true"
maxOpenPreparedStatements="1000"
removeAbandoned="true"
removeAbandonedTimeout="300"
logAbandoned="false"
/>


If I recall correctly the thing that solved my problem back then was the validationQuery parameter, which provides the pool with a method of verifying the connection (I usually create a dedicated 1 column - 1 row table for that).

Hope this works for you.

[kjkoster]

Dear Kees,

Thank you for the information. This also explains why Tomcat does not document its database connection pool.

For future reference, here is the Commons-DBCP documentation (http://commons.apache.org/dbcp/configuration.html).

I decided to try set "testWhileIdle" and use the validation query "SELECT 1=1". Nice and portable between databases. :-) Oh, and I added "logAbandoned" to the configuration of the test system for good measure. I'll do production without.


validationQuery="SELECT 1 = 1" testOnBorrow="false" testWhileIdle="true"
timeBetweenEvictionRunsMillis="60000"
logAbandoned="true"


Kees Jan

[Kees de Kooter]

Yes, much better validationQuery!!

[kjkoster]

Dear Kees,

Well, I have to admit I took the idea from another application.

You use quite small values for your 'timeBetweenEvictionRunsMillis'. Could you explain the rationale behind that decision? I chose to use one minute because the problem only happens after a few hours.

Oh, and I can confirm that this resolves the issue for me. W00+

Kees Jan

[Kees de Kooter]

No rationale. These settings solved my problems back then. The key to the solution seemed to be the validation query.