性能文章>一次MySQL数据库连接超时问题排查与解决>

一次MySQL数据库连接超时问题排查与解决转载

2月前
188201

导语

对于处理SQL的问题大家肯定不陌生,本文也是一篇实战MySql数据库连接超时的问题,希望能对大家日常工作有所帮助。

 

正文

操作系统 Windows 10 Enterprise,数据库 MySQL-5.5.16,c3p0-0.9.5.2

 

关于针对数据库的连接,之前没有特别注意过,直到遇到如下问题:

Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 54,812,410 milliseconds ago. The last packet sent successfully to the server was 54,812,411 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

从上面的信息得知,应用程序与数据库服务器的连接中断了,那么为什么会出现上面的问题呢?不是有连接池吗?里面不是保存有大量的连接吗?比如使用c3p0数据源配置如下:

ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setInitialPoolSize(5);
dataSource.setMaxPoolSize(20);
dataSource.setAcquireIncrement(1);


错误信息中已经说得很明白了,上一次客户端和服务的通信已经是54812411ms前了,但是MySQL默认配置的wait_timeout属性确是「28800s」,也就是「8小时」,该参数的意义是「MySQL在没有进行通信的连接上等待的最长时间直到服务器关闭该连接」,上文信息中指出不通信的间隔已经有54812.411s了,所以客户端与数据库服务器的连接已经被服务器强制关闭了,所以才会出现如上的错误。

D634DE80-C13E-429A-B258-1A5E6A9CC67B.png


但是信息中也给出了可以解决的办法,如下:

使连接过期
对连接的有效性进行测试
增加数据库服务器的超时时间
使用**autoReconnect**属性避免
通过捕捉异常来重试建立连接
上述几种方法可以从范围上分成数据库层次、应用层次。

一、数据库层次上解决

1.1 通过增大**wait_timeout**的值

使用这种方式是最直接、最暴力的,通过增加该属性的值可以增大MySQL服务器在不活跃的连接上等待的时间,但是这种方式影响的范围很大,因为数据库的使用面向的应用肯定不止一个,所以如果不在万不得已的情况之下,不要使用这种方式。

1.2 配置autoReconnect属性

表明数据库驱动是否会重新建立不活跃的连接,默认情况下是false。一般是在JDBC的连接URL中使用。如果设置为true,对于在不活跃的连接上进行的查询会抛出异常,这些查询属于当前的事务,事务中进行下一次查询时,驱动将会重新建立连接。这个属性不推荐使用,因为它对于会话状态和数据一致性会造成影响,尤其是当应用不能正确的处理SQLExceptions时。在考虑强一致性的时候,尤其要避免使用该属性。

二、应用层次上解决

2.1 使连接过期

此处使用的是c3p0数据源,在该数据源下配置连接的过期时间是通过**maxIdleTime**属性实现的,该属性的意义是:一个不使用的连接在数据库连接池中存在的最长时间,直到它被连接池抛弃。单位是秒,默认值是0,表示连接池中的连接永远不会过期。

ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setMaxIdleTime(1800);


配置的属性值要大于**wait_timeout**的值,以免在MySQL服务器强制断开连接之后,连接还没有过期。

2.2 对连接有效性进行测试

对与数据库服务器的连接进行有效性的测试是一个可行的方式,可以随时确定数据库服务器是否可用,而不仅仅是连接是否可用。这里使用c3p0数据源作为示例进行,其他的数据源,比如DBCP和druid其中也有相对应的方式完成这种操作。

c3p0提供了很多的方式来对连接池中的连接进行测试,通过这种测试来避免在应用程序的层次上看到上文中提到的错误。通过以下几个属性来完成这种测试:

下面三个属性是控制什么时候进行测试连接。

idleConnectionTestPeriod:如果该属性的值大于0,每隔固定的时间段,c3p0会测试所有闲置、保持在连接池中的连接。默认情况下是不进行测试的;
testConnectionOnCheckout:是否在取连接进行测试;
testConnectionOnCheckin:是否在放入连接时进行测试;


下面三个属性是决定如何进行测试连接。

automaticTestTable:c3p0会自动创建一个空表以备查询进行连接测试;
connectionTesterClassName:ConnectionTester接口的全限定名实现, 默认值是com.mchange.v2.c3p0.impl.DefaultConnectionTester,用来定义c3p0数据源如何测试连接,过于灵活,避免过度使用;


preferredTestQuery:定义的被用来执行连接测试的查询;

2.2.1 进行测试的时候要考虑的问题

在进行连接测试的时候,需要考虑很多其他的问题。然后根据这些需要考虑的额外的问题来确定最佳的测试方法。

2.2.1.1 性能问题

性能问题是一个非常重要的话题,尤其是在大型的复杂应用中,在测试有效性时,也不要给连接带来过大的负担。

 

「首先要考虑连接测试的时机」

从连接池中取出连接时:这种测试是最简单,也是最可靠的,但是会给客户端的性能带来极大的影响,可以通过配置属性testConnectionOnCheckout=true来开启在此时进行测试;


连接放入连接池时:此时进行测试时,需要同时结合idleConnectionTestPeriod,可以满足较高的可靠性,可以通过设置testConnectionOnCheckin=true,在这种情况下,在闲时和放入连接时进行异步测试,性能依然不会下降;


「其次要考虑测试的语句」

