mysql+c3p0+慢,C3P0连接池+MySQL的配置及wait_timeout问题的解决方法_MySQL

最新推荐文章于 2021-09-10 14:39:18 发布

Mather King

最新推荐文章于 2021-09-10 14:39:18 发布

阅读量351

点赞数

文章标签： mysql+c3p0+慢

一、配置环境

spring4.2.4+mybatis3.2.8+c3p0-0.9.1.2+Mysql5.6.24

二、c3p0的配置详解及spring+c3p0配置

1.配置详解

官方文档 : http://www.mchange.com/projects/c3p0/index.html

< default-config>

< property name="acquireRetryAttempts">30

< property name="acquireRetryDelay">1000

< property name="autoCommitOnClose">false

Test

false

100

null

false

100

< property name="numHelperThreads">3

root

password

< property name="preferredTestQuery">select id from test where id=1

300

false

true

root

false

2.spring+mybatis+c3p0的基本配置

初始化基本配置信息如下：

Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge26l9jv0ov961czeg8w|a2f51c, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge26l9jv0ov961czeg8w|a2f51c, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.6.24:3306/ETeam, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]

三、遇到的问题：

1.问题log：

严重: Servlet.service() for servlet [ETeam] in context with path [/ETeam] threw exception [Request processing failed; nested exception is org.springframework.dao.RecoverableDataAccessException:

### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 55,518,630 milliseconds ago. The last packet sent successfully to the server was 55,518,631 milliseconds ago. 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.

### The error may exist in com/mango/mapper/ProductMapper.java (best guess)

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### SQL: SELECT * FROM product

### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 55,518,630 milliseconds ago. The last packet sent successfully to the server was 55,518,631 milliseconds ago. 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 was 55,518,630 milliseconds ago. The last packet sent successfully to the server was 55,518,631 milliseconds ago. 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 was 55,518,630 milliseconds ago. The last packet sent successfully to the server was 55,518,631 milliseconds ago. 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.] with root cause

java.net.SocketException: Connection reset by peer: socket write error

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

at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)

at java.net.SocketOutputStream.write(SocketOutputStream.java:159)

at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)

at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)

at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3634)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2460)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)

at sun.reflect.GeneratedMethodAccessor46.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)

at com.sun.proxy.$Proxy138.execute(Unknown Source)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)

at com.sun.proxy.$Proxy357.selectList(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)

at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)

at com.sun.proxy.$Proxy376.getProductIndex(Unknown Source)

at com.mango.service.impl.ProductServiceImpl.getProductIndex(ProductServiceImpl.java:25)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:280)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)

at com.sun.proxy.$Proxy377.getProductIndex(Unknown Source)

at com.mango.controller.PageController.index(PageController.java:57)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)

at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:860)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

at com.mango.filter.BaseFilter.doFilter(BaseFilter.java:34)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)

at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)

at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)