mysql不设置超时时间 mysql语句超时时间设置

转载

编程小天才 2024-06-17 13:43:54

文章标签 mysql不设置超时时间 BasicDataSource 数据库连接池连接池 mysql 文章分类 MySQL 数据库

要时刻对线上系统保持敬畏之心，那么线上系统的JVM参数配置，数据库连接配置就得认真对待，不能疏忽

1. 常见问题

线上系统的数据库配置的参数的具体含义，以及该设置多大
线上系统对应的数据库最大支持的链接数是多少，通过压测能压到2000，还是5000 还是多少
apache的数据库连接池和阿里的DruidDataSource区别是啥。什么情况下该用哪个？
connectTimeout和socketTimeout 不配置或配置过大对系统有什么影响
socketTimeout,transcationtimeout, statementtimeout的关系
BasicDataSource源码分析，初始化策略，驱逐策略，怎么取连接的，怎么关闭链接?

2. 常见错误

The last packet successfully received from the server was 15,018 milliseconds ago.  The last packet sent successfully to the server was 15,017 milliseconds ago.

错误分析：

2.1 当数据库被突然停掉或是发生网络错误，由于TCP/IP的结构原因，socket没有办法探测到网络错误，因此应用也无法主动发现数据库连接断开。如果没有设置socket timeout的话，应用在数据库返回结果前会无期限地等下去，这种连接被称为dead connection。为了避免dead connections，socket必须要有超时配置。socket timeout可以通过JDBC设置，socket timeout能够避免应用在发生网络错误时产生无休止等待的情况，缩短服务失效的时间。

2.2 比如我们设置socket timeout为2000ms,当发生该错误时，需要进行对mysql链接进行失败重试，参数如下：autoReconnect=true&failOverReadOnly=false

2.3 像org.apache.commons.dbcp.BasicDataSource一般通过如下几个参数进行剔除无效的空闲链接，具体含义看下面解释
<property name="testWhileIdle" value="true"/>
<property name="timeBetweenEvictionRunsMillis" value="300000"/>
<property name="minEvictableIdleTimeMillis" value="300000"/>
<property name="numTestsPerEvictionRun" value="5"/>

3. 常见apache连接池配置

<bean abstract="true" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="mysqlParentDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="connectionProperties" value="useUnicode=true;characterEncoding=UTF-8;autoReconnect=true;connectTimeout=1000;socketTimeout=2000"/>
		<property name="username" value="${jdbc.mysql.username}"/>
		<property name="password" value="${jdbc.mysql.password}"/>
		<property name="initialSize" value="2"/>
		<property name="minIdle" value="2"/>
		<property name="maxIdle" value="5"/>
		<property name="maxActive" value="5"/>
		<property name="maxWait" value="1000"/>
		<property name="testWhileIdle" value="true"/>
		<property name="testOnBorrow" value="false"/>
		<property name="testOnReturn" value="false"/>
		<property name="timeBetweenEvictionRunsMillis" value="300000"/>
		<property name="minEvictableIdleTimeMillis" value="300000"/>
		<property name="numTestsPerEvictionRun" value="5"/>
		<property name="validationQuery" value="select 1"/>
		<property name="removeAbandoned" value="true"/>
		<property name="removeAbandonedTimeout" value="30"/>
	</bean>

