Report Performance

fccobb

Hi

I posted a question about this on the Designing Forum but I guess this may be a better place to see if someone can help.

We have a report that goes straight to PDF or Word missing out the viewer. It works just fine in the designer and produces what we want in a reasonable time.

I have deployed it to a Ubuntu 9.04, Tomcat 6 server with MySQL and the reports work but both take 3 or 4 minutes to run and I was wondering if anyone could help me improve this.

There seems to be something strange happening as once or twice the report has run in 35 seconds which would be great but then it goes back to 3 or 4 minutes again. I thought that this was due to setting up database replication but I have now seen that this has nothing to do with it.

I have tried to adjust the JAVA_OPTS settings but that seems to make no difference which Tomcat only seeming to want 300 MBytes of memory despite the fact there is more available if it wants it.

I have set up connection pooling in Tomcat and tested the data source with Lambda Probe and it works just fine.

I have tried both the Java Open JDK and the Sun one and there is no difference.

I have looked at the logs and I get the following message over and over:

Dec 5, 2009 9:24:00 AM org.eclipse.birt.data.engine.odaconsumer.Connection setOdaQuerySpec
INFO: Ignoring UnsupportedOperationException thrown by ODA driver (org.eclipse.birt.report.data.oda.jdbc); cannot set query specification before prepare.

along with this occasionally:


** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: The last packet successfully received from the server was127855 milliseconds ago.The last packet sent successfully to the server was 127855 milliseconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

STACKTRACE:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was127855 milliseconds ago.The last packet sent successfully to the server was 127855 milliseconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3270)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1932)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1021)
at com.mysql.jdbc.PreparedStatement.getMetaData(PreparedStatement.java:2497)
at org.apache.commons.dbcp.DelegatingPreparedStatement.getMetaData(DelegatingPreparedStatement.java:190)
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingPolicy0(Statement.java:368)
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:284)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:408)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:376)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:419)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:389)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.addColumnHint(PreparedStatement.java:1052)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.addColumnHints(DataSourceQuery.java:628)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepareColumns(DataSourceQuery.java:348)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:308)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:345)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:331)
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:437)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:189)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:177)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:143)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:476)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:117)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:253)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1825)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
at org.eclipse.birt.report.engine.executor.ListItemExecutor.execute(ListItemExecutor.java:66)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:64)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:167)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:75)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:882)
at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:973)
at org.eclipse.birt.report.service.actionhandler.BirtRunAndRenderActionHandler.__execute(BirtRunAndRenderActionHandler.java:76)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.presentation.aggregation.layout.RunFragment.doService(RunFragment.java:120)
at org.eclipse.birt.report.presentation.aggregation.layout.FramesetFragment.service(FramesetFragment.java:86)
at org.eclipse.birt.report.servlet.ViewerServlet.__doGet(ViewerServlet.java:181)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doGet(BirtSoapMessageDispatcherServlet.java:160)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3251)
... 64 more


** END NESTED EXCEPTION **

Does anyone have any ideas please - I am a bit lost what to do next.

Thanks very very much if you can help.

Chris

alex520

use "show variables ;" in your mysql command shell, at bottom of page there is a wait_timeout option, you can set it a large value by "set wait_timeout = 300000;" see if that works