MySQL超时参数以及相关数据集成、DataX数据同步案例分享

一、背景

MySQL系统变量提供关于服务器的一些配置和能力信息,大部分变量可在mysqld服务进程启动时设置,部分变量可在mysqld服务进程运行时设置。合理的系统变量设值范围,是保障MySQL稳定提供服务的重要因素。本文主要描述MySQL数据库的超时timeout相关的一些系统变量,部分参数同程序应用中常见到的CommunicationsException: Communications link failure异常息息相关。

本文也结合数据同步的场景,对使用DataX3进行MySQL数据同步时遇到的几个经典案例进行阐述,期望对遇到类似问题的同学有一定的参考作用。

安利一下:DataX 是一个异构数据源离线同步工具,致力于实现包括关系型数据库(MySQL、Oracle等)、HDFS、Hive、ODPS、HBase、FTP等各种异构数据源之间稳定高效的数据同步功能。详细介绍可见:阿里云开源离线同步工具DataX3.0介绍开源代码仓库WIKI地址

数据集成是阿里集团对外提供的稳定高效、弹性伸缩的数据同步平台,为阿里云大数据计算引擎(包括 MaxCompute、AnalyticDB等)提供离线(批量)的数据进出通道 。DataX是阿里云数加数据集成对外的开源版本,如何大家需要使用一站式的大数据集成方案,可以参见 数据集成产品首页数据集成产品帮助文档

二、超时系统变量介绍

MySQL同超时相关的系统变量名大都包含timeout关键字,使用MySQL show variables 即可查询当前变量值状态,查询SQL以及结果如下:

show variables like '%timeout%';

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

下面具体介绍各个超时系统变量的含义,内容主要翻译和参考了MySQL官方文档。

1.connect_timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

connect_timeout是MySQL服务端进程mysqld等待连接建立完成的时间,单位为秒。如果超过connect_timeout时间范围内,仍然无法完成协议握手话,MySQL客户端会收到异常,异常消息类似于: Lost connection to MySQL server at 'XXX', system error: errno. 遇到此类错误,一般可尝试性增加connect_timeout时间的大小。

顺便介绍下MySQL应用协议握手发包过程: MySQL协议握手

以Plain Handshake为例,在TCP三次握手建立后,进入MySQL应用层协议握手阶段,过程如下:

  • server sending Initial Handshake Packet
  • client replying with Handshake Response Packet

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

MySQL服务端会有监听线程循环接收请求,当有客户端请求过来时,会创建线程(或者从线程池中取)来处理这个客户端请求(一般基于网络的应用都是这个套路,监听线程不具体处理客户请求)。由于MySQL连接基于TCP协议,在MySQL应用层协议建立前,需要完成TCP三次握手,在TCP三次握手成功之后,客户端会进入阻塞,等待服务端的消息,进入应用层协议握手阶段。后续服务端会创建线程(或者从线程池中取)来处理请求,这里会进行权限相关的验证,比如host、用户名、密码验证等。host验证即用grant命令授权用户的时候是有指定host的(比如设置了仅允许指定ip的客户端连接数据库);用户名密码认证则是服务端先生成一个随机数发送给客户端,客户端用该随机数和密码进行多次sha1加密后发送给服务端验证。如果在connect_timeout时间范围内无法完成握手,则报告建立连接异常。

相对于MySQL数据查询Query来说,连接握手相对耗时较少,系统默认的10秒一般足够了,不太会出现需要修改的场景。

2.delayed_insert_timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

delayed_insert_timeout用于控制MySQL的延迟插入(DELAYED inserts)超时时间,由于在MySQL 5.6.7后,延迟插入不在支持,此系统变量也不建议修改或使用。

3.innodb_lock_wait_timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用事务失败。参数的时间单位是秒,最小可设置为1s(一般不会设置得这么小),最大可设置1073741824秒(34年),默认值是50秒。当锁等待超过设置时间的时候,类似会报如下的错误; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。

innodb_lock_wait_timeout只针对InnoDB存储引擎下的行锁,不影响或限制表锁,可以通过SET GLOBAL 或者 SET SESSION语句在运行时修改此值。

在数据同步场景下,MySQL写出使用了批量batch提交模式,每个batch会开启一个事务的,innodb引擎下,会受到此参数的影响。