如果使用的数据库驱动「支持JDBC4「,并且使用的」c3p0是0.9.5或以上」。可以直接调用JDBC的API来处理,其中有一个isValid()方法可以用来实现作为快速且可靠的连接测试。「默认情况下,c3p0使用这个方法进行测试」。如果想要设置一个超时时间,可以通过继承IsValidOnlyConnectionTester来实现,如下:

public class Tester extends IsValidOnlyConnectionTester{
  protected int getIsValidTimeout() { return 30; }
}


如果驱动不支持最新的API,默认情况下c3p0通过在连接上调用DatabaseMetaData的getTables()方法。这样可以与任何类型的数据库有效兼容,但是由于返回很大的数据量会对数据库连接池的性能造成显著影响;在JDBC3驱动以下(或者0.9.5版本之前)可以配置preferredTestQuery加速测试,但它优先于数据源进行加载,此时如果数据库中没有对应的表,则会造成错误,如下:

24BD3817-8E3A-4A96-B4A6-36A64C99183C.png


对于这种情况,则可以通过设置automaticTestTable属性来代替,c3p0会自动的创建一个空表来作为测试查询的对象;

2.2.1.2 兼容性问题

另外一个要考虑的是数据库兼容问题,比如需要兼容多种数据库时,比如同时要测试的数据库有MySQL、SQL Server和Oracle,则需要考虑测试语句的通用性。在这种情况下可以通过可以设定与数据库和表无关的语句,比如select 1来测试这个连接。但是针对不同数据库类型,支持的这种轻量级的测试语句并不相同,如下对不同的常见的数据库的支持:

2.2.1.3 灵活性的问题

如果对于数据源提供的测试方法或者需要自定义一些行为时,灵活性的问题就很重要了。

c3p0也提供了这样的支持,通过配置connectionTesterClassName属性,提供给实现类的完全限定名即可。这种情况不需要preferredTestQuery或者automaticTestTable,默认情况下该属性的值是com.mchange.v2.c3p0.impl.DefaultConnectionTester。可以通过继承DefaultConnectionTester的父类AbstractConnectionTester实现。简单实现如下:

public class MyConnectionTester extends AbstractConnectionTester {

    public int activeCheckConnection(Connection c, String preferredTestQuery, Throwable[] rootCauseOutParamHolder) {
        try {
            ResultSet resultset = c.createStatement().executeQuery("SELECT 1");
            while(resultset.next()){
                int result = resultset.getInt(1);
                if(result == 1){
                    System.out.println("test successfully");
                } else{
                    System.out.println("test failed");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    public int statusOnException(Connection c, Throwable t, String preferredTestQuery, Throwable[] rootCauseOutParamHolder) {
        return 0;
    }
}


配置如下:

ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setIdleConnectionTestPeriod(3);
dataSource.setConnectionTesterClassName("com.lmy86263.MyConnectionTester");

2.3 针对连接断开进行重试

由于目前所有的应用都是直接使用数据源来管理与数据库的连接,而在程序内部只是提供一些必须的配置项,所以如果要重试则需要通过编程手段来完成,如下在建立数据源的时候如果连接失败则会重试指定次数:

ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUser("root");
dataSource.setPassword("abc");

int retry = 3;
while(retry > 0){
    try {
        dataSource.getConnection();
        break;
    } catch (SQLException e) {
        retry--;
    }
}


如果对JDBC的超时的详细机制比较感兴趣可以参考官方文章。

 

更多思考

SQL的性能优化是非常多的,更多关于MySQL相关的内容可以阅读以下内容

面对MySQL千万级别大表,你要如何优化?

聊聊sql优化的15个小技巧

 

请先登录,感受更多精彩内容
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
MySQL 死锁套路:一次诡异的批量插入死锁问题分析
线上最近出现了批量insert的死锁,百思不得解。死锁记录如下:```2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: (1) TRANSACTI
MySQL 死锁套路:唯一索引下批量插入顺序不一致
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。先来做几个小实验,简化的表结构如下:```CREATE TABL
MySQL 死锁套路:再来看一例走不同索引更新的例子
前面有文章介绍了利用调试MySQL源码的方式来调试锁相关的信息,这里利用这个工具来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表:```CREATE TABLE `t3` ( `id
如何在 Mac 下用 Clion 调试 MySQL 源码
前面写了几篇文章来通过调试 MySQL 源码来分析死锁问题,有读者问如何用 IDE 调试源码,这篇文章简单介绍一下如何在 Mac 下调试。之所以使用调试的方式来分析死锁问题是因为在解决 MySQL 死
掉坑了!GROUP_CONCAT函数引发的线上问题
本文分享一篇在工作遇到的一个问题,关于MySQL GROUP_CONCAT函数导致的问题。希望能帮忙到你。 业务场景在说遇到的坑之前,先描述一下大致的业务场景。系统有一个排班的功能,一个医生一天可以排
空中楼阁之纸上谈兵 mysql的dbcp的配置
maxWait=-1 最大等待时间:当没有可用连接时,连接池等待连接被归还的最大时间(以毫秒计数),超过时间则抛出异常,如果设置为-1表示无限等待testOnBorrow=true指明是否在从池中取出
一个诡异的MySQL查询超时问题,居然隐藏着存在了两年的BUG
这一周线上碰到一个诡异的BUG。线上有个定时任务,这个任务需要查询一个表几天范围内的一些数据做一些处理,每隔十分钟执行一次,直至成功。通过日志发现,从凌晨5:26分开始到5:56任务执行了三次,三次都
1
0