Slow Inserts

Moderator: NorbertKrupa

binface
Newbie
Newbie
Posts: 13
Joined: Fri Jun 15, 2012 2:40 pm

Slow Inserts

Post by binface » Fri Jun 29, 2012 10:16 am

Hi,

I'm getting really slow insert speeds via a jdbc connection. The insert seems to serialise access to the database no matter how many client threads there are.

Can this not be multi threaded?

If so, how?

Cheers

Rob.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Slow Inserts

Post by JimKnicely » Fri Jun 29, 2012 4:14 pm

Hi!

From my experience so far, adding records to a table via INSERT statements in Vertica is painfully slow. Are you trying to run a bunch of indivudual INSERT statements?

I followed the advice from the admin guide when trying to run a large number of INSERT statements as follows:
Using More Threads

If your database is receiving a large volume of data to load or if it is performing many DIRECT loads or inserts, you should consider allowing the Tuple Mover to perform more operations concurrently by increasing the TM resource pool until the it can keep up with the anticipated peak load rate. For example:
=> ALTER RESOURCE POOL tm MEMORYSIZE '4G' PLANNEDCONCURRENCY 4 MAXCONCURRENCY 5;
You can check your current settings via the v_catalog.resource_pools system table:

Code: Select all

SELECT memorysize, maxmemorysize, plannedconcurrency, maxconcurrency
  FROM v_catalog.resource_pools
 WHERE name = 'tm';
That didn't seem to help me, but maybe it could be of some benefit to you. In the end I always try to batch load everything (via the COPY command) and avoid any plain old inserts...

Also from the Programmer's Guide (page 130) regarding JDBC data loads:
Loading Batches Directly into ROS
When loading large batches of data (more than 100MB or so), you should load the data directly into ROS containers. Inserting directly into ROS is more efficient for large loads than AUTO mode, since it avoids overflowing the WOS and spilling the remainder of the batch to ROS. Otherwise, the Tuple Mover has to perform a moveout on the data in the WOS, while subsequent data is directly written into ROS containers. This results in the data from your batch being segmented across containers.
Are you bypassing the WOS as suggested?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

jpcavanaugh
Intermediate
Intermediate
Posts: 149
Joined: Mon Apr 30, 2012 10:04 pm
Location: New York
Contact:

Re: Slow Inserts

Post by jpcavanaugh » Wed Jul 04, 2012 6:36 am

Can you give more detail of how many threads you are using and how many records each is inserting?

Rick
Newbie
Newbie
Posts: 23
Joined: Thu Jul 26, 2012 2:46 pm

Re: Slow Inserts

Post by Rick » Tue Jul 31, 2012 2:28 pm

Use copy instead if you can. Much faster

khine
Beginner
Beginner
Posts: 33
Joined: Wed Apr 03, 2013 2:54 am

Re: Slow Inserts

Post by khine » Fri Apr 05, 2013 4:00 am

How can i load data with insert command ? Any example steps to have a look ?

User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

Re: Slow Inserts

Post by piglet » Fri Apr 05, 2013 3:58 pm

Use the INSERT command!

eli
Newbie
Newbie
Posts: 4
Joined: Wed Apr 10, 2013 11:48 am

Re: Slow Inserts

Post by eli » Thu Apr 11, 2013 9:04 am

Hi
I was able to have very good response time by doing jdbc batch insert (batchsize=1000) , my jdbc test program run 10 threads in parallel and insert the data to te same table , my response time was 20k per secound, i was able to get this response only after i change the movout setting to 20 .

Post Reply

Return to “Vertica Performance Tuning”