4.innodb_rollback_on_timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

innodb_rollback_on_timeout只针对InnoDB存储引擎,默认为为OFF。ON表示回滚整个事务,OFF表示只回滚最后一条报错的sql。如果事务因为加锁超时,根据innodb_rollback_on_timeout进行回滚(完整事务或最后一条出错SQL),客户端会收到报错异常,用户可以选择是继续提交,或者回滚之前的操作,由用户选择是否进一步提交或者回滚事务。

此系统变量是一个布尔状态的参量,不是超时时间。

5.interactive_timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

interactive_timeout表示服务器关闭交互式连接前等待活动的秒数,参数默认值28800秒(8小时)。交互式客户端(如mysql命令行)定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。如果某个交互式客户连接在interactive_timeout秒内没有操作动作,MySQL服务器就认为该客户连接不再有保留的必要并自动关闭这个连接。对于非交互式的客户连接,MySQL服务器将使用wait_timeout变量的值作为这种超时等待时间。

一个交互式客户连接断开并重连的例子(mysql命令行客户端)如下:

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

DataX进行MySQL数据同步时,使用的MySQL驱动是mysql-connector-java 5.1.34版本,属于非交互式客户端。

6.wait_timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

wait_timeout表示服务器关闭非交互连接之前等待活动的秒数,参数默认值:28800秒(8小时)。在线程启动时,根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。如果某个非交互的客户连接在wait_timeout秒内没有操作动作,MySQL服务器就将认为该客户连接不再有保留的必要并自动关闭这个连接。对于交互式的客户连接,MySQL服务器使用interactive_timeout变量的值作为这种超时等待的秒数。这个变量仅使用于TCP/IP连接和Unix系统上的套接字文件连接。

DataX进行MySQL数据同步时,使用的MySQL驱动是mysql-connector-java 5.1.34版本,属于非交互式客户端。

这里介绍和wait_timeout相关的2个场景。

6.1MySQL经典的8小时连接超时问题

假设你的数据库是MySQL,如果数据源配置不当,将可能发生经典的8小时问题。原因是MySQL在默认情况下,如果发现一个连接的空闲时间超过8小时,将会在数据库端自动关闭这个连接。而数据源并不知道这个连接已经关闭了,当它将这个无用的连接返回给应用时,应用在此连接上执行的SQL Statement都会失败。一般数据库连接池(如c3p0、dbcp)都有保活机制、或者返回连接前检查连接的可用性。

此问题的相关异常栈一般如下所示:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.] with root cause
 java.net.SocketException: Software caused connection abort: 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:3852)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2484)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
at com.mysql.jdbc.ConnectionImpl.setReadOnlyInternal(ConnectionImpl.java:5484)
at com.mysql.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:5477)
at sun.reflect.GeneratedMethodAccessor145.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)

6.2DataX MySQL数据读取连接断开问题

此系统变量对于DataX数据同步有较大影响。使用DataX进行MySQL数据同步时,DataX是通过JDBC 相关接口发送数据读取SELECT SQL语句,进而读取MySQL数据库表数据的。如果MySQL表过大、或者有不合适的数据抽取控制where条件、或者数据表缺少相关的索引机制,导致数据读取SQL长时间不返回,有可能到达wait_timeout时间限制值,导致连接被MySQL服务端关闭,数据同步任务失败。

此时需要增大wait_timeout大小或者尝试优化数据读取SQL(表索引、有索引列的where条件、减少不必要的函数列等)的性能。

7.lock_wait_timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

lock_wait_timeout是元数据锁等待超时,任意锁元数据的语句都会用到这个超时参数,默认为一年。为了保证事务可串行化,不管是myisam还是innodb引擎的表,只要是先在一个session里面开启一个事务,就会获取操作表的元数据锁,这时候如果另一个session要对表的元数据进行修改,则会阻塞直到超时。这个超时会影响对表DML/DDL操作、视图、存储过程、存储函数、LOCK TABLES、FLUSH TABLES WITH READ LOCK、HANDLER 语句等。

8.net_read_timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

在MySQL服务器接收客户数据的场合,如果MySQL服务器在等待了net_read_timeout秒之后仍未收到来自客户连接的数据,就会产生一个读操作超时错误。这种倒计时只适用于TCP/IP连接。

这里介绍一个net_read_timeout相关的异常情景。

8.1超时问题案例

另一个数据同步任务,在数据写DRDS(分布式关系型数据库服务)的过程中,出现连接被关闭异常:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
        at com.mysql.jdbc.Util.getInstance(Util.java:360)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
        at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1232)
        at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1225)
        at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4568)
        at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.doBatchInsert(CommonRdbmsWriter.java:361)
        at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.startWriteWithConnection(CommonRdbmsWriter.java:291)
        at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.startWrite(CommonRdbmsWriter.java:319)
        at com.alibaba.datax.plugin.writer.mysqlwriter.MysqlWriter$Task.startWrite(MysqlWriter.java:78)
        at com.alibaba.datax.core.taskgroup.runner.WriterRunner.run(WriterRunner.java:56)
        at java.lang.Thread.run(Thread.java:662)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 302,332 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3427)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3327)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4846)
        at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.doBatchInsert(CommonRdbmsWriter.java:348)
        ... 5 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3337)
        ... 12 more

这里分析判断出现问题的原因是net_read_timeout过小导致的,MySQL服务端没有在net_read_timeout时间范围内读取到客户端MySQL驱动发送过来的数据,导致连接中断。

另外,这类错误不一定是net_read_timeout、net_write_timeout、net_retry_count设置值不合适导致的,也可能是网络基础环境、数据库负载压力、客户端负载压力等有异常情况,遇到问题时需要具体分析找到rootCase。

9. net_write_timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

在MySQL服务器往客户发送数据的场合,如果MySQL服务器在经过了net_write_timeout秒之后仍未收到来自客户端的响应,就将产生一个写操作超时错误。这种倒计时只适用于TCP/IP连接。

这里介绍一个net_write_timeout相关的异常情景。

9.1超时问题案例

一个数据同步任务,在数据读MySQL的数据过程中,出现连接中断异常,问题报错关键异常信息如下:

