2020-11-07

MYSQL的wait_timeout和interactive_timeout的配置与理解

最近部署在服务器上的项目出现的两个问题，经过百度后，发现这两个问题都和MSQL的连个参数wait_timeout和interactive_timeout有关，但是这两个问题的解决方案又相矛盾，让我想要去深入了解究竟该如何处理这两个问题。
这两个问题分别是：
1.too many connections
2.最近在测试项目的时候，特别每天第一次测试的时候，总是报数据库连接不上，连接超时的错误，连续连接才能解决问题

1. 参数基本定义解释

此处摘录自[MySQL中的配置参数interactive_timeout和wait_timeout(可能导致过多sleep进程的两个参数]
**wait_timeout:**服务器关闭非交互连接之前等待活动的秒数。
在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。
参数默认值：28800秒（8小时）
**interactive_timeout:**服务器关闭交互式连接前等待活动的秒数.
交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
参数默认值：28800秒（8小时）
MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL Server的连接上限数，这会报’too many connections’的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。

2. 配置过程中出现的问题

在进入etc/my.cnf文件配置wait_timeout的值，重启MySQL服务器后，通过show variables like “%timeout%”，发现刚刚设定的wait_timeout并未生效。而通过set命令行配置参数，当服务器重启后，该参数还原成默认值（8h)，经过百度后发现
如果在配置文件my.cnf中只设置参数wait_timeout=100,则重启服务器后进入，执行:
Mysql> show variables like “%timeout%”;要同时设置interactive_timeout和wait_timeout才会生效。
【mysqld】
wait_timeout=100
interactive_timeout=100
重启MySQL Server进入后，查看设置已经生效。

3.百度到的解决方案

经过一般搜索，解决第一个问题的方法是增大最大连接数max_connections以及减小wait_timeout和interactive_timeout,这样，就能减小很多闲置线程的连接，预留出更多的线程，减小出现连接过多的可能性。而解决第二个问题的方法恰恰相反。第二个问题出现的原因是，链接超过8小时 被MySQL 断掉，应用继续用已经被断掉的链接，因此报异常。 这个时候的解决方案就是要增大wait_timeout的值，扩大连接时长。这样这两个问题的解决方案就出现了矛盾，一个需要增大wait_timeout的值，一个需要减少wait_timeout的值，我一时间陷入了蒙蔽状态，但是我知道这个问题肯定是很常见且肯定是有解决方法的，所以我这个小菜鸡还没有放弃，还在持续百度Ing（我大概就是个百度小能手吧，哭唧唧，还不是因为自己太菜！！！）
第二个问题的报错信息是：
No operations allowed after connection closed
出现原因：
之所以会出现这个异常，是因为MySQL5.0以后针对超长时间DB连接做了一个处理，那就是如果一个DB连接在无任何操作情况下过了8个小时后(Mysql 服务器默认的“wait_timeout”是8小时)，Mysql会自动把这个连接关闭。这就是问题的所在，在连接池中的connections如果空闲超过8小时，mysql将其断开，而连接池自己并不知道该connection已经失效，如果这时有 Client请求connection，连接池将该失效的Connection提供给Client，将会造成上面的异常。所以需要配置相应的连接池参数，定时去检查连接的有效性，定时清理无效的连接。
解决办法：C3P0官方对于MySQL8小时问题的解决方案 The last packet successfully received from the server was x milliseconds ago

   <environment id="C3P0">
                <transactionManager type="JDBC"/>
                <dataSource type="com.pqy.tools.c3p0DataSourceFactory">
                <!--<dataSource type="com.mchange.v2.c3p0.ComboPooledDataSource">-->
                    <!-- mysql -->
                    <property name="driverClass" value="com.mysql.jdbc.Driver"/>
                    <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/medical_waste"/>
                    <property name="user" value="root"/>
                    <property name="password" value="123456"/>

                    <property name="initialPoolSize" value="20"/>               <!--初始化连接池默认大小-->
                    <property name="acquireIncrement" value="3"/>                <!-- 连接不足时每次申请个数 -->
                    <property name="minPoolSize" value="20"/>                    <!-- 连接池最小连接数 -->
                    <property name="maxPoolSize" value="100"/>                    <!-- 连接池最大连接数  -->
                    <property name="maxIdleTime" value="3600"/>                    <!-- 空闲连接等待1h后关闭 -->
                    <property name="maxIdleTimeExcessConnections" value="600"/>    <!-- 连接池超过最小连接数的空闲连接将在10m后关闭 -->
                    <property name="checkoutTimeout" value="20000"/>               <!-- 从连接池取连接若连接不足时，最长等待20s后超时 -->
                    <property name="idleConnectionTestPeriod" value="3600"/>        <!-- 守护线程每隔300s检测空闲连接有效性  单位是s，而不是毫秒-->
                    <property name="testConnectionOnCheckin" value="true"/>         <!-- 获取connection时测试是否有效 异步检测连接的有效性 -->
                    <property name="testConnectionOnCheckout" value="false"/>      <!-- 设置为true，所有的连接都将检测其有效性，会影响性能，所以将其设置为false-->
                    <property name="preferredTestQuery" value="SELECT 1"/>
                </dataSource>
            </environment>

所以说还是要一步一个脚印的踏实找问题啊！