Vertica v6.1.0-0: server closed the connection unexpectedly

yardenbar

Hi All,

I'm writing a script to export data from our vertica cluster.

The command I'm using is:

 

vsql MY_DB_NAME -At -F$'\t' -o /data/vertica_archive/MY_DATA_2015_07_01.tsv  -c "select * from DATA_TABLE where ts_date ='2015-07-01';"

Sporadically resulting with:

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
connection to server was lost

 

All of our queries are simple "select * from TABLE where ts_date=DATE", but some of the queries resultset will generate a 10GB files.

Is there any exit code documentation or can any one suggest how to debug?

 

yardenbar

Update:

opening vsql and leaving it open for some time also drops the connection:

MY_DB=> EXPLAIN SELECT * FROM NODES;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
MY_DB=>

 

Adrian_Oprea_1

 Connect to your Vertica Db nd run the following :

 

SHOW RUNTIMECAP;

- post the output.

 

yardenbar

Hi Adrian,

the result of your query is:

MY_DB=> SHOW RUNTIMECAP;
    name    |  setting  
------------+-----------
 runtimecap | UNLIMITED
(1 row)

Yarden

Adrian_Oprea_1

 I guess you are nt locally connected .

So there must be some network timeout or some local host session timeout.

yardenbar

I execute the vsql command locally.

 

The step that I take:

1. ssh myUser@myDbHost
2. vsql mydb -c "SELECT * FROM my_table WHERE tsdata='2014-08-11' ;"

 

Adrian_Oprea_1

 Can you check your pool definition of Runtime Cap and also look for any lock on that table while running the query , if any locks will be present then the Queue timeout will be applyed. 

Also check your  table error_messages table. (this might give you an idea of why your session dies)

select * from error_messages where 
user_name = 'your user' 
order by event_timestamp desc 

 

Another thing you might wanna look into is the tcp_keepalive_time Linux parameter.  Maybe this is causing the ssh connection ot be cut off. Lower the default value of it of 7200 to maybe something like 900. 

example:

echo 900> /proc/sys/net/ipv4/tcp_keepalive_time