2016-08-23 16:16:21.113 [9322429-0-0-reader] INFO  CommonRdbmsReader$Task - Begin to read record by Sql: [select hashcode,address from geohash6_address 
] jdbcUrl:[jdbc:mysql://ip:3306/database?yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true].

...

2016-08-23 16:22:54.894 [job-9322429] INFO  LocalJobContainerCommunicator - Total 429248 records, 371587338 bytes | Speed 343.11KB/s, 329 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 44.462s |  All Task WaitReaderTime 333.361s | Percentage 0.00%
2016-08-23 16:22:57.904 [9322429-0-0-reader] ERROR ReaderRunner - Reader runner Received Exceptions:
com.alibaba.datax.common.exception.DataXException: Code:[DBUtilErrorCode-07], Description:[读取数据库数据失败. 请检查您的配置的 column/table/where/querySql或者向 DBA 寻求帮助.].  - 执行的SQL为: select hashcode,address from geohash6_address  具体错误信息为:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 10 milliseconds ago.  The last packet sent successfully to the server was 396,755 milliseconds ago.
    at com.alibaba.datax.common.exception.DataXException.asDataXException(DataXException.java:26) ~[datax-common-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.util.RdbmsException.asQueryException(RdbmsException.java:81) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.reader.CommonRdbmsReader$Task.startRead(CommonRdbmsReader.java:220) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.reader.mysqlreader.MysqlReader$Task.startRead(MysqlReader.java:81) ~[mysqlreader-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.core.taskgroup.runner.ReaderRunner.run(ReaderRunner.java:57) ~[datax-core-0.0.1-SNAPSHOT.jar:na]
    at java.lang.Thread.run(Thread.java:662) [na:1.6.0_32]
  • 此DataX任务数据读取SQL为:select hashcode,address from geohash6_address
  • 此DataX任务已经运行一段时间,通过日志可以看到MySQL读取数据情况: Total 429248 records, 371587338 bytes | Speed 343.11KB/s, 329 records/s
  • 在2016-08-23 16:22:57.904时(距离开始发起读取SQL 390秒左右)出现了CommunicationsException异常,上次从服务端获取到数据为10毫秒前,上次驱动发送数据到服务端(即服务端收到确认)396,755毫秒(396秒)前
  • MySQL服务和超时相关的系统变量如下图:

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

  • 这里分析判断出现问题的原因是net_write_timeout过小导致的,MySQL服务端没有在net_write_timeout时间范围内获取MySQL驱动的ACK,连接中断。

另外,这类错误不一定是net_read_timeout、net_write_timeout、net_retry_count设置值不合适导致的,也可能是网络基础环境、数据库负载压力、客户端负载压力等有异常情况,遇到问题时需要具体分析找到rootCase。

10.net_retry_count

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

如果MySQL服务端在读写数据时,出现连接中断,会重试net_retry_count指定的次数。

11.slave-net-timeout

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

如果从服务器在经过slave-net-timeout秒之后仍未接收到来自主服务器的数据,就将产生一个超时错误,这种倒计时仅仅使用TCP/IP连接。在超时连接错误后,从服务器会尝试重连,第一次尝试重连发生在slave-net-timeout超时触发时,后续的重连间隔、重连次数都有相关的配置可自定义。

三、在数加Data IDE中测试连通性

大数据开发套件(Data IDE),提供了可视化开发界面、离线任务调度运维、快速数据集成、多人协同工作等功能,为您提供一个高效、安全的离线数据开发环境。并且拥有强大的Open API为数据应用开发者提供良好的再创作生态。用户可在其中添加数据源,配置数据同步任务,数据源添加时可进行连通性测试,具体效果如下:

MySQL超时参数以及相关数据集成、DataX数据同步案例分享

如果您在数据源测试联通时遇到一些问题,大致遇到的情况有:

  • 用户名、或密码错误
  • 由于白名单、或者MySQL数据库host访问限制,不允许连通测试机器访问您的数据库
  • 网络或数据库相关问题,没有在指定时间内3秒联通测试成功并返回
  • 您的数据库连接格式有误,不符合MySQL JDBC URL格式要求
  • 您配置的数据库IP、端口、有防火墙限制、是否路由可达等,此类错误一般异常消息大致如下:
Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

针对数据库连接超时相关,可能遇到的情况有:

1.socket超时

套接字或插座(socket)是一种软件形式的抽象,用于表达两台机器间一个连接的“终端”。针对一个特定的连接,每台机器上都有一个“套接字”,可以想象它们之间有一条虚拟的“线缆”。JAVA有两个基于数据流的套接字类:ServerSocket,服务器用它“侦听”进入的连接;Socket,客户端用它初始一次连接。侦听套接字只能接收新的连接请求,不能接收实际的数据包。
以基于TCP/IP协议栈为例介绍,每一次网络交互的sync和ack都有等待时间的上限。假如建立连接是设置socket超时时间为10s,那么当客户端发送sync之后在10s内没有收到服务端的ack信息就会超时。通常java会抛SocketTimeoutException。socket通信失效后下面除login timeout的其他超时机制均会失效。

socket超时时间可以在连接的url中指定。对于MySQL,可以在JDBC URL中指定socket超时时间,如将设置socket超时为10s:

jdbc:mysql:host:port/database?socketTimeout=10000

2.login超时

login timeout是jdbc尝试连接database最长等待时间。在jdbc和database握手过程中每个阶段都会检查是否超时,如果超时会抛SocketException异常,退出登录连接。可以通过DriverManager.setLoginTimeout(10)设置。

目前阿里云数加平台中,数据源连通性检查MySQL的登录超时时间是3秒。

3. connection超时

对应上面MySQL系统变量的connect_timeout参数

客户端和服务器建立的应用层连接,与服务器端无交互状态的连接,直到被服务器端强制关闭而等待的时间,此参数只对基于TCP/IP或基于Socket通信协议建立的连接才有效,单位为秒。连接的建立涉及到资源的分配,如连接池资源。可以通过连接串设置。如:

jdbc:mysql:host:port/database?connectTimeout=10000

4. statement超时

客户端发送一个sql等待服务器返回最大等待时间,客户端在发送sql之前会设置一个timer,如果在指定等待时间内还未收到服务器的执行结果,则会向服务器发出取消sql执行命令。并抛出SQLException。设置方法:statement.setQueryTimeout(100);注意:如果socket通信阻塞,这个异常是抛不出来的,方法调用会阻塞在向服务器发送取消命令。

上一篇:带你读《Python金融大数据挖掘与分析全流程详解》之三:金融数据挖掘案例实战1


下一篇:视频监控市场受到经济危机的影响