Skip to main content

how to set connection to vertica keep alive

  • January 29, 2015
  • 3 replies
  • 22 views
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?

    3 replies

    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • January 29, 2015
    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
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • March 16, 2015

     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
    • Rajashekar_1
    • Author
    • Participating Frequently
    • April 21, 2015

    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.

     

     

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement