数据库链接问题

1. jdbc链接失效问题

       错误表现：ERROR c.m.j.e.e.c.w.c.CrmEtlSourceController - syncCrmData send error msg: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure。

       问题分析：最近4天一直有很多数据出现这个问题。原因是运维人员调整了参数。通过查询：show global variables like '%timeout'; 其中有个wait_timeout 28800s=8h。之前是300s。而这个参数的意思是：如果mysql一个连接发现300s没有使用。就丢弃掉这个连接。而（1）mysql自身的连接和jdbc的连接都保存了连接的会话信息。（2）jdbc数据库连接池不知道这个连接已经失效了。当它再试图去连mysql的时候，会报以上错误。

     如果都不是以上问题，最大可能是数据库与应用之间存在安全设备，比如日志审计设备，也会导致时不时出现这个问题。一般在网络结构不是很科学时很容易出现。这个在目前公司出现过，无论配置怎么调节，都会解决不了，就应该想到网络或安全设备。

       所以，有几种解决方案。

      a. 修改mysql中my.cnf文件，将过期时间设置为1年。然后重启数据库。

          [mysqld]
          wait_timeout=31536000
          interactive_timeout=31536000

     b.建议方案。

         我们可以将数据库连接池的 validateQuery、testOnBorrow(testOnReturn)打开，这样在 每次从连接池中取出且准备使用之前(或者使用完且在放入连接池之前)先测试下当前使用是否好用，如果不好用，系统就会自动destory掉。

或者testWhileIdle项是设置是否让后台线程定时检查连接池中连接的可用性。c3p0及druid都有参数配置。

     c.mysql5.0以前使用。

        在jdbc连接url的配置中，你可以附上“autoReconnect=true”，但这仅对mysql5以前的版本起作用。

注意：

如我当前有环境，一个现网的后台管理系统，使用人数在50以内，那么我wait_timeout 就是默认8小时，c3p0不用做连接有效性检查等，都是万事ok的。

而我还有一个EPG前台管理系统，用户量在300万以内，如果我wait_timeout为8小时，那我一到高峰期肯定就是死翘翘的，会有太多的TCP连接没关闭，
数据库连接数肯定是不够的。

 

       因为这个问题在jdbc连接时经常出现。从我开始使用Mysql就遇到了。有必要再重新归纳下连接池相关的知识点。

       （1）为什么会出现连接池？-为了提高性能。在WEB应用系统中，如果JSP、Servlet或EJB使用JDBC直接访问数据库中的数据，每一次数据访问请求都必须经历建立数据库连接、打开数据库、存取数据和关闭数据库连接等步骤，而连接并打开数据库是一件既消耗资源又费时的工作，如果频繁发生这种数据库操作，系统的性能必然会急剧下降，甚至会导致系统崩溃。数据库连接池技术是解决这个问题最常用的方法，在许多应用程序服务器（例如：Weblogic,WebSphere,JBoss）中，基本都提供了这项技术。

       （2） 目前市面上的数据库连接池(JDBC)有哪些？c3p0,dbcp,proxool,还有国内的druid。

                  dbcp是一个依赖 Jakarta commons-pool 对象池机制的数据库连接池.DBCP 可以直接的在应用程序中使用，Tomcat 的数据源使用的就是 DBCP。Hibernate 官方宣布由于 Bug 太多不再支持 DBCP，而推荐使用 Proxool 或 C3P0。

                  c3p0是一个开放源代码的 JDBC 连接池，它在 lib 目录中与 Hibernate 一起发布,包括了实现 jdbc3 和 jdbc2 扩展规范说明的 Connection 和 Statement 池的 DataSources 对象。

                  proxool是一种 Java 数据库连接池技术。sourceforge 下的一个开源项目,这个项目提供一个健壮、易用的连接池，最为关键的是这个连接池提供监控的功能，方便易用，便于发现连接泄漏的情况。

                  以上三种是最常用的JDBC连接池。

                 druid不仅仅是一个jdbc连接池，还包括了ProxyDriver。一系列的jdbc组件库，一个SQL Parser。Druid对Mysql和Oracle做了特别的优化，比如 Oracle 的 PS Cache 内存占用优化，MySql 的 ping 检测优化。

                 Druid 的SQL Parser提供了 MySql、 Oracle、 Postgresql、 SQL-92 的 SQL 的完整支持，这是一个手写的高性能 SQL Parser，支持 Visitor模式，使得分析 SQL 的抽象语法树很方便。简单 SQL 语句用时 10 微秒以内，复杂 SQL 用时 30 微秒。

