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

Question

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:

* Very little performance degradation if the fields are not being queried for display. Right?
* 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

Louis Friend · Answer

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.

MattS · Answer

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

unknown · Answer

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.