|
I want foreign keys mainly to ensure referential integrity of the database. Additionally, I want to have an up-to-date documentation about the JIRA domain model in the database. Visualizing the domain model without foreign keys results in 72 unrelated tables floating in the air.
I want indices to speed up JIRA by avoiding full table scans on queries. I've received complaints about JIRA being too slow at the moment. The application server CPU usage and the database CPU usage are constantly near zero. Analytics showed many SQL queries doing full table scans, even the third most used SQL query was doing full table scan on jiraissues-table. This is not acceptable. I could add indexes to fix individual selects but I'd rather see JIRA adding indices automatically. > I want foreign keys mainly to ensure referential integrity of the database. Additionally, I want to have an up-to-date documentation about the JIRA domain model in the database. Visualizing the domain model without foreign keys results in 72 unrelated tables floating in the air.
The JIRA data model is defined in WEB-INF/classes/entitydefs/entitymodel.xml. This is where the tables (entities) and relations are defined., eg: <entity entity-name="OSUser" table-name="userbase" package-name=""> The only relations not captured in entitymodel.xml are the M-N relations, in the join tables nodeassociation and propertyentry. > I want indices to speed up JIRA by avoiding full table scans on queries. I've received complaints about JIRA being too slow at the moment. The application server CPU usage and the database CPU usage are constantly near zero. Analytics showed many SQL queries doing full table scans, even the third most used SQL query was doing full table scan on jiraissues-table. This is not acceptable. I could add indexes to fix individual selects but I'd rather see JIRA adding indices automatically. JIRA 3 does add indexes automatically (also defined in entitymodel.xml), but only for new databases. You could get them added by creating an XML backup, pointing JIRA at a clean database (where the schema will be created with indexes), and importing the XML backup. Alternatively, indexes can be created manually. See: http://www.atlassian.com/software/jira/docs/latest/indexing.html If you are having performance problems, there are a lot of potential causes. The following page lists some common ones: http://www.atlassian.com/software/jira/docs/latest/performance.html Having the relations defined in the entityengine.xml doesn't ensure referential integrity in the database. It would be really awesome to have a consistent data model in the database and not only on the application level.
Thanks for pointing me to the indexing-document. I hope it solves the performance issues. Still, autocreation of indices and foreign keys would be a nice feature, if it was fixed to work. > It would be really awesome to have a consistent data model in the database and not only on the application level.
It would, but we are quite far from having it at the moment; not only because of small entitymodel.xml problems like Still, thanks for raising the issue - it's a good thing for us to keep in mind and aim for. It appears that there are no foreign keys also in Postgresql. Is this also due to
Could you also clarify the above comment. Are all database modifications wrapped in transactions ? > It appears that there are no foreign keys also in Postgresql. Is this also due to
Not specifically. > Could you also clarify the above comment. Are all database modifications wrapped in transactions ? No. For some operations like workflow migrations we manually start a transaction. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
New Feature
Open
Major
Automatic creation of foreign keys and indices fails on Oracle database
I'm not sure what the problem is here? We specifically don't turn that option on for that exact reason.
Can you outline the problems that you get with it turned off, or let us know why you can't run JIRA without them turned on?