通过 Druid 提供的 SQL Parser 可以在 JDBC 层拦截 SQL 做相应处理，比如说分库分表、审计等。Druid 防御 SQL 注入攻击的 WallFilter 就是通过 Druid 的 SQL Parser 分析语义实现的。

 

       （3） 连接池的选择。性能dbcp<=c3p0<proxool    稳定性dbcp>=c3p0>proxool。

                 目前国内用Druid的比较多。可以考虑。参考：https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98

        （4）配置

                 c3p0

 1. 先引入jar包。

 2. xml配置

<!--<bean id="dataSourceTarget" abstract="true" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${jdbc.driver}" />
<property name="minPoolSize" value="${jdbc.minPoolSize}" />
<property name="maxPoolSize" value="${jdbc.maxPoolSize}" />
<property name="initialPoolSize" value="${jdbc.initialPoolSize}" />
<property name="maxIdleTime" value="${jdbc.maxIdleTime}" />
<property name="acquireIncrement" value="${jdbc.acquireIncrement}" />
<property name="checkoutTimeout" value="${jdbc.acquireCheckoutTimeout}" />
</bean>

&lt;!&ndash; 可以指定多个name，之间可以用分号（“；”）、空格（“ ”）或逗号（“，”）分隔开，如果没有指定id，那么第一个name为标识符，其余的为别名；若指定了id属性，则id为标识符，所有的name均为别名 &ndash;&gt;
<bean id="edp-dataSource" parent="dataSourceTarget" lazy-init="true">
<property name="jdbcUrl" value="${edp.jdbc.url}" />
<property name="user" value="${edp.jdbc.username}" />
<property name="password" value="${edp.jdbc.password}" />
</bean>→

3. 参数值

jdbc.driver=com.mysql.jdbc.Driver
jdbc.initialPoolSize=10
jdbc.minPoolSize=10
jdbc.maxPoolSize=20
jdbc.maxIdleTime=2000
jdbc.acquireIncrement=5
jdbc.acquireCheckoutTimeout=3000

 

 

            druid

     1. 引入jar包。    

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency>

  2.xml文件配置。

<bean id="edp-dataSource" class="com.alibaba.druid.pool.DruidDataSource"
destroy-method="close">
<!-- 数据库基本信息配置 -->
<property name="url" value="${edp.jdbc.url}" />
<property name="username" value="${edp.jdbc.username}" />
<property name="password" value="${edp.jdbc.password}" />
<property name="driverClassName" value="${jdbc.driver}" />
<!-- 借时检测是否空闲 -->
<property name="testWhileIdle" value="true" />
<!-- 每30秒运行一次空闲连接回收器 一般比minEvictableIdleTimeMillis小，每多少秒检查所有连接池中的空闲连接，把该值设置的小于mysql server的wait_timeout值即可。-->
<property name="timeBetweenEvictionRunsMillis" value="120000" />
<!-- 最大并发连接数 -->
<property name="maxActive" value="200" />
<!-- 初始化连接数量 -->
<property name="initialSize" value="30" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="10000" />
<!-- 最小空闲连接数 -->
<property name="minIdle" value="30" />
</bean>

 

 

  dbcp--jd

<!-- Datasource -->
<bean id="dataSourceBackup" class="org.apache.commons.dbcp.BasicDataSource">
 <property name="driverClassName" value="${datasource.jdbc.driver}" />
 <property name="url" value="${datasource.jdbc.url}" />
 <property name="username" value="${datasource.jdbc.username}" />
 <property name="password" value="${datasource.jdbc.password}" />
 <property name="validationQuery" value="select 1" />
 <property name="testOnBorrow" value="true" />
 <property name="testOnReturn" value="false" />
 <property name="initialSize" value="5" />
 <property name="maxIdle" value="20" />
 <property name="minIdle" value="20" />
 <property name="maxActive" value="${datasource.jdbc.maxActive}" />
 <property name="removeAbandoned" value="true" />
 <property name="removeAbandonedTimeout" value="15" />
 <property name="maxWait" value="5000" />
 <property name="defaultAutoCommit" value="true" />
</bean>

 

2.  数据库打满。

起因：客户批量发现出现系统异常。

问题分析过程：发现数据库cpu占用率很高。查询mysql-> show full processlist。发现很多任务在等待执行。

解决：

1.重启（不建议）。

2.关闭该任务。kill QUERY  4487855;  KILL [CONNECTION | QUERY] thread_id