Form runner takes 5 mins to load the summary page

[Nupur Ghatnekar]

We are using Orbeon 4.5 CE with MySQL as the database.

During the past week, we accumulated 3821 records in our orbeon_form_data table. Ever since then, the summary page in form runner is taking a long time to render. Also the individual form, takes time to render.

Logs when the summary page is fetched :

2014-09-16 10:48:24,400 INFO  ProcessorService  - /fr/service/mysql/search/testApp1/testForm1- Timing: 549124

2014-09-16 10:48:24,401 INFO  ProcessorService  - /fr/service/persistence/search/testApp1/testForm1- Timing: 549167

2014-09-16 10:48:24,402 INFO  DatabaseContext  - Committing JDBC connection for datasource: jdbc/mysql.

2014-09-16 10:48:24,464 WARN  XFormsServer  - xxf:instance() - instance not found {instance id: "fr-form-metadata"}

2014-09-16 10:48:24,465 WARN  XFormsServer  - xxf:instance() - instance not found {instance id: "fr-form-metadata"}

2014-09-16 10:48:24,718 WARN  XFormsServer  - xxf:instance() - instance not found {instance id: "fr-form-metadata"}

2014-09-16 10:48:24,732 WARN  XFormsServer  - xxf:instance() - instance not found {instance id: "fr-form-metadata"}

2014-09-16 10:48:24,732 WARN  XFormsServer  - xxf:instance() - instance not found {instance id: "fr-form-metadata"}

2014-09-16 10:48:24,858 WARN  XFormsServer  - xxf:instance() - instance not found {instance id: "fr-form-metadata"}

2014-09-16 10:48:24,859 WARN  XFormsServer  - xxf:instance() - instance not found {instance id: "fr-form-metadata"}

2014-09-16 10:48:25,383 INFO  ProcessorService  - /fr/testApp1/testForm1/summary - Timing: 551066

Logs when a particular instance of the form is fetched :

2014-09-16 11:09:45,981 INFO  ProcessorService  - /fr/testApp1/testForm1/edit/d3448d95a24a3ab147f0383ed4e49f2b101f551dae7a9e7776182dab993cc03d - Received request

2014-09-16 11:09:46,146 INFO  ProcessorService  - /fr/service/persistence/crud/testApp1/testForm1/form/form.xhtml - Received request

2014-09-16 11:09:46,180 INFO  ProcessorService  - /fr/service/mysql/crud/testApp1/testForm1/form/form.xhtml - Received request

2014-09-16 11:09:46,369 INFO  ProcessorService  - /xforms-server - Received request

2014-09-16 11:09:46,385 INFO  ProcessorService  - /xforms-server - Timing: 16

2014-09-16 11:13:18,955 INFO  ProcessorService  - /fr/service/mysql/crud/testApp1/testForm1/form/form.xhtml - Timing: 212775

2014-09-16 11:13:18,955 INFO  ProcessorService  - /fr/service/persistence/crud/testApp1/testForm1/form/form.xhtml - Timing: 212809

2014-09-16 11:13:20,098 WARN  XFormsServer  - unknown id {id: "fb-form-instance"}

2014-09-16 11:13:20,100 WARN  XFormsServer  - unknown id {id: "fr-captcha-group"}

2014-09-16 11:13:20,100 WARN  XFormsServer  - unknown id {id: "fr-captcha-group"}

2014-09-16 11:13:20,100 WARN  XFormsServer  - unknown id {id: "fr-captcha-group"}

2014-09-16 11:13:20,411 INFO  ProcessorService  - /fr/service/persistence/crud/testApp1/testForm1/data/d3448d95a24a3ab147f0383ed4e49f2b101f551dae7a9e7776182dab993cc03d/data.xml - Received request

2014-09-16 11:13:20,430 INFO  ProcessorService  - /fr/service/mysql/crud/testApp1/testForm1/data/d3448d95a24a3ab147f0383ed4e49f2b101f551dae7a9e7776182dab993cc03d/data.xml - Received request

2014-09-16 11:16:49,281 INFO  ProcessorService  - /fr/service/persistence/form/testApp1/testForm1 - Received request

2014-09-16 11:16:49,299 INFO  ProcessorService  - /fr/service/mysql/form/testApp1/testForm1 - Received request

