Lowering the Required Resource for Vsql loads

moiz_arafat

We are trying to load 5 Records into the system (There is no other process running). We are getting the below error : Insufficient resources to execute plan on pool general [Request Too Large:Memory(KB) Exceeded: Requested = 13661975, Free = 6426485 (Limit = 6426485, Used = 0)] Not sure why it will need 13G for loading. Is there any way to adjust this value .

Sharon_Cutter

You're loading the 5 records using COPY? INSERT? INSERT..SELECT? Do you have many projections defined for the target table? --Sharon

[Deleted User]

Hi Moiz, Could you post the exact COPY command that you are running? It is certainly unusual for COPY to want to use that much memory. It can happen if you have very wide rows (or have mis-typed the record terminator), if you are using your own Load function (written against our UDL API) that is very memory-intensive, or under various other less-common situations. Adam

moiz_arafat

Hi Adam, The file is tab delimited and have two projections . I have tried loading through Hadoop Pig Connector as well as Vsql Copy . Get the same error. Yes I have close to 250 columns but we have a different environment where the same code works fine. cat filename | vsql -a -h host -U user -w pass -c "COPY table name ( col1 , col2, . . . coln ) FROM LOCAL stdin direct delimiter ' ' EXCEPTIONS 'except.log' REJECTED DATA 'rejects.log' ;" Thanks, Moiz

[Deleted User]

Hi Moiz (and Sharon -- feel free to jump in too!), Out of curiosity, does this COPY statement work properly if you change it from "LOCAL stdin" to just plain "stdin"? There is a known issue with certain versions of COPY LOCAL using too much memory in some situations; I'm curious whether this is that. Beyond that, it does sound like Sharon is on the right track for other things to look at; if you could answer some of her questions, that'd be helpful too. Adam

moiz_arafat

plain Stdin does not work too . I am thinking is this an issue with 6.1.2 have used the same command from previous version like 6.0 and worked fine in the past. Sharon, I am using COPY command to load and I only have 2 projections . Thanks,

Sharon_Cutter

Hi Moiz, I tried a simple test case on 6.1.2 like what you're describing - 250 columns, and I tried with all varchar(1024) columns. The only time that I could trigger an insufficient resources was when I added projections. What do you get as projection targets if you EXPLAIN your COPY statement in vsql? \o | grep DT EXPLAIN … \o --Sharon

moiz_arafat

Hi Sharon, Not sure if this is what I should have looked for,but I can see the projections mentioned in the explain graph [rankdir=BT, label = " BASE BULKLOAD PLAN \nAll Nodes Vector: \n\n node[0]=node0101 (initiator) Up\n node[1]=node0001 (executor) Up\n node[2]=node0002 (executor) Up\n node[3]=node0003 (executor) Up\n node[4]=node0004 (executor) Up\n . . . 255[label = "DT(DIRECT)[P_Table_SEG_0]\n+Sort (keys = Ax2,Nx248) \nOutBlk=[UncTuple]", color = "blue", shape = "ellipse"]; 259[label = "DT(DIRECT)[P_Table_SEG_3]\n+Sort (keys = Ax2,Nx248) \nOutBlk=[UncTuple]", color = "blue", shape = "ellipse"]; 260[label = "Segment() \nOutBlk=[UncTuple(250)]", color = "green", shape = "ellipse"]; 261[label = "ValExpNode \nOutBlk=[UncTuple(250)]", color = "green", shape = "ellipse"]; 262[label = "DIST-Union (#cols = 250,#strms = 1) \nOutBlk=[UncTuple(250)]", color = "green", shape = "ellipse"]; 263[label = "Basic-Union (#cols = 1,#strms = 2) \nOutBlk=[UncTuple]", color = "green", shape = "ellipse"]; 264[label = "GroupBy(NOTHING) \nOutBlk=[UncTuple]", color = "green", shape = "box"]; Thanks, Moiz

Sharon_Cutter

Hi Moiz, The only other suggestion I have is to try the TRICKLE keyword instead of DIRECT just to see if the high memory requirements persist. DIRECT allocates memory for a sort, whereas TRICKLE won't. This would just be an experiment, not a recommendation for long-term use. It seems like you've hit a product issue and should open a support case. --Sharon

moiz_arafat

Thanks Sharon for the help . I tried trickle got the same error there Insufficient resources to execute plan on pool general [Request Too Large:Memory(KB) Exceeded: Requested = 12378903, Free = 6389621 (Limit = 6389621, Used = 0)] I will open a case with Vertica . Thanks, Moiz