0

One of the application I work on generates reports using large volume of data returned from DB. The application receives the request as a AJAX async request, executes a query based on the input parameters, stores the data in JSON format, generates an excel with the same contents and then returns the JSON result back to the browser to display the records.

At times due to high volume of data the server crashes due to high memory usage and at times the server threads are hung. The exception captured in the logs is mentioned below :

[3/20/14 21:55:07:051 IST] 0000001a ThreadMonitor W   WSVR0605W: Thread "WebContainer : 6" (00000038) has been active for 761754 milliseconds and may be hung.  There is/are 1 thread(s) in total in the server that may be hung.
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
at com.ibm.db2.jcc.a.ab.b(ab.java:168)
at com.ibm.db2.jcc.a.ab.c(ab.java:222)
at com.ibm.db2.jcc.a.ab.c(ab.java:337)
at com.ibm.db2.jcc.a.ab.v(ab.java:1447)
at com.ibm.db2.jcc.a.db.a(db.java:42)
at com.ibm.db2.jcc.a.r.a(r.java:30)
at com.ibm.db2.jcc.a.sb.g(sb.java:152)
at com.ibm.db2.jcc.b.zc.n(zc.java:1186)
at com.ibm.db2.jcc.b.ad.db(ad.java:1761)
at com.ibm.db2.jcc.b.ad.d(ad.java:2203)
at com.ibm.db2.jcc.b.ad.U(ad.java:489)
at com.ibm.db2.jcc.b.ad.executeQuery(ad.java:472)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:1102)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:723)
at com.abc(ABCDao.java:1250)
at com.abc(ABCFacade.java:159)
at com.abcservlet.doPost(ABCServlet.java:56)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)

Environment details are as follows : 1. Application Server - WebSphere Application Server 7.0.0.29 2. DB Server - DB2 3. JVM Memory - 512MB - 2048 MB

Wanted to know what should be the best approach to handle these kind of scenarios. We use poi to generate the reports and we using "org.apache.poi.xssf.streaming.SXSSFWorkbook" to keep less data on the server. But if the data exceeds 200000 records the server thread gets hung.

ﾤ ﾡ
  • 5,669
  • 6
  • 43
  • 57
Debarshi DasGupta
  • 351
  • 2
  • 5
  • 21
  • Post some code showing how you're doing it. Sounds like you need to do incremental parsing. http://stackoverflow.com/questions/444380/is-there-a-streaming-api-for-json/823632#823632 – Renato Mar 20 '14 at 21:08
  • Ah, sorry, didn't understand the question at first. Looks like you run out of memory when you make a query which returns insane amounts of data... why don't you paginate the query? And build the json and xml incrementally? – Renato Mar 20 '14 at 21:11
  • @Renato - thanks for the reply. Yes loading the data incrementally is a good option using scrollable resultset. But my application also provides an option to export the entire report as a .xlsx file and if the entire content is not available I can not generate the report. Is their any other option you have in mind ? – Debarshi DasGupta Mar 20 '14 at 21:40
  • Then you must limit the number of concurrent reports you can generate and make sure you clean up well (do not keep references to anything you don't need after you're done with the report)... other than that, not much that can be done, I'm afraid. Of course, the size of the report you can generate will be always limited by how much memory you have available unless you can not only load the data incrementally, but also generate the xlsx file "incrementally" if that's even possible. – Renato Mar 20 '14 at 21:49
  • Hmmm, you should be able to write the file on-the-fly (in Windows, this tends to manifest as files having "unknown size" in download dialogs). So you'd use a scrollable result-set as normal, etc. However, I'm not sure how to do this (and you might have an easier time writing out a `.csv`, which would be more portable regardless). You might also be able to write the file to the server first using the same technique. Although personally, I'd be more worried about the fact that you're giving somebody such a large portion of your data... – Clockwork-Muse Mar 21 '14 at 13:58
  • @Renato/@Clockwork-Muse- I really appreciate your suggestions.I might be making this discussion pretty broad and forgive me for that. I have following scenarios in mind : a. The DB processing time might result in some timeout (firewall is not in picture for me) - refer to http://www-01.ibm.com/support/docview.wss?uid=swg21590014 b. The "ServerIOTimeout" in WebSphere Application Server / IHS server plugin-cfg.xml might be the cause of timeout and causes the hung thread. – Debarshi DasGupta Mar 21 '14 at 18:45
  • http://javaeesupportpatterns.blogspot.in/2011/04/javanetsocketinputstreamsocketread0.html -- this article also points to a similar cause, I think I should use some network sniffers to understand the real problem. – Debarshi DasGupta Mar 21 '14 at 19:02

0 Answers0