2014-09-16 11:16:49,356 INFO  ProcessorService  - /fr/service/mysql/form/testApp1/testForm1 - Timing: 57

2014-09-16 11:16:49,356 INFO  DatabaseContext  - Committing JDBC connection for datasource: jdbc/mysql.

2014-09-16 11:16:49,369 INFO  ProcessorService  - /fr/service/persistence/form/testApp1/testForm1 - Timing: 88

2014-09-16 11:16:49,401 INFO  ProcessorService  - /fr/service/mysql/crud/testApp1/testForm1/data/d3448d95a24a3ab147f0383ed4e49f2b101f551dae7a9e7776182dab993cc03d/data.xml - Timing: 208972

2014-09-16 11:16:49,402 INFO  ProcessorService  - /fr/service/persistence/crud/testApp1/testForm1/data/d3448d95a24a3ab147f0383ed4e49f2b101f551dae7a9e7776182dab993cc03d/data.xml - Timing: 208991

2014-09-16 11:16:50,203 INFO  ProcessorService  - /xforms-server/orbeon-07076216f2b8349ec3b71a29728d4d6d63b98fda.css - Received request

2014-09-16 11:16:50,204 INFO  ProcessorService  - /fr/testApp1/testForm1/edit/d3448d95a24a3ab147f0383ed4e49f2b101f551dae7a9e7776182dab993cc03d - Timing: 424223

We noticed a few open issues related to MySQL and form runner's performance.

https://github.com/orbeon/orbeon-forms/issues/649

https://github.com/orbeon/orbeon-forms/issues/826

https://github.com/orbeon/orbeon-forms/issues/1239

We simply wanted to find out :

1. What are the size constraints we must adhere to? Form XML too big? Too many entries in the orbeon_form_data table?
2. Is there a work around for this problem, until the open issues are fixed?
3. Are there any configuration changes that need to be done to fix this issue?
4. How much data can Orbeon handle? What are the scalability concerns when using MySQL with Orbeon?

The Orbeon piece is extremely critical for our application. But the performance has now taken a major hit. 

Any help is appreciated!

Thanks,

Nupur

[Erik Bruchez]

Nupur,

See Alex's comments on this issue:

https://github.com/orbeon/orbeon-forms/issues/1893

Improving MySQL performance is one of our top issues:

https://github.com/orbeon/orbeon-forms/issues/649

The big challenge is search. Initially, we started implementing relational
supports with Oracle only, and performance was decent. However, with MySQL,
performance is much harder to achieve do to the fact that we are storing XML
data, and that we are not creating a different table per form definition
created.

We are now thinking that we might have to either change the database schema,
or use a search front-end such as Apache Solr. Either way, it's quite a bit
of work.

-Erik

--
View this message in context: http://discuss.orbeon.com/Form-runner-takes-5-mins-to-load-the-summary-page-tp4658967p4658989.html
Sent from the Orbeon Forms community mailing list mailing list archive at Nabble.com.

[Nupur Ghatnekar]

Erik,

As recommended by Alex we are trying out eXist-db. Being a native XML database, its performance will be way better than any relational database (Oracle /MySQL/MSSQL) , is our assumption. Please let us know your thoughts.

In our production environment, the summary page will not be accessed. But, directly the form to be filled will be accessed. The load time for the form is our major concern.

When using MySQL, we used the additional information stored in the row along with the XML. Also, we programmatically added entries in the table. Just wanted to ensure the same is possible with eXist database. 

Thanks for all your help!

Best,

Nupur

--
You received this message because you are subscribed to a topic in the Google Groups "Orbeon Forms" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orbeon/nD1YuhAZYhc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orbeon+un...@googlegroups.com.
To post to this group, send email to orb...@googlegroups.com.

--

Nupur Ghatnekar

[Alessandro Vernet]

Hi Ali,

Right now, we're doing the final testing for 4.7 and it is released, we'll
do the planning for 4.8, and see if we can schedule it for that release.
We'd like to get this taken care of asap, and we'll do our best, but can't
guarantee that we'll be able to deal with it for the next release.

Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
View this message in context: http://discuss.orbeon.com/Form-runner-takes-5-mins-to-load-the-summary-page-tp4658967p4659011.html