Bug 1049238
Summary: | Optimize DDL scripts for production use (indexes, ...) | ||||||
---|---|---|---|---|---|---|---|
Product: | [Retired] JBoss BPMS Platform 6 | Reporter: | Jiri Svitak <jsvitak> | ||||
Component: | jBPM Core | Assignee: | David Gutierrez <dgutierr> | ||||
Status: | CLOSED EOL | QA Contact: | Tibor Zimanyi <tzimanyi> | ||||
Severity: | urgent | Docs Contact: | Dawn Eisner <deisner> | ||||
Priority: | high | ||||||
Version: | 6.0.0 | CC: | abaxter, alazarot, bpms-support, dgutierr, hfuruich, ksuzumur, kverlaen, lpetrovi, lpierson, mbaluch, mczernek, mrobson, rrajasek, tkobayas, tzimanyi | ||||
Target Milestone: | ER4 | ||||||
Target Release: | 6.2.0 | ||||||
Hardware: | Unspecified | ||||||
OS: | Unspecified | ||||||
Whiteboard: | |||||||
Fixed In Version: | Doc Type: | Bug Fix | |||||
Doc Text: |
When load testing Timer processes with PerProcessInstance strategy and multiple client threads, deadlock is detected. This is caused by the lock on event types table that is deleted when timer fires. This completes the process and causes a cascade deletion of the process instance info, thus deleting the event types for that instance and causing a deadlock.
There are no workarounds for this issue at this stage.
|
Story Points: | --- | ||||
Clone Of: | Environment: | ||||||
Last Closed: | 2020-03-27 20:09:38 UTC | Type: | Bug | ||||
Regression: | --- | Mount Type: | --- | ||||
Documentation: | --- | CRM: | |||||
Verified Versions: | Category: | --- | |||||
oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |||||
Cloudforms Team: | --- | Target Upstream Version: | |||||
Embargoed: | |||||||
Bug Depends On: | 1059248 | ||||||
Bug Blocks: | 1179269 | ||||||
Attachments: |
|
Description
Jiri Svitak
2014-01-07 09:25:45 UTC
This feature was also requested by a customer, so I am adding a link to the customer portal and setting a higher severity. Community ddls, which were taken and added to product, are not optimized with necessary indexes. However product ddl scripts should be already optimized for production use. DLL scripts are currently auto-generated. Manually introducing improvements is a risk as it's very hard to test this (in the given time frame) for all supported databases. Could we at least provide guidance? For example: "If you are constantly running X task query, then you would benefit most from creating Y indexes" *** Bug 1147801 has been marked as a duplicate of this bug. *** Here is a customer's suggestion for performance improvement which was tested with Oracle. create index I18NText_TASK_SUBJECTS_ID on I18NText(TASK_SUBJECTS_ID); create index I18NText_TASK_NAMES_ID on I18NText(TASK_NAMES_ID); create index I18NText_TASK_DESCRIPTIONS_ID on I18NText(TASK_DESCRIPTIONS_ID); create index PA_PotOwners_TASK_ID on PeopleAssignments_PotOwners(TASK_ID); (from BZ1155855) customer improvement (CNAMTS with GSI SOPRA) please add also CREATE INDEX IDX_ATTACHMENT_1 ON ATTACHMENT(TASKDATA_ATTACHMENTS_ID); CREATE INDEX IDX_I18NTEXT_1 ON I18NTEXT(NOTIFICATION_DOCUMENTATION_ID); It will be also necessary to add indexes for the following tables: processinstancelog & bamtasksummary. All their columns need to be indexed not only the primary/foreign keys. For more details see: https://bugzilla.redhat.com/show_bug.cgi?id=1159764 Hisao says [1] that official Oracle recommendation is to create indexes for all foreign keys to prevent deadlocks [2]. [1] https://bugzilla.redhat.com/show_bug.cgi?id=1181377#c2 [2] https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754 It looks like this is a must have for production use - Increasing Severity to maximum. Adding index generation for all cases reported here to generated dlls. master: http://github.com/droolsjbpm/jbpm/commit/6319afe3e 6.2.x: http://github.com/droolsjbpm/jbpm/commit/a554e8675 Note that I didn't add index generation for any possible FK as this might also have a negative impact on performance, indexes were only added for cases where it was reported they helped solve a particular issue. Please do report issues (as I only was able to check the syntax on a few dbs, hopefully it will work for the others as well). Added indexes for all foreign keys as requested. master: http://github.com/droolsjbpm/jbpm/commit/02a06d79b 6.2.x: http://github.com/droolsjbpm/jbpm/commit/5157791ad Created attachment 993676 [details]
oracle.log
create index IDX_NotEmail_Header ON Notification_email_header(emailHeaders_id)
SQL Error: ORA-01408: such column list already indexed
The same error I found with DB2 database: create index IDX_NotEmail_Header ON Notification_email_header(emailHeaders_id) Query 110 of 164, Rows read: 0, Elapsed time (seconds) - Total: 0.288, SQL query: 0.288, Reading results: 0 Warning: DB2 SQL Warning: SQLCODE=605, SQLSTATE=01550, SQLERRMC=DBALLO00.UK_F30FE3446CEA0510, DRIVER=4.13.80 SQLState: 01550 ErrorCode: 605 DDLs for other databases execute well. Returning back for fix - for DB2 and Oracle. Jiri, could you please verify if removing that line (and that line only) makes the DDLs pass succesfully? Verified it's only this one line change on oracle, so assuming it's the same on db2 as well. master: https://github.com/droolsjbpm/jbpm/commit/aeff4be67 6.2.x: https://github.com/droolsjbpm/jbpm/commit/c538b502a The dashbuilder error only occurs when the script is executed manually which is not typically the case since the app. runs the script automatically on start up. It was caused by a "delimiter" statement syntax error. The fix has been pushed to master and backported to 6.2.x as requested. (master) https://github.com/droolsjbpm/dashboard-builder/commit/bf4b110bdde72ff03057c1fa39332c2660b6fd4e (6.2.x) https://github.com/droolsjbpm/dashboard-builder/commit/65a7a7015aaae179916ae36aa645d2055973ca3f I've manually executed the latest version of the script in GitHub on MySQL and it works. https://github.com/droolsjbpm/dashboard-builder/blob/master/modules/dashboard-webapp/src/main/webapp/WEB-INF/etc/sql/1-create-mysql.sql @Jiri Can you please double check you are executing the right version of the script? (In reply to David Gutierrez from comment #27) > @Jiri Can you please double check you are executing the right version of the > script? Jiri is no longer with Red Hat. Adding NEEDINFO on Tibor. From my testing MySQL dashbuilder script should be changed so DELIMITER commands are alone on single line. There is this line in the script: // DELIMITER ; which causes the script to fail in my case. But I think it depends on the version of MySQL interpreter that is used to run the script. If I change it to this // DELIMITER ; , the script runs fine. We should let it be like this, because I think this is the most compatible way how to write it. Please correct me if I'm wrong. One other note. Using DELIMITER in the script makes this script not testable without MySQL interpreter, because DELIMITER is a command of this interpreter. Maybe there is a workaround so we can create procedures and functions without it, but I don't know about any so please, if you know about some, let me know and update scripts. Other scripts are fine. I tested them and also SQL Server scripts pass (you only need to fill schema name in quartz script instead of 'enter_db_name_here'). I forgot - I tested scripts from 6.1.3.CR2 supplementary tools zip bundle. Fixed MySQL scripts as suggested. (master) https://github.com/droolsjbpm/dashboard-builder/commit/80cf67d0d41ba2c620963eaa8dadae517725766a (6.3.x) https://github.com/droolsjbpm/dashboard-builder/commit/dac039a25fb4c0336412e10872f7c9762f391be2 Tested against MySQL 5.5, both manually and from application startup. Verified in 6.2.0.ER5. I executed the scripts through Squirrel and MySql console + started Dashbuilder using Sybase DB. |