Vertica INSERT speed highly depends on server load

insertvertica

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?

Best Answer

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.

Related Question