Cannot Update / Slow Update

Moderator: NorbertKrupa

ncreekmur
Beginner
Beginner
Posts: 29
Joined: Tue Oct 23, 2012 3:56 pm

Re: Cannot Update / Slow Update

Post by ncreekmur » Thu Jan 10, 2013 3:46 am

Sharon,

Thanks for catching back up with us over here.

The main table does have a projection segmented on the join key (User_ID)... in addition to the super automatically created when we built the table using Create Table as select * from... However the temp table we're trying to update from does not have segmentation since we build it each night fresh and bulk copy data into it from raw files. After the insert of new records is complete and we update existing we drop this table. We could test creating a segmented projection on this temporary table but I am betting a re-design (which we're going to run tomorrow) will sort out the issue since we weren't experiencing this prior to recreating the table to change data types on the primary key.

I'm familiar with the basic under-workings of databases (its just Vertica that's messing with my brain) regarding Update statements... its just until I get a better grasp of what Vertica is doing 'under the hood' breaking the update action up into a singular insert and singular delete gives more predictable results.

I'll try to shy away from hand-creating projections until I get a bit more proficient with them. Its unfortunate that the 'incremental' option in Database Designer doesn't seem to remove existing projections, instead stacking them up. Knowing this in advance I can add an extra step to review the deploy sql and then the projections by table to keep them from piling up again. All of this being what it is the complete design tomorrow should fix us back up based on your comments.

The original table is gone. We were performing an 'illegitimate column conversion' per the manual and our process for most tables involved creating new columns keyed off the previous, then drop, then rename new column to old. However in some situations we were receiving errors around insufficient super projections. Many I could create a new super projection (easier than custom designing non super projections) and resolve the error... but some tables it was easier to recreate without the original column. This was one of the ones where we needed to recreate. As usual, time is of the essence and necessitated quick motion on the path of least resistance. All of this said we could create a copy of the table and put the primary key back to an integer field as we haven't started loading text values into the column yet... but with our data load process being impacted I don't have time to pursue it.

I appreciate all of your assistance.

Thanks,
Nick

ncreekmur
Beginner
Beginner
Posts: 29
Joined: Tue Oct 23, 2012 3:56 pm

Re: Cannot Update / Slow Update

Post by ncreekmur » Thu Jan 10, 2013 9:19 pm

Exciting Update:

We were 3 hours into the re-design. Database Designer errored out in the midst of creating, refreshing, analyzing, and dropping projections.

After finally getting the design session killed off... I went to re-start it and I received a message about projections needing to be dropped or refreshed. The names matched the design session I just ran. So as a neophyte in Vertica I checked the scripts created and found the deploy.sql file. I was able to skim thru it sequentially and find the create statements for the 4 projections and the proceeding refresh command (where we died).

Some days I have 2 brain cells to rub together and it seems like I can just finish the deployment manually with the SQL as long as I execute in order. Which is what I'm doing now.

Any problems with this plan? I could see it being an issue if the initial design hadn't been able to finish, but deployment is supposed to be able to be ran manually from my understanding.

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

Re: Cannot Update / Slow Update

Post by scutter » Thu Jan 10, 2013 9:45 pm

Sure - running the deployment manually is fine.

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

User avatar
janice
Intermediate
Intermediate
Posts: 51
Joined: Wed May 30, 2012 1:14 pm

Re: Cannot Update / Slow Update

Post by janice » Fri Jan 11, 2013 12:37 am

This whole thread has been a great learning experience (for me). I hope it all works out for you ncreekmur. Thanks for sharing!
Everyday is an adventure!

ncreekmur
Beginner
Beginner
Posts: 29
Joined: Tue Oct 23, 2012 3:56 pm

Re: Cannot Update / Slow Update

Post by ncreekmur » Fri Jan 11, 2013 1:28 am

Janice, to be honest the complete lack of easy googling for any Vertica content (complete lack is a stretch, but coming from SQL Server googling it seems like it) is why I keep coming back and updating the post. Just to help anyone who wants to read along.

Update:
Our largest table (Page Views) with nearly 3 billion records took 4 hours to refresh projections. Quite a hurdle.

I expect to be done by midnight with this manually and will come back and share results, which I think is often the most missing piece of Q&A online... people post a problem, get help... but no one ever confirms it was good or not.

ncreekmur
Beginner
Beginner
Posts: 29
Joined: Tue Oct 23, 2012 3:56 pm

Re: Cannot Update / Slow Update

Post by ncreekmur » Thu Jan 24, 2013 11:27 pm

The complete re-design has helped quite a bit. Data load window is back down to under an hour for 20 GBs of data files. For the interim I had to comment out the update records and just stack those up in a temp table for later actioning. Since then any query specific design sessions have not been automatically deployed. I've taken each output script and compared them to existing to decide if we want to add them... this way the designer isn't doubling up on projection columns.

I'll be re-approaching those updates in the next days and will update the thread again if they're functional. We're also entertaining just stacking all records up into more of a 'lookup' table with all instances, but that's quite a bit of storage.

Post Reply

Return to “Vertica Database Development”