INSERT speed highly depends on server load

Moderator: NorbertKrupa

Post Reply
andylens
Newbie
Newbie
Posts: 4
Joined: Sat Nov 14, 2015 12:57 pm

INSERT speed highly depends on server load

Post by andylens » Sat Nov 14, 2015 1:43 pm

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

Code: Select all

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 machine) 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?

scutter
Master
Master
Posts: 301
Joined: Tue Aug 07, 2012 2:15 am

Re: INSERT speed highly depends on server load

Post by scutter » Sat Nov 14, 2015 3:25 pm

One question is whether the INSERTs slowed down because they were queueing on the resource pools, or because system resources were truly pegged.

But you definitely don’t want to load large amounts of data using individual INSERT statements. Use COPY - the files don’t need to be local to the server. The client drivers can also automatically batch INSERT statements into COPY statements.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

andylens
Newbie
Newbie
Posts: 4
Joined: Sat Nov 14, 2015 12:57 pm

Re: INSERT speed highly depends on server load

Post by andylens » Sat Nov 14, 2015 7:50 pm

scutter wrote:One question is whether the INSERTs slowed down because they were queueing on the resource pools, or because system resources were truly pegged.

But you definitely don’t want to load large amounts of data using individual INSERT statements. Use COPY - the files don’t need to be local to the server. The client drivers can also automatically batch INSERT statements into COPY statements.

—Sharon
Thanks a lot for answer!

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: INSERT speed highly depends on server load

Post by NorbertKrupa » Sat Nov 14, 2015 10:47 pm

You also posted this on StackOverflow.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica Performance Tuning”