I was thinking about bug https://bugzilla.redhat.com/show_bug.cgi?id=1044515 and realized it might be good idea to add all necessary indexes to current database ddl scripts. Like for example in the bug above where an index on column instanceid of table eventtypes can prevent a deadlock on Oracle: CREATE INDEX IDX_EVENTTYPES ON EVENTTYPES (INSTANCEID); What do you think?
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.