Tableau client not able to get batches from DV

[sanjay_chaturvedi]

HI,

Scenario is that we are connecting teiid dv layer from tableau client and making a simple select. That select is heavy and expected to serve around 10 millions of rows.

What we have been struggling for some time is that our query is resulting error with following message:

TEIID31261 Max estimated size 53,219,612,589 for a single operation/table id 120 has been exceeded.  The server may need to increase the amount of disk or memory available, or decrease the number of max active plans. Sending error to client 1mqNEk34vl2U.8

We have already decreased max-active plan to 1, still this error is there. We have increased allocated heap, that caused thaaat failure a bit delayed but not resolved completely.

In teiid-command.log, what I observed that bufferspace size is keep on increasing, take a look of series of logs:

Obtained results from connector, current row count: 512

btained results from connector, current row count: 1024

Obtained results from connector, current row count: 1536

Obtained results from connector, current row count: 2048

.........................................................and so on then finally:

Obtained results from connector, current row count: 9791488

Error:

Exceeding buffer limit since there are pending active plans or this is using the calling thread.

My assumption is that if client is keep on fetching based on fetch size, this current row count should come as always what the connector or processor batch size is there. So current row count should always be like 1024,1024 and so on. What  am thinking is that possibly connector results in batches is not being served to client in batches. and causing this buffer to be exhausted. Correct me if I am wrong.

processor batch size: 512

Also in tableau, we could not found some setting like fetch-size or batch-size, was under impression if that size could be set to 0 then possibly whatever batch will come from DV, will get transferred.

Please confirm if my understanding is right, in case it is wrong, what could be the cause and resolution. please assist. Thanks.

[rareddy]

Are you using JDBC or ODBC from Tableau? I suspect Tableau is issuing a query that is cursorable where the server is keeping all the data and exhausting the disk. I am not sure how to fix that.

[shawkins]

> Correct me if I am wrong.

That depends on the processing plan and client cursoring.  If this is a forward only query, and no post processing is being performed that requires buffering, then yes the server will only proactively fetch a limited buffer from which the client will draw from such that the total rows ever held by Teiid would be minimal.

Can you confirm what type of cursoring the client is requesting (forward only vs. scrollable) and if the processing plan contains a top level sort or similar operation processed in Teiid that would require buffering?

> Also in tableau, we could not found some setting like fetch-size or batch-size, was under impression if that size could be set to 0 then possibly whatever batch will come from DV, will get transferred.

Yes 0 is interpreted as the default - the JDBC client will request up to 2048 rows.  However the server is not obligated to deliver that many rows.  It will typically deliver between 1 and 3 batches based upon the working batch size of query output - where the working batch size is adjusted up or down from the processor batch size depending upon how wide each data row is.

> TEIID31261 Max estimated size 53,219,612,589 for a single operation/table id 120 has been exceeded

That seems quite large even for 10 million rows.  Is there a lot of string or other wide columns?

[sanjay_chaturvedi]

Hello Ramesh,

Tableau is using ODBC.

[sanjay_chaturvedi]

Hi Stevan,

This is what we have in  ODBC settings:

Is there a lot of string or other wide columns?

Yes, there is lot of string and wide columns, but the size it generally serves form source is around 12gb. That buffer space limit reached upto that limit when we tested it by increasing max-buffer-space to 100Gb. Prior to that it was failing in less time.

Please assist, If something could be extracted from this piece of information. Will update on what type of cursoring is being used later.

Thanks,

Sanjay

[shawkins]

> Please assist, If something could be extracted from this piece of information. Will update on what type of cursoring is being used later.

When using declare/fetch it is up the the client flags passed in as to whether the cursor will be scrollable or not.  If you turn on trace logging for org.teiid.ODBC you can see the DECLARE statement for the cursor that the ODBC driver is creating.  There may be an option to use only forward only cursoring in tableau.

> Yes, there is lot of string and wide columns, but the size it generally serves form source is around 12gb.

It seems like the Teiid estimate has large variation from the actual size - the estimate here is actually derived from the heap estimation logic and not from the actual serialized bytes so that it can be enforced without serialization.  So in your case it ends up being very pessimistic about what constitutes runaway memory usage.  It may be good to capture on issue on this to relax the enforcement or just give a flag to turn it off.