validationQuery = "SELECT 1" 验证连接是否可用，使用的SQL语句
testWhileIdle = "true" 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.
testOnBorrow = "false" 借出连接时不要测试，否则很影响性能
timeBetweenEvictionRunsMillis = "30000" 每30秒运行一次空闲连接回收器
minEvictableIdleTimeMillis = "1800000" 池中的连接空闲30分钟后被回收,默认值就是30分钟。
numTestsPerEvictionRun="5" 在每次空闲连接回收器线程(如果有)运行时检查的连接数量，默认值就是5.
解释：
1. 配置timeBetweenEvictionRunsMillis = "30000"后，每30秒运行一次空闲连接回收器（独立线程）。并每次检查3个连接，如果连接空闲时间超过30分钟就销毁。销毁连接后，连接数量就少了，如果小于minIdle数量，就新建连接，维护数量不少于minIdle，过行了新老更替。
2. testWhileIdle = "true" 表示每30秒，取出3条连接，使用validationQuery = "SELECT 1" 中的SQL进行测试，测试不成功就销毁连接。销毁连接后，连接数量就少了，如果小于minIdle数量，就新建连接。
3. testOnBorrow = "false" 一定要配置，因为它的默认值是true。false表示每次从连接池中取出连接时，不需要执行validationQuery = "SELECT 1" 中的SQL进行测试。若配置为true,对性能有非常大的影响，性能会下降7-10倍。所在一定要配置为false.
4. 每30秒，取出numTestsPerEvictionRun条连接（本例是5，也是默认值），发出"SELECT 1" SQL语句进行测试，测试过的连接不算是“被使用”了，还算是空闲的。连接空闲30分钟后会被销毁
5. testOnBorrow和testOnReturn在生产环境一般是不开启的，主要是性能考虑。失效连接主要通过testWhileIdle保证，如果获取到了不可用的数据库连接，一般由应用处理异常。
6. 对于常规的数据库连接池，testOnBorrow等配置参数的含义和最佳实践可以参考官方文档。
数据源库连接池的实现原理与dropwizard无关，既然mysql server的wait_timeout等参数被设置为30秒，那么就会主动关闭不活跃的客户端连接，几个test参数设置为true可以通过充分的检测移除不可用连接，并重新创建新的连接，保证应用都获取到健康的连接。
7. 通过这个命令 show variables like '%timeout%' 可以查询数据库的超时 show variables like '%timeout%'，my.conf中的wait_timeout参数和interactive_timeout参数默认是28800秒，也就是8小时。一般在生产环境这个数值会被设置为7天甚至30天，目的是保证mysql不会因为流量稀少而主动关闭session. 至于是否会导致大量的sleep连接，

4. 常见hikari配置

spring:
  datasource:
    url: jdbc:p6spy:mysql://*******:3358/*****?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&socketTimeout=15000
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    hikari:
      # 指定连接池等待连接返回的最大等待时间，毫秒单位.
      max-wait: 6000
      # 连接池中维护的最小空闲连接数
      minimum-idle: 5
      # 最大池大小
      maximum-pool-size: 64
      # 允许连接在连接池中空闲的最长时间（以毫秒为单位）
      idle-timeout: 60000
      # 池中连接关闭后的最长生命周期（以毫秒为单位）
      max-lifetime: 60000
      # 池返回的连接的默认自动提交
      auto-commit: true
      # 是客户端等待连接池连接的最大毫秒数
      connection-timeout: 2000
      # 校验链接
      connection-test-query: SELECT 1
      # 生效超时
      validation-timeout: 3000

1. No operations allowed after connection closed. max-lifetime表示一个连接在连接池中的最大生命周期，如果一个连接距离上一次使用的时间大于mysql的wait_timeout，此时mysql将连接关闭了。如果hikari的max-lifetime小于wait_timeout，hikari会移除这个连接，再次使用时会使用新创建的连接，这个时候数据库访问正常，如果此时hikari的max-lifetime小于wait_timeout，再次使用时hikari认为这个连接仍然是正常的，于是继续使用这个连接访问数据库，而此时mysql已经关闭连接了，因此会报上面的提示。因此，max-lifetime必须比数据库的wait_timeout参数短一些。但是不能太短，hikari在初始化时会检测max-lifetime的值，如果小于30000ms则使用默认的值1800000ms，因此你会发现怎么设置都没有效果，可能是因为你的mysql的wait_timeout的值比1800s要小。
2. connectTimeout：表示等待和MySQL数据库建立socket链接的超时时间，默认值0，表示不设置超时，单位毫秒，建议30000
3. socketTimeout：表示客户端和MySQL数据库建立socket后，读写socket时的等待的超时时间，linux系统默认的socketTimeout为30分钟，可以不设置
4. maxWait：表示从数据库连接池取链接，连接池没有可用连接时的等待时间，默认值0，表示无限等待，单位毫秒，建议60000
5. Transaction Timeout:Spring提供的transaction timeout配置非常简单，它会记录每个事务的开始时间和消耗时间，当特定的事件发生时就会对消耗时间做校验，当超出timeout值时将抛出异常
6. Statement Timeout:statement timeout用来限制statement的执行时长，timeout的值通过调用JDBC的java.sql.Statement.setQueryTimeout(int timeout) API进行设置。不过现在开发者已经很少直接在代码中设置，而多是通过框架来进行设置。

5. 其它几个连接池

tomcat-jdbc: 提供了testOnBorrow开关，但受validationInterval控制，如果要求网络或数据库恢复后没有任何业务失败，可以将validationInterval设置为0，强制每次都检查，默认会使用validateQuery检查，但是用户可以提供自己的validator。如果关闭所有testXxx开关，则无论业务如何失败都不会认为是连接失效。

