Jdbc接收mysql大数据量报错的问题及解决
一个机器上10个jdbc客户端都新建jdbc连接,都向同一个mysql服务器获取数据,语句为select *```` from bms_gdm_ObjectTab limit 10000000
,单个客户端获取数据1GB左右,jdbc客户端所在机器跟mysql服务器用scp传输文件可达到130MB/s。程序如下
(几乎是教科书式的jdbc代码,并且运行时为了减少GC设置了12G堆内存):
java -Xmx12G -cp .:/app/mycat1.6.5/lib/mysql-connector-java-5.1.35.jar com.hw.MysqlNetSpeed
10个jdbc客户端只有1、2个会成功,大部分会在5分钟内被强行终止,报Communications link failure,详细如下:
这个问题绝不是百度上泛滥的wait_timeout配置问题,wait_timeout是由于长时间闲置连接而被mysql关闭的,一执行就报错,报错信息跟本问题几乎一致,所以有很大的疑惑性。本问题中每个jdbc客户端都是一启动就创建jdbc 连接然后发送sql请求,不存在长时间闲置,而且是statement. executeQuery这一jdbc的标准接口执行一段时间才报的错。
类似问题:
https://stackoverflow.com/questions/48262498/how-to-fix-this-java-mysql-exception-communications-link-failure https://stackoverflow.com/questions/13950496/what-is-java-io-eofexception-message-can-not-read-response-from-server-expect
客户端发送select语句后,服务端同时往10个客户端写大量数据(每个1GB),有的客户端需要等待超过1分钟才能继续写入,服务端就会把这种写入时超过1分钟(默认值)等待的连接直接关闭。而为什么会超过1分钟?跟服务端网卡、客户端网卡、客户端程序接收效率都有关系,在本例中,服务端跟客户端网卡都是10Gbit网卡,瞬时网络速度可以到280MB/s,都没有达到网络瓶颈,根因是jdbc程序在接收大量数据时会耗费大量的cpu跟内存资源,还需要不断做的GC,导致接收暂停,一旦GC时间超过net_write_timeout
,mysql则会关闭连接。
mysql-net_write_timeout-vs-wait_timeout-and-protocol-notes
net_write_timeout :The number of seconds to wait for a block to be written to a connection before aborting the write. applies only to TCP/IP connections,
由于java程序GC不可避免,在mysql服务端将net_write_timeout参数从60秒改为180秒
SET GLOBAL net_write_timeout =180;
SELECT @@global.net_write_timeout ;
SELECT @@session.net_read_timeout ;
show variables like '%timeout%'
net_write_timeout:60秒,mysql往客户端写数据过程中最大的等待时间。客户端select语句
java -Xmx12G -cp .:/app/mycat1.6.5/lib/mysql-connector-java-5.1.35.jar com.xx.MysqlNetSpeed
select * from bms_gdm_ObjectTab limit 10000000
/opt/mysql57/bin/mysql -uroot --port=3311 -pChangeme_123 -h100.100.77.170 -A
use bmsdb;
select * from bms_gdm_ObjectTab limit 10000000;
- 把net_write_timeout从60秒改为10秒,更快报上述异常
- 在10秒的基础上,客户端机器从1个增加到2个,总共2个jdbc客户端,还是很快报异常
- 在10秒的基础上,1个机器只启动1个jdbc客户端,异常会较晚抛出。接收速度很抖动,30MB到0KB/s之间。
- 在10秒的基础上,1个机器只启动1个原生的mysql shell客户端,正常。接收速度20MB/s,很均匀
- 在10秒的基础上,1个机器只启动7个原生的mysql shell客户端,正常。接收速度140MB/s,很均匀
- 在10秒的基础上,1个机器只启动10个原生的mysql shell客户端,有2个被kill掉。
- 改为2秒,jdbc 客户端报异常,mysql shell客户端正常
- 改为2秒,并设置比较大的堆内存跟新生代的伊甸园内存,使得不发生GC,正常。需要注意的是第一次避免了GC,客户端处理同样的sql再接收大量数据量时也会发生GC,所以这种设置了大量内存只能正常一次的做法不可取。
/usr/java/jdk1.8.0_121/bin/java -XX:+UseConcMarkSweepGC -XX:SurvivorRatio=32 -XX:TargetSurvivorRatio=32 -XX:MaxGCPauseMillis=500 -server -XX:NewSize=10G -XX:MaxNewSize=10G -Xmx14G -XX:InitialHeapSize=12G -cp .:/app/mycat1.6.5/lib/mysql-connector-java-5.1.35.jar com.hw.MysqlNetSpeed
或者
/usr/java/jdk1.8.0_121/bin/java -server -XX:NewSize=10G -XX:MaxNewSize=10G -XX:SurvivorRatio=32 -Xmx14G -XX:InitialHeapSize=12G -cp .:/app/mycat1.6.5/lib/mysql-connector-java-5.1.35.jar com.hw.MysqlNetSpeed
9.改为180秒,jdbc客户端只设-Xmx12G,1个jdbc客户端正常。
10.改为180秒,jdbc客户端只设-Xmx12G,10个客户端jdbc跟10个mysql shell都正常。
11.另外的总结:
以7、8为例。只有1个jdbc客户端或者1个mysql shell客户端,跑同一个SQL,保证正常的情况下,jdbc客户端的内存占用5.5GB左右cpu占用单核65%,而mysql shell的内存占用3GB左右,cpu占用单核45%。Jdbc客户端在处理大数据量时的性能比较差,但这是不发生GC的情况;
再改为180秒,jdbc客户端只设-Xmx12G(去除其他参数),内存占用持平,会发生19次GC,cpu最高占用1200%!
再改为180秒,jdbc客户端设置为8的jvm参数,第二次运行后内存占用持平(第一次不会GC),发生8次GC,cpu最高400%
再改为180秒,jdbc客户端设置为-XX:+UseConcMarkSweepGC -Xmx12G,第一次运行就会GC,发生24次,cpu最高700%
综上:jdbc客户端即使更换GC算法、设置比较大的伊甸园区等方式减少GC次数跟中断时间,运行过程中cpu占用至少是mysql shell的10倍!
mysql大数据量高并发传送的问题
2018-06-25 19:53:40 main excute sql:select * from bms_gdm_ObjectTab limit 10000000,Communications link failure
The last packet successfully received from the server was 157,628 milliseconds ago. The last packet sent successfully to the server was 157,630 milliseconds ago.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 157,628 milliseconds ago. The last packet sent successfully to the server was 157,630 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038)
at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2133)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1921)
at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3273)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:462)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2997)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2245)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2638)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
at com.hw.MysqlNetSpeed.main(MysqlNetSpeed.java:59)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 23 bytes, read 3 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914)
at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2116)
... 10 more
相关文章
- 基于-SLF4J-MDC-机制的日志链路追踪配置属性
ums: # ================ 基于 SLF4J MDC 机制的日志链路追踪配置属性 ================ mdc: # 是否支持基于 SLF4J MDC
- ajax-跨域访问
ajax 跨域访问 <!DOCTYPE html> <html xmlns:th="http://www.w3.org/1999/xhtml"> <head>
- 给第三方登录时用的数据库表-user_connection-与-auth_token-添加-redis-cache
spring: # 设置缓存为 Redis cache: type: redis # redis redis: host: 192.168.88.88 port
- Java动态代理
Jdk动态代理 通过InvocationHandler和Proxy针对实现了接口的类进行动态代理,即必须有相应的接口 应用 public class TestProxy { public
- Java读取classpath中的文件
public void init() { try { //URL url = Thread.currentThread().getContextClassLo
随机推荐
- 基于-SLF4J-MDC-机制的日志链路追踪配置属性
ums: # ================ 基于 SLF4J MDC 机制的日志链路追踪配置属性 ================ mdc: # 是否支持基于 SLF4J MDC
- ajax-跨域访问
ajax 跨域访问 <!DOCTYPE html> <html xmlns:th="http://www.w3.org/1999/xhtml"> <head>
- 给第三方登录时用的数据库表-user_connection-与-auth_token-添加-redis-cache
spring: # 设置缓存为 Redis cache: type: redis # redis redis: host: 192.168.88.88 port
- Java动态代理
Jdk动态代理 通过InvocationHandler和Proxy针对实现了接口的类进行动态代理,即必须有相应的接口 应用 public class TestProxy { public
- Java读取classpath中的文件
public void init() { try { //URL url = Thread.currentThread().getContextClassLo