Configure JDBC (dbcp/c3p0) Data source for Very Low-Throughput System

A ton of posts discuss the high-throughput and high concurrency system, and perhaps you are building a low-throughput system, and you may think it is simple to configure the datasource. Actually if you use dbcp or c3p0 datasource, and settings of datasource is default value, your application will run into exception occasionally, especially after run idle for hours or days.

Problem

If the connection is inactive, the MySQL and Oracle may close the connection. And then if your application receive request from users, and then pick up connection from connection pool, the connection maybe broken connection, and then your application throws IOException.

You may see the trace listed below:

A.       Oracle

Cause: java.sql.SQLRecoverableException: No more data to read from socket

; SQL []; No more data to read from socket; nested exception is java.sql.SQLRecoverableException: No more data to read from socket

B.       Oracle

Cause: java.sql.SQLRecoverableException: IO Error: Broken pipe

; SQL []; IO Error: Broken pipe; nested exception is java.sql.SQLRecoverableException: IO Error: Broken pipe

C.       MySQL

Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was43203 seconds ago.The last packet sent successfully to the server was 43203 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

; SQL []; The last packet successfully received from the server was43203 seconds ago.The last packet sent successfully to the server was 43203 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was43203 seconds ago.The last packet sent successfully to the server was 43203 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

                at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)

…………

Caused by: java.net.SocketException: Broken pipe

                at java.net.SocketOutputStream.socketWrite0(Native Method)

Solution

A:

I suggest you to use c3p0 datasource.  And set maxIdleTime=300.

                <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"

                                destroy-method="close">

                                <property name="driverClass">

                                                <value>${prop.jdbc.driverClassName}</value>

                                </property>

                                <property name="jdbcUrl">

                                                <value>${prop.jdbc.url}</value>

                                </property>

                                 <property name="user"> 

                                                <value>${prop.jdbc.username}</value>

                                 </property>

                                 <property name="password"> 

                                                <value>${prop.jdbc.password}</value>

                                 </property>

                                 <property name="maxIdleTime"> 

                                                <value>300</value>

                                 </property>

                </bean>

B:

If you are using Apache DBCP, I suggest you to upgrade to latest version, and the recommended setting similar to

                <bean id="dataSource_x" class="org.apache.commons.dbcp.BasicDataSource"

                                destroy-method="close">

                                <property name="driverClassName">

                                                <value>${prop.jdbc.driverClassName}</value>

                                </property>

                                <property name="url">

                                                <value>${prop.jdbc.url}</value>

                                </property>

                                 <property name="username"> 

                                                <value>${prop.jdbc.username}</value>

                                 </property>

                                 <property name="password"> 

                                                <value>${prop.jdbc.password}</value>

                                 </property>

                                 <property name="testWhileIdle" value="true"/>

                                 <property name="validationQuery" value="select 1 from dual"/>

                                 <property name="removeAbandoned" value="true"/>

                                 <property name="removeAbandonedTimeout" value="600"/>

                                 <property name="timeBetweenEvictionRunsMillis" value="600000"/>

                </bean>

About Author

Williams Voon, experienced programmer, devote to building distributed system monitoring tool EasyHA.