-1

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 result-set will generate a 10GB files.

Example row count:

MY_DB=> select count(*) from DATA_TABLE where ts_date ='2015-07-01';
  count
----------
 18213295
(1 row)

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

  • It would be helpful to know your environment (nodes, specs, network, etc.) – Kermit Oct 12 '15 at 1:30
  • 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=> – Yarden Bar Oct 13 '15 at 9:41
  • You're still not giving any information on the environment. Is there a connection timeout on the pool? – Kermit Oct 13 '15 at 13:46
  • How can I check for timeout? My cluster is composed of 4 nodes, 16-cores, 124GB memory, 4.3TB spin disks. Vertica 6.1, Centos 5.9 – Yarden Bar Oct 13 '15 at 17:24
  • Look at queuetimeout in resource_pools. – Kermit Oct 13 '15 at 18:14
1

Without knowing your environment, there are a couple things you can try. The first being adding an additional filter to make the output file smaller. The other is using a pipe viewer to monitor the progress.

If you're using the enterprise edition, you should report the issue to support.

0

Take a look at the RUNTIMECAP or QUEUETIMEOUT of the resource pool used for this query. I do hope you are doing this locally on the server and then copy them out of your server. Try to zip before moving it. See example:

vsql -U dbadmin -w dbadmin -F $'|' -At -c "SELECT * FROM table" | gzip -c > '/vertica/backup_table_one.gz'

You will save bandwidth and time as well.

  • Indeed I execute locally on the server – Yarden Bar Oct 15 '15 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.