OCI8.__execute hangs after > 1 hour #159
Comments
|
To add to this, I am reusing a connection object from the pre-idle queries to reconnect/test with a simple query before trusting that the connection is valid. However, the simple test query also fails. FYI, this is using the Sequel connection validator extension. |
|
Could you get C-level backtrace of the hung process? If you use Linux and can use gdb on the machine, $ ps -ef | grep ruby # get the process ID of the hung process.
$ sudo gdb /proc/PID/exe PID # replace PID with the process ID of the hung process. This may work without `sudo' depending on the machine setting.
gdb> info threads # get the list of native threads
gdb> thread 1 # change to thread 1
gdb> where # get backtrace
gdb> thread 2 # change to thread 2
gdb> where # get backtrace
... |
|
Not easily as I'm either on Heroku with no access to the dyno directly, or AWS running passenger under nginx. I'll see if I can replicate a test case locally instead and let you know. |
With the above failure locally, I see this from |
|
Thanks and sorry. It didn't provide usable information. By the way, is there a firewall between Oracle sever and client? If so, the firewall may drop inactive connections. It may be solved by setting (ENABLE = BROKEN) and configuring TCP keepalive settings in the OS. If keepalive packets are sent within inactive connection timeout, the firewall doesn't drop the connection. |
|
OK, I'll try and see if I can get more useful stack trace. I'll also review the firewall setup as that is also a possible solution. Thanks for support. |
|
As far as I checked, when According this linux manual page keep-alive packets are sent only when SO_KEEPALIVE is enabled. The default TCP keepalive time is 2 hours and it is configurable via procfs or sysctl. |
|
I tried the On AWS, we are now running the |
|
Edited: The first line of commands was fixed as @yahonda pointed. Could you use the latest git revision and set new OCI8.properties parameters? OCI8.properties[:tcp_keepalive] = true
OCI8.properties[:tcp_keepalive_time] = 480
# OCI8.properties[:tcp_keepalive_intvl] = ...
# OCI8.properties[:tcp_keepalive_probes] = ...When The other three parameters Note: The three parameters are available only on Linux. |
|
I'm interested in supporting |
|
@yahonda Thanks! I fixed it. |
|
@kubo That looks very interesting; I'll give that a try ASAP. Would you also consider supporting macOS too? |
|
macOS after Mountain Lion could be supported according to this archive. I'll try to support it this weekend. Well, this feature is implemented with hackish way. When |
|
Well, the good news is that this solves my firewall issues completely on Heroku! I've yet to test it, however, on the AWS setup but I'll let you know how it runs on there too. Thanks for all your help in getting this far. macOS support would be appreciated so we can run this in development mode too. |
|
And, having soak-tested this overnight on AWS, it appears to be working really well there too. Marvellous stuff! |
|
@mrship Edited: Linux and macOS could customize them. |
|
We went with this setup: |
…ies. Support :tcp_keepalive_time on macOS, Windows x86/x64 and Solaris x86_64 in addition to Linux i386/x86_64. #159
|
I pushed 0ffd0c8.
IMO, it will be supported on the following platforms if plthook supports them. They seem to have the
The next ruby-oci8 version including this feature will be released after rubyinstaller.org releases ruby 2.4.0 on Windows. |
|
I'm interested in this feature and considering to set some default value It does not look like RubyInstaller 2.4.0 released soon oneclick/rubyinstaller#350 . Would you consider to release newer version of ruby-oci8 without waiting for RubyInstaller 2.4.0, or could you tell me the reason. Thanks. |
|
Extension libraries made for ruby 2.x don't work with ruby 2.y. So I need to put pre-compiled binary files for each ruby version in a binary gem. For example, ruby-oci8-2.2.3-x64-mingw32.gem includes oci8lib_200.so, oci8lib_210.so, oci8lib_220.so, oci8lib_230.so and oci8lib_240.so for ruby 2.0, 2.1, 2.2, 2.3 and 2.4. |
|
Thanks for the explanation. Hope RubyInstaller 2.4.0 will be available soon. |
I am using OCI8 with Sequel and experiencing an issue with connections hanging in
__executeafter an idle period of ~ 1 hour.I'm running on Heroku that has a 30 seconds limit on requests, so the stack trace below is from using rack-timeout to force an error and stack trace after 15s. However, this also happens on AWS where there is no timeout, the connection just hangs indefinitely.
From what I can see on the Oracle, we get a reconnect (we see a login from Heroku after the hour idle) but the new connection then just times out. The only recourse is to restart Heroku dynos (or nginx on AWS) to get a "fresh" connection that doesn't time out. However, if we have another idle period of ~ 1 hour then this fresh connection suffers the same problem.
I have tried setting the various connection timeouts, but with no difference.
Can you advise on how I can further diagnose/fix? Thanks.
The text was updated successfully, but these errors were encountered: