INSERT very slow

Moderator: NorbertKrupa

Post Reply
walito
Newbie
Newbie
Posts: 6
Joined: Wed Mar 13, 2013 7:53 pm

INSERT very slow

Post by walito » Tue Apr 22, 2014 4:15 pm

Hello people.
I'm working in a tool, I'm using mysql binlog to replicate into vertica in real time. I'm using python between mysql / vertica, but the INSERT statement is very slow, there are other way to accelerate the INSERT?

thanks in advance

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

Re: INSERT very slow

Post by JimKnicely » Tue Apr 22, 2014 4:47 pm

Have you tried adding a DIRECT hint? You'll write directly to the ROS skipping the WOS.

For example:

INSERT /*+ DIRECT */ INTO mytab ....
Jim Knicely

Image

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

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

Re: INSERT very slow

Post by scutter » Tue Apr 22, 2014 5:09 pm

We need more details. Is this a large INSERT..SELECT that is slow? Or are you inserting the data row by row with individual INSERT statements? If the latter, you should batch them using the faster COPY bulk load. Also if it’s individual INSERT statements, don’t add the direct hint, because then you’re likely to get “too many ROS containers”. Also a direct load is slower than a load to WOS since direct loads need to sort the data.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

walito
Newbie
Newbie
Posts: 6
Joined: Wed Mar 13, 2013 7:53 pm

Re: INSERT very slow

Post by walito » Tue Apr 22, 2014 6:58 pm

Yes, I'm inserting row by row with individual INSERT statement.
I receive the data from binlog (mysql) and then I insert into vertica, I'm using ODBC / python2.7

Could you help me?

Thanks

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: INSERT very slow

Post by id10t » Wed Apr 23, 2014 7:52 am

Hi!

[DELETED]
Last edited by id10t on Wed May 06, 2015 5:32 pm, edited 1 time in total.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: INSERT very slow

Post by nnani » Wed Apr 23, 2014 8:21 am

Hi Skwa,

I was not sure on the Pyvertica usage last year, but it seems there are many new features with this driver now.
Thanks for pointing us to this. :)

Surely going to try this.
nnani........
Long way to go

You can check out my blogs at vertica-howto

walito
Newbie
Newbie
Posts: 6
Joined: Wed Mar 13, 2013 7:53 pm

Re: INSERT very slow

Post by walito » Thu Apr 24, 2014 2:34 am

I'll try to test your example.

Thanks so much!!

Post Reply

Return to “Vertica Data Load”