durid: 打开testOnBorrow开关后每次都会检查。对于业务SQL失败会对异常进行判断，以确定连接是否失效。

c3p0: 提供了testConnectionOnCheckout开关，如果打开后每次都会检查，提供了两种检查方式：jdbc validate和validate query。对于业务SQL失败不认为是连接失效。c3p0是一个老牌数据库连接池，它的参数不叫testXxx，是另外一套参数:testConnectionOnCheckout, testConnectionOnCheckin, idleConnectionTestPeriod,preferredTestQuery和connectionTesterClassName。testConnectionOnCheckout如果设置则每次拿连接的时候都会检测，但是c3p0的检测机制是可以自定义(connectionTesterClassName)，默认会使用com.mchange.v2.c3p0.impl.DefaultConnectionTester。而且c3p0可以不指定测试的query(preferredTestQuery)，如果不指定测试query，它会利用jdbc connection的isValid检测(对于mysql driver就是发送一个ping命令)。testConnectionOnCheckin是归还连接时测试，如果设置则每次归还都检测。idle检测受idleConnectionTestPeriod控制，默认不检测

HikariCP: 强制每次拿连接都会检查，没有提供validate query的时候使用jdbc validate检查(推荐)。对于业务SQL失败会对异常进行判断，进而判断连接是否失效。这是号称世界上最牛逼的jdbc connection pool(具体我没有测试过)。这个连接池没有testXxx的配置，只有一个connectionTestQuery，如果你配置了这个query则每次拿连接的时候用这个query测试，否则就使用java.sql.Connection的isValid测试。这个线程池在每次从池里拿连接都会进行有效性检测，没有开关可以关闭。至于它为啥号称更快(作为连接池本身来说，要提高性能其实就少减少锁的争用)，大致看了一下是实现了一个ConcurrentBag数据结构。里面使用了ThreadLocal，默认会从ThreadLocal里取连接，如果取不到则去一个全局的结构(sharedList, CopyOnWriteArrayList，因为这个连接池归还是归还到ThreadLocal里，所以对于sharedList是读多写少的，用CopyOnWriteArrayList可以减少读取时锁的竞争)里取，而且从这个sharedList里取并不会从中移除，而是只做一个标记。去全局结构里取的时候还会增加一个计数，如果还取不到则去一个SynchronousQueue里取，我们都知道这个queue其实就是一个transfer(左手倒右手)，而在归还的时候会判断这个计数，计数不为零则说明有线程在等待池，立即将其丢给SynchronousQueue，如果没有计数则放到threadLocal里。另外它使用一个线程池在不断地创建必要(也就是有条件的)的连接，创建的连接就是放在sharedList里，所以这个池子里所有的连接都会在sharedList里，是否空闲是看一个标记，另外大部分连接可能就在ThreadLocal里，整个结构可以做到无锁的，只需要原子操作这个标记即可，而且如果线程之间比较均衡，则对这个原子操作的争抢也应该很低。另外，HikariCP也会根据业务SQL的异常判断是否要从连接池剔除连接。

BoneCP: 拿连接时不会进行检查，也没有开关控制。但是对于业务SQL执行失败会对异常进行判断，以确认连接是否失效，并且对于其他一些连接不能完全确认失效的异常会做标记，返回连接到连接池时会对标记了的连接进行检查。BoneCP也号称它是最快的数据库连接池(同样没做性能测试，只看看它的一些策略)。BoneCP我就没有看到类似testOnBorrow的设置，也没看到它在从池里拿连接的时候有任何测试，但是可以配置idleConnectionTestPeriodInSeconds和connectionTestStatement执行空闲检查。但是BoneCP在执行业务SQL的时候，如果遇到异常会对异常进行判断，如果是连接失效的异常会将这个连接从连接池里移除，而如果是有些不太确定是不是连接失效的异常会给连接设置一个possiblyBroken的状态，当连接归还的时候如果这个状态设置了会进行连接有效性测试。BoneCP连接池的主要实现算法是将连接分为多个桶，每个桶是一个BlockingQueue，获取连接的时候对当前thread id取模，看看落到哪个桶，然后去对应的桶取，这样可以降低锁竞争的几率。

推荐使用方式：对于critical应用不推荐使用BoneCP，对于critical应用对于tomcat-jdbc建议打开testOnBorrow，并且将validationInterval设置为0。对于durid建议打开testOnBorrow，对于c3p0建议打开testConnectionOnCheckout。HikariCP是默认就检测

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。