Will having a lot of simple custom fields that are empty/null 95% of the time affect performance?

[Louis Friend]

Traditionally (a few years ago) the standard advice was not to use custom fields if you can avoid it, but I think that advice is not particularly good because I think people use hundreds, if not over a 1000 custom fields in an instance without coming to a crawl.

I know that:

• Type of custom field should make a difference e.g. a small single select puts of a load than a multi-select.
• Restricting custom fields to only certain projects via config scheme should not affect other projects.

I also know that JIRA uses 2 types of "databases" in the background that should directly affect performance.  The SQL creates a new row for every non-empty column of data (so no row if the column is empty I believe).  Similarly the Lucene "cache" should have little performance hit for fields that are not populated - based on how Lucene works.

The SQL db is only, or mostly only, used for single-record view of the issues, whereas the Lucene cache is used almost everywhere else.

So within the project using these mostly-null fields there should be:

1. Very little performance degradation if the fields are not being queried for display. Right?
2. If most of the fields are null then even if the JQL includes a filter clause that restricts on these fields' values then the performance would still be decent because reverse-index (or whatever it is called) would still be small.  Right?
   
   

I understand that there may be other factors that affect performance still but I'd like to know if anyone has real experience with this sort of scenario.

Thanks

[MattS]

I work with a customer who had 1600 custom fields and 1.5M issues. Deleting 400 custom fields made a big difference to the stability of JIRA. Atlassian recommends limiting custom fields to 600 somewhere and I agree.

I think the underlying problem is that the Lucene index using for JQL searches contains a value for every custom field in each Lucene document (JIRA issue). So if you have many issues and many custom fields, you get a huge Lucene index (i.e. 30GB) and writing that index becomes a bottleneck when you have many concurrent users.

So yes, keep the number of custom field down when you have lots of issues in JIRA

[Louis Friend]

Matt, just wanted to thank you for your answer. "Atlassian recommends limiting custom fields to 600 somewhere" - I find that it is difficult to find these nuggets.

[GabrielleJ]

In our JIRA Instance, we have 1,200 Projects, 600,000+ issues, 20,000+ users with hundreds of applications integrated with it (ALMQC, SVN Integration Script, etc... hitting our instance 24/7) and 424 custom fields.

We have peak times that we find JIRA crawling, usually Mondays where several teams create reports (JQL queries and export at the same time). We did not find this before, and we definitely know that the number of custom fields is a factor including the number of issues and project context schemes.

Have you read about the JIRA Five Secrets to performance? http://blogs.atlassian.com/2015/05/webinar-recap-five-secrets-jira-performance-scale/

[Louis Friend]

@Gabrielle Bautista [ACP-JA] Thanks for the response - it is good to get empirical /real-world evidence of what is happening. When you say "We did not find this before", what do you mean by "before" [what change]? On real projects (with real users) it is difficult to add a large amount of custom fields and then remove them because it "could" be a problem. In the real world, if someone (admin) creates a large amount of custom fields, it is because there is a real need to capture that information, not just for kicks. So, alternatives include doing custom scripting on less fields whereby the field holds complex information - but that does not mean that is not slow too. I did previously look at "JIRA Five Secrets to performance" and I did again after you mention it. It is a good article and their Conclusions regarding custom fields are worth repeating/quoting: * the number of projects has highest impact on the actions related to project count, i.e view all issues, search with JQL, create issue, browse projects * the number of projects seems to not have a negative effect on actions independent on project count and performed in the context of single project, such as open dashboard, view issue, edit Issue, project summary, browse boards However, based on watching the video and entire article, those conclusions still seem somewhat vague. I can't blame Atlassian for not giving more in-depth guidance on performance results because of so many factors involved but would like some discussion regarding internals and therefore how it _should_ behave.

[GabrielleJ]

Before when our project count,issues count and custom fields count are smaller (around 20% smaller). I can tell from the logs on what's going on from the slow loading queries to JQL statements. We really want to limit the number of custom fields. Note that these 1,200 Projects that I have only share 3-5 shared schemes. They can re-use an existing scheme only and fit their needs to those schemes. Yes it's counter productive and defies the convention of "the tool must fit your needs not the other way around". But this is the best way we think of in providing JIRA-as-a-Service model. The ScriptedFields are huge hogs too btw.