AWS JS report hanging there when bigger resulted dataset

[171334125]

We have a lot details on environment setup and the whole story, but in order not to be distracting, I think the real issue can be reflected as:

For one same report - Top N Application, in the case of N = 10 to show the top 10 records, it run properly, which proved Report and database connection were good; but in the case of N = 100, the report kept spinning there without return, from database perspective, it was confirmed that the database already returned the result dataset back. At same time, we saw the error exception in JS log - org.springframework.webflow.conversation.impl.LockTimeoutException: Unable to acquire conversation lock after 180 seconds 

Besides, the original found was: we had some reports running properly, but some can not; laterly, we found this factor - the size of resultset, the bigger resultset, the more likely Report is hanging there.

 

 

[alampitt]

1) What are the performance results when running the query outside of Jaspersoft?

2) What database technology are you using?

3) Anything you can share about the query complexity and relevant schema?

 

[brian.fleming]

We're using JDBC drivers with MYSQL.  I took out all the complexity in our query and am running a simple select statement with a inner join. Nothing fancy at all, no calculations or anything.  Our fields from our query are numeric values that are very large. For example 14719606800.00.  For the hell of it, I changed the query so these large values are all 0s in the table result.  I was then able to increase the number of rows I could receive.  Could there be some kind of transfer limit somewhere? Maybe it isn't the number of rows, so much as the amount of data that has to be returned.  Just one idea we had.

[marianol]

That is strange indeed. Where is your MYSQL Database RDS? if you check you MYSQL log does the query time and fetch time look normal? 

Jaspersoft comes with the MariaDB JDBC driver that works with MySQL but you may try using the Native MySQL J/Connect JDBC Drivert to see if this helps (i.e. is a driver issue) you can find information on how to install the native driver here http://community.jaspersoft.com/wiki/datasources

You can also set a middle man to log how the driver is behaving and what result sets are sent, check this page http://community.jaspersoft.com/wiki/determine-timing-sql-queries-log4jdbc it uses Log4JDBC as a middle man to log the queries beeing sent to the DB form the report 

[brian.fleming]

I'm kind of surprised this hasn't been reported before. The Jasper Reports instance runs on an amazon linux OS, and I finally got the right information.  The MTU setting on the network interface defaults to jumbo frames (9001).  It needs to be set to 1500, OR in my case, it only worked if I set it to 1000.

 

http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-drop-issues.html

 

 

[171334125]

It was solved by decrease MTU from 9000 to 1300.