Memory issues after upgrading to Vertica 7.1.0-1

Moderator: NorbertKrupa

Post Reply
suework
Newbie
Newbie
Posts: 3
Joined: Fri Sep 19, 2014 3:35 pm

Memory issues after upgrading to Vertica 7.1.0-1

Post by suework » Fri Sep 19, 2014 8:09 pm

We recently upgraded our Vertica database server from 6.4 to Vertica Analytic Database v7.1.0-1.

We use Talend, and since the upgrade, we have had issues with Talend jobs failing on our Vertica server due to memory issues.

The most reliable repro is a daily job - which previously always ran successfully, in less than 4 minutes - and now it times out and dies.

The error on the db logs:

Code: Select all

2014-09-19 10:56:15.533 Init Session:0x2b3e70013220-a000000742bfd5 @v_pdw_node0001: 53000/3587: Insufficient resources to execute plan on pool general [Timedout waiting for resource request: Request exceeds limits: Memory(KB) Exceeded: Requested = 4288595, Free = 118927 (Limit = 19981816, Used = 19862889) (queueing threshold)] 
LOCATION: Exec_compilePlan, /scratch_a/release/vbuild/vertica/Dist/Dist.cpp:1217 
2014-09-19 10:56:16.263 Init Session:0x2b3e701a4390-a000000742bfdf [Txn] Starting Commit: Txn: a000000742bfdf 'select atl.id, atl.APP_DOMAIN, atl.CREATE_DATE, atl.TRANS_ID, atl.SOURCE_PARTNER, atl.LAST_NAME, atl.FIRST_NAME, bcf.LP_ID LOYALTY_PROFILE_ID, atl.MEMBER_ID, bcf.TX_ID ORDER_ID, atl.AMOUNT, atl.BATCH_ID, atl.BATCH_KEY from"STAGING".JPMC_ATL_VW atl left outer join "STAGING".JPMC_BCF_VW bcf on atl.APP_DOMAIN = bcf.APP_DOMAIN and atl.TRANS_ID = bcf.MINOR_KEY where atl.BATCH_KEY > 12303 or bcf.BATCH_KEY > 12303 ;' 
Some interesting notes:
  • We have a single node with 28GB RAM
  • the identical job runs on our Staging Vertica server (which is also Vertica v7.1.0-1 and is the same as production in terms of the volume of data, single node with less memory) – with no problems
  • Talend version is 5.4.1
Things that we have tried that have not made any difference:
  • Increase the queuetimeout from 600 to 900 to 1200 of the general resource pool. Performance only degraded.
  • Increase maximum memory maps configuration of the db server
  • Modified plannedconcurrency to AUTO
  • Created new projections for the tables the query is using.
Our questions –
  • has anyone seen these sort of memory issues come up after upgrading to Vertica v7.1.0?
  • how can we resolve this? what else should we try?
Thanks in advance for your help.

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

Re: Memory issues after upgrading to Vertica 7.1.0-1

Post by NorbertKrupa » Fri Sep 19, 2014 9:10 pm

This sounds like something that you should reach out to verticahelp@vertica.com for.
Checkout vertica.tips for more Vertica resources.

suework
Newbie
Newbie
Posts: 3
Joined: Fri Sep 19, 2014 3:35 pm

Re: Memory issues after upgrading to Vertica 7.1.0-1

Post by suework » Fri Sep 19, 2014 10:19 pm

Indeed. So far verticahelp has not been helpful.

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

Re: Memory issues after upgrading to Vertica 7.1.0-1

Post by scutter » Sat Sep 20, 2014 2:07 am

Hi Sue,

I saw the discussion over on the vertica community as well. This specific query is timing out because it can’t get memory, and the discussion over in the community showed that 19G was in use in the general pool when this query was running (queued) - and a single query that’s running. So what’s using the 19G from the general pool? In Vertica once a query starts executing, if it needs additional memory (such as to build a hash table for a join) if it can’t get it immediately, it will outright fail not queue. So I’m thinking that some other query is holding that 19G. So check the currently acquired resources when the talend query is executing.

select * from resource_acquisitions where is_executing;

Or check the vertica.log - when a query times out there will be a dump of all queries that were running or queued when the timeout occurred.

What query does that show? Is it a recently-started query - from talend or another user? And what’s the explain plan for it?

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

suework
Newbie
Newbie
Posts: 3
Joined: Fri Sep 19, 2014 3:35 pm

Re: Memory issues after upgrading to Vertica 7.1.0-1

Post by suework » Tue Sep 23, 2014 6:15 pm

Hi Sharon,

Thanks for your post, it was helpful. We ended up adding an additional projection to help with updates, and that seems to have done the trick.

We still don't know if the start of the issue was just the (slow) growing size of the database crept over some boundary, or if it was related to the upgrade to Vertica 7.1, but in either case, we are back up and working now.

Sue

Post Reply

Return to “Vertica Error Codes”