Issue Summary

At present there is a repeatable upgrade task AddIndicesToForeignKeys to create indexes on all the foreign key columns to help improve the database query performance. This tasks gets executed only during the restart after initial installation or when Bamboo is upgraded.

When indexes are absent from foreign key columns, either because of manual intervention or because Bamboo hasn’t been restarted for a long time after its initial setup, this can cause serious performance degradation and make issue resolution more difficult and time-consuming.

Suggestion

To improve how Bamboo handles indexes on foreign keys, consider implementing one or more of the suggestions below.

• Execute the task AddIndicesToForeignKeys every time when Bamboo is restarted to make sure all the indexes are in place.
• Add one more task under <validation> block during startup to perform AddIndicesToForeignKeys task to help create all the indexes especially during the upgrade.
• Add a validation task to know what indexes expected by Bamboo are missing in the database to help diagnose the performance issues