how to set connection to vertica keep alive

Rajashekar_1

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?

[Deleted User]

Hi Rajashekar,

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

Adrian_Oprea_1

 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.

Rajashekar_1

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.