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.
Slow Inserts
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Slow Inserts
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:
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:
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:
You can check your current settings via the v_catalog.resource_pools system table: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;
Code: Select all
SELECT memorysize, maxmemorysize, plannedconcurrency, maxconcurrency
FROM v_catalog.resource_pools
WHERE name = 'tm';
Also from the Programmer's Guide (page 130) regarding JDBC data loads:
Are you bypassing the WOS as suggested?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.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Intermediate
- Posts: 149
- Joined: Mon Apr 30, 2012 10:04 pm
- Location: New York
- Contact:
Re: Slow Inserts
Can you give more detail of how many threads you are using and how many records each is inserting?
Re: Slow Inserts
Use copy instead if you can. Much faster
Re: Slow Inserts
How can i load data with insert command ? Any example steps to have a look ?
Re: Slow Inserts
Use the INSERT command!
Re: Slow Inserts
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 .
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 .