Options
how to set connection to vertica keep alive
I am connecting to Vertica via vsql client from my linux machine. After some idle time connection to Vertica I had from vsql client was timedout. Im trying to run the query which executes around 30mins, But after 6 to 8 mins im getting connection time out issue....
When I check by executing below query it still executing in background
SELECT * FROM QUERY_PROFILES WHERE IS_EXECUTING='true'..
Is there anyway to keep vertica connection alive?
When I check by executing below query it still executing in background
SELECT * FROM QUERY_PROFILES WHERE IS_EXECUTING='true'..
Is there anyway to keep vertica connection alive?
0
Comments
It sounds like your default keepalive settings are set pretty high and a firewall may be closing the connection since it sees that it is idle. You can easily prevent this timeout by adjusting your keepalived settings in linux.
You can check what your current settings are using the following commands:
# cat /proc/sys/net/ipv4/tcp_keepalive_time
7200
# cat /proc/sys/net/ipv4/tcp_keepalive_intvl
75
# cat /proc/sys/net/ipv4/tcp_keepalive_probes
9
The first two values here are in seconds and represent time passed before sending a keepalive packet and the interval between probes once keepalive has has begun. The last value is a number and is the count of the number of times in a row it will fail to receive a response to an ACK before breaking the connection.
You can adjust these values temporarily using these commands. The lower the first value is the sooner we will start sending keepalive packets to keep the connection active:
# echo 600 > /proc/sys/net/ipv4/tcp_keepalive_time
# echo 60 > /proc/sys/net/ipv4/tcp_keepalive_intvl
# echo 20 > /proc/sys/net/ipv4/tcp_keepalive_probes
Note that these will not persist through a restart. You may want to consider setting these to run on startup.
Source: http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/usingkeepalive.html
Let me know if you have any questions!
- Mitch
Have you tried using the set "SET SESSION RUNTIMECAP" to high value ?
See link here for more details
What i find interesting is that if you use default resource pool settings the runtimecap is set "NONE", meaning that there is no timeout set.
Maybe is you client that closes the session.
Also is a good idea to take a look at you RESOURCE_REJECTIONS table and see the reson your session was closed.
Thank you for your reply. Sorry for the late response
we have three nodes with one load balancer
10.0.1.x1 node1
10.0.1.x2 node2
10.0.1.x3 node3
10.0.1.x4 load balancer
when i connect to 10.0.1.x1 node through vsql client my connection is always be in keep alive
but ,when i connect to load balancer(10.0.1.x4) through vsql client,my connection getting lost after 5 to 6 mins.
In which machine should i change the below values?
#echo 600 > /proc/sys/net/ipv4/tcp_keepalive_time
# echo 60 > /proc/sys/net/ipv4/tcp_keepalive_intvl
# echo 20 > /proc/sys/net/ipv4/tcp_keepalive_probes.