[R语言]R语言使用多线程对数据库进行大批量访问时出现无法连接问题

问题描述：

在R中使用多线程对数据库进行写入，在服务器端运行脚本(linux环境)，总是在第6-7万个任务线程时，出现无法连接到数据库的问题。任务中断，错误信息为task 6xxxx failed，Can't connect to database。

而远程端在windows环境下执行时，却没有问题。

问题出现了很久，只所以动不起念头去解决，是隐约觉得问题出现在R语言工具包或linux操作系统底层的问题。

这两者都不是我能handle的领域。即使花了极大精力去定位问题，定位到了我也未必能解决。

最近由于脚本功能固定之后，有点厌倦每天在window端手动执行脚本了，然后等上30分钟才出结果。于是开始着手去研究。

错误的假设

最开始的猜想，认为可能是window下的odbc或防火墙等机制会在网络侧限制系统可创建的最大连接数，所以不会超过数据库的连接限制。

而在linux本地直连时，由于访问自身的端口，不存在类似的限制。于是乎线程不断生成，连接释放的速度跟不上连接创建的速度，以至于很容易就达到了数据库的MAX_connection。出现无法连接数据库的问题。

解决方案是想通过获取当前可用连接数，看是否准备资源不足。然后让脚本自行控制暂停，给资源释放争取一点时间。

但是在对服务器数据库状态进行测试时，却发现并不是这样。

 (查看数据库连接的状态参考：http://www.linuxidc.com/Linux/2016-05/131027.htm)

测试验证

测试发现，脚本多线程执行的时候，在本地创建了N 个connection。

下面是在程序在linux下执行时，服务器端通过netstat命令看到的信息：

系统的Active Internet Connections 一直在疯狂创建，但连接没有释放，导致本地端口耗尽。以致系统无法再新建连接。

而此时数据库服务端的Threads_connected一直维持在10左右，Threads_running在1，2，3浮动。

所以本地的连接管理出了问题，与数据库的状态无关。

那么，windows侧程序执行时是个什么情况？为什么在window执行没有问题？

window侧执行程序时：

服务器端的情况：

感觉连接的创建好像慢很多？最重要的是出现了CLOSING的connection，与上面的情况是不一样的。

此时Window本地段的端口状态：

虽然依然是疯狂创建端口进行连接，但是存在COLSE_WAIT的动作。

CLOSE的指令应该是windows端发起的。windows系统发现本地存在很多空闲不用的连接之后，发出了CLOSE的请求。不过这只是猜想。

原因：

因此问题的原因是本地不停申请端口，创建连接，连接得不到释放，以致端口耗尽，无法再新建连接。

之所以Linux和Window出现差异，可能是：两者的connection处理机制上存在细微差别。window系统会申请自己关闭，而linux则不会。

更根本的原因其实应该是下面提出的，R语言工具包(或本身？)实现机制的问题。

解决对策：

定位到问题之后，从两个思路去考虑解决方案。一是阻止线程不停创建连接，另外就是使系统将资源释放。

最好的方案应该是长连接复用。

其次应该是系统及时销毁TIME_WAIT的连接。相比上面，缺点是线程还需要不断新建连接，浪费资源。

以下是尝试的几个方案：

1. 长连接复用(长时间不关闭的连接)。

在进入多线程之前，先创建几个长连接，通过参数传入多线程环境中，长连接在子线程中反复使用。在任务完成之后再统一释放资源。

但是似乎是多线程机制的问题，导致在进入多线程前，创建的进程环境无法在子线程环境***享。程序在task1的时候就报错，声称没有环境资源(缺包缺函数定义)。

想了一下操作系统的多线程实现，多个连接同时分配到多个子线程环境的时候，或许是连接类型不支持分配复制成多个实例？

所以长连接复用没有实现。

2.在脚本中显式声明让语言/系统去回收资源。

查了一下在window和linux下显式关闭连接的方法，没有找到合适的实现方案。

查了下R语言实现多线程的包的文档，也没有对线程生成或线程环境进行操作的方法。

问题在于连接的资源不释放。即使限制了线程的生成速度，方案的效率及可行性依然存疑。

只有线程资源得到有效释放，才能比较合理的解决。

后来想通过调用gc()对创建的连接资源进行强制回收，调用间隔设置在3000，问题依然出现。细想了一下，在子线程下面调用gc()，回收的应该是子线程环境下的资源，而不是整个执行环境的资源。除非对每个线程都调用gc()，否则还是会存在风险。

然而对每个子线程都调用gc()之后，发现活动的的连接虽然大幅度减少，但是没有避免线程不停新建连接的问题。同时运行效率下降十分严重。完成全部任务需要将近40分钟的时间。而不对线程使用gc()时，完成全部任务花费不到5分钟。

可见对资源进行垃圾回收是个非常耗时的操作。

最后的方案

3.改变系统对连接的管理。

搜了下linux下如何释放time_wait连接。

参考这里对系统进行了设置：

http://blog.163.com/qingfeng_0105@126/blog/static/7506273820113794158655/

本意是想释放掉连接，由于不想影响其他时间的服务器性能，所以并没有选择修改连接的time_out等内容，而是优化的空闲连接的复用。

通过调整内核参数文件：

vi /etc/sysctl.conf

添加了以下两项设置：

# 表示开启重用。允许将TIME_WAIT sockets重新用于新的TCP连接，默认为0，表示关闭；

net.ipv4.tcp_tw_reuse = 1

# 表示开启TCP连接中TIME_WAIT sockets的快速回收，默认为0，表示关闭。
net.ipv4.tcp_tw_recycle = 1

最后通过执行 /sbin/sysctl -p 让参数生效。

更改了参数设置之后，再次执行脚本，没有再出现报错。

运行脚本期间，使用netstat 命令获取连接状态。发现本地依然瞬间生成大量连接，但是对获取到的信息进行比对，发现端口出现了重复。也就是没有释放掉的连接实现了复用。

虽然实现了连接的复用，但是瞬间生成几百几千个连接的样子，想想还是有点可怕。

另外：

前面测试时，数据库连接一直维持在10左右，表示每次同时只有几个线程在访问数据库。

即在脚本中分配多少条线程，就有多少线程在活动。并不是之前我想象的线程因为TIME_WAIT无止境的新建。

同时，数据库端应该已经Disconnect了之前的连接。

但本地为什么不释放连接是个迷。

由于通过gc()能将没用的资源给释放掉，所以还是怀疑R语言的实现存在问题。

可能是R语言包的RMySQL的dbDisconnect没有做好，也有可能是实现多线程的foreach和doParallel的资源释放没有做好。

另外，为什么本地会需要端口呢？如果用dbSendQuery去insert而不是dbGetQuery去insert不知道能不能避免这个问题。

即使能避免，也不是一个很好的方案。因为总会碰上需要多线程GetQuery的时候