vsql connection timeout

[weikang]

Hi,

I am sending a query (insert statement) from a server to vertica cluster, however, the insertion spent long time and the connection got timeout.

The server is our data center while vertica cluster is in AWS.

Anyone has idea how to fix this issue?


Thanks.

[weikang]

Actually, the data files size is only 4.5G located in a mounted disk on node000001. Wondering why the load spent so long time; is there good practice to optimize the loading performance?

[JimKnicely]

Hi,

Can you split the big file into smaller more manageable 1G sized files and run parallel inserts? Are you using the /*+ direct */ hint on the insert statement?

Also, check out this link that might help with timeouts:

http://www.vertica-forums.com/viewtopic.php?f=33&t=1496

[weikang]

Thanks, knicely87.

I cannot split the data file any more, and I am using direct.

I have changed to /proc/sys/net/ipv4/tcp_keepalive_time to 1800, but the vsql still lose the connection.

The query eventually got executed and data got inserted, however, the process just got stuck and not continue with next insert statement.

[JimKnicely]

Hi,

Can you post the INSERT statement? How many projections are there on the table where the data is being inserted? Is it possible to use a COPY command as opposed to and INSERT?

Here is some advice a friend mentioned to me about your issue:

Probably Topic Starter uses in OS MustDie (windows) and antivirus causes to timeout.
Take a look here: http://twigstechtips.blogspot.co.il/201 ... erver.html

PS
Take in mind VSQL - is a poor clone of PSQL, actually PSQL can easily connect to Vertica, so what is true for PSQL also true for VSQL.

Also:

1. From my experience loading data from S3 gives a best performance
2. Can configure VPN connection between his machine and AWS and it will solve a problem with timeout too.

[weikang]

knicely87:

I am using COPY statement:

BEGIN;
COPY ${STAGING_SCHEMA}.${staging_table} FROM '${data_file}' ON ${VERTICA_NODE_1}
GZIP
DIRECT
NO COMMIT;
COMMIT;

[JimKnicely]

Maybe something in the article "Connecting from Outside of Amazon EC2—Firewall Timeout Issue" can help? You may have already tried its suggestions.

http://docs.aws.amazon.com/redshift/lat ... dance.html

...
Example issue:

Your client connection to the database appears to hang or timeout when running long queries, such as a COPY command.
...

...
To avoid these timeouts, we recommend the following changes to your local configuration:

Increase client system values that deal with TCP/IP timeouts. You should make these changes on the computer you are using to connect to your cluster. The timeout period should be adjusted for your client and network. See To change TCP/IP timeout settings.

Set the TCP/IP keep-alive behavior for your client by specifying an additional connection parameter. See To specify the TCP/IP keep alive setting in a connection string.
...