1

I faced a problem with very slow INSERTs in Vertica DB. I made such test: create empty table with no projections and start inserting in it in batch, using

> INSERT INTO table
SELECT 111,222,333 UNION ALL
SELECT 444,555,666 UNION ALL
SELECT 777,888,999;

1000 rows in one query.

On idle server one query runs about 2sec and it's alright, but then I start generating some load making SELECTs (not very big load though, about 10 LA on 32 core server) and INSERTs start to performs 20x slower ( 40-60 sec each 1000 rows INSERT). It's too slow for my conditions. What can I do besides copying files to Vertica server and make COPY which seems to perform good, but too complicated?

migrated from stackoverflow.com Nov 14 '15 at 22:09

This question came from our site for professional and enthusiast programmers.

1

You shouldn't be surprised that a load takes longer when there's more resource usage. However, there's probably a simple explanation for your poor performance. I would examine the following:

  1. The INSERT with UNION ALLs is highly suspicious. Is the load more than 100MB? If yes, use COPY.
  2. Are the projections optimized for load?
  3. Is the query profiled and no locks being used on the target table?
  4. Most importantly, is the load local? From a client across a wide network? Test a load from a local file as a benchmark.

Your "1000 rows" isn't helpful because your row can be 10MB wide, or you could be loading JSON, loading LONG VARCHAR, etc. An actual load size is more valuable. On a typical DL380 Gen2, you should see around 350GB/HR/node.

  • Even if the load is under 100MB, I would still recommend using COPY. I think what @andylens just needs to know is that he can just use COPY LOCAL or COPY FROM STDIN. – woot Nov 17 '15 at 16:50
  • @woot I wouldn't use COPY for a single row. We'll have to agree to disagree. – Kermit Nov 17 '15 at 17:51

Your Answer

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